In this lesson, you can learn how to use filters in Excel.
Excel has a feature of filters that make it easy to find the data you need in your spreadsheets. Filters allow you to view and optionally print only selected data from the table, and perform mathematical operations, taking only the visible data.
In this article, we will show you how to use filters in Excel. We will start by explaining the different types of filters available in Excel, and then we will show you how to use them to filter data in a table.
Types of Filters in Excel
There are two main types of filters in Excel: automatic filters and advanced filters.
- Automatic filters are the most common type of filter. They allow you to filter data based on the values in a column. For example, you could use an automatic filter to show only the products that are in the “Category1” category.
- Advanced filters allow you to filter data based on more complex criteria or conditions. For example, you could use an advanced filter to show only the products that are in the “Category1” category and that have a price of less than $100.
How to Use Filters in Excel
To use filters in Excel, you first need to select the range of cells that you want to filter. Then, you can use the following steps:
- Click on the Data tab.
- In the Sort & Filter group, click on the Filter button.
- A drop-down menu will appear. Select the type of filter that you want to use.
- The filtered data will be displayed in the table.
Example – easy filtering by category in Excel
You will use a table of products which filters them by category.
Select the table header row.
Go to the Ribbon. In the Home tab, click the Sort & Filter icon and select Filter from the list.
In the table appeared some buttons to filter the data.
Click on the Categories. In the list, uncheck the category which you want to be hidden (filtered), such as Category2.
After applying a filter, you can’t see products from Category2.
On the picture above, you can also see that some rows are hidden. You don’t see 3 and 10 row. In the B1 cell, you can also see a symbol of filter close to the arrow. That means that this column is filtered.
If you want to filter more, you can. Let’s hide products from Producer3 and Producer5.
You can also filter prices. There’s number filters. Click filter arrow in D1 cell.
I want to find the cheapest. I select Less Than Or Equal To.
My budget is $150.
That’s how I filtered out products which I don’t want in my table.
Now it is much easier to choose one.
Note that you can also use advanced filters in Excel to filter data based on complex criteria or conditions. To do this, you’ll need to use the “Advanced Filter” option in the “Sort & Filter” group.
Examples of Using Filters in Excel
Here are some examples of how you can use filters in Excel:
- To filter data by category: Select the range of cells that you want to filter, and then click on the Filter button. In the drop-down menu, select Categories. A list of the categories will appear. Uncheck the categories that you don’t want to see.
- To filter data by price: Select the range of cells that you want to filter, and then click on the Filter button. In the drop-down menu, select Number Filters. Select the Less Than Or Equal To option, and then enter the price that you want to filter by.
- To filter data by multiple criteria: You can use the Advanced Filter option to filter data by multiple criteria. For example, you could use the Advanced Filter to show only the products that are in the “Category1” category and that have a price of less than $100.
Filters are a powerful tool that can help you find the data you need in your spreadsheets. By using filters, you can save time and effort, and you can make your data more manageable.
Here are some additional tips for using filters in Excel:
- Use descriptive names for your filters. This will make it easier to remember what each filter does.
- Save your filters. This will allow you to reuse them later.
- Use filters to create reports. This can help you summarize your data and make it easier to understand.
By following these tips, you can use filters to get the most out of your Excel spreadsheets.