stats

3 Ways To Count Highlighted Cells In Excel

Intro

Excel is an incredibly powerful tool that has been a staple of modern computing for decades. One of the most common tasks that users perform in Excel is counting cells that meet specific criteria. While Excel has a built-in function for counting cells based on various conditions, it does not have a native function for counting highlighted cells. However, there are several workarounds that you can use to achieve this. In this article, we will explore three different methods for counting highlighted cells in Excel.

Method 1: Using VBA Macro

Excel VBA Macro

One of the most straightforward ways to count highlighted cells in Excel is by using a VBA macro. VBA (Visual Basic for Applications) is a programming language that is built into Excel, allowing you to automate repetitive tasks and create custom functions. To count highlighted cells using a VBA macro, follow these steps:

  • Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
  • In the Visual Basic Editor, click Insert > Module to create a new module.
  • Paste the following code into the module:
Function CountHighlightedCells() As Long
    Dim cell As Range
    CountHighlightedCells = 0
    For Each cell In Selection
        If cell.Interior.ColorIndex <> xlNone Then
            CountHighlightedCells = CountHighlightedCells + 1
        End If
    Next cell
End Function
  • Close the Visual Basic Editor and return to your Excel worksheet.
  • Select the range of cells that you want to count.
  • Type =CountHighlightedCells() into a cell and press Enter.

The CountHighlightedCells function will return the number of highlighted cells in the selected range.

How it Works

The CountHighlightedCells function uses a loop to iterate over each cell in the selected range. It checks the Interior.ColorIndex property of each cell to see if it has a fill color. If the cell has a fill color, it increments the count. Finally, it returns the total count.

Method 2: Using Conditional Formatting

Excel Conditional Formatting

Another way to count highlighted cells in Excel is by using conditional formatting. Conditional formatting is a feature in Excel that allows you to format cells based on specific conditions. To count highlighted cells using conditional formatting, follow these steps:

  • Select the range of cells that you want to count.
  • Go to the Home tab in the ribbon and click on Conditional Formatting > New Rule.
  • Select "Use a formula to determine which cells to format" and enter the following formula:

=CELL("format",A1)>0

  • Click on the Format button and select a fill color.
  • Click OK to apply the rule.

This will highlight all cells in the selected range that have a fill color.

How it Works

The CELL function returns information about the formatting of a cell. The "format" argument returns a value that indicates the formatting of the cell. If the cell has a fill color, the value will be greater than 0. The conditional formatting rule uses this formula to highlight cells that have a fill color.

Method 3: Using Power Query

Excel Power Query

Power Query is a feature in Excel that allows you to import and transform data from various sources. You can also use Power Query to count highlighted cells. To count highlighted cells using Power Query, follow these steps:

  • Select the range of cells that you want to count.
  • Go to the Data tab in the ribbon and click on From Table/Range.
  • In the Power Query Editor, click on Add Column > Custom Column.
  • Enter the following formula:

=if [Color] <> null then 1 else 0

  • Click OK to add the column.
  • Click on the new column header and select Group By.
  • Select the new column and click on Sum.

This will return the number of highlighted cells in the selected range.

How it Works

The Power Query formula checks if the cell has a fill color by checking if the Color column is not null. If the cell has a fill color, it returns 1, otherwise it returns 0. The Group By and Sum functions then return the total count.

Gallery of Excel Highlighted Cells

We hope this article has been helpful in teaching you how to count highlighted cells in Excel. Whether you use a VBA macro, conditional formatting, or Power Query, there are many ways to achieve this task. Do you have any questions or comments about this article? Please let us know in the comments below!

Jonny Richards

Love Minecraft, my world is there. At VALPO, you can save as a template and then reuse that template wherever you want.