Count Cells by Color in Excel Without VBA: Best Methods

VBA & macros · Updated November 19, 2026

Yes. You can count cells by color in Excel without writing VBA: filter the range by color and count the visible records, or use a helper column based on the legacy GET.CELL function. If the color represents a rule—such as “Late” or “Over budget”—count the underlying values instead; that is usually the most reliable option.

Why Excel has no standard count-by-color formula

Excel’s familiar worksheet functions, including COUNTIF and COUNTIFS, evaluate cell values, not a cell’s fill color. A colored cell is formatting, and two cells that look identical can have different values—or no value at all. Conversely, cells with the same value can be colored differently by hand. There is no built-in COUNTBYCOLOR function in Microsoft 365 for Windows.

The right method depends on what the color means and whether you need a quick one-time total or a result that updates as the workbook changes. Filtering is straightforward for a quick count. A GET.CELL helper can report fill-color codes, but it has limitations. If color is applied by conditional formatting to represent data, counting the condition is generally better than counting the appearance.

Method 1: Filter by color and count visible records

For a quick count of colored rows, Excel’s filter menu can show only rows whose cells have a chosen fill color. Then use SUBTOTAL on a column that contains a value in every row. This works well when a table has a unique ID or other nonblank field for each record.

  1. Select the table or range, including its headers, and choose Data > Filter. Excel adds filter arrows to the header row.
  2. Open the dropdown for the column containing the fill colors. Choose Filter by Color, then select the fill color to count.
  3. With IDs in A2:A100 and the colored cells in B2:B100, place the following formula in an empty cell. It counts the visible, nonblank IDs after filtering:

With an ID in every row in A2:A100 and the color filter applied to B2:B100, use =SUBTOTAL(103,A2:A100). For example, if 12 rows remain visible and all 12 have IDs, the result is 12.

Function number 103 tells SUBTOTAL to count nonblank cells while excluding rows hidden by a filter. It also ignores rows hidden manually. If the chosen count column contains blanks, the result will be lower than the number of visible rows; use a reliably populated ID column instead. Clear the color filter when you want to see all the records again.

This method is convenient, but it is an interactive view rather than a permanent count for every color. To compare several colors, repeat the filter and note each result, or use a helper method below. Excel for the web also supports filters, though the exact controls available can depend on the workbook and web interface. On Mac, the same filtering approach is available, but menu placement can differ from Windows.

Method 2: Use GET.CELL in a named formula

For manually applied fill colors, a helper column can return a color code for each cell. GET.CELL is an old Excel 4.0 macro function, not an ordinary worksheet function: you enter it through a defined name, then use that name in cells. It does not require writing a VBA procedure, but it is a legacy feature and may be blocked by an organization’s security settings.

  1. Assume the cells to inspect are A2:A100. Insert a helper column immediately to the right, such as column B, and select B2.
  2. Choose Formulas > Name Manager > New. Name the definition ColorCode and enter =GET.CELL(63,INDIRECT("RC[-1]",FALSE)) in the Refers to box. The relative reference points one column left from the helper cell. Choose OK, then close Name Manager.
  3. In B2, enter the formula below and fill it down through B100:

With the color cells in A2:A100 and the helper column in B, enter =ColorCode in B2 and copy it down. Each helper result represents the fill-color code of the cell immediately to its left. The number itself is not a universal name for a color; compare codes from cells in the same workbook.

To count cells matching the fill of A2, use this formula with the helper results in B2:B100:

=COUNTIF(B2:B100,B2)

If A2’s fill occurs in seven cells in the inspected range, this formula returns 7. To count a different color, use the helper code for a cell with that color as the COUNTIF criterion. Keep the helper column beside the source column, since the defined name is designed to look one column to the left.

Color codes should be treated as a practical comparison aid, not a perfect description of every visible shade. Test the result on representative cells, especially if the workbook uses custom colors, themes, or many similar shades. GET.CELL reads cell formatting, so it is not a dependable way to count the color displayed because of a conditional-formatting rule.

Important limits of the GET.CELL approach

GET.CELL does not behave like a normal formula that automatically recalculates whenever you change a fill color. A formatting change may leave the helper values unchanged until Excel recalculates them. After changing fills, select the helper formulas and press F9 to recalculate; if needed, press Ctrl+Alt+F9 to force a full calculation. On Mac, use Fn+F9 if the function keys control system features, or use Excel’s calculation commands.

There are other trade-offs to keep in mind:

  • Security: GET.CELL is an Excel 4.0 macro function. Security policy may restrict it, and you should not lower security protections just to make a workbook work.
  • Sharing: Recipients need a compatible Excel environment and may need to allow the legacy function. Do not assume the helper will behave the same in every spreadsheet application.
  • Scope: The example reports fill-color information, not font color, borders, or the visual color created by conditional formatting.
  • Maintenance: A helper column adds setup and recalculation work. Document what it does so another person does not mistake the codes for meaningful data.

Method 3: Count the rule behind the color

If colors are assigned to communicate a value or status, count that value rather than the formatting. This approach is easier to audit, updates with the data, and works whether the color was applied manually or by conditional formatting. For example, a red fill might mean that a task’s status is “Late.” If the status is stored in column A, count the word “Late.”

With status labels in A2:A100, use =COUNTIF(A2:A100,"Late"). If nine cells contain the exact text Late, the formula returns 9. If the fill rule is based on a number instead, count the numeric condition. With scores in A2:A100, for example, count values below 50 with =COUNTIF(A2:A100,"<50"); if 3 scores are below 50, the result is 3.

For a conditional-formatting rule, inspect the rule that creates the color: select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. If the rule says that a status equals “Late,” COUNTIF can count that status directly. If a rule uses a formula or multiple tests, build a matching COUNTIF or COUNTIFS formula around the source data. Be sure the count formula uses the same conditions as the formatting rule, including boundary cases such as “greater than” versus “greater than or equal to.”

Find cells with a particular fill

Excel’s Find dialog can locate cells by formatting, which can be useful for a small, one-time check. Press Ctrl+F, choose Options, open the Format menu, and choose Choose Format From Cell if that option is available. Click a cell with the fill you want to search for, then choose Find All. The results list shows matching cells, which you can review or select.

Finding formatting is not the same as creating a live count formula. The dialog’s format criteria may also reflect other formatting characteristics, so check the results rather than assuming every match has only the same fill. The interface can differ across Excel versions and platforms; Mac users can open Find from Edit > Find or use the available keyboard shortcut. For repeatable totals, a filter or a data-based formula is usually clearer.

Choosing the right method

  • Choose Filter by Color for a fast, visual count of nonblank records in a filtered list.
  • Choose GET.CELL when the fills are manually applied, you need to compare several colors, and your environment allows the legacy function.
  • Choose COUNTIF or COUNTIFS when each color represents a value, status, threshold, or other rule that can be expressed in the data.
  • Choose Find by Format for occasional locating or selecting, not for a maintained summary.

If a workbook will be used for reporting, consider adding a status field such as “On track,” “Review,” or “Late,” then use conditional formatting to display the colors. That makes the meaning available to formulas, filters, readers who cannot distinguish particular colors, and future users who need to understand why a cell is highlighted.

Common problems and checks

A count that seems wrong often comes from a mismatch between the visible formatting and what the method actually examines. First ask whether the fill was applied manually or by conditional formatting. A manual-fill helper and a value-based count answer different questions; neither should be assumed to represent the other.

  • Filtered count is too low: Check that the SUBTOTAL range contains no blanks. Count a populated ID column rather than the colored column if some colored cells are empty.
  • Helper codes do not change: Recalculate after changing fill colors. If Excel blocks the name, check your organization’s security policy instead of weakening it.
  • Colors look alike but counts differ: Similar-looking shades can be different fills. Compare helper codes and verify the actual formatting of sample cells.
  • Conditional colors are missing: GET.CELL is not a reliable count of conditional-formatting display colors. Count the rule’s underlying values or filter by a relevant value.
  • Count changes after sorting: Check that the helper formulas remain aligned with their source cells and that the filtered range includes the full table.

Frequently Asked Questions

Can COUNTIF count cells by fill color?
No. COUNTIF evaluates cell values against a criterion; it does not test fill color. Use Filter by Color, a GET.CELL-based helper for manual fills, or count the condition that causes the color.

Can I count cells colored by conditional formatting?
Count the condition that triggers the formatting whenever possible. For example, if a rule colors statuses equal to “Late,” count “Late” in the status range. A formula that attempts to inspect the displayed conditional-format color is more fragile than counting its underlying rule.

Will the GET.CELL count update when I change a fill?
Not always automatically. Formatting changes may not trigger recalculation of the helper results, so recalculate the formulas after editing fills and verify the values. The function is also subject to legacy Excel macro-function security restrictions.

Can I count blank cells that have a color?
GET.CELL can return fill information for a blank cell, but a filter-and-SUBTOTAL count of nonblank cells will not count blank records in the counted column. For filtered rows, use a separate ID column populated on every record. Choose the method based on whether you mean colored cells or rows containing data.

Does this work in Excel for the web?
Filtering is available in Excel for the web, though controls can vary. Do not rely on a GET.CELL defined name working consistently in the web app or other spreadsheet programs; use a value-based formula or a compatible desktop Excel workflow for a dependable result.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

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

Spotted an error in this guide? Let us know.