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.

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

**📕 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.

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

**📕 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])**

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

**📕 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.

- 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**

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

**📕 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**