If you are starting out on Microsoft® Excel®, this article will introduce you to basic applications and methods for using filters. The filters are one of the most fundamental and popular functions of the software and mastering their use will enable you to advance in your Excel capabilities.
Applications of Excel Filters
One of the keys to getting the most out of Excel is creating a subset of data within a spreadsheet. Data subsets are created using filters, which allow you to drill down and find the precise information you need. Filtering provides a quick overview of the range of your data for a specific variable.
Depending on the information in your spreadsheet, you can filter data in three ways:
- Based on numbers
- Based on text data
- Based on dates
These three methods of filtering data provide a basic glimpse into the many possible applications for filtering data. For example, if you work in the sales department of a company, you may want to see the range of sales figures for a given product, see all of the sales associated with a particular sales representative, or see all of the sales on a particular date.
Start with a Basic Filter
To become familiar with the filter function in Microsoft Excel, let’s begin by applying a basic filter to an existing spreadsheet.
- Open a completed spreadsheet with columns and headers (here is an example you can use), and save it to your desktop.
- Click any single cell inside a data set in the spreadsheet.
- On the Data tab in the Sort & Filter group, click Filter.
- A small box with a grey downward triangle on the right side of the top row of the columns will be visible. Click the arrow and you will see all of the options available to you to limit the data.
- Click on Select All to clear all of the check boxes, then check the box for your selection and click OK.
- The result is a display of the figures exclusively for that selection.
Working with Filters and Limits
The basic filter options will default based on the data type in the column. For example, in the image below, dates in the month/day/year format show the year as the first layer with months as the next layer, and then days as the layer beneath.
To drill down further into this data to help certain values stand out, you may wish to set inclusive or exclusive limits. To filter the data in this spreadsheet and limit your data to include only the date of August 3rd, follow these steps:
- Uncheck Select All
- Under 2017, click the grey box with a plus sign on the left of August
- Select the checkbox left of 03
With this foundational understanding of how Excel filters work, you can ramp up your capabilities through practice as increasingly complex assignments and projects come across your desk.
Don't miss out!
Stay on top of the latest business acumen by subscribing to the Big Ideas for SMBs blog.