Excel Pivot Filter by Color: How to Filter the Data

Excel PivotTables cannot directly filter their source records by cell fill color or font color. To filter a PivotTable by what a color represents, add a field to the source data that records that category—such as Red, Amber, or Green—then use that field in the PivotTable’s filters or a slicer.

Why PivotTables do not filter by cell color

A PivotTable summarizes fields and values in its source data. Its filters work with those fields: for example, a region name, a date, or a sales amount. Cell color is formatting, not a field value, so a red fill in the source worksheet is not automatically treated as a category the PivotTable can filter.

This distinction matters because a color may have been applied manually, added by conditional formatting, or used only to make a worksheet easier to scan. The PivotTable has no dependable way to infer which meaning the person applying the color intended. A red cell might mean overdue in one workbook and high priority in another.

The dropdown on a regular Excel table or filtered range can offer Filter by Color. That feature filters the displayed rows in the range; it does not add a color field to a PivotTable or tell the PivotTable to exclude those records. A PivotTable built from the same source can still summarize rows hidden by a worksheet filter.

The reliable solution: add a category field

Put the meaning of the color in its own source-data column. For example, if a red fill means an item is overdue, add a Status column with values such as Overdue and On time. If colors mean priority, use a Priority column with values such as High, Medium, and Low. The PivotTable can then filter or group by those actual values.

When the colors are based on a rule, make the new field follow that same rule. For example, with priority scores in B2:B100, enter this formula in C2 and fill it down:

=IF(B2>=90,"Red",IF(B2>=70,"Amber","Green"))

For this layout, a score of 92 returns Red, 75 returns Amber, and 60 returns Green. The formula labels the category; it does not inspect the cell’s fill. Apply matching color formatting to the new category if you want the source table to keep its visual cues. If the existing colors are conditional formatting, reproduce the condition in the category formula rather than trying to read the displayed color.

For manually chosen colors, enter a corresponding category value when you color the row. If practical, make the category the thing users edit and have conditional formatting apply the color automatically. That avoids the two disagreeing—for example, a cell labeled High but manually filled green.

Build and filter the PivotTable

After adding the category field, create or update the PivotTable so that the new field is included in its source. In Excel for Microsoft 365 on Windows, click a source-data cell and choose Insert > PivotTable. Check that the selected table or range includes the category column, choose where to place the PivotTable, and select OK. If the source is an Excel table, adding a column to that table makes it easier for the PivotTable source to include the new field.

In the PivotTable Fields pane, drag the category field to Filters to create a report filter above the PivotTable. Open that filter and select the category or categories to show. Alternatively, put the field in Rows or Columns to display the categories as part of the report and filter from that field’s dropdown.

For a more visible control, select a PivotTable and choose PivotTable Analyze > Insert Slicer, then select the category field. Clicking a slicer button filters the PivotTable by the field value. A slicer is useful for readers who may not notice a report-filter dropdown, and it can make the available categories clearer.

Use Filters when you want a compact selector above the summary.
Use Rows or Columns when comparing totals across the categories is useful.
Use a slicer when you want a prominent, clickable filter.

What to do when color was applied manually

Excel worksheet formulas do not provide a standard function that returns the fill color or font color of another cell. A formula that classifies the underlying score or date is more robust than one that tries to identify formatting. For example, if cells are colored red whenever a due date is earlier than today, a Status field can test the date and return Overdue or On time. The filter then follows the reason for the color even if someone changes the workbook’s theme or formatting.

If colors were assigned by hand and there is no existing rule or label, the workbook needs a source of meaning before it can filter reliably. Add a category field and record the meaning for each row. If the category cannot be reconstructed from existing data, someone may need to review the colored records and assign the labels once. After that, use conditional formatting based on the labels so the visual appearance and filterable data stay aligned.

It is possible to use specialized workarounds, such as VBA or legacy Excel 4 macro functions, to inspect formatting. They add maintenance and recalculation complications and are usually a poor foundation for a shared PivotTable report. They also do not solve the underlying problem of color being ambiguous. A normal data field is easier to audit, refresh, and use across workbooks.

Why filtering the source sheet is not enough

Suppose a sales list has 500 transactions and you use a normal column filter to show only rows with red fill. The other rows are hidden on the worksheet, but hiding them does not remove them from the PivotTable’s source range. Refreshing the PivotTable does not generally make it treat the source sheet’s visible rows as the complete dataset. The PivotTable can therefore show totals for records that are currently hidden in the source view.

Filtering the source first and copying only visible records to a separate range can produce a PivotTable of that subset, but it is a snapshot. New rows, changed colors, or an updated filter require the copied data to be rebuilt. It is also easy to copy an incomplete selection. A category field avoids this fragile extra step: keep all records in the source, then select the desired category inside the PivotTable.

There are specialized data-model and query workflows for more complex reporting, but they still benefit from an explicit category column. Treating color as data only at the point where the source is prepared makes the logic transparent and helps prevent totals from silently including or excluding the wrong records.

Refreshes, new rows, and keeping categories consistent

A PivotTable does not necessarily update just because a source value or formula changes. After changing category values, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If you add records, confirm that the PivotTable source includes them. Using an Excel table as the source generally helps because the table expands as rows are added; you may still need to refresh the PivotTable to see the new data.

Formula-based categories can change as their inputs change. If a formula uses TODAY(), for example, an item might move from On time to Overdue when the date changes. The worksheet formula must recalculate, and the PivotTable must be refreshed to reflect the revised category in its summary. If a filter appears to omit a category, check both the source values and whether the PivotTable has been refreshed.

Keep category labels consistent. Red, red, and RED can be treated as the same text for many Excel comparisons, but accidental spaces or different wording such as High and High Priority can create separate PivotTable items. A dropdown list for data entry, or a formula that generates the labels, can reduce these mismatches. If a category no longer appears after its source records are removed or relabeled, refresh the PivotTable and check its item-retention settings if the old label remains visible.

Excel for Mac and Excel for the web

The core approach is the same in Excel for Microsoft 365 on Windows, Mac, and the web: put a meaningful category in the source data, then filter the PivotTable by that field. Ribbon placement and available PivotTable tools can vary by platform and release, so the exact button names or layout may differ. In desktop Excel for Mac, look for the PivotTable controls and field list after selecting the PivotTable; on the web, use the available PivotTable field and filter controls in the workbook.

If a particular interface does not offer the slicer or PivotTable command you want, the category field remains useful: use the available field dropdown or open the workbook in desktop Excel. Do not assume that switching from Windows to Mac or the web makes worksheet fill color a PivotTable data field.

Frequently Asked Questions

Can I filter an Excel PivotTable by cell color?
No. PivotTable filters work with source fields and values, not the source cell’s fill or font color. Add a field that states what the color means, then filter by that field.

Does Filter by Color work on a PivotTable?
Filter by Color is a feature of regular filtered ranges and tables, not a direct way to filter PivotTable source records. Filtering the source worksheet by color also does not reliably limit the records summarized by the PivotTable.

Can a formula detect a cell’s fill color?
There is no standard worksheet formula that returns another cell’s fill color. Use a formula based on the value or condition that caused the color, or enter a category explicitly if the color was assigned manually.

How do I filter a PivotTable to show only red items?
Add a source-data field such as ColorCategory, enter Red for the relevant records, refresh the PivotTable, and select Red in the field filter. You can also insert a slicer for that field.

Why does my PivotTable include rows hidden by a color filter?
A normal worksheet filter hides rows from view; it does not remove them from the PivotTable’s source data. Filter the PivotTable using a source category field instead, or deliberately build a separate data range containing only the records you intend to summarize.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.