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

## How to Calculate Percentage Based on Conditional Formatting: 6 Ways

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 the *Microsoft Excel 365* version here, you can use any other version 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 have the number of total rows in this dataset.

âž¤ Use the following formula in 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**.

To calculate 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 in Excel Based on Cell Color

__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 get 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 Complete Based on Dates in Excel

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

To have 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%**.

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

Here. we will use the **Find & Select** option to find 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*.

__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 to determine 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: **How to Calculate Contribution Percentage with Formula in Excel

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

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

**Download Workbook**

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

**Related Articles**

- How to Calculate Percentage of Month in Excel
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Calculate Percentage of Percentage in Excel
- Percentage Showing as Thousand in Excel
- Why Are My Percentages Wrong in Excel?
- How to Remove Percentage in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Cumulative Percentage in Excel

**<<Go Back to ****Calculate Percentage with Criteria in Excel ****| Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel**