Save Excel Settings in Custom Views

To save time when printing reports, store filter and print setting in Custom Views. Custom Views make it easier to print weekly reports, with different layouts for each version – all in a single file, with no macros. Unless, of course, your workbook contains a Named Excel table – you can’t use Custom Views in those files.

Introduction

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.

Store Settings in Custom Views

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:

• Filters applied to lists on each worksheet (not named Excel tables)
• Hidden rows and columns
• Print settings, such as Print area, headers and footers, orientation

Create Custom Views

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:

• Customer version, with pricing columns are hidden.
• Supplier report, filtered for a specific vendor, with hidden columns.
• Internal reports, with most columns and rows visible.

When creating Custom Views:

• First, create a default Custom view, with the layout settings that you use most often
• Next, create other Custom Views, with different filters, hidden row/columns or print settings.

Create 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

• In the workbook, go to each worksheet, and set it up the way you use that sheet most often. For example:
  • Unhide all rows and columns
  • Clear all filters
  • Enter header and footer text
  • Set the Print Area
• When all the sheets are ready, click the View tab on the Excel Ribbon
• Click Custom Views

• In the Custom Views dialog box click Add

• Type a short dexcriptive name for the Custom View
• Add check marks to the items that you want to include in the view:
  • Print settings
  • Hidden rows, columns, and filter settings
• Click OK, to create the Custom View

Set Up Additional Custom Views

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.

Show a Custom View

After you create one or more Custom Views, here are 2 ways to show them

1. Custom Views window

2. Custom Views drop down list

Custom Views Window

The default way to show a Custom View is with the Custom Views window:

• Click the View tab on the Excel Ribbon
• Click Custom Views
• In the list of Custom Views, click the name of the view that you want to show
• Click the Show button

Custom Views Drop Down List

To make it easy to switch between Custom Views, you can add a drop-down list of Custom Views to the Excel Ribbon.

• Right-click on an empty part of the Excel Ribbon, and click Customize the Ribbon

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

• 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

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

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

Click the drop down arrow, then select one of the Custom Views, to see that layout.

Delete and Replace a Custom View

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.

Show Current Custom View Name

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.

List the Custom Views

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.

• On a sheet named CustomViews:
• Type headings, ID and Custom View, in cells E1 and F1
• Type the numbers 1 to 5, in E2 to E6
• Type the Custom View names in cells F2 to F6
• Select a cell anywhere in the list
• On the Data tab of the Excel Ribbon, click the Filter button, to add drop down arrows in the heading row
• NOTE: Do NOT format this list as a named Excel table.

Add Formulas to get Custom View Name

Next, you'll add 2 formulas to the CustomViews worksheet, and 1 formula on the SalesData sheet:

Find the ID Number

• In cell B1, enter a SUBTOTAL formula, to return the highest number, for the visible rows:
  • =SUBTOTAL(4,$E$2:$E$6)
  • The first argument is 4, which is the MAX function.
  • 

Get the Custom View Name

• Next, in cell C1, enter an INDEX/MATCH formula, to return the Custom View name for the number in cell B1
  • =INDEX($F$2:$F$6,MATCH(B1,$E$2:$E$6,0))

Test the Formulas

To see how the formulas work, follow these steps:

• Filter the list of Custom View names, so only the Central name is showing
• The SUBTOTAL formula in cell B1 returns the ID number 4, because that is the highest number in the visible rows.
• In cell C1, the INDEX/MATCH formula returns the Custom View name for that IT, Central.

Link to the Name Formula Cell

The final formula goes on the SalesData sheet:

• Enter this formula in cell H1, on the SalesData sheet, to link to the INDEX/MATCH cell:
  • =CustomViews!C1

Set Up the Custom Views

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:

• Go to the CustomViews sheet
• In column F, clear any existing filters
• Apply a filter to choose the name for the Custom View that you're setting up

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.

Get the Sample File

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.

More Tutorials

Named Excel Tables

Workbook Tips

Worksheet Tips

AutoFilter Basics

AutoFilter Tips