Categories
Design

How to use custom views in excel to save your workbook settings

You can use a custom view to save specific display settings (such as hidden rows and columns, cell selections, filter settings, and window settings) and print settings (such as page settings, margins, headers and footers, and sheet settings) for a worksheet so that you can quickly apply these settings to that worksheet when needed. You can also include a specific print area in a custom view.

You can create multiple custom views per worksheet, but you can only apply a custom view to the worksheet that was active when you created the custom view. If you no longer need a custom view, you can delete it.

Create a custom view

On a worksheet, change the display and print settings that you want to save in a custom view.

Go to View > Workbook Views > Custom Views > Add.

In the Name box, type a name for the view.

Tip: To make a view easier to identify, you can include the name of the active worksheet in the name of a view.

Under Include in view, select the check boxes of the settings that you want to include. All the views that you add to the workbook appear under Views in the Custom Views dialog box. When you select a view in the list, and then click Show, the worksheet that was active when you created the view will be displayed.

Important: If any worksheet in the workbook contains an Excel table, then Custom Views will not be available anywhere in the workbook.

Apply a custom view

Go to View > Workbook Views > Custom Views.

In the Views box, click the name of the view that you want to apply, and then click Show.

Note: If your view was created on another worksheet, then that worksheet will be automatically displayed.

Delete a custom view

Go to View > Workbook Views > Custom Views.

In the Views box, click the name of the view that you want to delete, and then click Delete.

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to use custom views in excel to save your workbook settings

When working on an Excel worksheet, you may find yourself setting up different display settings at different times, such as zoom level or window position and size. The Custom Views feature allows you to set up and save different views to quickly switch among them.

For example, you may want to zoom in on the worksheet temporarily to see more details, or hide parts of the Excel interface to maximize your workspace (in addition to hiding the ribbon). You can set up a different view for each worksheet and save each view. The following settings are included in custom views: the zoom level, the current cell selection, column widths and row heights, display settings on the Advanced tab of the Excel Options dialog box, the current size and position of the document window, the window pane arrangement (including frozen rows and columns), and optionally print settings (including page setup) and hidden columns and rows.

Before setting up any custom views, it’s a good idea to save the current normal view as a custom view so you can easily revert back to it. To set the current view as the normal view, click the “View” tab.

How to use custom views in excel to save your workbook settings

In the Workbook Views section, click “Custom Views” or hold Alt and press W, then C on your keyboard.

How to use custom views in excel to save your workbook settings

On the Custom Views dialog box, click “Add”

How to use custom views in excel to save your workbook settings

Type a unique name for the view in the “Name” box. Since this is our normal view, we named it “Normal 100%”. Either check or uncheck the “Print settings” and “Hidden rows, columns and filter settings” boxes depending on what you want to include in the view. Click “OK”.

How to use custom views in excel to save your workbook settings

Now, we’ll create a custom view. As an example, we’re going to create a view with a 150% zoom and the Formula Bar and Headings hidden, so we can read our spreadsheet easily and have a bit more room on the worksheet. Set up the view the way you want it and then click “Custom Views” in the Workbook Views section on the View tab.

How to use custom views in excel to save your workbook settings

Click “Add” on the Custom Views dialog box.

How to use custom views in excel to save your workbook settings

Enter a unique name for your custom view and check or uncheck the boxes under Include in view as desired.

How to use custom views in excel to save your workbook settings

To switch views, click “Custom Views” in the Workbook Views section on the View tab, or hold Alt and press W, then C on your keyboard. Click on the view you want and click “Show”. You can also double-click the name of the view you want to show.

How to use custom views in excel to save your workbook settings

Custom views are only available in the workbook in which you create them, and they are saved as part of the workbook. They are also worksheet-specific, meaning that a custom view only applies to the worksheet that was active when you created the custom view. When you choose a custom view to show for a worksheet that is not currently active, Excel activates that worksheet and applies the view. So, you must create custom views for each worksheet in each workbook separately and be sure to save your workbook with your custom views.

Have you ever collaborated with someone else in a worksheet, looking at a large data set, and suddenly the table shrinks and you’re unable to finish your work? It’s pretty disruptive isn’t it?

Sheet views are an innovative way of letting you create customized views in an Excel worksheet without being disrupted by others. For instance, you can set up a filter to display only the records that are important to you, without being affected by others sorting and filtering in the document. You can even set up multiple sheet views on the same worksheet. Any cell-level edits you make will automatically be saved with the workbook regardless of which view you’re in.

Sheet views are currently limited to Excel 2007 or later files stored in OneDrive, OneDrive for Business, and SharePoint. If you save a local copy of a file that contains sheet views, the sheet views will be unavailable until the file is saved to SharePoint and opened from that environment. If you add sheet views to a workbook and save it as Excel 97-2003, the sheet views will be discarded.

When you use Sheet View in desktop Excel and save your document, any sorts and filters you have made will appear in the Default view for all users. Sheet Views are not affected.

How do I add a sheet view?

Select the worksheet where you want the sheet view, and go to View > Sheet View > New. Next, apply the sort/filter that you want. Excel will automatically name your new view: Temporary View. Your view is initially temporary, so if you want to keep it, select that view name from the sheet view switcher drop-down, type your new name, then press Enter.

If other people are working on the file, you can sort or filter, and we’ll ask if you want to apply that sort or filter for just you, or everyone. This is another entry point for sheet views.

When you’re ready to display a particular view, you can select it from the sheet view switcher drop-down.

The sheet view switcher only displays views for the active worksheet.

When a sheet view is applied, there will be an eye symbol next to the worksheet tab name. Hovering over it will display the active sheet view’s name.

When you first create a new sheet view, Excel will preserve your initial view and display it in the sheet view switcher as Default. Selecting the default option will reset your view to where it was when you started.

How do I close or switch between sheet views?

If you want to close a sheet view and return to the default view, go to View > Sheet View > Exit. To switch between views, go to View > Sheet View, and select your view from the sheet view switcher drop-down list.

How do I delete a sheet view?

If you decide that you no longer want a particular sheet view, you can go to View > Sheet Views > Options, select the view in question, then press Delete. You can use Shift/Ctrl+left-click to select multiple views to delete.

There is an Options dialog within the Sheet View group on the View tab. This dialog lists all sheet views that are associated with a given worksheet. You also have the options to Rename, Duplicate, or Delete existing views. To activate a view from the Options dialog, you can double-click it from the views list, or select it, then use the Switch to. button.

Why wouldn’t I want a sheet view? Let’s say you’re in a meeting, and need everyone to see what you do. Sheet views could get confusing if you’re not all looking at the same thing.

How do I exit a view? Go to the View tab > Sheet Views > Exit.

What happens when a sheet view is active and I close the file and reopen? Any active sheet view will automatically reset to the default view.

Is a sheet view private, and only for me? No, other people who share the workbook can see views you create if they go to the View tab, and look at the sheet view switcher drop-down in the Sheet Views group.

Can I make different sheet views? You can create up to 256 Sheet Views, but you probably don’t want to get overly complicated.

For desktop, it’s more useful when everyone in a document is using Sheet View so that when coauthoring, no one is being impacted by each others’ sorts and filters.

Add a sheet view

Select the worksheet where you want the sheet view, then click to View > Sheet View > New.

Apply the sort/filter that you want. Excel automatically names your new view Temporary View to indicate the sheet view isn’t saved yet. To save it, click Temporary View in the sheet view menu, type the new sheet view name, and then press Enter.

If other people are working on the file, you can sort or filter, and Excel asks if you want to apply that sort or filter for just you, or everyone. This is another entry point for sheet views.

When you’re ready to display a particular view, you can select it from the sheet view menu.

The sheet view menu only displays views for the active worksheet.

When a sheet view is applied, an eye symbol appears next to the worksheet tab name. Hovering over the eye will display the active sheet view’s name.

When you first create a new sheet view, Excel will preserve your initial view and display it in the sheet view switcher as Default. Selecting the default option will reset your view to the main view of the document.

Close or switch between sheet views

To close a sheet view and return to the default view, click View > Sheet View > Exit.

To switch between views, click View > Sheet View and then select your view from the sheet view menu.

Delete a sheet view

If you decide that you no longer want a particular sheet view, click View > Sheet Views > Options, select the view in question, and then press Delete.

Sheet View options

There is an Options dialog within the Sheet View group on the View tab. This dialog lists all sheet views that are associated with a given worksheet. You can also Rename, Duplicate, or Delete existing views. To activate a view from the Options dialog, you can double-click the in the sheet views list, or select it, then use the Switch to. button.

Frequently Asked Questions

Why do my Sheet View options appear grayed out? You can only use Sheet Views in a document that is stored in a SharePoint or OneDrive location.

Is a sheet view private, and only for me? No, other people who share the workbook can see views you create if they go to the View tab, and look at the sheet view menu in the Sheet Views group.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Normally we can save filter criteria by Custom Views feature in Excel, but the Custom Views feature can’t reserve custom sort criteria/order. This article will introduce you the method to save custom sort criteria or sort order in Excel.

This method will guide you to save the list you have sorted by custom criteria as a custom list, and then apply the custom criteria to other data easily. Please do as follows:

1. After sorting a list, please click File > Options (or Office button > Excel Options) to open the Excel Options dialog box.

2. In the Excel Options dialog box, click the Advanced in the left pane, and then click the Edit Custom Lists button in the General section. See screenshot:
How to use custom views in excel to save your workbook settings

Note: In Excel 2007’s Excel Options dialog box, please click the Popular in the left bar, and then click the Edit Custom Lists button in the Top options for working with Excel section. See screenshot:
How to use custom views in excel to save your workbook settings

3. In the popping out Custom Lists dialog box, go to Import list from cells box and click the Browse button to select the list you have sorted by custom criteria, and then click the Import button and the OK button successively. See screenshot:
How to use custom views in excel to save your workbook settings

4. Click the OK button in the Excel Options dialog box.

So far the custom sort criteria/order has been saved as a custom list in the Microsoft Excel program. To apply this custom sort criteria to other lists, please go ahead below steps.

5. Select the list you will sort by the saved custom sort criteria, and click Data > Sort. See screenshot:
How to use custom views in excel to save your workbook settings

6. In the opening Sort dialog box, specify the list you will sort from the Column drop down list, keep Values selected from the Sort On drop down list, and select Custom List from the Order drop down list. See screenshot:

How to use custom views in excel to save your workbook settings

7. In the opening Custom Lists dialog box, select the custom sort criteria you saved just now in the Custom lists box, and click the OK button. See screenshot:
How to use custom views in excel to save your workbook settings

8. Click the OK button in the Sort dialog box.

Now you will see the selected list is sorted by the saved custom sort criteria as below screenshot shown:
How to use custom views in excel to save your workbook settings

One click to backup (take a snapshot of) current workbook, and one click to restore workbook

When undoing some operations in Excel, generally you may click the Undo button repeatedly. However, this Undo feature is frequently unable to revoke operations by VBA code. Here we recommend Kutools for Excel’s Track Snap utility, which can help you temporally backup current workbook with only one click and recover your workbook to any backup versions with also only one click in Excel. Full Feature Free Trial 30-day!
How to use custom views in excel to save your workbook settings
These temporary backup versions will be removed automatically after closing the current workbook.

If you want to make the cell styles that you create in or copy into a workbook available in all future workbooks, you can save them in a template that is used for all new workbooks.

After you exit and restart Excel, the cell styles that you saved in your template workbook will be available in all new workbooks that you create.

Open the workbook that contains the styles that you want to make available.

On the File tab, click New and select Blank Workbook.

On the Home tab, in the Styles group, click the More button next to the cell styles box.

How to use custom views in excel to save your workbook settings

Click Merge Styles.

How to use custom views in excel to save your workbook settings

In the Merge Styles dialog box, in the Merge styles from box, click the workbook that contains the styles that you want to copy, and then click OK.

How to use custom views in excel to save your workbook settings

If both workbooks contain styles that have identical names, you must indicate whether you want to merge these styles by doing the following:

To replace the styles in the active workbook with the copied styles, click Yes.

To keep the styles in the active workbook as they are, click No.

Note: Excel displays this message only once, regardless of the number of pairs of identical style names.

On the File tab, click Save As.

In the File name box, type Book.

In the Save as type box, click Excel Template, or click Excel Macro-Enabled Template if the workbook contains macros that you want to make available in the template.

Click Browse and then locate and select the XLSTART folder.

Note: In Windows 10, the XLSTARTfolder is typically located in C:\Program Files(x86)\Microsoft Office\root\Office 16\XLSTART.

After you exit and restart Excel, the cell styles that you saved in Book.xltx (or Book.xltm) will be available in all new workbooks that you create.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Before you start Microsoft Office Excel, you can make sure that a specific workbook or a workbook template or worksheet template that has custom settings opens automatically when you start Excel. If you no longer need a specific workbook to open, you can stop it from being opened when you start Excel.

If a workbook that is opened when you start Excel contains automatic macros, such as Auto_Open, those macros will run when the workbook opens. If needed, you can prevent them from running automatically when you start Excel.

You can also customize the way that Excel starts by adding command-line switches and parameters to the startup command.

Automatically start Excel with a blank workbook

In Excel 2013 and later, Excel defaults to showing the Start screen with recent workbooks, locations, and templates upon starting. This setting can be changed to instead bypass this screen and create a blank workbook. To do so:

Click File > Options.

Under General, and then under Start up options, check the box next to Show the Start screen when this application starts.

Automatically open a specific workbook when you start Excel

To automatically open a specific workbook when you start Excel, you can place that workbook in the XLStart folder, or you can use an alternate startup folder in addition to the XLStart folder.

Locate the XLStart folder

Any workbook, template, or workspace file that you place in the XLStart folder is automatically opened when you start Excel. To find out the path of the XLStart folder, check the Trust Center settings. To do so:

Click File > Options.

Click Trust Center, and then under Microsoft Office Excel Trust Center, click Trust Center Settings.

Click Trusted Locations, and then verify the path to the XLStart folder in the list of trusted locations.

Use an alternate startup folder

Click File > Options > Advanced.

Under General, in the At Startup, open all files in box, type the full path of the folder that you want to use as the alternate startup folder.

Because Excel will try to open every file in the alternate startup folder, make sure that you specify a folder that contains only files that Excel can open.

Note: If a workbook with the same name is in both the XLStart folder and the alternate startup folder, the file in the XLStart folder opens.

Stop a specific workbook from opening when you start Excel

Depending on the location of the workbook that is automatically opened when you start Excel, do any of the following to make sure that the workbook no longer opens upon startup.

If the workbook is stored in the XLStart folder, remove it from that folder.

If the workbook is stored in the alternate startup folder, do the following:

Note: For more information about locating the startup folder, see Locate the XLStart folder.

Click File > Options > Advanced.

Under General, clear the contents of the At startup, open all files in box, and then click OK.

In Windows Explorer, remove any icon that starts Excel and automatically opens the workbook from the alternate startup folder.

Tip: You can also right-click that icon, click Properties, and then remove any references to the workbook on the Shortcut tab.

Automatically open a workbook template or worksheet template when you create a new workbook or worksheet

You can save workbook settings that you frequently use in a workbook template, and then automatically open that workbook template every time that you create a new workbook.

Do one of the following:

To use a workbook template, create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros, styles, and other formatting that you want to use in new workbooks that will be based on the workbook template.

To use a worksheet template, create a workbook that contains one worksheet. On the worksheet, include the formatting, styles, text, and other information that you want to appear on all new worksheets that will be based on the worksheet template.

Settings that you can save in a workbook or worksheet template

Cell and sheet formats.

Page formats and print area settings for each sheet.

The number and type of sheets in a workbook.

Protected and hidden areas of the workbook. You can hide sheets, rows, and columns and prevent changes to worksheet cells.

Text you want to repeat, such as page headers and row and column labels.

Data, graphics, formulas, charts, and other information.

Data validation settings.

Macros, hyperlinks, and ActiveX controls on forms.

Workbook calculation options and window view options.

Click File > Save As.

In the Save as type box, click Template.

In the Save in box, select the folder where you want to store the template.

To create the default workbook template or default worksheet template, select either the XLStart folder or the alternate startup folder. To find out the path of the startup folder, see Locate the XLStart folder.

To create a custom workbook or worksheet template, make sure that the Templates folder is selected.

The path is typically: C:\Users\ \AppData\Roaming\Microsoft\Templates

In the File name box, do one of the following:

To create the default workbook template, type Book.

To create the default worksheet template, type Sheet.

To create a custom workbook or worksheet template, type the name that you want to use.

Click File > Close.

Prevent automatic macros from running when you start Excel

Automatic macros (such as Auto_Open) that have been recorded in a workbook that opens when you start Excel will automatically run as soon as the workbook opens.

To prevent macros from automatically running, hold down SHIFT while you start Excel.

Tip: For more information about automatic macros, see Run a macro.

How to use custom views in excel to save your workbook settings

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. Here are a few Custom Views tricks that you can use. But remember, Custom Views don’t work if there are named Excel Tables in the workbook

Store Worksheet Settings

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.

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.

How to use custom views in excel to save your workbook settings

On the same worksheet, if I select the All_Columns Custom View, all the columns would print.

How to use custom views in excel to save your workbook settings

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.

How to use custom views in excel to save your workbook settings

On the same worksheet, the Print_Paper Custom View has nothing in the Left Header area.

How to use custom views in excel to save your workbook settings

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

How to use custom views in excel to save your workbook settings

  • When prompted, click Yes, to delete the old Custom View and replace it.

How to use custom views in excel to save your workbook settings

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

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.

How to use custom views in excel to save your workbook settings

  • 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?
__________

This is really a lose – lose situation in Excel and therefore a true Sophie’s Choice. Let’s elaborate on the two commands.

Excel Table

How to use custom views in excel to save your workbook settings

A table is the best way to make Excel dynamic. If you format your range as a table, all your charts that would take data from that table would be dynamic. So you would never have to select the data for a chart when you input new data again. Your formulas also take on a new look. Instead of =SUM(A1:A100) you now have =SUM(Table1[Column1]) and that formula is dynamic. Among other benefits, the Total Row should be mentioned. An aggregation formula can be chosen from the dropdown list and in addition takes into account all your filters. In the picture bellow, you can also see that the title row replaces the Headings of columns. Instead of A you now see a “Column 1” title.

How to use custom views in excel to save your workbook settings

Above are just some of the reasons why you should use Excel Tables in Excel if that is in any way possible, particularly if you want to use Pivot Tables.

Custom Views

Now imagine that a certain Excel file is viewed by many different users, or being used by one user but in many various ways. Now once you need to filter your data by three conditions and sometimes you wish to have six totally different filters applied. Also sometimes you hide a few columns for printing purposes and at different times, you might wish to see all the columns or better yet, hide a different set of columns.

All this can easily be solved by using the so called Custom Views. You can find the command on the VIEW tab.

How to use custom views in excel to save your workbook settings

When you use the Custom Views command, you get the Custom View manager. How to use custom views in excel to save your workbook settings

Here you can Show different views or create a new Custom View by choosing the Add button. When you choose to create a new Custom View, you get the Add View window.

How to use custom views in excel to save your workbook settings

Here you give it a name but more important, here you set if you want this View to remember the current Hidden rows, columns and Filter settings and/or Print settings. If you do save all of them, the next time you want to use them, all you need to do is to regardless of the current view you have of your data, you just choose VIEW/ Custom Views, select the desired view and press Show. All saved settings will be applied instantly. Pure brilliance.

The Choice

But there’s a catch. If you only have one Table in your entire Workbook, you cannot use Custom Views. The command is inactive.

How to use custom views in excel to save your workbook settings

So it really is a Sophie’s Choice. You can only use one or the other but not both. And this goes even deeper. If you use any of the Power BI features, you are almost certain to use Excel Tables and therefore you already know, that you cannot use the Custom Views command. The disappointment is almost as great as the first time you figure out that you cannot use the =Table1[Column1] syntax to define a Data Validation list source. So this really is a great shame and has even gotten me to a point where I installed Visual Studio on my computer, started studying C# and VSTO to try to make it work myself 🙂 . The lengths we go to for a good cause 🙂

Question

I’m sure this is simple, but I’ve been searching for hours and I’m still stumped.

The background.

I’ve created a new excel macro enabled workbook. Wrote a bunch of macro’s. Created a new custom ribbon tab. Added a number of groups to that tab. And finally, droped some macros in those groups.

Everything works great.

The problems.

This workbook is to be used as a base or template style workbook. What I mean by this is; I’ll open it, add data, anbd save it as a different name. Problem 1: on reopening it the macros in the customised ribbon point to the previous workbook name and location. I would like these buttons to point to the macros that are contained within the current workbook.

Problem 2: if someone else opens this file on their computer they don’t see the custom ribbon tab? I want to save the ribbon i’ve created with the workbook, so no matter who opens it, they see the buttons, and they can run the macros from those buttons.

In Summary.

How do I save my custom ribbon tab so it is accessible by anyone when they open this workbook?

How do I make the custom ribbon buttons point to the local file macros, not some file I created in the past?