Use Filters and the Subtotal Function to Count Colored Cells This solution is not dynamic and will need to be repeated each time you want to get the count.You can use this to search a selected range, the entire sheet or the entire workbook.You can use this to search for other types of formatting and not just fill color.Select the range in the sheet before pressing the Find All button to limit the search to the selection. If you only want to return cells in a given column, or range, this is possible. 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.

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. Once you have the format selected then click on the Find All button. 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. Formatting could be subtilty different and this might cause you to miss finding the right data! 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. Press Ctrl + F to open the Find and Replace menu.Ĭlick on the small down arrow in the Format button and select Choose Format From Cell.Ĭlicking 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. There is also a great keyboard shortcut for this. Go to the Home tab ➜ click on the Find & Select command ➜ then choose Find from the options. You can find all the cells of a certain color, then count them. Use the Find and Select Command to Count Colored CellsĮxcel has a great feature that allows you to find cells based on the format. Suppose cell A1 is colored Red and you ask for a formula to locate in cell B1, where the result should be 'Yes' if the color of cell A1 is Red, and 'No' if cell A1 is another color or has no color.In this post, I’ll show you 6 ways to find and count any colored cells in your data. To write a function to determine the color of a cell, the interior color object can be used. Some knowledge of programming concepts such as if-else conditions and looping may be useful to write user defined functions. Instead of a formula based on the color of a cell, it is better to write a function that can detect the color of the cell and manipulate the data accordingly. Excel allows defined functions to be executed in Worksheets by a user.

Count by color excel 2016