Excel Filter by Color and Text (with Easy Steps)

This article illustrates how to filter by color and text in excel. You can easily filter by color text in excel. But there is no direct way to do that by both criteria together. We will show you a way around doing that in this article. The steps are quick and easy to follow. So have a quick look through the article.

excel filter by color and text


Download Practice Workbook

You can download the practice workbook from the download button below.


Steps to Filter by Color and Text in Excel

Assume you have the following dataset. Here the colored cells are blank. You want to filter the dataset by the “Yellow” cell color and the ” Yes ” text in column E.

dataset to filter by color and text

We can not do that using the simple Filter command. So we will use Advanced Filter to get the desired result. Make a copy of your dataset and then follow the steps below to do that.


STEP 1: Replace Values of Colored Cells

  • First, select the data range or the colored cells. Then press CTRL + H to open the Find & Replace dialog box. Make sure you are in the Replace tab. Now click on the Format dropdown arrow for the Find what box. Then select “Choose Format From Cell”.

replce values of colored cells

  • Next, click on a yellow-colored cell. The format preview will change accordingly. Now enter a space in the “Replace with” Then pick the same cell color formatting as earlier. After that, select Replace All >> OK >> Close.

replace blank, colored cells with space

Read More: How to Filter by Cell Color in Excel (2 Easy Ways)


STEP 2: Set Criteria for Advanced Filter

  • Now copy the column header, a cell each from the cells with the desired color and the cells with the desired text. Then paste the cells on cell G4.

set criteria for advanced filter

Read More: How to Filter by Color Using Conditional Formatting in Excel


STEP 3: Apply Advanced Filter

  • Now select the cell where you want to get the filtered data.

set destination for filtered data

  • Then press ALT + A + Q to apply the Advanced Filter. You can also do that from the Data tab. Next, mark the radio button for “Copy to another location” in the Advanced Filter dialog box. Then select the entire dataset as the List Range. Next, select the cells in column G as the Criteria range. After that, select the location where you want to get the filtered data and then click OK.

Select actions for Advanced Filter

  • Finally, you will get the following result.

data filtered by color and text

Read More: How to Filter by Multiple Colors in Excel (2 Easy Methods)


Things to Remember

  • You can not use blank cells as the filter criteria. That’s why you need to replace the blank colored cells with a space.
  • Don’t set the filter criteria manually. Rather copy the desired cells from the dataset and paste them into the criteria range.
  • Make a copy of your dataset to avoid any data loss while using the replace command.

Conclusion

Now you know how to filter by color and text in excel. Is this what you needed? You can let us know in the comment section below. Please share your queries or suggestions too. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo