__Method 1 – __Calculate Percentage Based On Conditional Formatting Manually

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

**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.
**Get the number of highlighted cells as

**4**.

To calculate the 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 go to **Home **Tab >> **Number **Group >> **Percent Style **Option.

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

You will get a percentage of **50%** based on **Conditional Formatting**.

__Method 2 – __Calculate Percentage Based on Conditional Formatting Using Filter Option

** Steps**:

➤ Select the data range and then go to

**Data**Tab >>

**Sort & Filter**Dropdown >>

**Filter**Option.

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

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

You will get the number of total rows **8**.

Before counting the highlighted cells, we must filter the **Item **column based on the highlighted color.

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

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.

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

__Method 3 – __Calculate Percentage Based on Conditional Formatting Using Table Option

Use the **Table** option to calculate the percentage based on **Conditional Formatting**.

** Steps**:

➤ Go to

**Insert**Tab >>

**Table**Option.

The **Create Table **dialog box will appear.

➤ Select the range of your dataset.

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

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

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

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 select the **Filter by Color **option and finally choose the *Yellow *color box from the **Filter by Cell Color **option.

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

** Steps**:

➤ Go to

**Home**Tab >>

**Editing**Group >>

**Find & Select**Dropdown >>

**Find**Option.

The **Find and Replace **Dialog Box will pop up.

➤Select the **Format **Option.

The **Find Format **Dialog Box will appear.

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

You will have the **Preview **section as follows.

➤ Click the **Find All **option.

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

➤Write down this number in cell **C14**.

➤ For calculating total rows number type 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.

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

** Steps**:

➤ Go to

**Formulas**Tab >>

**Name Manager**Option.

The **Name Manager **Wizard will appear.

➤ Select the **New **Option.

The **New Name **Dialog Box will pop up.

➤ Type any name in the **Name **Box, 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.

Get the *Color Code ***6 **for the *yellow *color cells of the **Item **column.

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

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.

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

__Method 6 – __Using VBA Code

** Steps**:

➤ Go to

**Developer**Tab >>

**Visual Basic**Option.

The **Visual Basic Editor **will open up.

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

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

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

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

**Download Workbook**

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