How to Count Highlighted Cells in Excel

Q: How do I count colored cells in Excel on a Mac?

The formula-free methods all work on Mac: Find & Select → Find → Format → Choose Format From Cell and Filter by Color behave the same as on Windows, and the GET.CELL named-formula trick works in a .xlsm workbook. VBA also runs on Mac, but the Mac Visual Basic Editor is more limited — there is no macro recorder on Mac for most versions, so you cannot record a color-count macro and must type or paste the code in manually. For color-reading specifically, prefer Filter by Color + SUBTOTAL on Mac to avoid VBA entirely.

Excel has no built-in function that counts cells by fill or highlight color, so you count highlighted cells with one of four workarounds: Find & Select’s Find by Format paired with a helper count, a legacy GET.CELL named formula, SUBTOTAL combined with filter-by-color, or a short VBA macro. The right method depends on whether the highlight comes from manual fill color (use Find by Format, GET.CELL, filter-by-color, or VBA) or from conditional formatting (use the same logical rule in a COUNTIF/COUNTIFS instead).

Counting highlighted cells is a common task in Excel, especially when working with large data sets. Highlighting cells can be an effective way to visually organize and analyze data. However, counting the highlighted cells can be a time-consuming process if done manually. Fortunately, Excel provides a simple and straightforward method to count highlighted cells using the built-in conditional formatting feature. In this blog post, we’ll show you how to count highlighted cells in Excel with just a few simple steps.

Step 1: Select the Data Range

Before we begin the process of counting the highlighted cells, we first need to select the range of cells we want to count. Simply click and drag over the cells you want to include in your count.

Step 2: Click on ‘Conditional Formatting’

Next, click on the ‘Conditional Formatting’ option located under the ‘Home’ tab in the Ribbon.

Step 3: Select ‘Highlight Cells Rules’

From the ‘Conditional Formatting’ drop-down menu, select ‘Highlight Cells Rules’.

Step 4: Choose the Type of Highlight to Count

Choose the type of highlight you want to count. In this case, we will select ‘Equal To’ and choose the color of the highlight you want to count.

Step 5: Specify the Counting Range

In the ‘Equal To’ box, specify the count of the highlighted cells you want to find. Keep the ‘Format’ drop-down set to ‘Default’ and click ‘OK’.

Step 6: View Your Results

You should now see the count of the highlighted cells in the specified range.

Bonus Tip: Use a Formula

If you want to automate the process of counting highlighted cells, you can use a formula — but with an important caveat. COUNTIF and COUNTIFS match cell values (a specific word, number, or condition), not manual fill colors, because Excel has no native function that reads a cell’s color. So =COUNTIF(range,"Done") works when the highlight is driven by a value you can also test, and the same logic works for cells highlighted by conditional formatting — just point COUNTIF at the same rule the formatting uses. To count cells that were manually filled with a color, use the legacy GET.CELL named-formula trick or the VBA macro below instead.

Bonus Tip: GET.CELL Named Formula

GET.CELL is a legacy macro function that can read a cell’s fill color, and you can wrap it in a named formula to avoid VBA. Go to Formulas → Define Name, name it something like CellColor, and set Refers to to =GET.CELL(38,Sheet1!A1) (38 returns the fill ColorIndex; adjust the sheet/cell reference). In a helper column type =CellColor next to your data, then use =COUNTIF(helper_range, color_number) to count a given color. Because it relies on a macro function, the workbook must be saved as macro-enabled (.xlsm).

Bonus Tip: Filter by Color with SUBTOTAL

If your range has a header, turn on filters, click the filter arrow, and choose Filter by Color to show only the highlighted cells. Put =SUBTOTAL(103,range) (the 103 function counts visible non-blank cells) below the column and it updates to show the count of the filtered color. See filtering by color for the full walkthrough.

Bonus Tip: Find & Select by Format

You can also use Home → Find & Select → Find, click Format → Choose Format From Cell, pick a highlighted cell, then Find All. The status bar of the results dialog reports how many cells matched that exact format — a quick, formula-free count.

Now that you know how to count highlighted cells in Excel, you will save time and increase your productivity when working with large datasets. Happy Excel-ing!

Using VBA to Count Highlighted Cells in Excel

If you’re comfortable with using VBA (Visual Basic for Applications), you can automate the process of counting highlighted cells with a few lines of code. First, open the Visual Basic Editor by pressing ‘Alt’ + ‘F11’. Then, insert a new module and copy and paste the following code:

Sub CountHighlightedCells()
    Dim rng As Range
    Dim c As Range
    Dim cnt As Long
    Set rng = Selection
    cnt = 0
    For Each c In rng
        If c.Interior.ColorIndex = 6 Then 'Replace 6 with the number of the color you want to count
            cnt = cnt + 1
        End If
    Next c
    MsgBox "The range " & Selection.Address & " contains " & cnt & " highlighted cells.", vbInformation, "Highlighted Cell Count"
End Sub

In the code above, we first define the range of cells we want to count by using the ‘Selection’ object. Then, we create a loop that checks each cell in the range to see if it has the highlight we want to count. If it does, we add 1 to our count. Finally, we display the total count in a message box.

Limitations of Counting Highlighted Cells

While counting highlighted cells can be a useful tool for visualizing and analyzing data in Excel, it’s important to remember its limitations. Counting highlighted cells does not provide any information about the actual data in those cells, and it’s possible to miss important information while focusing solely on the highlighted cells. It’s best to use highlighting as a complement to a more comprehensive data analysis approach.

In Conclusion

Counting highlighted cells is a simple and effective way to organize and analyze data in Excel. By following the steps outlined above, you can quickly and easily count the number of cells with a specific highlight. Whether you’re new to Excel or an experienced user, this technique can save you valuable time and increase your productivity.

Frequently Asked Questions

Here are answers to some common questions you may have about counting highlighted cells in Excel:

Can I count cells that are highlighted with a pattern instead of a color?

Yes, you can count cells based on patterns by using the ‘Conditional Formatting’ feature in Excel. Simply select the option ‘Use a formula to determine which cells to format’, and then use a formula to define the pattern. You can then use the ‘COUNTIF’ formula or the VBA code outlined above to count the cells with the specified pattern.

Is there a way to count cells with multiple colors?

Yes, you can use conditional formatting to highlight cells with multiple colors. When you select ‘Equal to’ in the ‘Highlight Cells Rules’ option, you can choose the drop-down option ‘More Colors’, and then select up to three colors to highlight together. You can then use the ‘Conditional Formatting’ to count the cells with the specified colors.

Can I count cells that are highlighted with a theme color?

Yes, you can use the ‘Use a formula to determine which cells to format’ option in the ‘Conditional Formatting’ menu to count cells that are highlighted with a theme color. In the formula, use the INDEX function to retrieve the theme color value, and then use an ‘Equal To’ rule to highlight the cells that match that value. You can then use the ‘COUNTIF’ formula or the VBA code outlined above to count the cells with the specified color.

Is there a way to count cells that are highlighted in a row or column?

Yes, you can use the ‘Conditional Formatting’ feature and then select the ‘Use a formula to determine which cells to format’ option. In the formula, use a reference to the first cell in the row or column, and then use an ‘Equal To’ rule to highlight the cells that match the criteria. You can then use the ‘COUNTIF’ formula or the VBA code outlined above to count the cells with the specified color.

Can I use conditional formatting to highlight cells based on a formula?

Yes, you can use a formula to highlight cells that match specific criteria. For example, you can use a formula to highlight cells that contain a certain word or value. You can then use the ‘Conditional Formatting’ feature and the ‘Use a formula to determine which cells to format’ option to highlight the cells that match the criteria. You can then use the ‘COUNTIF’ formula or the VBA code outlined above to count the cells with the specified criteria.

Is there a built-in function to count cells by color?

No. Excel has no native COUNTBYCOLOR (or similar) function in any version on Windows or Mac, and COUNTIF/COUNTIFS cannot read fill color. To count manually filled cells you must use a workaround: Find & Select’s Find by Format, the legacy GET.CELL named formula, Filter by Color with SUBTOTAL, or a VBA macro. When the color comes from conditional formatting, count it with a COUNTIF/COUNTIFS that re-uses the formatting rule’s logic.

How do I count colored cells in Excel on a Mac?

The formula-free methods all work on Mac: Find & Select → Find → Format → Choose Format From Cell and Filter by Color behave the same as on Windows, and the GET.CELL named-formula trick works in a .xlsm workbook. VBA also runs on Mac, but the Mac Visual Basic Editor is more limited — there is no macro recorder on Mac for most versions, so you cannot record a color-count macro and must type or paste the code in manually. For color-reading specifically, prefer Filter by Color + SUBTOTAL on Mac to avoid VBA entirely.

Does the VBA macro update automatically when colors change?

No. Excel does not treat a cell’s fill color as a value that triggers recalculation, so a VBA function or the GET.CELL helper will not refresh by itself when you change a color. You must re-run the macro (or press a forced recalculation) after recoloring cells. This is one reason filtering by color with SUBTOTAL(103,…) is handy — the count updates as soon as you re-apply the color filter.

What’s the difference between counting manual highlights and conditional formatting?

A manual highlight is a fill color you applied by hand, which is why it needs a color-reading workaround like GET.CELL or VBA. A conditional formatting highlight is generated by a rule, so the underlying condition is what matters — count it directly with COUNTIF/COUNTIFS using the same test, or see counting conditionally formatted cells. For a broader look at color-based counting, see counting colored cells.

Why does my GET.CELL formula return an error or 0?

Two common causes: the workbook is not saved as macro-enabled (GET.CELL is a legacy macro function and requires a .xlsm file), or the cell’s color comes from conditional formatting rather than a manual fill — GET.CELL(38,…) reads only the manually applied ColorIndex, not formatting-rule colors. Also make sure the named formula’s reference (e.g. Sheet1!A1) is relative so it follows the helper column down the rows.