EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tips Count Colored Cells in Excel

Count Colored Cells in Excel

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated July 12, 2023

Count Colored Cells in Excel

Table of Contents

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

  • Example #1: Using the Find and Replace method
  • Example #2: Using SUBTOTAL Formula and Color Filters
  • Example #3: Using GET.CELL and COUNTIF Function
  • Things to Remember to Count Colored Cells In Excel

Introduction to Count Colored Cells in Excel

Count Colored Cells in Excel, or formatting makes Cells organized, increases readability, and makes data presentable. For instance, if you have data from a sales report, you can highlight or color cells greater than $5000 in red and less than $5000 in green. It makes it easy to identify and categorize information in your data. However, there is no inbuilt function to count colored cells in Excel. The COUNT and COUNTIF functions count cells with numbers but do not count them based on their colors. To count colored cells in Excel, we can use a combination of different functions.

In this article, we have discussed three methods to count colored cells in Excel as follows:

  • Using the Find and Replace Method
  • Using the SUBTOTAL Formula and Color Filters
  • Using GET.CELL and COUNTIF Function

How to Count Colored Cells in Excel?

Let’s understand how to Count Colored Cells in Excel with a few examples.

You can download this Count Colored Cells Excel Template here – Count Colored Cells Excel Template

Example #1: Find and Replace Method

It is the simplest way to count colored cells in Excel. Let’s consider the following example to understand how to count cells using this method.

As an entrepreneur exporting tea, coffee, and green tea to multiple countries (Canada, Germany, France, USA, Australia, and the UK), you have sales data for 2021 and 2022. The data is color-coded, with pink representing total sales over $5000, green indicating sales between $1000 and $5000, and blue indicating sales below $1000. You want to calculate the total sales greater than $5000.
Example 1

Solution:
Step 1: Select the data.
Step 2: In the “Home” tab, select the “Find and Select” option under the “Editing” group.
Example 1 Step 2

Step 3: Select “Find“, as shown below.
Count Colored Cells in Excel-Example 1 Step 3

A dialog box of “Find and Replace” will open up.
Step 4: Click “Options” under the “Find” tab, as shown below.
Eg 1, S4

 Step 5: Click on the drop-down menu (highlighted in the below image) of the “Format” option.

Note: The shortcut to open the Find and Replace menu is “Ctrl + F“.

Example 1 Step 5

Step 6: Select “Choose Format From Cell“, as shown below.
Count Colored Cells in Excel-Example 1 Step 6

An eye-dropper symbol will appear after selecting the Choose Format From Cell option. Now, move this eye-dropper to the cell (with color) you want to count. In our case, it is Cell A2.
Step 7: Select “Cell A2″. After selecting Cell A2, the “Find and Replace” dialog box preview option will show the selected cell’s color.
Step 8: Press “Enter“.
Example 1 Step 8

Result: The dialog box will list the cells matching the selected color format (shown in the green border below). The total number of cells will be displayed in the lower-left corner of the dialog box, as shown below.

Example 1 Step 8-2

Similarly, if you want to know the total number of cells for the blue color, follow the above steps.
The dialog box will show the total count for the blue color, as shown below.
Count Colored Cells in Excel-Example 1 Step 8-3

Note: In this example, the total count of cells with different colors is shown in the dialogue box.

Example #2: SUBTOTAL Formula and Color Filters

To display and sum colored cells with a specific format in Excel, you can utilize the SUBTOTAL and COUNTIF functions.

For example, you want to display data where the total sales exceed $5000 (represented by cells with pink color).
Example 2

Solution:
1. calculate the total number of cells using the SUBTOTAL function.
Step 1: Select “Cell F15″ and enter the formula:

=SUBTOTAL(102,F2:F13)

Count Colored Cells in Excel-Example 2 Step 1

Note: The SUBTOTAL function in Excel calculates the range of values or numbers depending on specific operations. The 102 operator of the SUBTOTAL function counts visible cells and ignores all the hidden cells between the given range (F2:F13).

Example 2 Step 1-2
Step 2: Press “Enter“.
The formula will display 12, the total count of cells with all colors.
Count Colored Cells in Excel-Example 2 Step 2
2. Now, we will add filters to the data to count the pink color.
Step 1: Select the headers of the data, and Go to “Data”> Click on “Filter” under the “Sort & Filter” group.
Example 2 2 Step 1

As shown below, the filter option is applied to all the headers.
Example 2 2 Step 1-2

Step 2: Select the filter icon, click “Filter by Color”, and choose the color you want to count. In our case, choose the pink color.
Eg2, S7

Result:  By applying the filter, we can see all the cells that are colored pink. As a result, the formula will show data only for cells with total sales greater than $5000. Consequently, the count will change from 12 to 6, representing the total number of cells with the pink color.

Count Colored Cells in Excel-Example 2 2 Step 2-2

Similarly, you can count cells with green color (total sales between $1000 and $5000), as shown in the following image.
Example 2 2 Step 2-3

Example #3: GET.CELL and COUNTIF Function

The GET.CELLS function in Excel, which specifically works with named ranges, is useful for counting colored cells. It provides the color code for highlighted or colored cells in the data. You can count the number of cells with the same color by combining the GET.CELL function with the COUNTIF function.
Let’s learn how to use these functions to count colored cells.

Solution:
Step 1: Insert the column “ColorCode” beside the “Total Sales”, as shown below.
Count Colored Cells in Excel-Example 3 Step 1

Step 2: Go to the “Formulas” tab and click “Define Name”.
Example 3 Step 2

A “New Name” dialog box will appear.
Step 3: Type “ColorCode” in the “Name” section and enter the below formula in the “Refers to”:

=GET.CELL(38,’Sheet 1!$A2)

Example 3 Step 3

Formula Explanation: =GET.CELL(38,’Sheet 1′!$A2)
In this formula:

  • 38 represents the cell code that specifies the color.
  • ‘Sheet 1’ refers to the sheet number where the cell with the desired color format is present.
  • $A2 represents the column cell value of Sheet 1 with the color format.

This formula will assign the color of cell A2 in Sheet 1 a specific number known as the ColorCode.
Step 4: Select “Cell G2” and enter “=ColorCode”.
Example 3 Step 4

Step 5: Press “Enter“.
The formulas have assigned 40 for the pink color.
Example 3 Step 5

Step 6: Drag the “Cell G2” downwards.

Result: The GET. The CELL function assigns a specific number (ColorCode) to each color. The cells with the same background color have the same number assigned by GET.CELL.

Count Colored Cells in Excel-Example 3 Step 6
In this case, the formula assigns 40, 37, and 36 to represent pink, blue, and green colors, respectively.
Example 3 Step 6-2

Note: The formula will return 0 if the data has no background color.

Now, let’s calculate the total count for each colored cell using the ColorCode and COUNTIF functions.
Step 7: Select “Cell B15″ and enter the formula:

=COUNTIF($G$2:$G$13,ColorCode).

Example 3 Step 7

Step 8: Press “Enter.”
The COUNTIF function will display 6, i.e., the total number of cells in pink.
Example 3 Step 8

Step 9: Drag the AutoFill handle to apply the COUNTIF formula to other cells.

Result: The COUNTIF function displays the total count of cells with the corresponding background color.

Count Colored Cells in Excel-Example 3 Step 9

Things to Remember About Count Colored Cells In Excel

  • In the SUBTOTAL function, use the 102 function operator to count all cells with the specified color and 109 to sum all the cells.
  • The keyboard shortcut to add or remove filters from the data is “Ctrl + Shift + L”.
  • The filters option in Excel is not dynamic. If you make any changes in the data, like changing the color format or adding or deleting data in the table, you must reapply the filters option.
  • The GET.CELL function uses the Macro4 legacy function, which sometimes cannot be supported by Microsoft.
  • When applying to GET.CELL function in Excel worksheet, save the file in the xlsm macro to enable format.

Recommended Articles

This article provides a step-by-step guide on counting colored cells in Excel using the top three methods with practical examples. We have also provided a downloadable Excel template. You can also read our other articles below to learn about Excel features.

  1. Scrollbar in Excel
  2. Remove Duplicates in Excel
  3. COUNTIFS in Excel
  4. Excel Row Count
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Count Colored Cells Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Count Colored Cells Excel Template

EDUCBA

डाउनलोड Count Colored Cells Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW