If you are starting out on Microsoft® Excel® or want a quick review, this article builds on the basic filter and limit concepts explained in Microsoft Excel for Beginners. The same concepts also apply to the reporting features in many ECi software packages.
Filters: Diving Deeper
To review, filtering in Excel enables you to create a data subset from which you can drill down and find the precise information you need.
To filter, follow these steps:
- Click in any cell in your spreadsheet.
- Using the toolbar, navigate to the Home tab.
- In the Editing group on the far right, click Sort and Filter, then Filter.
Note: Filters will have all cells selected by default, indicated by checkmarks on the left of the boxes within the filter.
When filters have been applied, you can set inclusive and exclusive limits, as well as limits at extremes. The shortcut to Filter is holding down the Ctrl key, then the Shift key, while typing L on your keyboard. You can also format cells based on limits to help certain values stand out. In this tutorial, we will explain these terms and how you can implement them in Excel.
Inclusive Filter Limits
Inclusive filters enable you to filter out data that does not meet your criteria, or conversely, to include only data that does meet your criteria. The steps for setting inclusive filter limits are simple:
- Click on the down arrow of a column.
- Hover over Number filters.
- Set the desired values, such as Between 200 AND 500, so that only values between 200 and 500 are included in the visible column.
Notes: The filter only applies to a column if there are multiple columns. You can type in numbers without any formatting, such as dollar signs.
If you have a large set of data, you may want to use the Search box in the filter or the Number Filters > Equals option instead. The Equals option is especially helpful if you are looking for a smaller number or text value that may be contained in other cells if you are using the Search box in the filter. For example, if we use the Search box on column D and type 55, then both the cell D7 and D12 are included.
Exclusive Filter Limits
Exclusive filters enable you to limit the column to data that does not meet a certain criterion. For example, you can uncheck the box for zero so that cells with a zero value are not shown.
Undesired Cell Included with Search
To exclude rows from being included in your filter, follow these steps:
- Right-click the row.
- Click Hide to hide it, as in the Subtotal row 12 below.
Notes: The row will still be there if you need to go back to unhide it, but it will not be visible onscreen while hidden. Columns and sheets can also be hidden in the same way. In the example below you may not want to include the Subtotal row in a filter if you want to work only with individual cells.
Subtotal Row Hidden
Now we can search for numeric values, such as cells containing a 55, with the Subtotal row excluded from our search results on the column.
Excel automatically provides Date filters for cells formatted as Dates. For example, you can follow these steps:
- Click on the down arrow of a column.
- Hover over the Date filter,
- Move right over All Dates in the Period,
- Select a desired calendar month or quarter.
Note: You can also click on any of the filters listed to automatically to go to a specific date range, such as This Month.
Options that are followed by three periods(…) will direct to a new prompt to offer you additional Custom Options. On the right side of the Custom Options is a Calendar icon that you can click on to navigate through to help prefill the Custom field.
If you would like to see all of your data again, follow these steps:
- Click the dropdown arrow.
- Click Clear Filter from individual columns.
- In the Home tab within the Editing group.
- Click Sort and Filter.
- Then click Clear to clear filters from all columns.
Knowing the types of limits you can apply to filters enables you to quickly determine the best method to narrow down your data for further analysis.
Don't miss out!
Stay on top of the latest business acumen by subscribing to the Big Ideas for SMBs blog.