Using Custom Views with Autofilter

 

If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool.  Custom Views can be easily set up  based on your Autofilter criteria.  Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them.

 

For example, the worksheet below contains the following information:  Month, Region, Salesperson, Sales.  This information needs to be viewed in a variety of ways:

 

            Sales for each individual region

            Sales over $500.00

            Sales for each individual month

 

 

 

Download this worksheet

 

Step 1:  Add the Custom View button to your Excel Toolbar

 

  1. From the Excel menu, select View, Toolbars, Customize.
  2. Select the Commands tab.
  3. In the Categories box, select View.
  4. In the Commands box, select Custom Views and drag “Custom Views” to your Excel toolbar and release the mouse button.
  5. The Custom Views button is now on your toolbar
  6. Click Close to close the dialog box.

 

 

Step 2:  Using the Custom Views Toolbar

 

In your worksheet, use Autofilter to display the records that match your desired criteria.  In this case,  Autofilter is used to display sales for the North Region only.  (Note: Autofilter is accessed by clicking in the list and selecting  Data, Filter, Autofilter from the Excel menu.  For more information about Autofilter, refer to An Introduction to Autofilter)

 

Once the records are displayed, click in the Custom Views box on your toolbar and type a name for the Custom View. Press Enter.  When the Add View dialog box displays, click OK.

 

 

To display the entire worksheet, click the Autofilter arrow next to “Region” and select All. Create additional views for any criteria you desire.  In this worksheet, Custom Views have been created for each of the regions using Autofilter.  In addition, a view was created for all regions. 

 

From now on, instead of using Autofilter to filter your records, you can click on any view in your Custom View toolbar box to quickly display the desired records.