To filter highlighted cells in Excel, turn on filtering, open the drop-down arrow in the column that contains the colored cells, choose Filter by Color, and select the fill color you want to keep visible. Excel hides rows that do not match; it does not delete them. Before starting, make sure the data has a header row and that the color you want to filter is in the same column as the drop-down you will use.
What You’ll Need
- An Excel workbook with a list or table and a header row.
- Cells in one column that have the fill color you want to filter. The color may be applied manually or by conditional formatting.
- Microsoft Excel for Microsoft 365 on Windows for the steps below. Excel for Mac has a similar filter-by-color command, although ribbon placement and labels can vary slightly.
You do not need a formula, add-in, or macro. Filtering is most straightforward when each record occupies one row, every column has a distinct header, and the list does not contain merged cells.
Step-by-Step
- Check the shape of your data. Look over the list before adding a filter. Put field names such as Status, Owner, or Priority in the first row, with one record per row underneath. Avoid blank rows or columns inside the list because Excel may treat them as boundaries and filter only part of the data. If your data is split into separate blocks, select and filter each block independently, or combine it into one continuous list first.
- Choose whether to use a range or a table. You can filter an ordinary range, or convert the list to an Excel table. To make a table, click a cell in the list, choose Insert > Table, confirm the range, and check My table has headers if the first row contains headings. A table automatically adds filter arrows to its headers and usually expands as you add rows. You do not have to create a table if the list is already set up as a continuous range.
- Turn on the filter arrows if they are not already visible. Click a cell inside the data range, then choose Data > Filter. You can also use Home > Sort & Filter > Filter. Small drop-down arrows should appear in the header cells. If the list is a table, those arrows may already be there. Avoid selecting only the colored cells before turning on the filter: Excel needs to know which rows belong to the full list so it can hide complete records.
- Open the arrow for the column containing the highlighted cells. For example, if colored cells appear in the Status column, click the arrow in the Status header. Do not choose the arrow in an unrelated column merely because it is nearby. The color filter is applied to the column whose arrow you opened, and it displays or hides entire rows based on the cells in that column.
- Open the color-filter choices. In the drop-down menu, point to or select Filter by Color. Excel displays color choices available for that column. Depending on the formatting present, the choices can include cell fill colors, font colors, or conditional-formatting icons. If you want rows highlighted with a background color, choose the color under the cell-color or fill-color choices, not a font color that merely changes the text.
- Select the color you want to show. Click the matching swatch. Excel filters the list so that rows with that color in the selected column remain visible. For example, choosing the yellow fill in a Priority column shows records whose Priority cell has that yellow fill. The other records are hidden, not erased. If you do not see the intended shade, check the cell itself and confirm that you are using the correct column’s menu.
- Check the filtered results before using them. Look at the visible rows and confirm that each one meets your intended color rule. If the list is long, scroll to check more than the first few records. You can combine this color filter with criteria in other columns: for instance, show yellow-priority rows and then select one department in the Department column. Each filter narrows the rows still visible.
- Clear or change the filter when you are done. To choose a different color in the same column, open its arrow, return to Filter by Color, and select another swatch. To remove just that column’s filter, open its arrow and choose Clear Filter From followed by the column name. To clear all active filters, use Data > Sort & Filter > Clear. The rows reappear, but the fill colors remain unchanged.
The same basic workflow applies in Excel for Mac: enable filtering from the Data tab, then use the relevant header’s filter menu and its color option. In Excel for the web, available filter choices can differ from the Windows desktop version. If Filter by Color is missing or the menu looks different, open the workbook in the desktop app and check there.
Common Mistakes
- Opening the wrong column menu. The filter checks the cells in the column whose arrow you use. If the fill is in Status but you open the Owner arrow, Excel will not filter by the Status cell’s fill.
- Confusing fill color with font color. A yellow background and yellow text are different formatting properties. Choose the fill swatch when you mean the background highlight.
- Assuming hidden rows were deleted. A filter only hides rows that do not meet its criteria. Clearing the filter restores them. Do not copy, delete, or overwrite data based on the assumption that the other rows are gone.
- Filtering a partial list. If you selected only one column or Excel detected a boundary at a blank row, the visible results may no longer represent complete records. Turn off the filter, correct the range, and apply it to the full dataset.
- Expecting color to be a reliable data value. Color is formatting, not a category stored in its own field. Two cells that look similar may have different formatting, and a color can be changed without changing the underlying text. For recurring analysis, a Status or Priority column is usually more dependable.
Tips
- Use a consistent color scheme. Apply the same fill to the same meaning throughout the column. If yellow means Needs review in one part of the list but Complete elsewhere, a color filter will still group the yellow cells together, even though the meanings conflict.
- Keep a text label alongside important colors. A value such as Review, Approved, or Delayed makes the information understandable when the workbook is printed, viewed with different accessibility settings, or opened in an app that handles formatting differently.
- Combine filters deliberately. Filters in different columns work together. If the color filter is active and you choose a date range in another column, Excel shows only rows that meet both conditions. If the result is empty, clear one criterion at a time to find out which condition excludes the rows.
- Use a table for lists that grow. Tables make headers and filter arrows easier to manage, and new rows added directly below a table are generally included in it. Check the table boundaries if rows added elsewhere do not appear in filtered results.
- Reapply after changing formatting or values. If you edit cell fills, conditional-formatting rules, or the data that controls those rules while a filter is active, refresh the filter view with Data > Sort & Filter > Reapply. Reopen the color menu and choose the required color again if the visible results do not update as expected.
Troubleshooting
Filter by Color is not in the menu. First confirm that you opened a filter arrow in the same column as the colored cells. Check that at least one cell in that column actually has a fill or font color, rather than a color shown only by a nearby cell or a manually drawn shape. If the color comes from conditional formatting, confirm that the rule is applying to the cells you are filtering. Then close and reopen the menu. If you are using Excel for the web or a different spreadsheet app, try opening the workbook in Excel for Microsoft 365 on Windows.
The expected color swatch is missing. The cell may have no fill, may use a theme shade that looks close to another shade, or may be colored by a conditional-formatting rule that currently evaluates differently. Select a sample cell and inspect its fill using Home > Fill Color. If conditional formatting controls the appearance, review Home > Conditional Formatting > Manage Rules and check the rule’s range and condition. After correcting the formatting or rule, reopen Filter by Color and select the available swatch.
Rows that should match are hidden. Verify that the fill is in the filtered column and that the cells really use the same color. Similar-looking theme colors can be distinct. Also check other column arrows: an additional text, date, or color filter may be excluding those rows. Use Data > Sort & Filter > Clear to remove all criteria, then apply the color filter again by itself. If formatting or conditional-formatting results changed, use Reapply first.
The filter affects only part of the list. Clear the filter and inspect for completely blank rows or columns, merged cells, or records outside the selected range. Remove unnecessary breaks, make sure each record stays on one row, and apply Data > Filter again with a cell inside the complete list selected. Converting the corrected range to a table can help keep future rows within the filtered range.
The color filter is no longer wanted, but some rows remain hidden. Other column filters may still be active. Choose Data > Sort & Filter > Clear to remove all filters, or clear the criteria from each relevant header menu. If rows still appear missing after all filters are cleared, check whether they were hidden manually or grouped; filtering and manual row hiding are separate actions.
Frequently Asked Questions
Can Excel filter by a cell’s background color?
Yes. Open the drop-down for the column containing the colored cells, choose Filter by Color, and select the desired fill swatch. Excel then shows the matching rows.
Can I filter by font color instead of fill color?
Yes, when font colors are present in the selected column. Open that column’s filter menu, choose Filter by Color, and select the font-color choice rather than the cell-fill choice.
Can I filter cells colored by conditional formatting?
Excel can offer color filtering for formatting shown in a column, including conditional-formatting results when they are available in the filter menu. If the swatch is missing or results look stale, check the rule and its applies-to range, then reapply the filter.
Can I show two highlight colors at the same time?
A color filter selects one color choice at a time for a column. To include multiple colors, you can filter by one color, copy or review those results, and repeat for the other color, or use a separate text category column to represent the groups you want to combine.
Does filtering by color delete the other rows?
No. It hides rows that do not match. Choose Clear Filter From in the column menu, or clear all filters from the Data tab, to show the hidden rows again.
Why do two cells that look the same behave differently?
They may use different fill shades, theme colors, or formatting sources, or one may be controlled by a conditional-formatting rule. Inspect both cells’ formatting and check any rules before filtering again.
Related
- How to Email an Excel File That’s Too Large Safely
- Excel Audit Tools: How to Enable and Use Them
- How to Enable an Audit Trail in Excel: Track Changes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Microsoft Excel SUMIF Examples and How to Use ThemIn Microsoft Excel, SUMIF adds values that meet one condition. For example, with categories in A2:A6 and amounts…
- Filter Dates in Excel Oldest to Newest: A Step-by-Step GuideTo put dates in chronological order in Excel for Microsoft 365 on Windows, select a cell in the…
- COUNTIFS Excel Array: Criteria, Formulas, and ExamplesIn Excel for Microsoft 365, COUNTIFS can evaluate an array of criteria and return a separate count for…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Gross Margin Excel Calculation: Formula and ExamplesWith revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with…
- Excel COUNTIF for Dates Between a RangeTo count dates between two boundaries in Excel, use COUNTIFS, which applies both a start-date and an end-date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.