# 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. ## 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. 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’. • Go to the C15
• Then insert the given formula:

=COUNTA(F6:F13) • Then select cell C16.
• Insert the following formula:

=COUNTIF(F6:F13,”Green”) • Finally select cell C18.
• Again type the formula below to see percentage of green cells.

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

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. • Select cell C15 and again write down the Given formula:

=COUNTA(B6:B13) • 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:

=SUBTOTAL(3,B6:B13) • Finally, Calculate the percentage as we did in the previous method. Accordingly, you will be able to calculate percentages based on the conditional formatting.

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

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

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. • And click New and write the given formula into the “Refers to” box.

=GET.CELL(38,’5′!\$B6) • Then go to your worksheet and add a new column for inputting Color code.
• Go to cell and insert the following formula:

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

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

## 📄  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.

`📌`  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  