Excel Filter
Excel Filter
Filters can be applied to sort and hide data. It makes data analysis easier.
Note: Filter is similar to formatting a table, but it can be applied and deactivated.
The menu is accessed in the default Ribbon view or in the Data section in the navigation bar.
Applying Filter
Filters are applied by selecting a range and clicking the Filter command.
It is important to have a row of headers when applying filters. Having headers is useful to make the data understandable.
Note: Filters are applied to the top row in a range.
Like in the example below, the dedicated row is row 1.
Let's apply filters to the data set, step by step.
Select range A1:E1
Click the Sort & Filter menu
Click the Filter command
New buttons have been added to the cells in the top row. This indicates that the Filter was successfully applied. The buttons can be clicked to access the different Sort & Filter options.
A Non-Working Example
Lets delete row 1 (the header row) and apply filters to the new row 1, to see what happens.
The filter is applied and has replaced the header row. It is important to dedicate a header row for the filter.
Filter options
The filter options allow for sorting and filtering.
Applying filter keeps the relationship between the columns while sorting and filtering.
Clicking the options button opens the menu.
Sorting
Ranges can be sorted and the relationship between the columns is kept.
Sort Ascending (A-Z) sorted from smallest to largest.
Sort Descending (Z-A) sorts from largest to smallest.
You can read more about Sorting in a previous chapter.
Filtering
Filters can be applied to hide and sort data.
This is helpful for analysis, to select the data that you want to see or not.
Example Filter
Use the filter option to filter on Pokemon that is Type 1, Bug.
Step by step
Click the drop down menu on C1 (
) and choose the Filter option. This is the Column which holds the Type 1 data.
Note: "Items" are the different categories in that column; Grass, Fire, Water and so on.
All items are checked by default. The checked items are the ones that are shown. Uncheck to hide.
Uncheck all items, except Bug, which is the type that we want to show.
Click OK
Good job! The range was successfully sorted by Type 1, Bug. All shown Pokemons are Bug type.
Note: The unchecked rows are hidden, not deleted.
This is explained by looking at the row numbers. The numbers are jumping from 1 to 11 and 16 to 22. The rows in between are hidden.
Note: Checking the items will have the rows shown again.
Another Example
Use the filter option to filter the Pokemons which have Type 1, Bug and Type 2, Poison.
- Click the filter option in D1
- Uncheck all items except Poison
- click OK
That is on point! We have sorted the range based on Type 1, Bug and Type 2, Poison. The filter option is helpful to make ranges easier to analyse.
