How to filter data by conditional format color in Excel

Key points of this article

The background color and font color generated by conditional formatting can be directly used to find the corresponding rows using the "Filter by Color" function, without the need to manually change the color first.
The icon set and data bar are not cell colors and cannot be found when filtering by color. You need to use auxiliary columns to write formulas and then filter.
Before copying the filter results, make sure that only the visible rows are selected, otherwise the hidden rows will be taken away.
You can only select one color at a time. When you want to view multiple colors at the same time, it is recommended to use a formula to generate a classification column.

When making tables, conditional formatting is often used to mark numbers that exceed the standard in red and completed tasks in green. Once the table is long, I want to separate rows of the same color for viewing. Excel does not have a separate "Filter Color by Condition" command, but the filter function itself supports filtering by cell color, and colors generated by conditional formatting can also be used. This article is written for people who have already colored the data and want to quickly filter out the corresponding rows.

After reading, you will know when it is enough to filter directly by color, when it is necessary to switch to auxiliary columns, and how to safely copy the filter results to other places without missing rows or accidentally modifying the original table.

How to filter Excel data by color in conditional formatting? One sentence conclusion

If the conditional format only fills the cell with a background color or changes the font color, just use "Filter by Color" in the filter drop-down menu. When encountering effects such as icon sets and data bars that are not presented in cell colors, you must first use a formula to generate a column of text marks, and then filter by this column.

your situation	Recommended practices	reason
Conditional formatting fills cells with background color or font color	After turning on filtering, use "Filter by color"	Filtering recognizes colors generated by conditional formatting
Colors are filled manually	Also use "Filter by color"	The operation is the same, but the color does not change with the data
Conditional formatting uses icon sets or data bars	Add auxiliary columns to write formulas and filter by auxiliary columns	Icons and bars are not cell colors and cannot be found by color
Want to see rows of two or more colors at the same time	Use a formula to mark each row as a category, and then filter by the mark.	Only one color can be selected in one color filter
Tables are just color images in PDF	Convert the table to Excel first and then process it	Colors in PDF are just images, there are no conditional formatting rules

Before you start: Confirm these points first

Determine the source of the color before you do it, as it determines whether you can filter directly by color.

Colors are not generated by conditional formatting. Modify a value and see if the color changes accordingly. If it changes, it means that it comes from the rules, and you can safely filter by color; if it doesn't change, it means that the colors are filled manually, and the rules will not be updated in the future.
Which effect is used for conditional formatting. The background color and font color can be filtered by color, but the icon set and data bar cannot be filtered. Auxiliary columns must be used instead.
The data is not a complete area. If there is a blank line in the middle, the filter will only recognize the part before the blank line, which should be sorted into continuous areas first.
Do you want to keep the original table? If you want to save the filtering results separately or send them to others, try to copy them to a new worksheet to avoid directly changing the original data.
Table is not from PDF. There are no rules to follow for colors in PDF, and they need to be converted into Excel files first.

Steps to filter by color in Excel

Select any cell in the table, or select the entire data range including the header.
Turn on the filtering function, and a drop-down arrow will appear in each column of the table header.
Click the arrow of the column you want to filter and find the "Filter by Color" option in the drop-down menu. The position and text of different versions may be slightly different, but they are all in the filter menu of this column.
Select a color, and the table will only display rows of this color, and other rows will be temporarily hidden.
Check whether the displayed rows are as expected, and then copy or perform other operations after confirming that they are correct.
To view another color, clear the filter for the current column and then select the new color.
After the work is completed, cancel the filtering and return the table to full display to avoid subsequent calculations only for visible rows.

If the effect of the icon set or data bar needs to be filtered, you can write a judgment formula in the blank column next to it, such as =IF(B2>=1000,"达标",""), mark the rows that meet the conditions as text, and then filter based on the content of this column. Just replace the values and column positions in the formula with those of your own table.

Check each item after completion

The results of screening and copying are prone to omissions or misjudgments, so it is recommended to check them one by one.

Check items	How to see	What to do if there is a problem
Does the number of rows match?	Compare the number of rows of this color before and after filtering, or look at the count in the status bar	When the number is incorrect, check whether the data area is interrupted by blank lines.
Does the color come from a rule?	Modify a value and observe whether the color changes accordingly	No change means manual coloring, use auxiliary columns or reset the rules
Are similar colors confusing?	Check a few lines color by color to confirm they are consistent with the rules	When the color difference is subtle, use formulas to generate text marks instead.
Is the copy result missing?	Compare the number of rows and key fields after copying	Re-select the visible cells and copy them. Do not directly select the entire column.
Whether the original table has been modified	Check whether the data and format of the original table are consistent with those before filtering	Restore with undo, or retrieve from backup

🎯 Which method is suitable for you?

Only want to temporarily view rows of a certain color: Just use "Filter by color" and cancel the filter after reading.
To give the results to others or save them as a new file: Filter first, then copy the visible cells to a new worksheet and check the number of rows.
Conditional formatting is an icon set or data bar: Use auxiliary columns to generate text markers, and then re-filter each time the data is updated.
Table from PDF: It can be exported to Excel using the PDF editor of the OmniDoc desktop client. The download address is download.omnidoc.top. After export, the colors usually no longer correspond to conditional formatting rules and need to be reset.

❓ Frequently Asked Questions (FAQ)

1. Conditional formatting uses an icon set. Can it be filtered by color?

Generally not. The icon set shows the graph instead of the cell color, and color filtering can't find it. You can use formulas to generate text such as "Meet the standard" and "Not meet the standard" in the auxiliary column, and then filter by this column.

2. Why does the data copied after filtering have extra hidden rows?

This is usually because an entire column or range is selected, rather than the visible cells. Select the filtered visible area and then copy it, or select the data first and then select by positioning the visible cells.

3. Why is there no "Filter by color" in the drop-down menu?

First make sure that the column actually has a color. The menu will not show this option if there is no fill or font color. In addition, if the Excel version is older, the functions may be incomplete. Please upgrade and try again.

4. What should I do if I want to filter two colors at the same time?

Only one color filter can be selected at a time. You can use a formula to uniformly mark the rows of two colors in the auxiliary column, for example, mark them both as "key points", and then filter by this mark.

📝 Summary

To filter Excel data by color in conditional formatting, the key is to first confirm that the color comes from the rule, and then decide to filter by color directly or with the help of auxiliary columns. Check the number of rows before copying the results to avoid missing rows and incorrect corrections. After mastering this method, various colors on the same table can be quickly found.

Open OmniDoc and work with your spreadsheet file