How To Count COLORED Cells in ExcelStep by Step Guide using 4 Different Methods

Last Updated: February 9, 2023

Coloring your cells with fill colors can be a great tool for organizing and presenting your data in an easy-to-understand format.

However, these fill colors can also be used to help color code data for quantitative uses as well.

There are several methods that can be used to count colored cells so that this data can be analyzed.

By learning about a few of these methods, you can determine which is most suited for your own needs.

With that said, here are a few of the methods that you can use to count colored cells in your Excel worksheet.

Why Would You Want To Count Colored Cells?

Often you may color cells in a worksheet in order to categorize certain information, represent the status of certain categories, make it easier to understand, or even just improve its appearance.

These are all equally valid reasons to use colored cells, and in each of these cases, you may have reason to count these cells.

For example, consider a manager reviewing a spreadsheet of active employees while creating schedules for the upcoming week.

On this spreadsheet, employees that are currently available are displayed in green, employees that are currently on leave are in yellow, and employees that are currently out sick are displayed in red.

If the manager wants to see how many employees are currently able to work in the upcoming week in order to begin making schedules, they may want to be able to quickly count how many cells are in green without manually counting them.

Counting colored cells can also be helpful for:

Comparing differently colored categories of data;
Simplifying reports that separate data using colored cells;
Improving the utility of data in spreadsheets that incorporate colored cells for visual appeal;
Providing more exact results on data in spreadsheets containing a large amount of data;
Converting data into an easier-to-use format for performing analytical functions.

How To Use “Find and Select” To Count Colored Cells

One of the quickest and easiest ways to count colored cells is by using Excel’s “Find & Select” tool. This does not require any difficult-to-understand formulas and can be accomplished simply by using the tools available on the ribbon.

You can easily view the count and then record the results manually if you need to.

You will need to start by selecting “Find & Replace.” Go to the “Home” tab, and in the “Editing” group, select the “Find & Replace” button. The icon will look like a magnifying glass with the words “Find & Replace” located below it.

Once selected, the “Find & Replace” button will open a drop-down menu, and here you will select “Find” to open the “Find & Replace” menu. Alternatively, you can also open this menu with a keyboard shortcut by pressing the keys “CTRL” + “F.”

Navigate to the “Find” tab and select the small arrow located in the “Format” button. On the drop-down menu, select the “Choose Format From Cell” option. Using this option, you can click on a cell to let Excel know what type of cell you would like it to count. Select a cell containing the color you would like Excel to count for you, and once you do, the “Preview” should change to show the color of the selected cell.

Once the Preview button contains the correct color, simply select “Find All.” Excel will then list all of the cells that meet your defined criteria in the small box on the lower portion of the “Find & Replace” box. It will also provide a count of all the cells that match the formatting below on the bottom-left of the “Find & Replace” box. This number is the final count of all cells containing your selected color.

Using a Filter to Count Colored Cells

If you intend to keep updating the data in a workbook and wish to keep a dedicated count of the colored cells, then this second method is often the best way.

This method involves the use of both a filter and a function to count colored cells, and here is how.

Select all of the data in your worksheet to allow Excel to identify all of the colored cells. Navigate to the “Data” tab on your ribbon and select the “Filter” button from the “Sort & Filter” group. This will add filter tabs to your column headers which will look like a small arrow at the top of each column.
Now click an employ cell below your data and enter the “SUBTOTAL” function, which will instruct Excel to count the number of cells in a given range. Once filters are applied, this will only count the colored cells. This formula is =SUBTOTAL(number of cells, starting range: ending range).
Select the “Filter” tab on the appropriate column and choose “Filter by Color.” Choose the color you want Excel to count, and any cells that do not contain this color will be removed from sight.
Now that only the colored cells remain, the subtotal function below will only show the count of the remaining cells. This will give you the total of all colored cells.

This is an easy method for filtering out a given color of the cell to be counted whenever it is needed.

This also has the advantage of allowing you to apply other filters as well to examine your data in different ways.

How To Count Colored Cells With a Table

You can also use a table to count colored cells which is a strong option because, in most cases, a table is a good choice for organizing and analyzing stored data.

This makes it easier to apply filters like the above and gains access to additional features that can only be applied if data is in a table.

To count cells using this method, follow these steps.

A good way to begin is by isolating each color using filters as above. This will remove all cells except the color you choose to remain. You may also wish to keep your full data set intact, in which case you can also copy and paste all of your cells into a separate worksheet beforehand.
Next, create a table by selecting any cell in your data set and navigating to the “Insert” tab. Within the “Tables” group, select the “Table” button. This will bring up the “Create Table” dialog box, which should have selected all the cells in your data set. If your data set has column headers, select the “My table has headers” option and “OK.”
Excel will format your data into a table; with a cell in the table selected, navigate to the “Table Design” tab. This should be located on the right-hand side of the ribbon whenever a cell in the table is selected.
In the “Table Styles Options” group, select the “Total Row” option. This will add a new row to the bottom of your table. On the bottom left, the word “Total” will be displayed, and on the right-hand side, a number. When this number is selected, a small arrow will be displayed to the right. Select this arrow to bring up a list of options. Select count to display a count of the number of cells in your table.

In addition to providing a count of your colored cells, an Excel table is a strong option due to the suite of analytical and organizational features it can provide.

Also, it makes it easy to present your data in a clear and well-formatted manner.

How To Count Colored Cells With VBA

For those comfortable working with using Excel’s underlying programming language, it is also possible to use Visual Basic for Applications (VBA) codes to count colored cells.

This has the advantage of being flexible and, once first applied, easy to reapply way to count the colored cells in your data sets.

You can do this by creating a “User Defined Function” and inputting it into the VBA editor.

Here is how.

Open the VBA editor by going to the “Developer” tab and selecting the “Visual Basic” button from the “Code” group. If you do not have the “Developer” tab, you can add this by going to the “File” tab, “Options,” and “Customize Ribbon.” Here you can look under “Main Tabs” and select the “Developer” check box.
Insert a new module from the “Insert” menu and then “Module.”
Insert this code into the code window and put it into the current workbook or your Personal Macro Workbook.

Function COLORCOUNT(CountRange As Range, FillCell As Range)

Dim FillColor As Integer

Dim Count As Integer

FillColor = FillCell.Interior.ColorIndex

For Each c In CountRange

    If c.Interior.ColorIndex = FillColor Then

        Count = Count + 1

    End If

Next c

COLORCOUNT = Count

End Function

You can use this function like any other, and it will count the cells within a range and update as you edit your data.

This is not quite as easy and user-friendly to set up in the first place as the other options above, but it is extremely flexible and easy to use once it is set up.

Tips for Ensuring Accuracy

In order to ensure that your results are reliable, it is important to keep a few things in mind when relying on new techniques to count large datasets where you are not able to manually ensure accuracy.

First of all, try to ensure that the method you choose is working correctly by testing it with a simple data set that can be manually verified.

This is particularly important when applying a new formula before applying it to more complex or large data sets.

Next, try to ensure that you are using the same method to count cells with each distinct color in your dataset.

Though each method should provide you with the same result, it can save you a lot of time by applying the same method for each color and avoids the risk of misapplying a method you are not used to.

Finally, consider using a table to store data sets when possible.

This simplifies inputs by allowing a data entry form to be used to make new entries and streamlines existing data.

This can also open up a lot of options for easy filtering and analyzing your data.

Conclusion

Fill colors are an incredibly useful tool for organizing and presenting information in a stylized and easy-to-understand format.

However, if you would like to analyze this data quantitatively, it is often necessary to have an accurate count of the colored cells present in a data set.

Fortunately, despite the fact that Excel does not provide a direct option for this, there are several easy ways that can be applied to count the colored cells in your datasets.

Here we have shown you several of the best methods you can use and their advantages.

No matter which you choose to use, you will be able to get an accurate count of the colored cells in your worksheets.