It’s a popular way to visualize your data! While colored cells are a great way to highlight data to quickly grab someone’s attention, they are not a great way to store data. Unfortunately, a lot of users will color a cell to indicate some value instead of creating another data point with the value. For example, they might color a cell green to indicate an item is approved instead of creating another data point with the text Approved. This causes a lot of problems when you actually need to find out how many items were approved. Excel doesn’t offer a built-in way to count colored cells. In this post, I’ll show you 6 ways to find and count any colored cells in your data.
Use the Find and Select Command to Count Colored Cells
Excel has a great feature that allows you to find cells based on the format. This includes any colored cells too! You can find all the cells of a certain color, then count them. Go to the Home tab ➜ click on the Find & Select command ➜ then choose Find from the options. There is also a great keyboard shortcut for this. Press Ctrl + F to open the Find and Replace menu. Click on the small down arrow in the Format button and select Choose Format From Cell. Clicking on the main part of the Format button will open up the Find Format menu where you can select any combination of formatting to search for. This is perfect if you know exactly what color you are searching for, but more often you will be best served by setting the format by example. Formatting could be subtilty different and this might cause you to miss finding the right data! When you click on the small arrow inside the Format button, it will reveal more options including the ability to set the format by selecting a cell. Once you have the format selected then click on the Find All button. The lower part of the Find and Replace dialog box will show all the cells that were found matching the formatting and in the lower left you will find the count. Press Ctrl + A to select all the cells and then press the Close button and you can then change the color of all these cells or change any other formatting. If you only want to return cells in a given column, or range, this is possible. Select the range in the sheet before pressing the Find All button to limit the search to the selection.
Pros
Easy to use.You can use this to search for other types of formatting and not just fill color.You can use this to search a selected range, the entire sheet or the entire workbook.
Cons
This solution is not dynamic and will need to be repeated each time you want to get the count.
Use Filters and the Subtotal Function to Count Colored Cells
This method will rely on the fact that you can filter based on cell color. First step, you will need to add filters to your data. Select you data and go to the Data tab then choose the Filter command. This will add a sort and filter icon to each column heading of your data and these will allow you to filter your data many different ways. There is also a handy keyboard shortcut for adding or removing filters from your data. Select your data and press Ctrl + Shift + L on your keyboard. Another option for adding filters is to turn your data into an Excel Table. I wrote a post all about Excel tables and the great features they come with. You can convert your data into a table with either of these two methods.
Select a cell inside your data ➜ go to the Insert tab ➜ click on the Table command.Select a cell inside your data ➜ press Ctrl + T on your keyboard.
You table should come with filters by default. If not, go to the Table tab and check the Filter Button option in the Table Style Options section. Now you can add the above SUBTOTAL formula to count the non empty cells where Order ID is the column which contains the colored cells you would like to count. The first argument of the SUBTOTAL function tells it to return a count while the second argument tells it what to count. The special trick here is that the SUBTOTAL function will only count visible cells, so it will update the count based on what data it is filtered on. This means you can filter on the colored cells and you will get a count of those colored cells! Now you can filter your data by color. Now the SUBTOTAL result will update and you can quickly find the count of your colored cells. If you adjust colors, add or delete data in the table. You will need to reapply the filters as they don’t update dynamically. Go to the Data tab and click on the Reapply button in the Sort & Filter section.
Pros
Easy to use.
Cons
Requires manually filtering data.The filters don’t update and you will need to reapply them when you change your data.Since the count is based on the filtering, the result can be different for each user when collaborating on the workbook.
Use the GET.CELL Macro4 Function to Count Colored Cells
Excel does have a function to get the fill color of a cell, but it is a legacy Macro 4 function. These predate VBA and were Excel’s formula based scripting language. While they are considered deprecated, it is still possible to use them inside the name manager. There is a GET.CELLS Macro4 function that will return a color code based on the fill color of the cell. You can create a relative named range that uses this by going to the Formulas tab and clicking on Define Name. This will open up the New Name menu and you can define the reference. Give your defined name a Name like ColorCode. This is how you will refer to it in the workbook. Add the above formula into the Refers to section. For this formula you data will need to be in a table named Orders with a column called Order ID, but you can change these to fit your data. This formula will always refer to the Order ID cell in the current row to which it’s referenced. If your data is not inside a table, then you could use the above formula instead, where B is the column containing the fill color you want to count. This uses a fixed column and relative row reference to always refer to column B of the current row. With the define name, you can now create another column using the above formula in your data to calculate the color code for each row. The result will be an integer value based on the fill color of the cell in the Order ID column. Now you can count the number or colored cells using the above COUNTIFS formula. This formula will count cells in the ColorCode column if they have a matching code. In this example, it counts all the 10 values which correspond to the green color.
Pros
You can calculate the fill color for each row of data and it will update dynamically as you change the data or fill color of the data.
Cons
This method uses the Macro4 legacy functions and they may not continue to be supported my Microsoft.Harder to implement.You will need to save your workbook in the xlsm macro enabled format.You can’t move your referenced column if you’re using the column notation inside the named range.You can’t change your column name if you’re using the table notation inside the named range.You need to create an additional column and use a COUNTIFS function to get the count.
Use a LAMBDA Function to Count Colored Cells
This will use the same GET.CELL Macro4 function as the previous method, but you can create a custom LAMBDA function to use it inside the workbook. The LAMBDA function is a special function that allows you to build custom functions via the name manager. This is a new function, so it’s not generally available and you need to be on Microsoft 365 office insiders program at the time of writing this post. Go to the Formulas tab and click on Define Name to open the New Name menu. Give the define name a name like GETCOLORCODE and add the above formula into the Refers to section. This will create a new GETCOLORCODE function that you can use inside the workbook. It will take one argument called cell and return the cell’s fill color code. Now all you have to do is create a column to calculate the color code for each row using the above formula. Again, you can count the number or colored cells using the the above COUNTIFS formula.
Pros
You can build a function that calculates the color code for a given cell.Allows you to directly reference a cell to get the color code.
Cons
This method uses the LAMBDA function in Excel for M365 and is currently not generally available.Harder to implement.This method uses the Macro4 legacy functions which may not be supported in the future.You will need to save your workbook in the xlsm macro enabled format.Requires creating an additional column and using a COUNTIFS function to get the count.
Use VBA to Count Colored Cells
Pros
You can create a function that counts the colored cells in a range.The results will update as you edit your data or change the fill color.User friendly option to use once it is set up.
Cons
Uses VBA which requires the file is saved in the xlsm format.Less user friendly to set up.
Use Office Scripts to Count Colored Cells
Office Scripts are the brand new method for automating tasks in Excel. But it’s only available for Excel online and only with an enterprise plan. If you have an enterprise plan, then you’re good to go with this method! First, you will need to set up two named cells which the code will refer to. Select any cell and type a name like ColorCount into the name box and press Enter. This will create a named range which can be referred to in the code. This means we can move the cell and the code will refer to its new location. You will also need to create a Color named range for the input of the color to count. Now you can create a new office script. Go to the Automate tab in Excel online and click on the New Script command. This will open up the Code Editor and you can paste in the above code and save the script. Press the Run button and the code will execute and then populate the ColorCount named range with the count of the colored cells found in the Order ID column.
Pros
This is the newest method and will be supported going forward.The script can be run from Power Automate for a no click solution.
Cons
This method is difficult to set up.This requires an enterprise license and you need to use Excel online.
Conclusions
Hopefully Microsoft will one day create a standard Excel function that can return properties from a cell such as its fill color. Until that happens, at least you have a few options available that allow you to find the count of colored cells without manually counting them. Do you have a preferred method not listed here? Let me know in the comments!
