Excel Custom Views Tricks

Excel Custom Views Tricks

iconcustomviews 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. Here are a few Custom Views tricks that you can use. But remember, Custom Views don’t work if there are named Excel Tables in the workbook


Store Worksheet Settings

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.

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.

CustomViewPrint02

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

CustomViewPrint01

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.

CustomViewPrint03

On the same worksheet, the Print_Paper Custom View has nothing in the Left Header area.

CustomViewPrint06

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

CustomViewPrint04

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

CustomViewPrint05

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

JKP_NameManager

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.

CustomViewPrint07

  • 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?
__________