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.

calculating percentage based on conditional formatting


📁 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.

Sample Dataset

Method

1. Inserting Color Names Manually

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’.

Adding a column that contains the cell Color

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

=COUNTA(F6:F13)

Using COUNTA function to get total sales

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

=COUNTIF(F6:F13,”Green”)

Using COUNTIF function to get apple product sales

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

=C16/C15

calculating percentage based on conditional formatting

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

Method

2. Using Filter Feature

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.

Selecting Filter option

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

=COUNTA(B6:B13)

Using COUNTA function to get total sales

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

Filtering cells by color

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

=SUBTOTAL(3,B6:B13)

Using SUBTOTAL function to get apple product sales

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

calculating percentages based on conditional formatting

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

Method

3. Using Table Feature

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

=COUNTA(B6:B13)

  • The formula will appear automatically like this.

=COUNTA(Table1[Product])

Inserting COUNTA function to get total sales

  • 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:

=SUBTOTAL(3,B6:B13)

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

=SUBTOTAL(3,Table1[Product])

Inserting SUBTOTAL function to get apple product sales

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

calculating percentage based on conditional formatting

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

Method

4. Utilizing Find & Select Feature

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.

Choosing the Find and Select option

  • Go to the Format option.

Selecting Format option

  • Proceed as I demonstrated in the figure.

Previewing Find and Select options

  • Finally, Click on Find All button.

Previewing Find and Select options

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

Finding cells that contain green fill

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

calculating percentage based on conditional formatting

Accordingly, you can calculate a percentage based on conditional formatting

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

Method

5. Applying GET.CELL 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.

Selecting Name Manager from Formulas tab

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

=GET.CELL(38,’5′!$B6)

Previewing Name Manager options

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

=colorcode

Adding Color code of column B

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

Using COUNTA function to get total sales

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

Using COUNTIF function to get apple product sales

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

calculating percentage based on conditional formatting


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.

Fixing GET.CELL block problem

  • 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.

Fixing GET.CELL block problem

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

Method

6. Applying VBA Macro

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.

Selecting Visual Basic 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

Inserting VBA Code

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

calculating percentage based on conditional formatting

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

(Visited 58 times, 1 visits today)
Md Shamse Tabrej

Md Shamse Tabrej

Hi, I'm Shamse Tabrej. I'm an admirer of Excel who enjoys sharing and solving problems in Excel. I make an effort to investigate all of the choices so that I may point readers toward the most viable solutions. Both the globe and its problems are boundless. Let's assist one another in developing. Reader questions and suggestions are most welcome. 

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo