Calculate Percentage in Excel Based on Conditional Formatting

In this article, I’ll show you three excellent methods for how to calculate the percentage value in Excel based on the conditional formatting feature. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Calculate Percentage Value Based on Conditional Formatting with These 6 Useful Techniques

Here in this article, we will learn how to compare addresses in Excel using different approaches. To be exact, I’ve provided a total of 3 methods down below. We will consider sales data as a data set containing four columns like Product, Agent, Region, and Sales. In the dataset, the Product has conditional formatting. I will try to calculate the percentage Value based on the conditional formatting.

At first, we will employ the manual approach to calculate percentages based on conditional formatting. In this process, we will manually input the color of the formatted cell and later we will apply a different function like COUNTA, COUNTIF, etc to accomplish our goal. The main disadvantage of the technique is it is time-consuming. Let’s explore this approach step by step

⬇️⬇️ STEPS ⬇️⬇️

• Add a column to the left that denotes the fill color of column ‘Product’.

• Go to the C15
• Then insert the given formula:

• Then select cell C16.
• Insert the following formula:

• Finally select cell C18.
• Again type the formula below to see percentage of green cells.

That’s how you can calculate a percentage value based on the conditional formatting.

📕 Read More: 4 Easy Ways to Calculate Percentage in Excel Based on Cell Color

Now I will use Filter Fetaure for the same task. In Excel, you can concentrate on the data that you want to see by using Filter to temporarily hide some of the data in a table so that you can do so. We will also use COUNTA and SUBTOTAL functions.

The steps are given below:

⬇️⬇️ STEPS ⬇️⬇️

• Select the dataset and select the Filter option from the Home tab.

• Select cell C15 and again write down the Given formula:

• Then click the Filter option and choose “Green” from the option Filter by Color.

• This option will hide all fill color cell rows except the green color.
• Then go to cell C16.
• Insert the Given formula:

• Finally, Calculate the percentage as we did in the previous method.

Accordingly, you will be able to calculate percentages based on the conditional formatting.

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

Now we will create a Table for calculating the percentages value based on the conditional formatting. Creating a table from a selection of cells in Excel facilitates the management and analysis of related data. Here I demonstrated the process step by step.

⬇️⬇️ STEPS ⬇️⬇️

• First and foremost we will create a table by pressing Ctrl+T and selecting the entire dataset.
• Select cell C15 and write down the formula given here

• The formula will appear automatically like this.

• Then click the table Filter option and choose “Green” from the option Filter by Color. This option will hide all fill color cell rows except the green color.
• Then go to cell C16.
• Insert the given formula:

• And the formula will appear in the formula box like this.

• Lastly, calculate the percentage as we did in the previous method.

In this way, you can calculate percentage value based on the conditional formatting color.

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

I will use Find and Select Feature to solve this problem. In Excel, you can use Find and Replace function to search for and replace the selected text with new content. Selecting all cells containing formulae, notes, conditional formatting, constants, data validation, etc. is a breeze with Excel’s Go To Special function. Here, I gave a detailed explanation of the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• Select Home > Editing > Find and Select option > Find option.

• Go to the Format option.

• Proceed as I demonstrated in the figure.

• Finally, Click on Find All button.

• Accordingly, you can see number of green formatted cells in a window.

• Then find the percentage as we did in the previous approaches.

Accordingly, you can calculate a percentage based on conditional formatting

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

We applied the GET.CELL formula here. If you want more information about the worksheet’s environment than the cell() method provides, you can use the  Get.cell (an ancient XL 4 macro function). There’s no need to write (or know) any VBA code. In addition, you can’t simply use get.cell in the worksheet, which adds another layer of complexity.

I will show the steps of the technique one by one.

⬇️⬇️ STEPS ⬇️⬇️

• Select Name Manager menu from the Formula Tab.

• And click New and write the given formula into the “Refers to” box.

• Then go to your worksheet and add a new column for inputting Color code.
• Go to cell and insert the following formula:

• Select the cell and find the total row using the COUNTA function as we used previously.

• Then Insert the given formula in C16 to calculate the green formatted cell.

• And finally, find the percentage as we did in the previous techniques.

What To Do When GET.CELL is Giving Output “Blocked”?

If you face GET.CELL option block, following these steps, can solve your problem.

⬇️⬇️ STEPS ⬇️⬇️

• Select File tab > Options > Trust Center > Trust Center setting.

• Then go to Macro Setting and check Enable Excel 4.0 macros when VBA macros are enabled Again check the Disable VBA macros with notification option.

In this way, you can fix GET.CELL option block.

Finally, we used VBA macro in this technique. In Excel, you may leverage the Visual Basic programming language to make your own user-defined functions and expedite otherwise time-consuming manual operations. The steps are given in proper sequence below:

⬇️⬇️ STEPS ⬇️⬇️

• Go to the Developer tab and select Visual Basic menu.
• Select Module from Insert option.

• Write down the following VBA code:

Sub color()
Dim p, q As Integer
Dim AB As Integer
Dim XY As Integer
AB = 4
XY = Range("B" & Rows.Count).End(xlUp).Row
p = 0
q = 0
Do Until q > XY
    Range("B" & (AB + q)).Select
    If Selection.Interior.ColorIndex = 36 Then
        p = p + 1
    End If
    q = q + 1
Loop
MsgBox ("The percentage of yellow colored cells is : " & _
Round((p / (q - 4)) * 100, 2) & "%")
End Sub

• Finally, you will see the output in the message box.

Thus you will be able to calculate percentage based on conditional formatting.

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

📄  Important Notes

🖊️  Verify that you’ve employed Absolute Reference when it was required. Hence to get an absolute cell reference, press the F4 key.

🖊️  However, always look for ways that are easy to use in different situations.

🖊️  Finally, every time you try to use shortcuts on the keyboard.

📝 Takeaways from This Article

📌  You can calculate a percentage value based on the conditional formatting manually.

📌  Moreover, You can calculate a percentage value based on the conditional formatting color using GET.CELL option.

📌  In addition, You can calculate a percentage based on conditional formatting using VBA macro.

📌  Finally, you can calculate a percentage based on conditional formatting using Find and Select option.

Conclusion

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to calculate percentage values based on the conditional formatting feature. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook that I’ve shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.

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