Custom Views

In this chapter, you’ll learn about a powerful automation tool that can instantly transform workbooks. The overlooked Custom Views feature can vastly improve the accessibility of spreadsheets. Among other things, Custom Views can hide and unhide rows, columns, and worksheets. The Custom Views feature can also apply print settings, apply filter settings, adjust the positions of windows in Excel, and more. The Table and, sometimes, Power Query features disable the Custom Views feature, so I’ll show you how to identify when that situation has arisen.

We will cover the following main topics in this chapter:

• Introducing Custom Views
• Multipurpose worksheets
• Creating a Custom Views Quick Access Toolbar dropdown
• Hiding and unhiding worksheets
• Automating filtering
• Custom Views conflicts

By the end of this chapter, you’ll be able to automate repetitive tasks for yourself and others. You may be able to minimize ongoing spreadsheet...

Technical requirements

The Custom Views feature is available in all versions of Excel. The workbook that I used in this chapter can be downloaded from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter08.

Introducing Custom Views

Custom Views captures and applies visual aspects of Excel workbooks and worksheets. To be clear, Custom Views does not change the contents of worksheets cells. In that regard, I discussed Excel’s Scenario Manager feature in Chapter 6, What-If Analysis, which does enable you to swap out different sets of inputs. Custom Views enables you to manage the following aspects of an Excel workbook:

• Worksheet and feature settings:
  • Hidden or visible status of worksheets
  • Hidden or visible status of rows and columns
  • Freeze panes settings
  • Cell selection and cursor positioning
  • Filter Buttons and settings
  • Page Setup settings, including print ranges and page orientation
  • Displaying page breaks
  • Displaying worksheet columns from right to left (instead of left to right)
• Visual settings:
  • Display modes, including Normal, Page Break Preview, and Page Layout
  • Window sizes and positions
  • Worksheet zoom settings
  • Row and column headings
  • Displaying formulas instead of calculated results...

Creating multipurpose worksheets

You’ve likely reached the tipping point in an Excel worksheet where so much data accumulates that you think, Wow, I should create a summary of this information. And away you go, adding another worksheet to your workbook. This, in turn, raises the bar on spreadsheet maintenance, as adding more information to the detailed worksheet can then require making continuous changes to keep the summary worksheet up to date. Instead of adding more worksheets, you may be able to use the Custom Views feature to have both detail and summary views on a single worksheet.

The Figure 8.1 shows a hypothetical financial report that we’ll use as the basis for our Custom Views. We’ll be able to toggle the worksheet between views to see details for the entire year and activity by quarter, as well as a third executive summary view, all from a single worksheet:

Figure 8.1 – Example data for creating a Custom View

Creating...

Creating a Custom Views Quick Access Toolbar shortcut

It’s easy to forget or be unaware that Custom Views have been created in a workbook. Adding a Custom Views drop-down list to the Quick Access Toolbar for specific workbooks provides instant access and eliminates the need to train users on how to switch views.

Tip

See Chapter 3, Quick Access Toolbar Treasures, if your Quick Access Toolbar isn’t visible in Microsoft 365.

Here’s how to add the Custom Views drop-down list to your Quick Access Toolbar:

1. Carry out the steps in the Creating multipurpose worksheets section of this chapter if you haven’t done so to create at least two Custom Views.
2. Choose File | Options | Quick Access Toolbar to display the Quick Access Toolbar section of the Excel Options dialog box.
3. As shown in Figure 8.7, choose the name of your workbook, such as Chapter 8 – Custom Views, from the Customize Quick Access Toolbar list at the top-right of the Excel...

Hiding and unhiding worksheets

As I mentioned previously, the Custom Views feature captures views for the entire workbook, including the hidden or visible status of each worksheet. First, let’s learn how to manually hide and unhide worksheets, which we’ll then automate with a Custom View.

You can easily hide any number of worksheets:

1. Open the example workbook for this chapter.
2. Select the January worksheet, hold down the Shift key, and click on the Table Feature worksheet.
3. Right-click any worksheet tab and then choose Hide (or choose Home | Format | Hide & Unhide | Hide Sheet).

At this point, only the Summary worksheet should be visible. Excel requires that you have at least one visible worksheet in each workbook.

Tip

If you want to hide an entire workbook but still keep it open in Excel, choose View | Hide. You can then choose View | Unhide to redisplay the workbook.

Historically, unhiding worksheets in Excel has been a tedious...

Automating filtering

The Custom Views feature can apply one or more filters at once, rather than you having to do so individually. First, we’ll briefly look at the Filter command. Many Excel users are familiar with the Sort command, which physically rearranges data within a list. Conversely, the Filter feature displays a subset of information in a list, thus hiding rows with data that doesn’t match the filter.

Let’s filter the list shown in Figure 8.12 for beaches greater than 100 kilometers in length:

1. Activate the Filtering worksheet of this chapter’s example workbook and select any cell within the list.

Tip

There are only a few instances in Excel where it’s necessary to select an entire list before filtering, such as when a list contains blank rows or blank columns. Another instance that comes to mind is when a list contains three or more header rows. In that case, you’ll want to select from the last header row down through...

Applying print settings on demand

By default, Custom Views captures the print settings currently in place for each worksheet included in the view, unless you clear the Print Settings checkbox when creating a view. Let’s create an audit view of a worksheet that will illustrate some additional settings that you can manage by way of the Custom Views feature:

1. Open the example workbook for this chapter and activate the Loan Calculator worksheet.
2. Maximize the worksheet window if necessary so that you can see how Custom Views can resize windows:
   • Excel for Windows: Click the Maximize button, which looks like a square in the top right-hand corner of the title bar.

Tip

If you see two overlapping squares in the top right-hand corner of the title bar – which represent the Restore Down button – then your window is already maximized.

• Excel for macOS: Click the green Enter Full Screen button in the title bar of the window.

3. Choose View...

Updating a Custom View

You cannot edit existing Custom Views that you've created, but you can replace a view using either of these approaches:

• Delete and recreate the view:
  1. Choose View | Custom Views and then click once on the view that you wish to update.
  2. Click the Delete button and then click Yes to confirm that you wish to delete the view.
  3. Create a new version of the view.
• Replace an existing view:
  1. Choose View | Custom Views | Add, enter the exact name of an existing Custom View in the Name field of the Add View dialog box, and then click OK.
  2. Click Yes on the prompt that informs you that the view already exists and asks if you would like to delete it and continue.

Removing all Custom Views from a workbook

Sometimes, you may encounter a workbook that has numerous Custom Views. For instance, the Review | Share Workbook command creates a Custom View for each user that accesses the workbook. Over time, this can result in an overwhelming number of Custom Views...

Custom Views conflicts

After singing the praises of Custom Views all through this chapter, I now have some sad news to deliver. The Custom Views feature is unavailable any time that you use the Table feature, which I sang the praises of in Chapter 7, Automating Tasks with the Table Feature. A conflict can sometimes arise with the Power Query feature, which I will discuss in Chapter 12, Power Query. Older versions of Excel for macOS permitted users to use Custom Views and the Table feature together in the same workbook, but the Windows version never has. This means you will sometimes have to choose between which feature (Custom Views, Table, or Power Query) will offer the biggest most effective automation and data integrity in each a given workbook. In addition, the Review | Protect Sheet and Review | Protect Workbook commands can also sometimes pose conflicts with Custom Views.

Nuance

Power Query only disables Custom Views when results are returned to a Table. This means that...

Summary

In this chapter, you saw the range of features that can be controlled by way of Excel’s Custom Views feature. Instead of creating supplemental worksheets that offer summarized views of detailed worksheets that you must maintain when the detailed worksheets change, you may instead be able to use the Custom Views feature to create multipurpose worksheets.

It's easy to overlook Custom Views that you or others have created. You can make this feature more accessible by adding the Custom Views drop-down menu to the Quick Access Toolbar of any workbook that utilizes the Custom Views feature. Remember, you cannot edit a Custom View, but you can save over an existing view. You can also delete an view and then create a replacement.

You can also spend less time navigating within large workbooks by hiding sheets that aren’t relevant to your current task, and then easily unhide all sheets again with a couple of mouse clicks. You can also assign multiple print ranges...