Excel filter by column refers to displaying only the rows that match a certain criterion while hiding the other rows. Filtering allows you to limit the data in a display so you can focus on the important stuff.
This feature may seem not so useful though, but if your data contains a gazillion rows, filtering becomes just indispensable. Below are some instances where filtering is especially useful:
- To show only important piece of information saving you hours of headaches.
- To print a report that shows only customers who live in a particular location or city.
- To calculate information, like sums and averages, for some specific group of products.
How to filter by column in Excel
There are several techniques to filter data in Excel. In this tutorial, I’m going to explain them all.
But before I begin, Excel filter by column works with both data in tables (structured tables) and ordinary worksheet data. These techniques, therefore, apply to any kind of data (whether in a table form or not).
How to add drop down filter in Excel Column Heads
Well, filtering uses the drop-down column headings at the top of your table or data. When you click the drop-down arrow, you’ll see a list of all the filter options for that column.
Excel displays the drop-down filter automatically when you create a table for your data. However, it can be turned off unless you turn it back on.
Also, ordinary worksheet data can be filtered with the help of the drop-down headings at the top of the data. But it needs to be turned on first.
Whether you have a table without the drop-down filter, or just an ordinary worksheet data, to add the drop-down filter, see below:
- Go to Data→Filter (Under the Sort & Filter section).
- Click the Filter icon to toggle the drop-down headings on or off.
See screenshot:
How Excel filter data based on cell value
When you click the drop-down arrow, you will see a list of all the distinct values in the column by which you can filter.
- To create a filter, uncheck the Select All checkbox, and select the desired value by which you wish to filter. Then press OK to apply the filter.
For example, in the table below, using the region column lets filter out West only.
Now see the results below:
As seen in the screenshot above, the data has been filtered to display records related to only one region (i.e. West). However, you can filter by multiple values in a column by checking multiple items.
For example, to filter the data to show only the West and South regions, place a checkmark next to both items in the drop-down list.
See screenshot:
Now see the results below:
The table is now filtered to display data related to two regions, South and West.
Bonus Tips: When data is filtered in excel, some of the row numbers will be gone hidden. For instance, in the above example, you notice that the row numbers jumped from 3 to 6 then to 12, indicating that all the rows in between are hidden. These rows are hidden because they contain data we don’t need to display at the moment. Also, the drop-down filter button shows a different icon to indicate that the column is filtered.
See screenshot:
Excel Advanced Filter
Excel has more filtering features that can really help you do more advanced filtering. Depending on the type of data in a column (whether text, numbers or date values), you will get a range of useful filter options when you click the drop-down filter button. Let’s see how it all works.
Excel Date Filter
Excel can help you filter dates that fall between two dates, before or after another date, or use periods like last week, next week, this month, next quarter, this year, and so on.
Consider the following example:
In this example, the date column contains dates ranging from January to April. Now let’s learn how to filter dates in excel:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Click the drop-down column heading for the date column. The filter menu appears.
- Click Date Filters to display the date filtering options.
See screenshot:
Excel Number Filter
Excel Number Filtering options include the following:
- Equals: Numbers that match exactly.
- Does Not Equal: Numbers that does not match exactly.
- Greater Than Or Equal To: Numbers that are greater than or equal to another number.
- Less Than: Numbers that are smaller than a specific number
- Between: Numbers that fall between two specific numbers
- And so on…
To use Number Filters:
- Click in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Number Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Text Filter
Apart from the Dates and Number filter, you can also filter text that matches exactly or contains a particular piece of text.
Just like filtering dates and numbers, to filter text follow the steps below:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Text Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Filter by Color
Filter by color is yet another awesome filtering feature in Excel that allows you to filter out data, based on either the cell background color or the cell font color.
With no font or background colors in a column, it is impossible to filter by color. You can only use this feature if you have some colors in your data.
To filter a column by color just follow these steps:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Filter by Color. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
When you choose to filter by color, Excel will scan the entire column in search for colors by which you can filter. Selecting a particular color will filter the column to show rows with that color alone.
See screenshot:
Excel filter by column Shortcut
Excel drop-down filters are very handy when it comes to filtering data; but there is a little faster way to it – the right-click menu.
But how simple is this technique?
Find a cell that contains the value you want to filter, make some three mouse clicks and the data is filtered to the selected value. Nothing could be simpler.
To filter by this shortcut:
- Right-click a value by which you want to filter
- Select Filter, the filtering options will appear.
- Select Filter by Selected Cell’s Value.
With this technique, you can also filter by the cell’s Color or Font Color.
See screenshot:
Excel filter by column refers to displaying only the rows that match a certain criterion while hiding the other rows. Filtering allows you to limit the data in a display so you can focus on the important stuff.
This feature may seem not so useful though, but if your data contains a gazillion rows, filtering becomes just indispensable. Below are some instances where filtering is especially useful:
To show only important pieces of information saving you hours of headaches.
- To print a report that shows only customers who live in a particular location or city.
- To calculate information, like sums and averages, for some specific group of products.
How to filter in excel
There are several techniques to filter data in Excel. In this tutorial, I’m going to explain them all.
But before I begin, Excel filter by column works with both data in tables (structured tables) and ordinary worksheet data. These techniques, therefore, apply to any kind of data (whether in a table form or not).
how to add drop down filter in excel
Well, filtering uses the drop-down column headings at the top of your table or data. When you click the drop-down arrow, you’ll see a list of all the filter options for that column.
Excel displays the drop-down filter automatically when you create a table for your data. However, it can be turned off unless you turn it back on.
Also, ordinary worksheet data can be filtered with the help of the drop-down headings at the top of the data. But it needs to be turned on first. Whether you have a table without the drop-down filter, or just an ordinary worksheet data, to add the drop-down filter, see below:
- Go to Data→Filter (Under the Sort & Filter section).
- Click the Filter icon to toggle the drop-down headings on or off.
See screenshot:
How does Excel filter data based on cell value
When you click the drop-down arrow, you will see a list of all the distinct values in the column by which you can filter.- To create a filter, uncheck the Select All checkbox, and select the desired value by which you wish to filter. Then press OK to apply the filter.
For example, in the table below, using the region column lets filter out West only
As seen in the screenshot above, the data has been filtered to display records related to only one region (i.e. West). However, you can filter by multiple values in a column by checking multiple items.
For example, to filter the data to show only West and South regions, place a checkmark next to both items in the drop-down list.
See screenshot:
Now see the results below:
The table is now filtered to display data related to two regions, South and West.
Bonus Tips: When data is filtered in excel, some of the row numbers will be gone hidden. For instance, in the above example, you notice that the row numbers jumped from 3 to 6 then to 12, indicating that all the rows in between are hidden. These rows are hidden because they contain data we don’t need to display at the moment. Also, the drop-down filter button shows a different icon to indicate that the column is filtered.
See screenshot:
Excel Advanced Filter
Excel has more filtering features that can really help you do more advanced filtering. Depending on the type of data in a column (whether text, numbers, or date values), you will get a range of useful filter options when you click the drop-down filter button. Let’s see how it all works.
Excel Date Filter
Excel can help you filter dates that fall between two dates, before or after another date, or use periods like last week, next week, this month, next quarter, this year, and so on.Consider the following example:
In this example, the date column contains dates ranging from January to April. Now let’s learn how to filter dates in excel:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Click the drop-down column heading for the date column. The filter menu appears.
- Click Date Filters to display the date filtering options.
See screenshot:
Excel Number Filter
Excel Number Filtering options include the following:
- Equals: Numbers that match exactly.
- Does Not Equal: Numbers that does not match exactly.
- Greater Than Or Equal To: Numbers that are greater than or equal to another number.
- Less Than: Numbers that are smaller than a specific number
- Between: Numbers that fall between two specific numbers
- And so on…
To use Number Filters:
- Click in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Number Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Text Filter
Apart from Dates and Number filter, you can also filter text that matches exactly or contains a particular piece of text.
Just like filtering dates and numbers, to filter text follow the steps below:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Text Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Filter by color
Filter by color is yet another awesome filtering feature in Excel that allows you to filter out data, based on either the cell background color or the cell font color.
With no font or background colors in a column, it is impossible to filter by color. You can only use this feature if you have some colors in your data.
To filter a column by color just follow these steps:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Filter by Color. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
When you choose to filter by color, Excel will scan the entire column in search for colors by which you can filter. Selecting a particular color will filter the column to show rows with that color alone.
See screenshot:
Excel filter Shortcut
Excel drop-down filters are very handy when it comes to filtering data; but there is a little faster way to it – the right-click menu.
But how simple is this technique?
Find a cell that contains the value you want to filter, make some three mouse clicks and the data is filtered to the selected value. Nothing could be simpler.
To filter by this shortcut:
- Right-click a value by which you want to filter
- Select Filter, the filtering options will appear.
- Select Filter by Selected Cell’s Value.
With this technique, you can also filter by the cell’s Color or Font Color.
See screenshot:
Excel filter refers to displaying only the rows that match a certain criterion while hiding the other rows. Filtering allows you to limit the data in a display so you can focus on the important stuff.
This feature may seem not so useful though, but if your data contains a gazillion rows, filtering becomes just indispensable. Below are some instances where filtering is especially useful:
- To show only important piece of information saving you hours of headaches.
- To print a report that shows only customers who live in a particular location or city.
- To calculate information, like sums and averages, for some specific group of products.
How to filter in excel
There are several techniques to filter data in Excel. In this tutorial, I’m going to explain them all.
But before I begin, Excel filter works with both data in tables (structured tables) and ordinary worksheet data. These techniques, therefore, apply to any kind of data (whether in a table form or not).
how to add drop down filter in excel
Well, filtering uses the drop-down column headings at the top of your table or data. When you click the drop-down arrow, you’ll see a list of all the filter options for that column.
Excel displays the drop-down filter automatically when you create a table for your data. However, it can be turned off unless you turn it back on.
Also, an ordinary worksheet data can be filtered with the help of the drop-down headings at the top of the data. But it needs to be turned on first.
Whether you have a table without the drop-down filter, or just an ordinary worksheet data, to add the drop-down filter, see below:
- Go to Data→Filter (Under the Sort & Filter section).
- Click the Filter icon to toggle the drop-down headings on or off.
See screenshot:
How Excel filter data based on cell value
When you click the drop-down arrow, you will see a list of all the distinct values in the column by which you can filter.
- To create a filter, uncheck the Select All checkbox, and select the desired value by which you wish to filter. Then press OK to apply the filter.
For example, in the table below, using the region column lets filter out West only.
Now see the results below:
As seen in the screenshot above, the data has been filtered to display records related to only one region (i.e. West). However, you can filter by multiple values in a column by checking multiple items.
For example, to filter the data to show only West and South region, place a check mark next to both items in the drop-down list.
See screenshot:
Now see the results below:
The table is now filtered to display data related to two regions, South and West.
Bonus Tips: When data is filtered in excel, some of the row numbers will be gone hidden. For instance, in the above example, you notice that the row numbers jumped from 3 to 6 then to 12, indicating that all the rows in between are hidden. These rows are hidden because they contain data we don’t need to display at the moment. Also, the drop-down filter button shows a different icon to indicate that the column is filtered.
See screenshot:
Excel Advanced Filter
Excel has more filtering features that can really help you do more advanced filtering. Depending on the type of data in a column (whether text, numbers or date values), you will get a range of useful filter options when you click the drop-down filter button. Let’s see how it all works.
Excel Date Filter
Excel can help you filter dates that fall between two dates, before or after another date, or use periods like last week, next week, this month, next quarter, this year and so on.
Consider the following example:
In this example, the date column contains dates ranging from January to April. Now let’s learn how to filter dates in excel:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Click the drop-down column heading for the date column. The filter menu appears.
- Click Date Filters to display the date filtering options.
See screenshot:
Excel Number Filter
Excel Number Filtering options include the following:
- Equals: Numbers that match exactly.
- Does Not Equal: Numbers that does not match exactly.
- Greater Than Or Equal To: Numbers that are greater than or equal to another number.
- Less Than: Numbers that are smaller than a specific number
- Between: Numbers that fall between two specific numbers
- And so on…
To use Number Filters:
- Click in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Number Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Text Filter
Apart from Dates and Number filter, you can also filter text that matches exactly or contains a particular piece of text.
Just like filtering dates and numbers, to filter text follow the steps below:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Text Filters. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
Excel Filter by color
Filter by color is yet another awesome filtering feature in Excel that allows you to filter out data, based on either the cell background color or the cell font color.
With no font or background colors in a column, it is impossible to filter by color. You can only use this feature if you have some colors in your data.
To filter a column by color just follow these steps:
- Click anywhere in the dataset and press Ctrl+Shift+L to display the filter drop-down buttons.
- Open the drop-down column list
- Click Filter by Color. The filter options menu appears.
- Choose one of the filter options to filter.
See screenshot:
When you choose to filter by color, Excel will scan the entire column in search for colors by which you can filter. Selecting a particular color will filter the column to show rows with that color alone.
See screenshot:
Excel drop-down filters are very handy when it comes to filtering data; but there is a little faster way to it – the right-click menu.
But how simple is this technique?
Find a cell that contains the value you want to filter, make some three mouse clicks and the data is filtered to the selected value. Nothing could be simpler.
To filter by this shortcut:
- Right-click a value by which you want to filter
- Select Filter, the filtering options will appear.
- Select Filter by Selected Cell’s Value.
With this technique, you can also filter by the cell’s Color or Font Color.
See screenshot: