Excel Tutorials
Learn Excel - Chapter 8
Excel Autofilter
The basic Excel filter (also known as the Excel Autofilter) allows you to view specific rows in an Excel spreadsheet, while hiding the other rows. When the Excel autofilter is added to the header row of a spreadsheet, a drop-down menu appears in each cell of the header row. This provides you with a number of filter options that can be used to specify which data of the worksheet are to be displayed.Activating the Excel Autofilter
To active the Excel autofilter we do the following steps:1) First click on the range of cells that you want to filter.
2) Excel will automatically detect the entire data range when applying the autofilter. However, if the range of cells that you want to filter has missing rows or columns of data, Excel will not select all the data, so you may need to manually select the range of cells that you want to apply the filter to. Make sure that the required data is selected before giving the command,
3) Select the Filter option from Data tab on the Excel ribbon
You should now have the drop-down menus on each of your header cells, which can be used to select the rows to be displayed.
The above worksheet shows Student's Marks for Practical and Theory Marks for a particular subject. The basic Excel filter has been applied to all six columns and the drop-down menu each column is displayed.
This drop-down menu offers a check list for the contents of the cells in the filtered column. The user can opt to display all rows or to display cells containing one or more selected values (specified by checking / unchecking values from the list).
Current versions of Excel also allow you to filter by color. This may be text color or the color of a cell background. As you can see, the worksheet has few rows with different colored background. We will be later on filtering the worksheet based on the colors too.
Also, the above image shows the option to apply number filters. Excel has displayed this option because the data in the filtered column is numeric. However this option will vary depending on the data within the filtered column. Columns containing dates will have date-specific filters and columns containing text will have text-specific filters.
The Excel filters in the Number Filters... category are:
Simple Numeric Conditions (Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, Between). If you select one of these options, Excel will open up a dialog box in which you can specify up to two simple numeric conditions;
(Top 10...) - Display rows containing the top N values;
(Above Average) - Display numeric values that are above the average value;
(Below Average) - Display numeric values that are below the average value;
(Custom Filter...) - This opens up the same dialog box as you get when selecting the individual Numeric Conditions (Equals, Does Not Equal, etc), to allow you to specify up to two numeric conditions.
The two options - (Top 10) and (Custom) are discussed in detail below.
(Top 10...)
The (Top 10...) option is only available for columns that Excel identifies as containing numeric values.This Excel filter option allows you to display only the N highest or the N lowest values from the current column. If you click on this option, Excel brings up a dialog box which contains 3 options:
1) Select the Top (highest) values or the Bottom (lowest) values;
2) Option to select the number, N;
3) Select N Items or N Percent of entries to be displayed.
(Custom Filter...)
Selecting the (Custom Filter...) option causes the 'Custom Autofilter' dialog box to open. This dialog box allows you to define the conditions that specify which rows you want to display.The conditions that are presented to you in the 'Custom Autofilter' dialog box depend on the type of data that is present in the column to be filtered:
If your column contains numeric values, you will be presented with number-related criteria, such as 'equals', 'does not equal', 'is greater than', etc.
If your column contains dates, you will be presented with 'before', 'after' and 'between' criteria types.
If your column contains text values, you will be presented with text-related criteria, such as 'equals', 'does not equal', 'begins with', 'contains', etc.
Up to 2 criteria can be combined, separated by an 'and' or an 'or'.
Filter By Color
If you use colors to code your data, filtering by color is a great feature to learn. The above worksheet contains few rows which are colored. To view only a specific color in your worksheet, click the filter drop down option, click over Filter By Color and then select the particular color.When you click on a color, you'll see only the cells with a matching filled color.
Clear the Excel Filter
We can clear the filters set in a particular column by either1) Clicking on the Clear Option in the Sort & Filter Group or
2) By the Clear Filter From option from the drop down menu of that particular column.
Removing the Excel Filter
To remove the filter from your spreadsheet, simply select the Data tab on the ribbon at the top of your spreadsheet, and from within this, click on the Filter optionAdvanced Filter In Excel
To Learn Advanced Excel - Advanced Filter Command Whatapp/Call 8422000829. Disount Code WEBSAB501
Home Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 9 Chapter 10 Excel Shortcuts
Keep Learning...