Filtering allows us to display only records that meet specified criteria or criterion. This lets us focus on only specific records without the distraction of the entire dataset.
Activating Filters
To activate the Filter controls, select a cell within the desired table, and click Data -> Create a Filter.
This activates the filter controls within the header row of the selected table.
This can also be performed by clicking the Filter button on the far right of the toolbar.
Ensure there is at least 1 row of blank cells above your table, otherwise, the data range selection will extend beyond the header row.
Basic Filters (Filter by Values)
By selecting a Filter control, we can hide or show items from the table by placing or removing checkmarks to the left of item names.
Filter by Condition
By selecting a Filter control, we can also filter by a condition. These include conditions such as:
- Greater Than
- Less Than
- Includes/Excludes
- Between/Not Between
- Empty/Not Empty
- Starts With/Ends With
- Date In Range/Out of Range
- Date Before/After Filter by Color
If you have an applied font color or cell color, you can filter by either of these colors.
Filter Indicator
If you suspect that some data may be missing, it could be that the data has been filtered.
To detect which columns have applied filters, examine the header row of the data for a funnel icon.
Filter Views
If you have data filtered, then sort the filtered results, the position of the records will change in the underlying table. This means when you remove the filter to display all records, portions of the original list will have changed.
To combat this, we can use what is known as Filter Views.
A Filter View behaves like a snapshot of the filtered data. Once activated, you can freely filter and sort the data as you wish without altering the original version of the data.
If you are collaborating with others, their version of the data remains unchanged as you filter and sort your perspective of the data.
Creating a Filter View
If you have already filtered the data, select Data -> Filter Views -> Save as Filter View.
Managing a Filter View
Once a Filter View has been applied, you will notice a large dark gray border of the sheet.
Filter Views can be managed via the gear icon in the upper-right of the window. This allows you to apply or change a view’s name, update the filtered data range, and duplicate or delete views.
Applying a Saved Filter View
Once a view has been saved, it can be applied at any time by selecting it from the Filter icon menu.
Returning to an Unfiltered View
To exit a Filter View and return to the original spreadsheet view of the data, either click the Close icon in the upper-right of the worksheet or click Data -> Filter Views -> None.
Leila Gharani
I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
