Excel Custom Views Tricks

We took a look at Excel Custom Views last week, and used them to filter data, and hide or show columns. The Custom Views make it easier to print weekly reports, with different layouts for each version – all in a single file, with no macros.

When you create a Custom View, it stores the current settings for all the sheets in the workbook. You could use that to set up multiple sheets for monthly reports, and store a default Custom View, with all the sheets set with no filters applied, and all rows and columns visible.

Here are a few more tricks for you Custom Views sorcery kit. And remember, Custom Views don't work if there are named Excel Tables in the workbook.

Set the Print Area

When you create a Custom View, you can include the print settings, such as Print Area. In this PrintABCD Custom View, only cells A1:D9 are included in the Print Area.

On the same worksheet, if I select the All_Columns Custom View, all the columns would print.

Custom Headers and Footers

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.

Delete and Replace a Custom View

After reading last week's article on Custom Views, Don emailed me, to ask if he could change the Custom View at month end, from the July to the August sheet. 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 :

Apply the Custom View

Make the filter, layout, and print setup changes in the workbook

Create a new Custom View, using the same name as the old Custom View

When prompted, click Yes, to delete the old Custom View and replace it.

For example, here's how Don could set up the August sheet, when he's ready to switch:

Apply the Custom View to the July sheet, so all the filters, hidden columns and print settings are applied.

Copy the July sheet

(optional) On the original July sheet, choose the default Custom View, to remove any filters, etc. These settings will be stored when you do the next step.

On the July (2) sheet, create a new Custom View, with the same name as the old Custom View

When prompted, click Yes, to delete the old Custom View and replace it.

Rename the copied sheet, as August (you could do this before creating the Custom View, if you prefer)

Tweak the Custom View Settings

Despite the fact that there's no Edit button in the Custom View dialog box, you can do a bit of tweaking.

To do this, you'll need to install a copy of Jan Karel Pieterse's awesome Name Manager add-in. Of course, you should install this add-in, even if you don't want to tweak the Custom View settings!

After you install the Name Manager add-in, go to the Formula tab on the Ribbon, and click the Name Manager command, at the far right. Don't click the built-in Excel Name Manager – this trick doesn't work there.

In the Name Manager, you can see some of the Custom View settings – they have wvu in their name. Here's how to tweak a setting:

Click on a name, and you can see its definition in the Edit box, below the list.

In the Edit box, change the cell references. Originally, the Print Area for the PrintABCD Custom View was set for A1:D9, so I can change that to A1:D13.

Click the green plus sign at the top of the Name Manager window, and click Yes, to confirm the change.

Any Other Custom Views Tricks?

I hope these tricks inspire you to try Custom Views for some of your reporting. Do you have any other Custom Views tricks that you can share in the comments?

I have several custom views of a single worksheet, depending on the purposes for which I send out a report. The headers/footers are basically the same for all views, but I need to add a variable header or footer that shows the view/purpose of each report.

I have looked into Excel VBA but I can find no reference to which view is currently ACTIVE. This makes it clear that the worksheet cannot identify which view is being currently printed. Is there no way of identifying it – perhaps by examining and editing the XML structure of the workbook?

There is a way (not a pretty one though) via VBA using customviews.item(“viewname”).name. One way would be to have a list box where you select which custom view to display and a simple macro to call it. The benefit would be to have a macro that rolls through all your custom views (and prints the view for example), saving you a few clicks on the mouse.

I have a question I can not seem to find the answer to. I might not be using the right terminology. I have over 150 tabs in my worksheet. I am trying to find a way to custom view multiple tabs of the same type of tab. EX. 4 companies and each company has many tabs. I have company each company and their corresponding tabs color coded to the same color. Each company has a P & L sheet. Is there any way I can create a custom view to only see the 4 P & L sheets while hiding the other tabs. Any advice or help would be greatly appreciated. Thank you