To save time when printing reports, store filter and print setting in Custom Views. Custom Views are an underrated Excel feature that make it easier to print standard reports, with different layouts for each version – all in a single file, with no macros.
Unfortunately, Custom Views do not work if your workbook contains any Named Excel Tables. This is an serious shortcoming, and I don't think that Microsoft has any plans to fix this situation.
To save time when printing reports, you can save filter and print setting in Custom Views. Unless, of course, your workbook contains a named Excel table – you can’t use Custom Views in those files.
Watch this video to see the steps for setting up Custom Views, switching between them, and creating formulas to show the name. There are written steps below the video.
To quickly show the different layouts, without any programming, you can create Custom View. When you create a Custom View, it stores the current settings for all the sheets in the workbook.
In each Custom View, you can store worksheet settings, such as:
You could use Custom Views to set up multiple sheets for printing your monthly reports. For example, each month you might need to print 3 versions of a sales workbook:
When creating Custom Views:
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.
After you set up the default worksheet Custom View, create additional Custom Views -- as many as you need.
Follow the Default Custom View steps above, but for each additional Custom View, set up the worksheets in a different way, based on your reporting requirements.
For example, in this worksheet, only cells A1:D9 are included in the Print Area, and this Custom View is named PrintABCD
You can also create different Headers and Footers for the Custom Views, and store those in the Print Settings. In the PrintABCD Custom View, the Left Header has the Custom View name.
On the same worksheet, the Print_Paper Custom View has nothing in the Left Header area.
After you create one or more Custom Views, here are 2 ways to show them
The default way to show a Custom View is with the Custom Views window:
To make it easy to switch between Custom Views, you can add a drop-down list of Custom Views to the Excel Ribbon.
On the Excel Ribbon’s View tab, you'll see the Custom Views drop down list.
Click the drop down arrow, then select one of the Custom Views, to see that layout.
Unfortunately, there's no Edit button in the Custom View dialog box, so there's no easy way to change it.
If you want to make significant changes to a Custom View, the only solution I've found is :
After you select a view, there’s no built-in feature that lets you show its name on the worksheet. However, you can use a bit of filtering, and a couple of formulas, to show the name of the selected view.
For example, in the screen shot below, the view named “ALL” is showing, and its name is in cell H1.
The setup steps are shown in the video, at the top of this page, and there are written steps below.
The first step in this technique is to create a list of all the Custom Views in the workbook.
Do this BEFORE you set up the Custom Views. Or, to add this technique later, use the Delete and Replace steps shown above.
Next, you'll add 2 formulas to the CustomViews worksheet, and 1 formula on the SalesData sheet:
To see how the formulas work, follow these steps:
The final formula goes on the SalesData sheet:
Finally, set up the Custom Views that you need, following the steps above -- with one additional step required.
As you set up each Custom View:
That filter will be included in the settings for the Custom View, so its name will appear in cell H1 on the data sheet, when you apply the Custom View.
To see the Custom View example from the video, where the name of the current view is shown in a cell, download the Custon Views sample file. The zipped file is in xlsx format, and does not contain any macros.
Last updated: September 14, 2023 12:38 PM