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.
How to Filter Multiple Values in One Cell in Excel: 4 Suitable Ways
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.
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.
- 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.
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.
Read More: How to Filter with Multiple Criteria 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
- 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.
Read More: Excel Filter Data Based on Cell Value
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)
- 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.
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.
- 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.
- 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.
Read More: How to Filter Cells with Formulas 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.
- 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.
Read More: How to Perform Custom Filter 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
<< Go Back to Data | Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
