Filtering data in excel means seeing limited data according to your choice to make data analysis very fast and efficient. That means to filter out the data which you’d not like to see and retain only those data on screen that you’d like to analyze.
This doesn’t mean that filtering will delete your filtered out data. It lives happily in the background till you remove the data filters.
When you have tons and tons of data in your worksheet, it will be very tedious work to look out for a certain data or a certain category of information in your excel sheet.
To tackle these problems, we will now talk about how to filter data in MS-Excel?
We will learn about two types of filtering techniques namely:
- Auto Filter
- Advanced Data Filter
Let’s Start.
Auto Filtering Data in Excel:
Excel has an inbuilt feature of Filter. Excel automatically analyzes the data in your worksheet and categorizes your data based on different parameters found inside your worksheet.
To apply an auto filter.
Click on any table header of the database you want to filter.
Then navigate to the Data tab and under the Sort and filter category click in Filter.
To demonstrate this let us take an example.
Suppose we have the following data
Now:
- Click on any table header.
- Go to Data tab present on the ribbon.
- Click on Filter button under the Filter and Sort category
You’ll notice that the table headers will now have a drop-down arrow.
Now, we can filter out our relevant data by clicking on these drop-down arrows and selecting the required options. Suppose with respect to the example data we have provided, we want to display only the product which belongs to the notebook category.
To do this simply click on the arrow besides the Product column->
On the drop-down menu,
Uncheck the Select All option then check the Notebook option and click on OK.
You can see that we have filtered out our data to show only the product details of the Notebook product.
You can further filter out data by choosing the store names or you can filter the data by date of purchase.
Here we have again filtered more data, this time by store names. Only the store with name S1’s details are shown.
To do this follow the same steps discussed above and uncheck the select all button and then check your preferred data to show.
Text Filters
You can also apply text filters to show data starting with a certain text or ending with some other text.
You can also specify exactly for what text it should filter out the data by using conditional filtering in the text options.
Suppose we want to view the product details of the items located in Mumbai as well as Bangalore. To do this operation we can use conditional text formatting. Let us get started to implement this with the following data.
Steps:
- Click on the drop–down arrow besides the city header field of the table.
- Select the Text-filters option from the menu and then click on Equals to.
- A Custom AutoFilter dialog box will appear
In this box, choose the condition, which in our case is equals and then enter the city name you want to match the condition with.
Also, to display another city we will choose the “OR” option, which means either display this or that. And then enter the second city name to be displayed.
Then click on OK.
Hurray! We have successfully filtered out the relevant data.
In this way we can apply text filtering, which has many other options you can try on to get the exact results which your work needs.
Also, we now know how to use conditionals while filtering out data.
Note: If you are trying to filter data in Numerical fields, you’ll find the Number filters option in the drop-down menu and can perform numerical filters as well as conditional numerical filters like we had done in our examples for text filters.
Let us now learn about some advanced filtering techniques of Excel.
Advanced Filtering in Excel
If we couldn’t find a filtering technique beneficial to our particular need under the Auto-Filter options. We can always look forward to advanced filtering in Excel to get the most out of filtering.
Proceeding with the below dataset:
Suppose we want to filter out only those products which are in Mumbai region and has more than 3 quantity sales using advanced filtering.
To do this operation follow the below steps:
- Copy and paste the cell headers into a separate position in the worksheet.
- Provide condition below the headers to clarify your advanced filter criteria conditions.
Specify the required criteria according to your needs. Here we have specified our city to be only Mumbai where sold quantity is greater than 3.
- Now select the entire data set to filter the details from.
You should also include the headers of the data-set.
Like this:
- Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box.
- In the dialog box give the following details
The list range is the selection you made.
To specify the criteria range, click on the 
- If you want to copy your filtered data to another location, click on the radio button Copy to another location and then specify the location in Copy to: field by clicking on the arrow button and then select the cell location where you want to copy the filter data.
If you want to filter only the unique records, check the Unique records only option.
In case you just want to replace your actual data with the filter data, just click on Filter the list, in-place radio button. Then no need to provide the Copy to information.
In our example we are copying the data to the specified location given on the above picture.
- After selecting each range, press enter to go back to the dialog box.
- Now click on OK
Voila! We have filtered out the relevant data using advanced data filtering.
Now only that detail is filtered where the city is Mumbai and the Quantity is greater than 8.
In this way you can specify your own criteria and conditions to filter data using advanced data filtering option in Excel.
Have a look at the final worksheet we end up with:
I hope you have learned a lot in this tutorial of Filtering techniques in Microsoft Excel.
Keep Learning!
