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.

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.

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?

__________

8 comments to Excel Custom Views Tricks

  • JP

    I borrowed some code from your site and wrote a procedure that creates a Custom View for each unique value in column A. Check it out at http://www.codeforexcelandoutlook.com/blog/2011/06/simulate-slicers-in-excel-2003/

  • MK

    Is there any way to store even the font and highlight settings in the custom view?

  • Doug

    Is it possible to use a formula to have the name of the active "custom view" appear in a cell? That would be helpful, especially when printing the worksheet. THANK YOU !!

    • Brian Hershman

      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?

  • gary

    I second Doug's request; any answers??

    "Is it possible to use a formula to have the name of the active "custom view" appear in a cell? That would be helpful, especially when printing the worksheet. THANK YOU !!"

  • Peter

    I "treble" both Doug and Gary's request - take it that it's not possible because no solution?

  • Anders

    1. If I use filter the selection disappear when I use Custom Views.
    2. If I open a "New window" with the same spread sheet. I can't have 2 differnt Custom Views in each window.

    Any suggestions to get around this?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>