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

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

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF