Представления (Custom Views) in Microsoft Excel is a very convenient and practical tool, which, for some reason, is deprived of the attention of most users. In 10+ years of training and consulting, I’ve seen very few people (one hand is probably enough) who use it. But in vain.
Let’s restore justice. You’ll like it, you’ll see.
So what’s the point?
Performance is a saved view of a table on a given Microsoft Excel worksheet. When you create a view, it remembers:
- current cell position
- which rows-columns are hidden and which are visible
- which row-column groups are collapsed and which are expanded
- filtration conditions
- print options
In practice, this means that you can create several views for the same table and quickly switch between them by changing the mode of operation.
Views can be controlled using the button Views (Custom Views) tab Viewwhich opens the corresponding window:
As you can easily imagine, using the buttons on the right, you can create, delete, and switch between views.
Another, faster way is to put a drop-down list with views on the Quick Access Toolbar in the upper left corner of the Excel window. To do this, click File – Options – Quick Access Toolbar (File – Options – Quick Access Toolbar), then from the drop down list at the top select All Commands and add a list Views (Custom Views) to the panel:
Let’s look at a few practical examples of using views in real life tasks.
Example 1 Memorable Filter Sets
Suppose we have a table with a list of employees:
… and we often search this table using filters for the same people, for example:
- all men / women
- all heads of departments
- all pensioners, etc.
To avoid having to manually enable these filters each time, you can save them as separate views. For example, filtering male pensioners, and then creating a view on the tab View – Views – Add and enter its name:
By repeating the same steps for each scenario, you can make several convenient views for different cases and then conveniently switch between them using the drop-down list on the quick access panel:
Also, for convenience, you can add a view with the name All, where the filters will be disabled and the full list is visible.
Example 2: Detailed or general report
You have a report with income data for the year:
You can make two views for convenient work with it – detailed (all data is visible) and short (only quarterly totals are visible, months are hidden). You can use grouping, or a macro to hide rows-columns, or you can make everything easier – views.
The logic of actions is the same:
Select in turn the columns with the months D: F, H: J, L: M, PR and hide them with the right mouse button – Hide (Hide) or keyboard shortcut Ctrl + 0.
Creating a View Through a Tab View – Views – Add. You can also simply enter the name of the new view in the drop-down list on the quick access toolbar and click on Enter.
Now you can quickly switch between the general and detailed options using the same drop-down list in the upper left corner of the Excel window:
A similar case is a table with data of the Plan-Fact type, where sometimes you want to see only planned or only actual values, i.e. temporarily hide unnecessary columns:
Notes
- The drop-down list on the Quick Access Toolbar displays views from all sheets at once, therefore, in order not to get confused in them, it makes sense to name views using the sheet name, for example Sales_Fact и Sales_Plan, to understand which sheet they apply to.
- Views are not compatible with smart tables. If at least one “smart table” is created in your workbook on any sheet, then the views stop working. Sadness, but there is hope that they will fix it in future versions of Excel.
- Quickly hide unwanted rows and columns in Excel sheet
- Multilevel row grouping