Filtering values in Excel is a fun thing to do. You can apply multiple filters on the dataset to make it presentable according to your needs. Applying multiple filters in one cell by using Excel formulas is an important thing. How to filter multiple values in Excel, do you know?
Let’s find out the simple procedure to perform this function:
Easy Ways to Filter Multiple Values in Excel
Suppose you have a large dataset including sales representatives. In Columns C and D, your data contains values of Products and the Revenue Earned by the sales representative.
Now, you are supposed to filter multiple values in one cell with Filter Command, Advanced Filter Command, etc.
How to Filter Multiple Values in Excel at Once with Advanced Filter Command?
In this process, you will learn how the Advanced Filter command can help in this function. For this, you need to follow the steps given below:
- First, you have to select the cell range from the Data tab.
- Open the Data tab, click on the Sort & Filter option, and choose Advanced.
- The Advanced Filter dialog box will pop up when you click on the Advanced option. From this box, select Filter the list in place located under the Action.
- In the list range box field, enter the cell range from the dataset, which is $B$4:$D$14.
- Now, select $F$4:$F$5 in the Criteria range box.
- Press OK.
Below you can see the multiple values in one cell have been filtered.
How to Filter Multiple Values in Excel with FILTER Function?
Now, let’s learn how the FILTER option can filter multiple values in Excel. For this, let’s follow the steps given below:
- First, make a data table with similar headers as the original data.
- Select cell F5.
- Enter the following formula in the cell:
=FILTER(B4:D14, ISNUMBER(MATCH(B4:B14, {“Joe”}, 0)), “Not Found”)
Now, press the ENTER key from the keyboard and the required outcome will be in front of you.
How to Filter Multiple Values in Excel with the COUNTIF Function?
Now, let’s find out how the COUNTIF function can help us filter multiple values in Excel. For this, let’s follow the steps given here:
- First, choose cell E5 and enter the following formula:
=COUNTIF(B5:D14, B5)
- Press the ENTER key from the keyboard and the COUNTIF function displays 2 as the output.
- In column E, AutoFill the COUNITF function to the remaining cells.
- Press CTRL + SHIFT + L from the keyboard. It will make a drop-down list.
- In each column, you will notice a filter drop-down list in the header.
- Now, click on the filter drop-down beside the Remark and a new window will open up.
- From this window, check 2 and then press OK.
- Same as above, you can now filter Philip’s data from the dataset.
How to Filter Multiple Values in Excel with Filter Command?
With the Filter command, you can filter Austin’s data in this method. With this time-saving approach, you can follow the steps to filter multiple values in Excel:
- First, select cell range B4 to D14.
- Once the cell range is selected, open the Data tab and then choose the Sort & Filter option.
- Click on the Filter menu.
- A drop-down will pop up in the header of each column.
- Beside Name, click on the filter drop-down and a new window will open up.
- From that window check Austin and then press OK.
Once the above steps are completed, you can now filter Austin’s data from the dataset.
Summary:
In this post, you have learned how to filter multiple values in Excel. You can Excel filter multiple values in one cell with the help of the FILTER Function, COUNTIF function, FILTER Command, and Advanced FILTER Command. All these 4 methods are worthy enough to try at least once so that you can understand which method can help you learn how to select multiple values in filter Excel.
