Excel shortcuts

How to Filter Data in Excel: The Ultimate Guide

Excel is a powerful tool for data analysis, but it can be tricky to know how to filter data in Excel to get the results you need. In this ultimate guide, we'll show you how to filter data in Excel so that you can focus on the information that's most important to you.

We'll start with a basic overview of how to filter data in Excel, including how to use the built-in filter options and how to create your own custom filters. Then we'll dive into some more advanced filtering techniques, including how to filter by multiple criteria, how to use wildcards for more flexible filtering, and how to use advanced filter options to really fine-tune your data analysis.

By the end of this guide, you'll be an expert at filtering data in Excel and you'll be able to quickly and easily find the information you need to make better decisions.

How to Filter Data in Excel

Excel makes it easy to filter data using a variety of built-in options. To filter data in Excel, first select the data you want to filter. Then click the Data tab on the ribbon and click the Filter button in the Sort & Filter group.

This will insert drop-down arrows next to each column header. To filter by a specific value, simply click the drop-down arrow and select the value you want to include in the filter. For example, if you want to see only the rows where the Status column is "Open", you would click the drop-down arrow next to the Status column and select "Open" from the list.

You can also use the filter options to filter by multiple criteria. To do this, simply click the drop-down arrow next to the first column you want to filter and select the first criterion. Then click the drop-down arrow next to the second column you want to filter and select the second criterion. Excel will automatically apply an "AND" filter and will only include rows that meet both criteria.

If you want to use an "OR" filter, you can do this by holding down the Ctrl key while you click the drop-down arrows for each column you want to filter. This will apply an "OR" filter and will include rows that meet any of the criteria you've selected.

You can also use the filter options to filter by a specific date range. To do this, click the drop-down arrow next to the column you want to filter and select "Date Filters" from the list. Then select the "Between" option and enter the start and end dates for the date range you want to filter by.

There are a few other built-in filter options that can be useful in specific situations. For example, you can use the "Top 10" filter to only include the top 10 values in a column, or the "Bottom 10" filter to only include the bottom 10 values in a column. You can also use the "Custom" filter option to filter by a specific value or by a specific date range.

To remove a filter, simply click the Data tab on the ribbon and click the Filter button again. This will remove the drop-down arrows from the column headers and will show all of the data again.

How to Create a Custom Filter in Excel

In addition to the built-in filter options, you can also create your own custom filters in Excel. To do this, click the Data tab on the ribbon and click the Filter button. Then click the "Custom" option from the list.

This will open the "Custom AutoFilter" dialog box. In the first drop-down menu, you can select the column you want to filter. In the second drop-down menu, you can select the operator you want to use. The available operators will depend on the data type of the column you're filtering.

For example, if you're filtering a column of numbers, you can use the "Greater than" or "Less than" operators. If you're filtering a column of text, you can use the "Begins with", "Ends with", or "Contains" operators.

In the third field, you can enter the value you want to filter by. For example, if you're filtering a column of numbers and you want to only see values that are greater than 100, you would enter "100" in this field.

You can also use the "Custom" filter option to filter by multiple criteria. To do this, simply click the "Add to Filter" button and enter the second criterion in the new row that appears. You can add as many criteria as you want.

Excel will automatically apply an "AND" filter and will only include rows that meet all of the criteria you've entered. If you want to use an "OR" filter, you can do this by holding down the Ctrl key while you click the "Add to Filter" button. This will apply an "OR" filter and will include rows that meet any of the criteria you've entered.

When you're finished adding criteria, click the "OK" button to apply the filter. To remove a custom filter, click the Data tab on the ribbon and click the Filter button. Then click the "Clear" button.

How to Use Wildcards for More Flexible Filtering

Wildcards are a powerful tool that can be used for more flexible filtering in Excel. Wildcards are special characters that can be used to represent one or more other characters. The two most commonly used wildcards are the asterisk (*) and the question mark (?).

The asterisk wildcard can be used to represent any number of characters. For example, if you want to filter a column of data for all values that start with "A", you would enter "A*" in the filter criteria field. This would match all values that start with "A", including "A", "Aardvark", and "Abcdefg".

The question mark wildcard can be used to represent any single character. For example, if you want to filter a column of data for all three-letter words that start with "A", you would enter "A???" in the filter criteria field. This would match all three-letter words that start with "A", including "Abe", "Ace", and "Art".

You can also use the asterisk and question mark wildcards together. For example, if you want to filter a column of data for all four-letter words that start with "A", you would enter "A???" in the filter criteria field. This would match all four-letter words that start with "A", including "Abe", "Ace", "Art", and "Axel".

Wildcards can be used with any of the built-in filter options or with custom filters. To use wildcards, simply enter the wildcard characters in the filter criteria field. Excel will automatically interpret the wildcards and will apply the filter accordingly.

How to Use Advanced Filter Options

In addition to the built-in filter options and the ability to create custom filters, Excel also provides a few advanced filter options that can be useful in specific situations. To access the advanced filter options, click the Data tab on the ribbon and click the Filter button. Then click the "Advanced" option from the list.

This will open the "Advanced Filter" dialog box. In the "List range" field, you need to enter the range of cells that contains the data you want to filter. This can be a single column or a range of columns.

In the "Criteria range" field, you need to enter the range of cells that contains the filter criteria. This must be a single column or a range of adjacent columns. The first row of this range should contain the column headers from the "List range" field.

You have a few options for how the filter criteria is applied. The "Filter the list, in-place" option will filter the data in the "List range" and will only include rows that meet the criteria. The "Copy to another location" option will copy the data in the "List range" to another location, and will only include rows that meet the criteria. The "List range" and "Criteria range" fields will be blank if you select this option.

If you want to use an "OR" filter, you can do this by holding down the Ctrl key while you click the "Add to Filter" button. This will apply an "OR" filter and will include rows that meet any of the criteria you've entered.

When you're finished adding criteria, click the "OK" button to apply the filter. To remove an advanced filter, click the Data tab on the ribbon and click the Filter button. Then click the "Clear" button.

How to Use Filters in Pivot Tables

Pivot tables are a powerful tool for data analysis, and they can be a great way to filter data in Excel. Pivot tables allow you to quickly and easily summarize large data sets and to find patterns and trends in your data.

To create a pivot table, click the Insert tab on the ribbon and click the PivotTable button.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.