How to Filter by Color in Excel — Complete Guide (2026)

How to Filter by Color in Excel — Complete Guide (2026)

Color-coded spreadsheets are everywhere — yellow for follow-ups, green for closed deals, red for overdue items. Then comes the moment you actually need to act on the colors. Short answer: Data → Filter handles it in four clicks.

The catch nobody mentions until you're elbow-deep in a quarterly report: Excel filters one color at a time per column. Two colors? Three? You need a workaround. That's where the second half of this guide earns its keep.

This guide covers how to filter by color in excel across every recent version — Excel 365, 2021, 2019, 2016, and Excel for Mac. Each method comes with the exact menu path. If you're warming up on basics, the how to use excel walkthrough covers ribbons, cells, and saving.

For the bigger picture of why Excel matters in any office role, the microsoft excel hub pulls together certifications, formulas, and the skills employers test for.

Method 1 — Filter Dropdown by Color

This is the path you'll use 90% of the time. It works on any table where you've manually colored cells, used conditional formatting, or applied font colors. Four clicks, no formulas, no macros.

The exact steps. Click any cell inside your data range. Go to Data → Filter. Tiny dropdown arrows appear in your header row. Click the arrow on the column you want to filter. Hover over Filter by Color. A submenu opens showing every color Excel detected — cell colors at the top, font colors below.

Click the color you want. Done. The sheet hides every row that doesn't match. To clear, click the same arrow and choose Clear Filter From [Column Name]. To remove filters entirely, hit Data → Filter again to toggle the arrows off.

Worth knowing: if Filter by Color is grayed out, the column has no colors detected. Excel only shows the option when it finds at least one colored cell. That's the most common confusion point — people apply a theme color that's really just a default header style, and Excel doesn't register it as a fill.

Method 2 — Sort by Color (Grouped, Not Hidden)

Sometimes you don't want to hide the other rows. You want to group the colors together — all yellows up top, greens below, then uncolored rows last. That's sort, not filter, and Excel handles it through a different menu.

The path: Data → Sort opens the multi-level sort dialog. In the Sort On dropdown, change the default Cell Values to one of three options. Cell Color. Font Color. Conditional Formatting Icon. Pick the color in the Order column and choose whether it appears On Top or On Bottom.

Click Add Level to layer multiple colors. This is the only built-in way to handle several colors in one shot. Sort by red first (on top), then yellow, then green, and your sheet stacks itself into a traffic-light hierarchy.

Worth flagging: sort modifies the row order permanently until you undo it. If your data has formulas that depend on row position, sort with caution. Filter doesn't have that problem — it hides rows but leaves positions intact. The excel shortcuts page lists the keyboard combo (Alt+D+S on Windows).

For working with sheets containing colored data, the excel sheet guide covers protection and structure considerations that affect sorting.

Filtering Multiple Colors at Once

Here's the limitation Microsoft is quiet about. Excel filters one color per column at a time. Pick yellow, get yellow rows. Switch to green, you lose the yellow filter. No checkbox multi-select. No Ctrl+click. Not in 365, not in 2021, not on Mac.

It's been the same since Excel 2010 introduced color filtering. Still bites people in 2026. The workarounds split into three buckets. Sort by color groups all the colors visually — not technically a filter, but often what you actually wanted.

Helper column with GET.CELL assigns a numeric code to each color, then you filter that numeric column with checkboxes. VBA macro writes the color index of every cell to an adjacent column in one pass, giving you the same checkbox filter without typing GET.CELL manually. Pick based on how often you'll repeat the task.

VBA and Power Query Workarounds

Helper Column with GET.CELL (No VBA Required)

GET.CELL is a legacy XLM macro function. It works in modern Excel but only inside a Named Range. Here's the setup. Go to Formulas → Name Manager → New. Name it ColorCode. In the Refers to box, type =GET.CELL(38,Sheet1!A2) where A2 is the first colored cell.

Then in a helper column next to your data, type =ColorCode and drag down. Each cell returns the color index — 6 for yellow, 3 for red, 4 for green, and so on. Filter that helper column normally. You can check multiple color codes at once. Save the file as .xlsm — XLM functions don't survive .xlsx format.

VBA Macro (For Repeat Tasks)

If you filter by color every week, the helper-column trick gets old. A short VBA macro does the same job in one keystroke. Press Alt+F11 to open the VBA editor, insert a new module, and paste a six-line loop that walks down your data range and writes cell.Interior.ColorIndex to the adjacent column.

Run the macro. Filter the new column with multi-select checkboxes. Done. Bonus: the macro also catches conditional formatting colors if you use cell.DisplayFormat.Interior.Color instead of Interior.ColorIndex. Worth the four-minute setup if color filtering is recurring.

The excel formulas reference covers function syntax if you want to extend the macro with additional checks. For users who'd rather avoid macros entirely, the next section covers Power Query — Excel's modern, macro-free approach.

Power Query Approach

Power Query (called Get & Transform in older versions) is the modern alternative. The catch: Power Query doesn't read cell colors directly. It reads values only. So the workflow is layered.

Add a helper column in the source sheet that captures color via VBA or GET.CELL. Then load into Power Query, where you can filter multiple values cleanly with the checkbox interface. Heavier setup, but if your data refreshes from external sources, Power Query is the right home for it.

The pivot tables in excel guide shows how to combine the same helper column with PivotTables for color-based aggregation. Handy when you want totals per color, not just a filtered view. This pairs nicely with the SUMIF-style aggregations covered in the excel formulas guide.

Conditional Formatting Tie-In

This trips people up constantly. If your colors come from conditional formatting excel rules — say, "highlight any cell over $1,000 in green" — the Filter by Color menu does still see those colors. You don't need to convert them to manual fills first.

Excel resolves the rule, sees the green, and shows green in the color submenu. Same goes for font colors driven by rules and the colored icon sets you find in Home → Conditional Formatting → Icon Sets. The system is smarter than people give it credit for.

There's one gotcha. The Sort by Color dialog has a separate entry for Conditional Formatting Icon in the Sort On dropdown. That's only relevant when you're using icon sets — the green check, yellow exclamation, red X icons.

If you're sorting by the cell color that a conditional formatting rule produced, pick Cell Color, not Conditional Formatting Icon. The naming is confusing but consistent across versions. Microsoft has never simplified it.

One more practical note. If you're building a dashboard with conditional formatting, set up your rules before you build the filter. Why? Because rules added after the filter is applied sometimes don't get picked up until you toggle the filter off and back on. Annoying but easy to spot once you know.

Mac vs Windows Differences

The good news: on the surface, Excel for Mac and Excel for Windows handle color filtering identically. Data → Filter → arrow → Filter by Color works on both. The differences are in the corners.

VBA is fully supported in Excel for Mac 2019 and later, but the editor interface is sparser than on Windows. Some conditional formatting APIs behave slightly differently — if you're porting a Windows macro, expect to tweak a few property names. GET.CELL works on Mac in .xlsm files.

Power Query has historically been Windows-first. Mac support arrived later and lags a release behind on new connectors. If you're on Mac and relying on Power Query for color workflows, double-check your version. Excel for Mac 16.69 or later is the safe baseline. Anything older and you'll find missing connectors.

Keyboard shortcuts also differ. Alt+D+S (Sort) on Windows becomes Cmd+Shift+R or accessed via the menu bar on Mac. The excel shortcuts cheat sheet documents the platform differences in full.

Accessibility, Color-Blindness, and Excel 365

Quick accessibility note that experienced sheet-builders learn early. About 8% of men and 0.5% of women have some form of color vision deficiency. A red-green status column is invisible to a chunk of your audience. Worth knowing before you ship a dashboard.

The fix isn't to abandon color — it's to pair color with shape or text. Use icon sets from Conditional Formatting → Icon Sets (the check/exclamation/X set is colorblind-safe). Or add a text column ("OK", "Watch", "Critical") alongside the colors.

Filter and sort still work on icons through the same Filter by Color and Sort by Color menus. Excel treats icons as a third color category alongside cell and font.

On the Excel 365 side, all the color filter capabilities described here work in the current Microsoft 365 release. There's no new multi-color filter feature as of 2026. The one-color-per-column limit holds across every version.

What Excel 365 adds is dynamic-array support. If you build a FILTER() formula that returns rows where the helper-column color index matches multiple values, you can produce a multi-color filtered output without touching the column dropdown. It's a workaround, but it works. The same FILTER() function is also handy for the how to find duplicates in excel workflow — color filtering and duplicate detection often share the same source data.

Pro Tip — Count Colored Cells While You're at It

Once you've set up the GET.CELL helper column, you've also solved counting colored cells. Use =COUNTIF(HelperRange, 6) to count yellow cells, where 6 is the color index. Same setup, two payoffs.

People ask about counting cells by color on every Excel forum. The helper-column trick is the canonical answer. It also pairs well with pivot tables in excel — drop the color index into a PivotTable's Rows area and you get instant counts per color, no formula required.

One last thing worth knowing before you close this tab. Color-based filtering is fundamentally a display trick. Excel doesn't store "this row is yellow" as data — it stores the color as a formatting attribute attached to the cell. That's why removing duplicates with how to remove duplicates in excel doesn't consider color at all. If you want color to count as data, you need the helper column. Period.

The same logic explains why sorting by color and sorting alphabetically give different results even when the data looks identical to your eye. Color is metadata. Text is data. Excel treats them in completely separate code paths.

Common Mistakes That Break Color Filtering

A few errors come up over and over in support threads. Worth seeing them before you hit them yourself.

Mistake one: filtering before turning a range into a Table. If you apply Data → Filter to a regular cell range, the filter doesn't auto-expand when you add new rows. Add 50 rows tomorrow and they sit outside the filter, invisible. Press Ctrl+T first to convert to a Table. Filter then auto-includes new rows.

Mistake two: applying color via a theme instead of fill. Theme colors (the muted palette in Page Layout) aren't always stored as cell fills. Excel sometimes treats them as inherited formatting, which the filter dropdown ignores. If your color is missing from the Filter by Color submenu, that's the cause 80% of the time. Re-apply the color directly through Home → Fill Color and the filter sees it.

Mistake three: assuming filter and sort behave the same. They don't. Filter is non-destructive — it hides rows. Sort rewrites the row order permanently. Mix the two on a sheet with formulas referencing specific row numbers and you'll spend an afternoon rebuilding references. Save before you sort.

When to Skip Filter by Color Entirely

Worth being honest. For datasets over 100,000 rows, color filtering gets slow. Excel has to scan every cell's formatting attribute, and that's expensive when fills come from conditional formatting rules with formulas inside them. If your sheet takes more than a second to apply the filter, switch strategies.

The right move for large datasets is a value-based column. Add a status column with text values like "Open", "Closed", "Pending" and color the cells based on that column with conditional formatting. Then filter on the value column, not the color. Same visual outcome, fraction of the lookup time. The excel formulas guide covers IF and IFS patterns for generating status values automatically.

And one final scenario where color filtering simply doesn't fit. If your team works across Excel, Google Sheets, and CSV exports, color is fragile. Export to CSV and every fill disappears. Open in Google Sheets and conditional formatting rules may not translate. For cross-platform data, store status as text from day one. Color stays a presentation layer, not a data layer.

Bottom line: color filtering is fast, visual, and built right into Excel — but it has hard limits. Single color per column. One sheet at a time. No CSV survival. Know the limits before you build a workflow around it. With the helper-column workaround in your toolkit, you can stretch those limits far enough for almost any real-world report.