How to filter data with autofilters in Excel
If you're working with a small table of data, oftentimes it just takes a couple of quick sorts to clean it up and make it readable for your users. But for larger spreadsheets — those with tens, hundreds, or thousands of rows — a little more firepower is often necessary when you're looking for specific data points.
For this, we use an Excel feature called
Defining the problem
Take a look at the spreadsheet below, which shows SnackWorld's sales numbers by customer and month.
What would we do if we wanted to isolate a particular portion of this spreadsheet — for example, only show rows for which the
Using basic autofilters
Enter autofilters. This Excel feature will allow us to narrow our data range based to a smaller set of rows based on criteria we select. To get started, place your cursor anywhere within the data table you're working with, and press the
When you see these arrows, you'll know that you're now able to play around with filtering.
Let's go ahead and press the arrow button next to the
Let's review the key features of the filtering menu. First, you'll notice that there are
Next, notice that all the values that appear within the
Finally, we'll press the
The beauty of this tool is that our actual table has not changed at all. All of the data for
The other great feature of filters is that we can select as many or as few data points as we want. Open the filtering menu back up on the
Modifying filtered data
One of Excel's most convenient features is that it only modifies data that has been filtered, and excludes everything else. So if we wanted to delete all the rows in which
So we've filtered our data and done what we need with it; but afterwards, how do we get our full dataset back? The answer is simple: Excel's
When working with a data set in Excel, a small "filter" icon next to the column header () will indicate that a column has been filtered. In the example below, this symbol appears next to the
After clicking this button, the filter will clear and our dataset will be back to normal:
The Search feature
Our filters are working great when we only have three months to select between. But what if our table were so big that it was difficult to find values to select?
Fortunately, Excel has a handy
At this point, checking the box next to
Multiple filters in Excel
Above, we looked at how to apply a basic filter to one column. But it's also possible to apply multiple filters — to multiple columns — at once in Excel. Find out how below.
First, let's apply a
Now, let's say we wanted to only show orders from the customer called
Click the checkbox next to
We can apply as many filters as needed onto our data set, filtering across multiple columns with multiple criteria. Just remember: whenever you're done, be sure to
Let's open up our
Excel automatically tailors these advanced filtering options to the type of column that we're filtering on — in this case, a
Numerical columnsuse advanced number filters, which allow you to filter based on 'equal' and 'does not equal' criteria; 'greater than', 'less than', and 'between' criteria; 'above average' and 'below average' criteria, and 'Top 10' value lists. Text columnsuse advanced text filters, which allow you to filter based on 'equal' and 'does not equal' criteria; 'begins with' and 'ends with' criteria; and 'contains' and 'does not contain' criteria. Date columnsuse advanced date filters, which allow you to filter based on 'before', 'after', and 'between' criteria; daily, weekly, monthly, quarterly, and yearly comparisons; 'year to date' criteria; and other individual time periods.
Those are the basics of using autofilters — including filters on multiple columns — in Excel! Questions or comments on the process? Be sure to let us know in the Comments section below.
Save three hours of work a day with these 5 simple Excel hacks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.
- Why the TRUE and FALSE functions are so important...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...