Microsoft Office Tutorials and References
In Depth Information
Using Custom Views with Filtering
Using Custom Views with Filtering
The Excel Filter feature is handy for hiding specific items in a list. For example, if you have a
mailing list, you can choose to display only the rows in which the State column contains Montana.
You can filter a list by using as many columns as you need, and the Custom option provides even
more flexibility.
This tip describes how to save various filter configurations by using custom views.
For reasons known only to Microsoft, the View➜Workbook Views➜Custom Views
command isn’t available if your workbook contains a table (created by choosing
Insert➜Tables➜Table). Therefore, this tip applies only to filtering done using a normal
range of data. If your data is in a table, convert it to a normal range by using Table
Tools➜Design➜Convert to Range.
To enable filtering of a range, select a cell in the range and choose Data
Excel displays drop-down arrows in the header row of the range. Use these drop-down arrows to
filter the data.
Sort & Filter
In a range that has filtering enabled, the column headers make it look like a table, but
it’s not a table. For example you can’t add a Total row to such a range.
Excel doesn’t allow you to give a name to a particular set of filters. Therefore, if you tend to use
several different filtering criteria for a particular list, you can waste a lot of time specifying and
re-specifying the filters manually.
The solution: The rarely used View
Workbook Views
Custom Views command. Here’s how to
do it:
1. Apply filtering to your range and specify the filters to your liking.
2. Choose View
Workbook Views
Custom Views to display the Custom Views dialog box.
3. Click the Add button to display the Add View dialog box, shown in Figure 162-1.
4. Provide a name for the view and make sure that the Hidden Rows, Columns and Filter
Settings check box is selected.
5. Click OK to close the Add View dialog box.
Repeat these steps for as many different filter settings as you like. You might also want to create
an unfiltered view, to display the list with no filtering applied.
Then, to apply a set of filter settings, choose View
Custom Views and select
the named view from the list displayed in the Custom Views dialog box.
Workbook Views
Search JabSto ::

Custom Search