Depending on different conditions you may sometimes highlight some rows using **Conditional Formatting **and then you may need to calculate percentage based on **Conditional Formatting **to get an idea of how many rows have fulfilled these conditions. This article will give you the easiest ways to calculate this percentage. So, let’s dive into the main article.

**Table of Contents**hide

## Download Workbook

## 6 Ways to Calculate Percentage Based on Conditional Formatting

Here, we have highlighted some cells of the **Item **column based on the product **Apple**. By calculating the percentage based on this **Conditional Formatting** we will be able to know how many *Apples *we have among other products as it is the condition here for this **Conditional Formatting**.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Calculate Percentage Based On Conditional Formatting Manually

Firstly, we will **calculate the percentage of the highlighted cells** by writing the color’s name manually in the **Color **column. Then, we will calculate the number of total rows and colored rows using the **COUNTA function** and the **COUNTIF function**.

** Steps**:

➤ Put down the name of the background colors of the cells of the

**Item**column.

➤ Type the following formula in cell **C14 **for counting the total rows.

`=COUNTA(B5:B12)`

Here, **B5:B12 **is the range of the cells of the **Item **column and **COUNTA **will count the non-blank cells of this range.

After pressing **ENTER**, we are having the number of total rows in this dataset.

➤ Use the following formula in the cell **C15 **for counting the colored cells

`=COUNTIF(F5:F12, "Yellow")`

**COUNTIF **will give the number of cells having **Yellow **in the range **F5:F12**.

➤ Press **ENTER.**Then, you will get the number of highlighted cells as

**4**.

For calculating percentage, we have to divide the **Colored Rows **by the **Total Rows **by using the following formula

`=C15/C14`

To apply the **Percentage **form, select the value in cell **C16** and then go to **Home **Tab >> **Number **Group >> **Percent Style **Option.

You can also select it using the shortcut key **CTRL+SHIFT+%**.

Finally, you will get the percentage of **50%** based on **Conditional Formatting**.

**Related Content:** **How to Calculate Percentage of a Number in Excel (5 Easy Ways)**

__Method-2__: Calculate Percentage Based on Conditional Formatting Using Filter Option

Here, we will use the **Filter **option to filter the dataset based on the highlighted color, and then we will calculate the percentage by using the **COUNTA function **and the **SUBTOTAL function**.

** Steps**:

➤ Select the data range and then go to

**Data**Tab >>

**Sort & Filter**Dropdown >>

**Filter**Option.

Then, we will have the filter symbols on the header row of the dataset.

➤ Type the following formula in cell **C13 **for counting the total rows.

`=COUNTA(B4:B11)`

Here, **B4:B11 **is the range of the cells of the **Item **column and **COUNTA **will count the non-blank cells of this range.

Then, you will get the number of total rows **8**.

Before counting the highlighted cells we have to filter the **Item **column based on the highlighted color.

➤ Click on the dropdown sign in the **Item **column and then select the **Filter by Color **option and finally choose the *Yellow *color box from the **Filter by Cell Color **option.

After that, we will get the following dataset which only shows the colored rows and hides the non-highlighted rows.

➤ Type the following formula in cell **C14**.

`=SUBTOTAL(3,B4:B11)`

**SUBTOTAL **will count the number for only the unhidden cells and **3 **is for **COUNTA function **and **B4:B11 **is the range of the **Item **column.

Afterward, we are getting the number of yellow color cells which is **4**.

To get the percentage value, divide the **Colored Rows **by the **Total Rows**.

`=C14/C13`

After applying *Percent Style*, we are getting the percentage of the highlighted cells as **50%**.

**Read More:**** How to Calculate Percentage Increase in Excel (Examples with All Criteria)**

__Method-3__: Calculate Percentage Based on Conditional Formatting Using Table Option

In this section, we will use the **Table** option to calculate the percentage based on **Conditional Formatting**.

** Steps**:

➤ Go to

**Insert**Tab >>

**Table**Option.

Afterward, the **Create Table **dialog box will appear.

➤ Select the range of your dataset.

➤ Check the **My table has headers **option and click **OK**.

Then, we will have the following table, and as we can see the name of the table is **Table3**.

➤ Select cell **C13 **and start typing the formula

`=COUNTA(B4:B11)`

Here, **B4:B11 **is the range of the cells of the **Item **column and **COUNTA **will count the non-blank cells of this range.

But, when start to select the range **B4:B11**, Excel will convert them automatically to the structured reference system and modify the formula as follows

`=COUNTA(Table3[Item])`

**Table3 **is the table name and **[Item] **is the column name.

Prior to counting the number of yellow cells, we have to filter the **Item **column based on the highlighted color.

➤ Click on the dropdown sign in the **Item **column and then select the **Filter by Color **option and finally choose the *Yellow *color box from the **Filter by Cell Color **option.

In this way, we will get the following table which only shows the highlighted rows and hides the non-highlighted rows.

➤ Type the following formula in cell **C14**.

`=SUBTOTAL(3,Table3[Item])`

**SUBTOTAL **will count the number for only the unhidden cells and **3 **is for **COUNTA function **and **Table3[Item] **is the range of the **Item **column.

For having the percentage value, divide the **Colored Rows **by the **Total Rows**.

`=C14/C13`

After applying *Percent Style*, we are getting the percentage of the highlighted cells as **50%**.

**Related Content: How to Calculate Percentage of a Number in Excel (5 Easy Ways)**

**Similar Readings:**

**How to Calculate Revenue Growth Rate in Excel (3 Methods)****Forecast Growth Rate in Excel (2 Methods)****Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)****How to Calculate Reverse Percentage in Excel (4 Easy Examples)****Calculate Bacterial Growth Rate in Excel (2 Easy Ways)**

__Method-4__: Calculate Percentage Based on Conditional Formatting Using Find & Select Option

Here. we will use the **Find & Select** option for finding the number of highlighted cells and then based on this number we will calculate the percentage.

** Steps**:

➤ Go to

**Home**Tab >>

**Editing**Group >>

**Find & Select**Dropdown >>

**Find**Option.

After that, the **Find and Replace **Dialog Box will pop up.

➤Select the **Format **Option.

Then, the **Find Format **Dialog Box will appear.

➤ Select **Fill **Option, choose the *Yellow* Color, and press **OK**.

Afterward, you will have the **Preview **section as follows.

➤ Click the **Find All **option.

Eventually, you will see the number of yellow color cells which is **4 **on the left bottom corner of the dialog box.

➤ Now, write down this number in cell **C14**.

➤ For calculating total rows number type the following formula in cell **C13**.

`=COUNTA(B4:B11)`

Here, **B4:B11 **is the range of the cells of the **Item **column and **COUNTA **will count the non-blank cells of this range.

After that, we will calculate the percentage by dividing the **Colored Rows **by the **Total Rows**.

`=C14/C13`

After adding the **Percent Style **to this fraction number, we will get **50% **as the percentage of *Apples *among other *Items*.

**Read More: How to find percentage between two numbers in Excel**

__Method-5__: Calculate Percentage Based on Conditional Formatting Using GET.CELL Function

In this section, we will use **GET.CELL **for creating a function to define the codes of the color of the **Item **column.

** Steps**:

➤ Go to

**Formulas**Tab >>

**Name Manager**Option.

Then, the **Name Manager **Wizard will appear.

➤ Select the **New **Option.

After that, the **New Name **Dialog Box will pop up.

➤ Type any name in the **Name **Box, here we have used **ColorCode **and selected the **Workbook **Option in the **Scope **Box.

➤ Use the following formula in the **Refers to **Box

`=GET.CELL(38,Function!$B4)`

**38** will return the *Color Code *and **Function!$B4 **is the first highlighted cell in the **Function** sheet.

After pressing **OK**, type the created function name **ColorCode **in cell **F4**.

➤ Press** ENTER **and drag down the **Fill Handle **Tool.

In this way, we will get the *Color Code ***6 **for the *yellow *color cells of the **Item **column.

Now, we will use the color code values for determining the number of highlighted cells.

➤ Type the following formula in cell **C14**

`=COUNTIF(F4:F11,6)`

**COUNTIF **will give the number of cells having **6 **in the range **F4:F11**.

Now, for the *Total Rows *use the following formula in cell **C13**

`=COUNTA(B4:B11)`

**B4:B11 **is the range of the cells of the **Item **column and **COUNTA **will count the non-blank cells of this range.

For determining the percentage value, use the following formula in cell **C15**

`=C14/C13`

After adding *Percent Style*, we will get the percentage based on **Conditional Formatting**.

**Related Content:** **Excel formula to calculate percentage of grand total (4 Easy Ways)**

__Method-6__: Using VBA Code

Here, we will use a **VBA** code to determine the percentage easily.

** Steps**:

➤ Go to

**Developer**Tab >>

**Visual Basic**Option.

Then, the **Visual Basic Editor **will open up.

➤ Go to **Insert **Tab >> **Module **Option.

After that, a **Module** will be created.

➤ Write the following code

```
Sub colorcell()
Dim x, y As Integer
Dim FR As Integer
Dim LR As Integer
FR = 4
LR = Range("B" & Rows.Count).End(xlUp).Row
x = 0
y = 0
Do Until y > LR
Range("B" & (FR + y)).Select
If Selection.Interior.ColorIndex = 6 Then
x = x + 1
End If
y = y + 1
Loop
MsgBox ("The percentage of yellow colored cells is : " & _
Round((x / (y - 4)) * 100, 2) & "%")
End Sub
```

Here, we have declared **x**, **y**, **FR**, **LR **as **Integer**, we have assigned **FR **to **4 **which is the starting row of our dataset and **LR** will determine the last row of the dataset.

**DO UNTIL** loop will count the colored cells for each cell of **Column B **and then the **IF** statement will identify if the color of the cell is yellow ( color code = 6 ) and for these cells **x **will be increased by **1** and finally using the formula **Round((x / (y – 4)) * 100, 2) **we will get the percentage value rounded up to **2 **values after the decimal point. **y – 4 **will give the total rows and here you have to subtract the first-row number from **y**.

➤ Press **F5**.

Finally, you will have the percentage based on **Conditional Formatting **as **50% **in a message box (**MsgBox**).

**Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)**

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, we tried to cover the ways to calculate percentage based on **Conditional Formatting**. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.