Custom Views: a little-known treasure

I stumbled upon Custom Views in Excel by accident a number of years ago, in the pre-ribbon era. I use them very occasionally, but when I do, they offer lots of power and options.

Custom Views allow you to have multiple visual settings for a worksheet, and for these to be selected depending on what you're trying to achieve—or who's accessing the information—without having to hack around at your underlying data. They can be used to control the following features:

• Print settings
• Hidden rows and columns
• Column filters.

Here's what you do.

Once your data is complete, set the above attributes according to how you'd like your View to look. Hide whatever columns and rows are redundant for that particular function or audience, filter the columns as you need them to be filtered, and configure your print settings appropriately.

Once you're happy that the spreadsheet looks as you'd like it to look under that View, Select the View ribbon and hit Custom Views and hit Add.

Give your view a name that makes sense to the audience and select which attributes you'd like the view to respect, and hit OK.

Repeat these steps to create as many views as you need.

If you want to enable a View, then you do so via the same menu. It would be useful if there were a dropdown below the button allowing quicker selection (similar to the many such dropdowns beneath the Home ribbon), but if you need quicker access, then it's relatively simple to record a macro and assign it to either a button or a shortcut.

Custom Views are useful for any type of data: risks and issues, defect lists, employee lists, invoice lists etc. They're useful to show multiple cuts of the same data to a single individual: open risks, outstanding invoices, high-priority open defects.

But they come into their own when showing different cuts of the same data to different groups of individuals. Management want to see different aspects of risks and issues than do operational staff. HR need to see different information about employees than the heads of each business area. Hiding columns and filtering out rows is particularly useful in this regard.

A word of warning: Custom Views are not compatible with Tables. So if you have a formatted table anywhere in your workbook, you won't be able to use Custom Views. Such is life.

• Tweet