Create and use Custom views

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Once you have filtered your data to show only particular values, and have formatted the rows/columns of your data range (e.g. showing/hiding columns) in the way that you want, then you might wish save down this view of your data to re-visit later on, or to create various different views (subsets) of your data and them switch between them without having to re-apply the different filters each time.

This is what we can use the Custom Views functionality for – it will save down a snapshot of the filters applied as well as settings related to how the data is displayed (e.g. column widths, hidden rows/columns and print settings such as page settings, margins, headers & footers). Having this as a custom view will allow you to quickly apply these settings again to that worksheet when needed rather than having to recreate the view.

In this topic we’ll have a look at how to create and apply Custom views. Whilst this is clearly a useful feature, there are some limitations with using this such as:

• The view won’t restore any filter settings applied through an Advanced Filter ( Filter for Multiple Criteria or Filter for Unique values)
• If you have tables anywhere in your Excel workbook (or Lists in Excel 2003), the ‘Custom Views’ option will be greyed out

Firstly let’s filter and format the data in the way we want for a particular view. We can’t use Advanced Filters here but we can use the Text Filters – so in the sample Excel file we might want to filter for all values beginning with ‘Item 1’ and Hide the Descriptions in Column B. So we click on the ‘Filter’ icon, and from the drop down arrow next to ‘Item no’ we click on Text Filters -> Begins with and in the ‘Custom AutoFilter’ dialog box that shows (below) we select ‘begins with’ and put in ‘Item 1’ as the text

We then hide Column B by selecting it, right-clicking and selecting ‘Hide’. Our data now looks like this:

We then want to save this down as a view to revisit later on, so we click on the View tab and then ‘Custom Views’. We’re presented with the dialog box below. To add a new custom view we click on ‘Add’ and then give it a meaningful name. You can also select whether you want to have Print settings and/or filter settings captured by the view.

Click on OK and then it will return to your dataset.

We can then change/remove the filters applied to this data, unhide Column B and can add or remove items (e.g. we add some more items, some of which we being with ‘Item 1’ and some which don’t).

We then want to re-apply the view that we saved down– we click on View -> Custom Views, highlight the one we want and click on ‘Show’.

This should now automatically apply our filters (including on the data we added) and hide Column B.

We can create multiple views, each with different filter settings, and then switch between them using the Custom Views functionality.

In the Topic Example Workbook, filter for all Items beginning with item 2 and hide Column B, and then save down this view as a new Custom view.

Go back into the data, remove the filter, unhide column B and add some new items including some beginning with ‘item 2’. Then try re-applying the view.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.