Working in big companies or large institutions may often lead to dealing with large number of datasets. In these cases, it’s not only challenging to deal with the calculation, but it can prove to be really difficult to find certain information.
However, there’s a really efficient and simple tool in Excel to make things easier. Wondering how this tool can help? Well, in this writeup, we’ll be exploring the ways to filter data, add filters for numbers and text values, and so on. We’ll also be covering the procedures of using filter with search.
So, if you’re interested in how to add filter in excel, and all the additional data that surrounds the topic, you’ve come to the right place. We won’t be dragging the introductions any further. Let’s get straight to it.
Contents
Defining AutoFilter
The AutoFilter is typically known as the Excel Filter by many users. It’s generally used to omit data and view only the necessary or relevant data at a particular time. Also, the displayed data can be printed accordingly without rearranging the entire worksheet.
AutoFilter can filter data in worksheets by value, criteria or by format.
The Difference Between AutoFilter and Excel Sort
Before we move any further with explaining how to add filter in Excel, let’s clarify the difference between Auto Filter and Excel Sort. They might seem similar, but there are some fundamental differences in the functionalities. Let’s discuss:
- First of all, the Sort option is an included feature in the Excel AutoFilter.
- In case of Text values, you can sort by A-Z, or Z-A or even by color.
- The color sorting still remains if you want to Sort Smallest to Largest or Largest to Smallest numbers.
- Similarly, you can easily sort Oldest to Newest, Newest to Oldest, and by Color.
- In Excel Sort, you’re basically rearranging the data set by a specific value parameter. However, while filtering the data in Excel, only the data you want to see are kept in display while the others are removed.
Adding Filter in Excel
Now that we’re done with the definitions and the differences, let’s dive into practice. First things first. For the Excel AutoFilter to work, the dataset that you’re working on should have a row of headers that include the names of the columns as shown below.
If you’ve placed the column headings accordingly, it’s time to move on to the next step.
3 Ways to access the Filter Option
As we all know by now, there are multiple ways in Excel to get a single task done. Similarly, there are three ways to apply filter in Excel.
- The first way is to simply go to the Home tab and click on the filter button under the Sort and Filter dropdown button of the Editing group.
- You can also find the Filter option in the Data tab, under the Sort and Filter group.
- Lastly, you can just use the keyboard shortcut to bring up the Filter option or to make it disappear: Ctrl+Shift+L
Ways to Apply Filter in Excel
Now that we’ve gone trough thew ways for you to add the filter, now it’s time to explain how to apply it. First of all, if you see this drop-down arrow in the column heading, it implies that the filter has been added.
Now, if you hover around the arrow icon, you’ll see a screen dip that says Showing All. So, if you want to filter the data in a given column, follow the steps below:
- Within the column that you want to filter the data in, click on the dropdown arrow.
- To deselect all the data, you’ll have to uncheck the Select All box.
- In the next step, you have to select all the data you want to be displayed.
- Then click Ok.
An example is shown below where we only want to keep the Delivered articles on display.
Now that it’s done, only the selected Delivered articles will be shown. Here’s another thing. If you hover the mouse cursor over the filter applied column’s Filter Icon , it’ll show the filters that have been applied.
Applying Filter in Multiple Columns
Since we’re talking about applying filters, let’s discuss the procedure of applying filters in multiple columns. All you have to do is, repeat the steps that I’ve mentioned above and that should be enough. This’ll narrow the data down to exactly what you want to see.
Suppose, you only want to see the articles that John delivered. After applying the filter, the results should look like the image below:
Note that the filter window can be dragged to increase or decrease the size according to your needs. All you have to do is, hover over the grip handle at the bottom of the window. As soon as the double headed arrow appears, drag it as you please.
Some Additional Tips
We’ve already gone through the basics. Now it’s time to go into the advanced functionalities that the AutoFilter has to offer. But before that, let’s give you some tips that’ll help you out in the long run.
- The first thing you should note is that all Excel Filters are mutually exclusive. Suppose, a single column can be filtered by a set value or a type of color. However, you can’t do both at the same time.
- Suppose, a column contains various types of values. If you mix different value types in the filter, the filter will only be added for the data set that occurs the most.
- For example, if you add Text Filters and Number Filters at the same time, and the numbers are formatted as text, only the Text Filter will appear for the column. Not the Number Filter.
Still not clear about the concept? Not to worry, we’re about to go into the details.
Filtering Text Data
In a particular scenario when you want to filter a text column for very specific results, you can use the advantages offered by the Excel Text Filter. This includes a number of advanced options that include:
- You can Filter cells that begin or end with a specific character or a number of characters.
- You can Filter the cells that contain or do not contain a given character set or a specific word in the text.
- You can even filter cells that are precisely equal or not equal to a specified character or characters.
Now, let’s show you how it works in practice. When you add filter to a column that contains text values, The Text Filters options should appear automatically in the dropdown menu of the AutoFilter.
For example, you want to filter out the rows that contain the word Tech. Here’s how to do it.
- Bring down the dropdown menu by clicking on the arrow icon (In the heading where you placed the AutoFilter).
- Now, within the Text Filter option, select Does Not Contain.
- Once you’ve done that, the custom AutoFilter Dialog box will show up.
- In the box that’s located in the right of the filter, you’ll have to type the desired text from the dropdown list. In this case, “Tech”.
- Then, click OK to see the desired results.
Filtering a Column with Two Criteria
If you want to set two criteria in your Filter, then you have to execute the steps we’ve explained above. Then, you’ll want to do the following:
- Check the And or Or option depending on whether you need both or either criterion to be true.
- Select the given comparison operator you want for the second criterion.
- Once that’s done, enter the desired text value within the box to the right.
The image above shows how you can filter out the rows containing Tech and Travel.
Filtering Numbers in Excel
The Numbers Filter option is also available in the AutoFilter feature. Here you’ll find multiple ways to manipulate the numeric data. Let’s discuss:
- You can Filter numbers that are equal to or not equal to a certain number.
- You can Filter numbers that are greater than, less than or between the specified number or numbers.
- You can even Filter top 10 or bottom 10 numbers.
- It’s also possible to Filter cells that are above average or below average.
As you can see in the screenshot above, there are a whole bunch of Number Filter options in Excel.
Suppose, if you want to create a filter that shows only the wordcounts between 15,000 and 30,000, you just have to follow the procedure shown below:
- Just like before, you’ll have to click on the filter arrow on the column heading that contains numbers and select Number Filters.
- The next thing you’ll want to do is, choose a comparison operator from the list that’ll appear.
- In this case, you’ll want to select Between…
- Once the Custom AutoFilter dialog box appears, you’ll have to enter the lower and upper bound values.
- By default, Excel will suggest Greater than or equal to, and Less than or equal to comparison operators.
- If you don’t want the Threshold values to be included, you can just change them to Greater than and Less than.
- Once you’ve set the values, click OK.
- After doing that, you should be able to see the desired results.
Filtering Dates in Excel
If you want to filter time records for a certain time period fast and easy, Excel Date filters provide a great variety of choices.
There are somethings you should know beforehand:
- Excel AutoFilter groups all dates by default in a column by the hierarchy of years, months, and days.
- Different levels can be expanded by clicking on the plus, or minus signs that are placed next to a given group.
- If you select, or clear a higher level group, it selects or clears all nested levels.
- The Date Filters give you the option to display or hide a specified data for a year, quarter, month, week, day, before or after a particular date, or in between two dates.
The image shows all the available options there are within the Excel Date Filter.
The date filter options can be executed really quick. If you want to filter the rows for the current month, all you have to do is, point to Date Filters and click on This Month. Then of course, there is the Equals, Before, After, Between operator or Custom Filter.
Clicking on these will bring up the Custom AutoFilter dialog box for you to specify the desired criteria.
Suppose, you want to only keep the dates between the first 20 days of December. In that case, you’ll have to click on between and configure the filter accordingly.
Then all you have to do is, click OK.
Filtering by Color in Excel
In certain cases, the data in your worksheet can be formatted manually or by conditional formatting. It’s possible to filter the set of data through color. All you have to do is, click on the AutoFilter dropdown arrow and click on Filter by Color. This will then bring out a couple of options.
This of course depends on what type of formatting is applied to the specific column. You can either Filter by:
- Cell Color
- Font Color
- Cell Icon
Suppose, if you’ve formatted the cells in a particular column with three distinct colors and you want to only see the orange cells, here’s how to do it:
- Just like before, click on the filter arrow located in the hear cell.
- The next step is to point to Filter by Color.
- Then, select the desired color.
That’s pretty much it. Once you click Ok, only the rows with the selected color will be visible.
Filtering in Excel with Search Option
Here’s some FYI. Starting with the Excel 2021 version, the Filter interface has facilitated the search box. This particularly comes in handy while dealing with large datasets. This allows the user to quickly filter rows that contain specific, texts, numbers, or dates.
All you have to do here is:
- Click on the dropdown arrow of the Filter tool, start type in what you’re looking for in the search box.
- Excel Filter will show you immediately all the items that match the search.
- Once you’re done typing, just click on OK to finalize the search.
- And voila! The desired rows will be displayed.
Just in case you want to filter multiple searches, apply the steps that have been described above. Then you’ll want to type in the second term just as soon as the results appear. After that you’ll have to check the “Add current selection to filter” box.
Then all you have to do is, click OK.
Reapplying Filter after Changing the Data
Here’s something you might find useful. Suppose, you’ve edited or deleted data in the cells where you’ve applied the filters. Excel AutoFilter doesn’t update itself based on the changes made. To Re-apply the filter, all you have to do is:
- Go under the Data tab and click Reapply under the Sort & Filter group.
- Or, Click on Sort & Filter and select Reapply from the dropdown menu in the Editing group of the Home Tab.
Filtering Blank/ Non-Blank Cells
In order to filter data in Excel skipping blank/ non-blank cells, you can do the following:
If you want to filter out the blank cells and show only the non-blank cells, click the AutoFilter arrow. Here you’ll have to ensure that the select all option is checked and the Blanks option is unchecked. And this will display the cells that have any value within them.
To display blank cells, all you have to do is, uncheck Select All and check the Blanks option. Once it’s done, you’ll be able to see the rows with an empty cell in the given column.
Final Words
The Excel Filter option is really a handy tool that can be used in multiple scenarios. Be it at your office, in your school homework tasks, or some financial management, it can do wonders and save a whole lot of time.
Accessing the Filter tool has more than one pathway and there are multiple ways to apply them depending on the circumstances. So, I hope this article helped you find out how to add filter in Excel. Farewell for now.
Helpful Links: