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

Posted: July 31, 2013 by MaxFraudy in Excel
Tags: , , ,

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 Custom View

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.

Advertisements
Comments
  1. Thomas Ellebæk says:

    I believe there is some limitation with protected sheets as well, bringing the usefulness down a great deal.

  2. Thomas Ellebæk says:

    Or at least that’s what I concluded when I explored the feature and listed it on my “not-so-good”-list. But perhaps I should investigate it further. Thanks for bringing it up.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s