Quickly Run Reports with Different Settings using Custom Views

by Avantix Learning | Updated May 17, 2015

Applies to: Microsoft Excel 2010 and 2013

You can display several variations of the same Excel worksheet with different print settings, filters and hidden columns or rows using the powerful Custom Views command. This command is particularly useful if you are running reports for multiple audiences.

Recommended articles: 10 Timesaving Excel Selection Shortcuts and No Mouse? Using Keyboard Only Navigation in Microsoft Office

What can you do with custom views?

Custom views can be used to display variations of a worksheet with different:

  • Print settings including print area, margins, sheet settings and headers and footers
  • Filters
  • Hidden rows and columns
  • Column widths and row heights
  • Window settings

You can create multiple custom views in a worksheet but you can only apply a custom view to the worksheet that was active when you created the custom view.

With custom views, you could display variations of an Excel worksheet for multiple audiences such as:

  • A report for managers with a set of filtered records
  • A report for a client with hidden columns and filtered records
  • A report for a different client with different hidden columns and different filtered records
  • A default report with no filtering and no hidden columns or rows

You could also create custom views for different departments in your own organization to display data relevant to each department with different print areas.

Users can display different custom views using the Ribbon without using any VBA (Visual Basic for Applications) programming.

The Custom Views command appears on the View tab on the Ribbon:

Custom Views on the Excel Ribbon.

Issues and limitations

Custom views have a few limitations:

  • They cannot be used in a workbook where named tables (not pivot tables) have been created (using Insert Table or Format as Table).
  • Users may not be able to access custom views if protection has been applied in a worksheet used in a custom view.

Creating a default custom view

It’s a good idea to create a default custom view first with the settings you use most often. This could be a default view with no hidden columns or rows and no filtering.

To create a default custom view:

  1. Set up the worksheet with the print settings, filtering and hidden rows and/or columns you prefer.
  2. Click the View tab in the Ribbon.
  3. Click Custom Views. The Custom Views dialog box appears.
  4. Click Add.
  5. Enter a name for the view such as Invoices_Default. You may use spaces. It’s a good idea to include the name of the worksheet as well.
  6. Check the options you wish to include in the view.
  7. Click OK.

Create custom default view dialog.

Creating alternate custom views

Once you have created the default custom view, you can create multiple alternate views:

  1. Set up the worksheet with the print settings, filtering and hidden rows and/or columns for the view.
  2. Click the View tab in the Ribbon.
  3. Click Custom Views. The Custom Views dialog box appears.
  4. Click Add.
  5. Enter a name for the view such as Invoices_View1 or Invoices_Toronto.
  6. Check the options you wish to include in the view.
  7. Click OK.
  8. Repeat the process for any other views.

Adding a drop-down list of views by customizing the Ribbon

To make it easy to display different custom views, you can add a drop-down list of custom views to the Ribbon:

  1. Click the File tab in the Ribbon.
  2. Click Options.
  3. In the categories on the left, click Customize Ribbon.
  4. On the right side of the window, click on the plus sign (+) to the left of the View tab.
  5. Click Workbook Views to select that Group and then click the New Group button.
  6. With the New Group selected, click Rename.
  7. Type a name for the new group and click OK. For example, enter My Custom Views.
  8. With the My Custom Views group selected, click on the drop-down arrow for Choose Commands From.
  9. Select Commands Not in the Ribbon under Choose Commands from.
  10. Scroll down and click Custom Views and then click Add to move that command to the My Custom Views group.
  11. Click OK.

Customize the Ribbon to display drop-down list of custom views.

Displaying custom views

To display custom views if you have not customized the Ribbon:

  1. Click the View tab in the Ribbon.
  2. Click Custom Views.
  3. Click the view you wish to apply.
  4. Click Show.

To display different custom views if you have customized the Ribbon and added a drop-down menu:

  1. Click the View tab in the Ribbon.
  2. Select a custom view from the Custom Views drop-down list you added if you customized the Ribbon.

Custom views drop-down menu on the Ribbon.

Editing a custom view

To edit a custom view:

  1. Apply the custom view.
  2. Make any changes to the filters, layout and print settings in the worksheet.
  3. Create a new custom view and name it the same name as the one you want to replace.
  4. Click OK. A dialog appears.
  5. Click Yes to replace the old custom view with the current view.

Deleting a custom view

To delete a custom view:

  1. Click the View tab in the Ribbon.
  2. Click Custom Views.
  3. Click the View you wish to delete.
  4. Click Delete. A dialog box appears.
  5. Click Yes to delete the view.

Custom views are another one of those overlooked tools that can save you a lot of time in Excel.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

To request this page in an alternate format, contact us.

Recommended Microsoft Excel training courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel:  Visual Basic for Applications (VBA) | Introduction

VIEW MORE COURSES >

Copyright 2019 Avantix® Learning Inc.

You may also like

How to Create 3D References in Microsoft Excel

You can use 3D references in Excel formulas to calculate across multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Pin It on Pinterest

Share This