How to Calculate Percentage Based on Conditional Formatting (6 Ways)

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.

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.

calculate percentage based on 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.

calculate percentage based on conditional formatting

Steps:
➤ Put down the name of the background colors of the cells of the Item column.

calculating manually

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

calculate percentage based on conditional formatting

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

calculating manually

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

calculating manually

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

calculate percentage based on conditional formatting

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

=C15/C14

calculating manually

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

calculating manually

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

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

calculate percentage based on conditional formatting

Steps:
➤ Select the data range and then go to Data Tab >> Sort & Filter Dropdown >> Filter Option.

using Filter option

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

using Filter option

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

calculate percentage based on conditional formatting

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

using Filter option

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.

calculate percentage based on conditional formatting

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.

using Filter option

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

using Filter option

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

=C14/C13

using Filter option

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

calculate percentage based on conditional formatting

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.

calculate percentage based on conditional formatting

Steps:
➤ Go to Insert Tab >> Table Option.

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

using Table option

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

calculate percentage based on conditional formatting

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

using Table option

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.

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

using Table option

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

=C14/C13

using Table option

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

calculate percentage based on conditional formatting

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


Similar Readings:


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.

calculate percentage based on conditional formatting

Steps:
➤ Go to Home Tab >> Editing Group >> Find & Select Dropdown >> Find Option.

Find & Select option

After that, the Find and Replace Dialog Box will pop up.
➤Select the Format Option.

Find & Select option

Then, the Find Format Dialog Box will appear.
➤ Select Fill Option, choose the Yellow Color, and press OK.

Find & Select option

Afterward, you will have the Preview section as follows.
➤ Click the Find All option.

calculate percentage based on conditional formatting

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

Find & Select option

➤ Now, write down this number in cell C14.

Find & Select option

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

Find & Select option

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

=C14/C13

Find & Select option

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

calculate percentage based on conditional formatting

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.

calculate percentage based on conditional formatting

Steps:
➤ Go to Formulas Tab >> Name Manager Option.

GET.CELL Function

Then, the Name Manager Wizard will appear.
➤ Select the New Option.

calculate percentage based on conditional formatting

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.

GET.CELL Function

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

GET.CELL Function

➤ Press ENTER and drag down the Fill Handle Tool.

GET.CELL Function

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

GET.CELL Function

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.

GET.CELL Function

Now, for the Total Rows use 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.

GET.CELL Function

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

=C14/C13

GET.CELL Function

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

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

calculate percentage based on conditional formatting

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

calculate percentage based on conditional formatting

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

VBA Code

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

calculate percentage based on conditional formatting

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.

Practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo