MS Excel Custom Views

Do you work in huge MS Excel files and constantly find yourself scrolling this way and that, trying to get to the right place?

And then again to another location?

And so on.

If you have enough of these locations you need to go to, it quickly becomes impossible to remember cell locations. That means the “Go To” feature is out!

In addition to all that scrolling, you may have to make changes to print areas, print settings, filters, etc. each time you move to a new location.

What a pain!

This all leaves you wondering, “What can be done to remedy the situation”?

Is there a way to create a list of the locations we need to jump to? One that will make more sense than cell location and retain all the extra settings?

Yep, you bet! It’s all in how you view the worksheet.

A possible solution may be MS Excel’s Custom View feature.

The best way to define the Custom View feature is probably to let the Office Assistant do the talking. So, according to him, a Custom View is where:

“You can save the current appearance of a workbook so that you don’t have to change the settings every time you view or print the workbook. You can save the different view settings, including hidden rows or columns and filter settings, as well as print settings. If you hide a sheet before a view is added, Microsoft Excel will hide the sheet each time you show the view.

Before you create a view, set up the workbook to appear the way you want to view it, as well as, how you want it to look when you print it. If you include print settings in a view, the view will include either the currently defined print area for each sheet or the entire worksheet if the sheet has no defined print area.”

In other words, it’s a powerful tool that can be used to save you time and energy when it comes to navigation and repetitive tasks in files used frequently.

Want to know how?


Good! So, now that we know some of the possible “whys” for the Custom View feature, let’s get right to the “hows” of the process.

The first thing you need to do is open your workbook and go to the location you want to work with.

At this point, make the necessary changes to settings and other things at this location. (For example, print areas, hidden rows, filters, subtotals, etc).

Now, we’re ready to create the view.

Go to the View menu, Custom Views choice.

The Custom Views window will open.

Click the Add button to create the new view.

The window that opens will allow you to name the view (something meaningful will be the most helpful) and to make a couple of choices about how much is to be saved in the view.

Name the view and uncheck any boxes for settings you don’t want saved.

Print settings will save the currently selected print settings with the new view.

Hidden rows, columns and filter settings will save these options with the new view.

When you’ve named the view and made all your choices, click OK.

You’ll be returned to the workbook exactly where you were when you started the process.

You can repeat these steps to create different views within the same file: any location, any settings, etc.

To actually use the Custom Views, you simply need to return to the View menu, Custom Views choice.

In the Custom Views window, select the name of the view and click Show.

Instantly, you’ll be taken to the selected location with everything as you set it up to be!

Should you ever need to remove a view from the list, simply return to the Custom Views window, select the name of the view to remove it and click the Delete button.

Cool! If only I had this feature for the house; click and we return to the clean view of the living room.

Sorry, day dreaming again!

~ April