To save the criteria while filtering data,, you need to use Custom View in Microsoft Excel.
What is Custom View?
This tool is used to save the current display and print settings as a custom view that you can quickly apply in the future.
Let’s take an example and understand how we can save the filtering criteria -.
Add the “Custom View” icon to the toolbar.
- Click on the “File” menu bar onthe ribbon.
- Click on “Options”.
- The “Excel Options” window will appear.
- Click on “Customize Ribbon” from the “Choose Commands From” drop down listselect “View” select “Custom Views” click on “Add>>” and then click on OK. You can see the Custom View shortcut below.
I have data in range A1:E19. In which column A contains the “Region”, column B “City”, column C “Product”, column D “Total Revenue” and column E contains “Net Revenue”.
Save the Filtering criteria
- Select a cell from the column containing the criteria you want to filter with.
- Go to “Data” tab in the “Sort & Filter” group and select “Filter”.
- In column C filter, select product fruit and vegetable from the drop down list.
- Click on “Custom Views”, its dialog box will appear
- Click on the “Add” option, type name for this view as “Fruits and Vegetables” and click on OK.
To check the “Custom Views” follow the below mentioned steps:-
- Open the filter by pressing the key “CTRL+Shift+L”.
- Click on the “Custom View” icon.
- The dialog box will appear, click on show.
- The saved custom view will be displayed.