How to Create and Manage Custom Views in Microsoft Excel

When working with large, multifaceted spreadsheets in Microsoft Excel, you frequently need to present different subsets of information to different audiences. For example, an executive review might require hiding detailed cost columns and filtering for quarterly summaries, whereas an audit view requires every column and formula to be fully displayed. Rather than manually hiding rows, applying individual filters, and readjusting print margins every time you change tasks, you can save and instantly switch between workbook configurations using Custom Views.

What Are Custom Views in Microsoft Excel?

The Custom Views tool allows you to take a complete snapshot of your current worksheet display and print environment under a custom name. When you switch to a saved view, Excel automatically re-establishes that exact visual arrangement. A single custom view preserves:

• Row and Column Visibility: Which rows or columns are currently hidden or unhidden.
• Filter Criteria: Active AutoFilter and Advanced Filter parameters applied across headers.
• Window Settings: Split panes, frozen panes, zoom percentages, and active cell selections.
• Print Settings: Print areas, page orientation (portrait vs landscape), margins, headers, footers, and page breaks.

Important Prerequisites: Excel Tables Limitation

Before implementing Custom Views, be aware of one crucial architectural limitation: Custom Views are disabled if your workbook contains an official Excel Table (created via Insert > Table or Ctrl + T). If the Custom Views button on your ribbon appears greyed out, you must convert your formatted tables back into standard normal ranges:

1. Click anywhere inside the Excel Table.
2. Navigate to the Table Design tab on the Ribbon.
3. In the Tools group, click Convert to Range.
4. Confirm the prompt by clicking Yes.

Your cell formatting, fonts, and data remain completely intact, and the Custom Views feature will immediately become available.

How to Create a Custom View in Microsoft Excel

Setting up your views requires configuring the sheet first, then saving the layout state:

1. Format your worksheet exactly as you want it saved. Filter specific columns, hide sensitive salary or operational rows, adjust zoom levels, and configure your desired print area under the Page Layout tab.
2. Go to the View tab on the Excel Ribbon.
3. In the Workbook Views group, click Custom Views.
4. In the Custom Views dialogue box, click the Add button.
5. Enter a descriptive name for your layout (for example, Executive Summary or Internal Audit View).
6. Under the Include in view options, verify that both Print settings and Hidden rows, columns, and filter settings are ticked.
7. Click OK to save the configuration.

It is best practice to always save an initial baseline view called Show All before hiding any rows or applying filters. This allows you to reset the worksheet to its original state with a single click.

How to Switch Between Saved Custom Views

Once you have configured two or more custom views, toggling between them is effortless:

1. Navigate to the View tab and click Custom Views.
2. Highlight the view you wish to apply from the list.
3. Click Show.

Excel instantly reapplies the saved visibility rules, zoom level, print dimensions, and filter conditions across your workbook.

How to Delete or Update an Existing Custom View

If your reporting requirements change or a saved view becomes obsolete:

• Updating a View: Custom Views cannot be renamed or edited in place. To update an existing view, apply it, make your changes on the sheet, delete the old view in the Custom Views dialogue, and re-add it using the same name.
• Deleting a View: Open View > Custom Views, highlight the unwanted view, click Delete, and confirm your choice.

Adding Custom Views to the Quick Access Toolbar for One-Click Switching

For high-frequency workflows, you can place a Custom Views dropdown directly onto your Quick Access Toolbar:

1. Click the small down-arrow on the Quick Access Toolbar and select More Commands.
2. In the Choose commands from dropdown, select Commands Not in the Ribbon.
3. Scroll down and select Custom Views (represented with an interactive dropdown box).
4. Click Add >>, then click OK.

You now have an instant dropdown selector located at the top of your Excel window to jump between custom views without ever opening a dialogue box.