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.

CustomizeRibbon05

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

CustomViews01

  • In the Custom Views dialog box click Add

CustomViews02

  • Type a name for the Custom View, then click OK

CustomViews03

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.

CustomViews04

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.

CustomViews05

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

CustomizeRibbon01

  • 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.

CustomizeRibbon02

  • 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

CustomizeRibbon03   

  • 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.

CustomizeRibbon04

  • 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.

CustomizeRibbon05

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.

CustomViews06  

____________