Quick Reports With Excel Custom Views
In an Excel file, you might need to change the layout, before you print a report. For example,
- in a customer report, the pricing columns are hidden.
- for a supplier report, you filter for a specific product, and hide some columns.
- for your internal reports, all the columns and rows are visible.
To quickly show the different layouts, without any programming, you can create Custom Views, and select one from a drop down list.
NOTE: In Excel 2007 and Excel 2010, the Custom View options are not available, if there is a named Excel Table, anywhere in the workbook.
Set Up a Default Custom View
If you’re creating Custom Views, you should create a default Custom View first, with the layout that you use most often. In this example, the default worksheet layout has all the columns and rows visible.
To create a Custom View
- On the Excel Ribbon, click the View tab
- Click Custom Views
- In the Custom Views dialog box click Add
- Type a name for the Custom View, then click OK
Set Up the Alternate Custom Views
After you set up the default worksheet Custom View, change the layout for the next Custom View. In this example columns C:E are hidden.
- Click the Custom Views command again, and add a Custom View for this layout – Print_Hidden.
Create as many custom views as you need. This example has a filter applied for paper products, and the Custom View will include those filter settings.
Add a Custom Views List to the Ribbon
To make it easy to switch between Custom Views, you can add a drop-down list of Custom Views to the Excel Ribbon. (If you're using Excel 2007, you can add this drop-down list to the Quick Access Toolbar, instead of the Ribbon.)
- In Excel 2010, right-click the Ribbon, and click Customize the Ribbon
- In the Excel Options window, at the right, click the + to the left of the View tab.
- Click Workbook Views, to select that Group, and click the New Group button. That will add a new Group below Workbook Views.
- With the new Group selected, click Rename
- Type a name For the new group, and click OK – in this example the new group is called MY VIEWS
- With the MY VIEWS group selected, click the drop down arrow for Choose Commands From
- Click on Commands Not in the Ribbon
- Scroll down and click on Custom Views, then click Add, to move that command to the MY VIEWS group.
- Click OK, to close the Excel Options window.
Test the Custom Views
On the Excel Ribbon’s View tab, you'll see the Custom Views drop down list. Select one of the Custom Views to see that layout.
No Excel Tables With Custom Views
Remember though – if you have a named Excel table in your workbook – on any sheet – the Custom Views options will not be available. Strange, but true.