How to How to Create Custom Views in Excel in Excel - Practical Guide

Custom Views in Excel allow you to save multiple filtered, frozen, and formatted versions of the same worksheet without creating duplicate sheets. This advanced technique enables teams to view data differently—hiding columns, applying filters, and freezing panes—while preserving the original layout. Perfect for sharing tailored reports with different stakeholders from a single data source.

Why This Matters

Custom Views streamline collaboration by letting multiple users access tailored data perspectives without modifying the master dataset. This skill is essential for analysts managing complex reports across different departments or stakeholder requirements.

Prerequisites

•Working knowledge of Excel filtering, sorting, and column hiding
•Understanding of worksheet structure and data organization
•Familiarity with freezing rows/columns (View > Freeze Panes)

Step-by-Step Instructions

Prepare Your Worksheet

Set up your data with filters, hidden columns, frozen panes, and formatting as desired. Your current view will become the basis for the custom view you're about to save.

Access Custom Views Dialog

Click View tab > Custom Views (right side of ribbon). If unavailable, enable it via File > Options > Quick Access Toolbar > choose 'Commands Not in Ribbon' and add Custom Views.

Create New Custom View

In the Custom Views dialog, click Add. Enter a descriptive name (e.g., 'Sales Summary - Finance') and choose whether to include Print Settings and Hidden Rows/Columns, then click OK.

Apply Different View Settings

Modify your worksheet—apply new filters, hide different columns, or adjust freezing—then repeat Steps 2-3 to save this as a second custom view with a different name.

Switch Between Custom Views

Go to View > Custom Views, select your desired view name from the list, and click Show. Excel instantly applies all saved settings (filters, hidden columns, frozen panes, print settings).

Alternative Methods

Use Workbook Themes & Conditional Formatting Instead

If Custom Views aren't available in your Excel version, create separate sheets with identical data but different formatting rules and conditional formatting to simulate multiple views.

Combine with PivotTables for Dynamic Views

Create multiple PivotTables from the same data source, each showing different perspectives, then use slicers to filter across views simultaneously.

Tips & Tricks

✓Use clear, descriptive names for custom views (e.g., 'Executive Dashboard - Q4' instead of 'View 1') to avoid confusion when switching between them.
✓Save your workbook after creating custom views; they are stored within the file and won't transfer between different files.
✓Test each custom view after creation by switching back and forth to ensure all filters, frozen panes, and formatting persist correctly.
✓Custom Views work best with relatively static data; avoid using them on datasets that change structure frequently.

Pro Tips

★Combine Custom Views with Data Validation rules to create a selection dropdown that users click to auto-switch between predefined views.
★Include Print Settings in your custom views to save page setup, margins, and headers/footers unique to each view for direct-to-print workflows.
★Use Custom Views alongside frozen header rows to ensure stakeholders see column/row labels consistently across all saved perspectives.
★Document your custom views in a worksheet note or external guide listing which view is for which audience to streamline team collaboration.

Troubleshooting

Custom Views option is greyed out or unavailable.

Custom Views are disabled in shared/co-authored workbooks. Save the file in single-user mode (Unshare Workbook), create your views, then reshare if needed.

Switching between views is slow or causes display lag.

Close unnecessary workbooks or disable live data connections temporarily. Large filtered datasets slow view switching; consider breaking data into separate sheets if views are complex.

Custom views are lost after saving and reopening the file.

Ensure you saved the file as .xlsx or .xlsm format; .xls legacy format does not fully preserve custom views. Also check that the file wasn't converted during transfer.

Hidden columns/rows don't reappear when switching away from a custom view.

Right-click column/row headers > Unhide while not in any custom view, or edit the view and uncheck 'Hidden Rows and Columns' in the Custom Views dialog.

Frequently Asked Questions

Can I share custom views with other Excel users?

Custom views are stored in the workbook file itself, so anyone opening the .xlsx/.xlsm file can access them via View > Custom Views. However, each user sees them independently; changes one user makes to a view don't automatically sync to others.

Do custom views work in Excel Online or Google Sheets?

Custom Views are not supported in Excel Online or Google Sheets. They only function in desktop versions of Excel (2013 and later). If you open a file with custom views in Excel Online, the views are preserved but inaccessible until you open the file in desktop Excel.

How many custom views can I create per worksheet?

Excel doesn't impose a strict limit, but practical performance typically supports 10-20 custom views per sheet without noticeable slowdown. Avoid creating excessive views; instead, use meaningful names to organize them logically.

Can I delete or rename a custom view?

Yes. Open View > Custom Views, select the view you want to modify, and click Delete. Unfortunately, Excel doesn't offer a direct rename function—you must delete and recreate the view with the new name.

Will custom views work if I add or remove columns after creating them?

If you add columns before those included in the view, the view still works but may show unexpected column positions. Deleting columns used in a view can cause errors. It's best to finalize your data structure before creating custom views.