Excel Filter by Color and Text (with Easy Steps)

Here’s an overview of filtering data by color and text.

We have the following starting dataset where the colored cells are blank. We want to filter the dataset by the “Yellow” cell color and the ” Yes ” text in column E.

Step 1 – Replacing Values of Colored Cells to Filter by Color and Text

• Select the data range or the colored cells.
• Press Ctrl + H to open the Find & Replace dialog box. Go to the Replace tab.
• Click on the Format drop-down arrow for the Find what box.
• Select “Choose Format From Cell”.

• Click on a yellow-colored cell. The format preview will change accordingly.
• Enter a space in the “Replace with” box.
• Pick the same cell color formatting as earlier for that box.
• Select Replace All, then click OK on the notification and press Close.

Step 2 – Setting the Criteria for the Advanced Filter

• Copy the column header and a cell each from the cells with the desired color and text.
• Paste the cells starting with cell G4.

Step 3 – Applying the Advanced Filter

• Select the cell where you want to get the filtered data.

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

• You will get the following result.

Things to Remember

• You can’t use blank cells as the filter criteria. We replaced the blank colored cells with a space.
• Don’t set the filter criteria manually. 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.

Download the Practice Workbook

<< Go Back to Color Filter | Filter in Excel | Learn Excel