Count Cells by Color in Excel: Practical Guide
Learn practical methods to count cells by color in Excel using GET.CELL, VBA UDFs, and conditional formatting strategies. Step-by-step guidance, examples, tips, and reliability considerations for manual colors and color rules.
Excel doesn't have a single built-in formula to count cells by color. To tally colored cells, use a GET.CELL-based helper with SUMPRODUCT or write a small VBA UDF. This quick answer previews both approaches and when to use each, so you can pick the method that fits your workbook style.
Why color counting is tricky in Excel
Color coding in Excel is a visual cue, not a stored numeric value. Excel does not expose a universal, built‑in function to count cells by fill color. This creates two broad scenarios: manually colored cells and colors produced by conditional formatting. According to XLS Library, counting by color typically requires a helper approach or a small macro, not a single worksheet formula. This means you’ll either establish a color-index helper via GET.CELL or implement a VBA UDF to tally the cells that match your target color. The trade‑offs include workbook portability, ease of maintenance, and how consistently the colors are applied across sheets and workbooks.
Common approaches to counting by color
There are three practical approaches most Excel users employ. First, the legacy GET.CELL method, which relies on a defined name to pull the color index for each cell. Second, a VBA User-Defined Function (UDF) that accepts a range and a color, returning the count. Third, counting by color when colors are driven by conditional formatting by reusing the same underlying condition in a helper column or by applying a color-based test in a parallel calculation. Each method has benefits and drawbacks, particularly around portability and performance. For ongoing workbooks, consider documenting the method so others can reproduce the result.
Method 1: GET.CELL + a named range (no macros)
GET.CELL is an Excel 4 macro function that can be used to fetch a cell’s color index. Start by creating a defined name that returns the color index for the first cell in your target range, then apply the same formula to adjacent cells. Finally, use SUMPRODUCT to count how many cells in the index array match the color you want. Example:
- Define Name: ColorIndex = GET.CELL(63,Sheet1!A1)
- Fill across: In a helper range (e.g., B1:B100), set =GET.CELL(63,Sheet1!A1) and fill down
- Count: =SUMPRODUCT(--(ColorIndexRange=ColorToCount))
Note: This approach relies on older Excel macro functions and may behave differently across Excel versions. Keep your workbook documentation clear so collaborators understand the helper range and the color you’re counting.
Method 2: VBA User-Defined Function (UDF) to count color
A small VBA UDF provides a straightforward way to count colored cells. Open the VBA editor (Alt+F11), insert a module, and paste this function:
Function CountColor(rng As Range, color As Long) As Long
Dim c As Range
Dim cnt As Long
For Each c In rng
If c.Interior.Color = color Then cnt = cnt + 1
Next c
CountColor = cnt
End FunctionUsage: =CountColor(A1:A100, RGB(255,0,0)) or =CountColor(A1:A100, ColorIndexFromCell)
Tips:
- Pass a reference color as RGB or obtain the color from a sample cell using ColorIndex.
- Save the workbook as .xlsm to preserve the macro.
- Use Option Explicit and comments for maintainability.
Method 3: Count by color when colors are driven by conditional formatting
If the color comes from a conditional formatting rule, you can’t reliably extract the color via a direct color property. Instead, replicate the condition in a helper column. For example, if cells turn red when value < 0, create a parallel column with the same logical test (e.g., =A2<0) and count TRUE values. This ensures your color count remains accurate even if the formatting changes. Alternatively, apply a custom function that tests the same rule used by the CF rule and count its TRUE results.
Practical examples: sample workbook layout
Layout A (manual colors): Data in A2:A100 with fill colors chosen by the user. Use a UDF or GET.CELL-based approach to summarize counts in B2:B100 and a final tally in B101. Layout B (conditional colors): Use a helper column C with the same logic as the CF rule (e.g., =A2<0) and count C:C with SUMPRODUCT. In both layouts, document which color (or condition) you’re counting and keep a sample color map to avoid miscounts.
Performance, reliability, and maintenance considerations
GET.CELL-based methods can be sensitive to workbook state and Excel version; results may vary between environments. UDFs run quickly on small ranges but can slow down large sheets. If you distribute the workbook to others, provide clear steps and a small FAQ, and consider a dedicated worksheet tab that explains the chosen approach. For long-term use, document your color criteria and keep a changelog for color changes.
Tools & Materials
- Excel installed (Windows or macOS)(Prefer a recent version (Excel 2016 or newer) for better compatibility with newer features.)
- Target data range(Identify the range you will count colors in (e.g., A2:A100).)
- GET.CELL helper setup (optional)(Create via Formulas > Name Manager > New; formula: =GET.CELL(63,Sheet1!A1))
- VBA editor access (optional)(ALT+F11 to insert a VBA module if using the CountColor UDF.)
- Macro-enabled workbook or trusted access(If using the UDF, save as .xlsm and enable macros.)
Steps
Estimated time: 20-40 minutes
- 1
Decide on counting method
Review the data and determine whether colors are manual or conditional. Choose between GET.CELL (no macros) or a VBA UDF for counting, considering workbook distribution and maintenance needs.
Tip: Document the chosen method early to avoid compatibility issues later. - 2
Set up a color reference
For GET.CELL, create a color index helper by defining a named formula that returns the color index of a sample cell. For VBA, determine the RGB or color index value you’ll count.
Tip: Use a single sample cell to define the target color to count. - 3
Implement the counting mechanism
If using GET.CELL, populate a helper range with the color index values and apply a SUMPRODUCT count. If using a UDF, paste the code in a module and call the function in a cell.
Tip: Keep the helper range clearly labeled to prevent errors during edits. - 4
Test with various colors
Change a few cells’ colors to verify that the count updates correctly. Ensure the target color is consistently applied across the range.
Tip: Include color variations to validate edge cases. - 5
Document and save
Add a short note or sheet documenting the method, color criteria, and any limitations. Save the workbook in a macro-enabled format if using UDF.
Tip: Maintain a changelog for future color rule changes.
People Also Ask
Can I count cells by color with a built-in Excel function?
No. Excel does not include a single built-in function to count cells by color. You must use a helper approach (GET.CELL) or a VBA UDF to tally by color.
Excel lacks a native color-count function; use a helper or macro and then sum the results.
What is GET.CELL and is it safe to use?
GET.CELL is part of Excel's older 4-macro functions. It can return a cell's color index but is not officially documented for general use in modern workbooks. Use with caution and clearly document its behavior.
GET.CELL is an old Excel macro function; use it carefully and document its behavior.
Do I need to enable macros to use a color-counting UDF?
Yes. If you use a VBA UDF, you must enable macros and save the workbook as a macro-enabled file (.xlsm). Without enabling macros, the UDF will not run.
Macros must be enabled for VBA functions to work.
Can I count by font color or only fill color?
Excel's typical color-counting approaches target cell fill color. Counting font color is possible with a custom VBA function or by extracting font color properties, but it’s less common and requires additional code.
Usually you count fill color; font color counting needs extra code.
What if my colors come from conditional formatting?
If colors are from conditional formatting, replicate the same condition in a helper column or use a UDF that tests the underlying rule. This keeps counts accurate even when formats change.
For conditional colors, mirror the rule in a helper column to count.
Are there safer alternatives to counting by color?
Yes. Instead of color counting, count based on the underlying data criteria (e.g., values, categories) or use filters/slicers to group by color-driven rules. This avoids brittle color-based formulas.
Count by data criteria or use filters instead of relying on color alone.
Watch Video
The Essentials
- Color counting in Excel requires a helper or macro, not a single built-in function
- GET.CELL and UDFs are two viable paths; choose based on distribution and maintenance needs
- If colors come from conditional formatting, replicate the underlying rule in a helper column for reliability
- Document the method and test thoroughly on representative data
