How to Filter Multiple Values in One Cell in Excel (4 Methods)

While working with large Microsoft Excel, sometimes we need to filter multiple values in one cell. Filtering data is more important in Excel. We can easily filter multiple values in one cell in Excel by using Excel formulas. This is an easy and time-saving task also. Today, in this article, we’ll learn four quick and suitable ways to filter multiple values in one cell in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Suitable Ways to Filter Multiple Values in One Cell in Excel

Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the Products and the Revenue Earned by the sales representatives are given in Columns C, and D respectively. We will filter multiple values in one cell in Excel using the Filter Command, Advanced Filter Command, the COUNTIF function, and the FILTER function. Here’s an overview of the dataset for today’s task.

excel filter multiple values in one cell


1. Apply Filter Command to Filter Multiple Values in Excel

In Microsoft Excel, a Filter command is a powerful tool to filter data. From our dataset, we will filter Austin’s information using the Filter command. This is an easy and time-saving way also. Let’s follow the instructions below to filter multiple values in one cell!

Step 1:

  • First of all, select cells array B4 to D14.

Apply Filter Command to Filter Multiple Values in One Cell in Excel

  • After selecting the cells array, from your Data tab, go to,

Data → Sort & Filter → Filter

  • As a result, a filter drop-down will appear in the header in every column.

Apply Filter Command to Filter Multiple Values in One Cell in Excel

Step 2:

  • Now, click on the filter drop-down which is situated beside the Name Hence, a new window pops up. From that window, firstly, check Austin. Secondly, press the OK option.

  • Finally, after completing the above process, you will be able to filter Austin’s information from our dataset which has been given in the below screenshot.

Apply Filter Command to Filter Multiple Values in One Cell in Excel


2. Use Advanced Filter Command to Filter Multiple Values in One Cell

Now, we will use the Advanced Filter command to filter multiple values in one cell. We will filter based on the Vinchant’s information from our dataset. We can easily do that. Let’s follow the steps below to filter multiple values in one cell!

Steps:

  • After selecting the cells array, from your Data tab, go to,

Data → Sort & Filter → Advanced

Use Advanced Filter Command to Filter Multiple Values in One Cell in Excel

  • After clicking on the Advanced option, a dialog box named Advanced Filter will appear in front of you. From the Advanced Filter dialog box, firstly, select Filter the list,in-place under the Action Secondly, type the cell range in the List range typing box, from our dataset, we will select $B$4:$D$14. Thirdly, select $F$4:$F$5 in the Criteria range input box. At last, press OK.

  • Hence, you will be able to filter multiple values in one cell which has been given in the below screenshot.

Use Advanced Filter Command to Filter Multiple Values in One Cell in Excel


3. Apply COUNTIF Function to Filter Multiple Values in One Cell

In this method, we will apply the COUNTIF function to filter multiple values in one cell. Let’s follow the instructions below to filter multiple values in one cell!

Step 1:

  • First of all, select cell E5, and write down the below formula,
=COUNTIF(B5:D14,B5)

Apply COUNTIF Function to Filter Multiple Values in One Cell in Excel

  • After that, press ENTER on your keyboard, and you will get 2 as the output of the COUNTIF function.

  • Hence, autoFill the COUNTIF function to the rest of the cells in column E.

Apply COUNTIF Function to Filter Multiple Values in One Cell in Excel

Step 2:

  • Now, press Ctrl + Shift + L simultaneously on your keyboard to create a filter drop-down list.

  • Hence, a filter drop-down list pops up in the header in every column.

Apply COUNTIF Function to Filter Multiple Values in One Cell in Excel

  • After that, click on the filter drop-down which is situated beside the Remark Hence, a new window pops up. From that window, firstly, check 2. Secondly, press the OK option.

Apply COUNTIF Function to Filter Multiple Values in One Cell in Excel

  • Finally, after completing the above process, you will be able to filter Philip’s information from our dataset which has been given in the below screenshot.

Apply COUNTIF Function to Filter Multiple Values in One Cell in Excel


4. Perform FILTER Function to Filter Multiple Values in Excel

Last but not the least, we will use the FILTER function to filter multiple values in one cell. This is a dynamic function. We will filter based on Joe’s information from our dataset. Let’s follow the instructions below to filter multiple values in one cell!

Step 1:

  • First of all, create a data table with the same header as the original data. Then, select cell F5.

Perform FILTER Function to Filter Multiple Values in One Cell in Excel

  • Further, type the below formula in the selected cell. The formula is,
=FILTER(B4:D14,ISNUMBER(MATCH(B4:B14, {"Joe"},0))," Not Found ")

Formula Breakdown:

⃟   MATCH(B4:B14, {“Joe”},0)

The MATCH function will match the “Joe” in the cells array B4:D14. 0 is used to exact match.

⃟   ISNUMBER(MATCH(B4:B14, {“Joe”},0))

When a cell contains a number, the ISNUMBER function returns TRUE; otherwise, it returns FALSE.

⃟   FILTER(B4:D14,ISNUMBER(MATCH(B4:B14, {“Joe”},0)),” Not Found “)

Inside the FILTER function, B4:D14 is the cells filtering array, ISNUMBER(MATCH(B4:B14, {“Joe”},0)) works like a Boolean array; it carries the condition or criteria for filtering.

  • After typing the formula, simply press ENTER on your keyboard, and you will get your desired output which has been given in the below screenshot.

Perform FILTER Function to Filter Multiple Values in One Cell in Excel


Things to Remember

👉 You can use the FILTER function in Office 365 only.

👉 You also can create a filter drop-down list by pressing Ctrl + Shift + L simultaneously on your keyboard.


Conclusion

I hope all of the suitable methods mentioned above to filter multiple values in one cell will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo