4 Easy Ways to Calculate Percentage in Excel Based on Cell Color

Sometimes we have to calculate the percentage of a certain value from the total value. It can be a certain sell value of a product from all the products sold in a company. So it can come in handy if we can somehow differentiate a certain percentage of a value from the total value. If the sale of any particular product is lower than a certain percentage we can take necessary measures. So in this article, we are going to learn four different ways to calculate the percentage in Excel based on the cell color.

📁 Download Excel File

Download the practice file from here.

Learn to Calculate Percentages in Excel Based on the Cell Color with These 4 Methods

In this article, we will learn four different methods to calculate the percentage of a certain value from the total value. We will use Excel functions like SUBTOTAL, SUMIF, SUMIFS functions, and the VBA option. Let’s assume we have a product data-sheet. We are trying to know how much percentage is the costs of television, light bulb, and bed with respect to the total product costs. For our demonstration purpose, the sample dataset is as follows.

In this section, we will use the SUBTOTAL function to calculate the percentage in Excel based on cell color. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to cell E13 and type this formula there.

• We get the Total cost in cell E13.

• Now we select the data range.
• Following that, we go to Sort & Filter option.
• After that, we select the Filter option.

• After that, we select the dropdown from Total Cost.
• We select the Filter by Color option.
• We select the fill color.

• Now we get the Total Cost of the colored item.

• Following that we divide the cell E13 by E16 to get the percentage.
• Finally, we get the result in cell E14.

📕 Read More: 3 Ways to Add Percentage to Price in Excel with Formula

In this section, we will learn how to use the SUMIF function to determine the required percentage in excel based on the cell color. Therefore the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Formulas option.
• Then we select the Name Manager.
• We provide a name in our name section.
• After that, we fill this function in Refers to the box.

• Finally, we press OK.

• Now we select the Close option from the dialogue box.

• After that, we write this function in cell F6.

• Following that we use the Fill Handle to drag the formula.

• Now we write this formula in cell F13.

• Finally, we divide cell F13 by F14 to get the result in cell F15.

📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference

In this section, we will learn to use the SUMIFS function to calculate the percentage in excel based on the cell color. Therefore the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Just like the previous method first, we make the Color code column.
• Now we write this formula in the cell F13 formula box.

• Finally, we divide cell F13 by F14 to get the result in cell F15.

📕 Read More: 5 Easy Methods to Add 10 Percent to a Number in Excel

In this final section, we will use the Excel VBA to calculate the percentage in the Excel based on the cell color. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Developer option.
• After that, we go to the Visual Basic option.

• Following that, we go to the Insert option.
• Then we select the Module.

• We will get a window. Now we write this script in the module.

Function Sumcolor(cellvalue As Range, Totalrange As Range)
Dim summation As Long
Dim colour As Integer
colour = cellvalue.Interior.ColorIndex
For Each cl In Totalrange
If cl.Interior.ColorIndex = colour Then
summation = WorksheetFunction.sum(cl, summation)
End If
Next cl
Sumcolor = summation
End Function

• Now we write this formula in cell E13.

• Finally, we divide cell E13 by E14 to get the result in cell E15.

📕 Read More: 3 Quick Ways to Add 20 Percent to a Price in Excel

How to Create Cell Color with Formula in Excel

In this section, we will learn how to use Conditional Formatting for the Excel cell color formula. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the data range.
• After that, we go to Conditional Formatting.
• Then we select the New Rule.

• After that, we select the Rule Type.
• Now we write this formula in the formula box.

• Here ISFORMULA returns us the result of cells containing formulas.
• Now we select the Format option.

• Now we go to the Fill option.
• After that, we select the background color.
• Finally, we press OK.

• Now we get this result with cell color.

📕 Read More: Calculate Percentage in Excel Based on Conditional Formatting

How to Count Colored Cells in Excel Using Formula

In this section, we will learn how to count colored cells in Excel using formulas. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we write this formula in cell E13.

• Following that we select our data range.
• Then we go to the Sort & Filter option.
• Then we select the Filter option.

• Now we select the dropdown bar from the right.
• After that, we select the Filter by Color option.
• Then we select the Fill color.

• Finally, we get the number of colored cells in cell E13.

📄 Important Notes

🖊️  When we use the function, we must be careful about the function argument.

🖊️  While using the VBA we have to save the workbook as macro enabled workbook.

📝 Takeaways from This Article

📌  We can use the SUBTOTAL function to calculate the percentage in the Excel based on cell color.

📌  SUMIF function can be another way to calculate the percentage in Excel based on cell color.

📌  If we want we can imply the SUMIFS function to do the same thing.

📌  Also VBA is another efficient way to determine the percentage in Excel based on the cell color.

Conclusion

In this article, we have learned four different methods to calculate the percentage of a certain value from the total value. We can use Excel functions like SUBTOTAL, SUMIF, SUMIFS, etc. If we want we can also use the VBA in Excel. Users can use the functions or the VBA whichever they want. If you possess any queries regarding this article you can put your comment in the comment box. The writer will try his best to come up with a solution. For further queries, you can visit our website www.Excelden.com.

Related Articles

• 3 Easy Ways to Calculate Accuracy Percentage in Excel
• 4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel
• Calculate Percentage Change with Negative Numbers in Excel
• 6 Ways to Forecast Growth Rate in Excel
• Calculate Profit and Loss Percentage with Formula in Excel