Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
Now Reading
67. Amortization Schedule in Excel
68. Master Cell References in Excel
As a child, I was fond of using colored sketch pens in my notebook. Even now that I work on MS Excel as an adult, I find it easier to sort and store data using colors, which makes the data sheet easier on the eye.
In Excel, ‘count cells with specific color’ option is also available.
For example, we prepared a sheet that contained the marks of 15 students in Mathematics. All the marks above 80 have been flagged green, those between 50 and 80 have been flagged yellow, and those below 50 have been flagged red.
Although it is easy to keep a count of these manually in case of small data sets, it becomes a nerve-wracking task for sheets that contain humongous amounts of data.
I have figured out a few ways, which I will be sharing in this Excel tutorial today. Stick through till the end to understand how to count colored cells in Excel.
You can count colored cells in Excel by cell color as well as by font color.
Let me first make it clear to you that these functions are not going to be available by default, unlike other functions like Sum, Average, etc. But don’t worry! I have also covered how to add this function to your workbook.
Open an Excel sheet and press “Alt+F11”.
The Microsoft Visual Basic window will appear on the screen. From there, choose “Insert” and select “Module”. There, write the code:
|
Voila! The functions are now added to your Excel workbook.
Now, prepare a data sheet. I will be using the example that I have cited in the introduction. Next, follow the steps mentioned below:
Step 1: Segregate the data using colors.
Step 2: Use another cell in the sheet close to your table. Insert the colors that you have used in the table. For example, I have used three colors here - green, yellow, and red. I have filled these three colors in the cells H6, H7, and H8.
Step 3: In the cells adjacent to the colored cells, where you want the values to be displayed, insert the formula =CountCellsByColor(Cell range, criteria), where cell range denotes the cells where the data is entered, and criteria denotes the color whose cell count you want to extract.
For example, in the given data sheet, if we want to count the number of green cells, we must use the formula =CountCellsByColor(E6:E20, H6). Drag the formula downwards to apply the same for the other two colors.
The result will appear thus:
This makes the data easy to comprehend. From the above datasheet, we can decode that 5 students scored above 80, 8 students scored between 50 and 80, and 2 students scored below 50.
To count colored cells in Excel by font color, you will first have to add the function to your workbook. Use the code in the Microsoft Visual Basic:
|
This way, the count cells by font color will be added to your workbook.
Next, open a worksheet and enter the data. I will be using the same dataset as above, the only difference being that instead of filling colors in the cell, I will be using colored fonts.
Follow the steps mentioned below to count colored cells in Excel by font color:
This way, the result will appear on your screen.
To find the sum, colored cells in Excel can be employed simply by using two formulae. I’ve discussed them below:
This function is not available on Excel by default; you will have to add it. To add the function, press “Alt+F11” and open the Microsoft Visual Basic window.
Go to “Insert” and click on “Module”. There, insert the code:
|
This will add the function to your workbook.
Now, prepare your dataset.
To find the sum of the values in cells that are colored green, use the formula =SumCellsByColor(Table2[Marks out of 100], H4)
.
Once you obtain the result, drag and drop to the next two cells to get the sum of the values in blue and red cells as well.
You will first have to add the function to your workbook. Follow a procedure similar to the one you used while adding the countcellsbycolor function.
Press “Alt+F11” to open the Microsoft Visual Basic.
There, use the code:
|
Now, you are ready to use this function in your workbook.
First, prepare your datasheet like this:
To find the sum of cells whose font is green, go to the cell where you want the result displayed and enter the formula =SumCellsByFontColor(Table2[Marks out of 100], H4). This will give the sum of all the values written in green.
Drag and drop in the next two cells to apply the same to them.
If you are not very confident about adding a function in your workbook, you can still count colored cells in Excel. The steps I am mentioning below are an alternate way of counting colored cells without VBA.
If you want to count only the cells containing green font, first add the filter “Filter by Color” and choose the green color.
You will get the result. The cell will contain the value denoting the number of cells whose contents are written in green.
You can also count colored cells in Excel using the SUBTOTAL function. Have a look at the steps that I have mentioned below:
Once you plunge into the pool of Excel, you will come across endless opportunities that can make your day-to-day computing and data analytics tasks easy. You may not find all the functions by default, but adding them is not a mammoth task that requires immense coding capabilities!
Therefore, start learning from the leading industry experts and validate your knowledge with a certificate with upGrad’s free online certification course in Excel. Visit upGrad to explore courses and choose the one that suits you the best.
How do I count the sum of colored cells in Excel?
You can count the sum of colored cells in Excel using the syntax =SumCellsByColor(Cell Range, Criteria). However, you will first have to add this function using Microsoft Visual Basic.
How do I count colored cells in sheets?
You can count colored cells in Excel sheets using the SUBTOTAL function, with the help of a table, or using the CountCellsByColor function. The tutorial above provides a detailed step-by-step explanation of all three.
How do I count colored cells in Excel calendar?
You can use the COUNTIF function to count colored cells in the Excel calendar. All you need to do is specify the criteria as the cell color. First, use "Conditional Formatting" to assign colors to different cells. Then, use COUNTIF(range, "color") to count cells with a specific color.
How do I sum colored cells in Excel without VBA?
If your data set is not very large, find the sum of colored cells in Excel using the =SUM function, followed by selecting the cells of the specific color. You can also apply a filter and sort the cells of the desired cell color, copy and paste them into the adjacent cells, and apply the autosum function.
How do you count colored cells in Excel with text?
An easier way to count colored cells in Excel with text is by using the SUBTOTAL function. A detailed guide on how to do it is explained in the tutorial above.
How do I sum and count cells by color in sheets?
A complete guide on how to sum and count cells by color in sheets is explained in the tutorial above.
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...