Trick of the Week :: Custom Views to Control Workbook Display Options

When your Excel dashboard has multiple users, you might want to customise the options available to different sets of users.

Scenario 1: You created a sales dashboard, you might want pricing column hidden from certain people, or you might want only relevant products visible to the suppliers, or you may want to call the same report by different names for different sets of people and so on and so forth. Essentially, you want to control what you want to show to whom.

Scenario 2: You have an Excel report with huge volumes of data of all regions. You want to set your print area such that each region can print only their own data and the header is customised as per their region name.

Scenario 3: You have some sheets with sensitive data or mappings in your workbook that you want to be available to only developers. Or maybe some sheets need to be hidden to some users and visible to others.

I can list down more such scenarios but let’s come to the point.

Excel provides us with a feature called Custom Views. You can use this feature to save display settings with each custom view. The display settings include row/column size & visibility, print areas, sheet visibility, filters, header & footer etc. To create a custom view, go to View tab -> Workbook Views group -> Custom Views

Click on Add and give the view a name. Check/uncheck the option boxes to decide which setting you want to Include. To create another view, simply set your workbook the way you want it to be seen and add another view. All the available Custom Views will be visible in this dialog box once they are saved.

Now, that we know what custom views are and how they work, you might ask how does this apply to the scenarios that I’d mentioned in the beginning. Here’s how – Once you have all the views added, you can select the view that you want to display and click the Show button. Or else, if don’t want to do this manually, you can use some VBA to achieve this. You can create Custom Views and use VBA to determine which view you want to Show and activate it. Read the next post here to see how.

Note: Apparently, if you have Excel tables in your workbook, the Custom Views option is not available. Not sure why.