How to Calculate Percentage in Excel Based on Cell Color (4 Methods)

Sometimes, in our Excel worksheet, we need to calculate the percentage based on cell color. In this article, we’ll show you the simple methods to Calculate the Percentage in Excel based on Cell Color.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. In this case, we have colored the net sales of some salesman and we’ll show you how to find out the percentage of the colored net sales to the total net sales.

calculate percentage in excel based on cell color


Download Practice Workbook

To practice by yourself, download the following workbook.


What is Percentage?

A number or ratio expressed as a fraction of 100 is known as a Percentage. The sign of percentage is ‘%’. The basic percentage is calculated by the formula:

Percentage = (Part / Whole)*100


4 Methods to Calculate Percentage in Excel Based on Cell Color

1. Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

We know, Excel provides various Functions and we use them to perform many operations. At first, we’ll use the Excel SUBTOTAL function for computing the percentage based on the cell color along with the Filter feature. The SUBTOTAL function performs a particular task based on the function number applied in the argument. Therefore, follow the steps below to know how to do the task.

STEPS:

  • Firstly, select cell D12 and type the formula:
=SUBTOTAL(9,D5:D10)

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

Here, in the argument, 9 is the function number for SUM and D5:D10 is the range of cells.

  • Then, press Enter and it’ll return the sum result.
  • After that, copy the result and paste it as values in cell F4 like it’s shown in the image below.

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • Now, select the range of cells you want to work with.

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • Then, select Filter from the Sort & Filter drop-down in the Editing group under the Home tab.

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • After that, select the drop-down icon beside the Net Sales.
  • And then, select the Green colored box in Filter by Cell Color options like it’s shown in the following picture.

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • Consequently, you’ll see the Green colored cells only.
  • Additionally, the sum result in cell D12 will get updated automatically and now it’ll return the sum of those green-colored cells only.

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • And then, select cell D13 and type the formula:
=D12/F4

Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

  • After that, press Enter.

  • Next, select the ‘%’ icon in the Number group under the Home tab.

  • Finally, you’ll get the desired percentage value of green-colored cells.

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


2. Apply SUMIF Function for Calculating Percentage Based on Cell Color

Secondly, we’ll use another Excel function to calculate the percentage based on cell color. The Excel SUMIF function computes the sum of a range of cells based on a specific condition given in the argument. In this method, we’ll use the SUMIF function to find out the percentage. So, follow the steps to perform the task.

STEPS:

  • At first, select Define Name from Define Names drop-down under the Formulas tab.

Apply SUMIF Function for Calculating Percentage Based on Cell Color

  • As a result, a dialogue box will pop out. There, type ColoredNumber in the Name section.
  • Then, in the Refers to section, type the formula:
=GET.CELL(38,‘sumif’!$D5)
  • After that, press OK.

Apply SUMIF Function for Calculating Percentage Based on Cell Color

Here, the GET.CELL function takes 38 to return the code color, sumif is the name of the worksheet and D5 is the cell reference.

  • Subsequently, a Name Manager dialogue box will pop out and there press Close.

Apply SUMIF Function for Calculating Percentage Based on Cell Color

  • Now, select cell E5 and type:
=ColoredNumber

Apply SUMIF Function for Calculating Percentage Based on Cell Color

  • Then, press Enter and use the AutoFill tool to fill the series.
  • Consequently, it’ll return the color codes.

Apply SUMIF Function for Calculating Percentage Based on Cell Color

  • Next, select cell D12. Here, type the formula:
=SUM(D5:D10)

Apply SUMIF Function for Calculating Percentage Based on Cell Color

  • Then, press Enter.
  • After that, select cell D13. There, type the formula:
=SUMIF(E5:E10,E8,D5:D10)

Here, E5:E10 is the criteria range, E8 is our criteria and D5:D10 is the sum range.

  • Now, press Enter.
  • And then, select cell D14 and type the formula:
=D13/D12

  • Afterward, press Enter and select the ‘%’ icon in the Number group under the Home tab.

  • At last, the accurate percentage result will appear in cell D14.

Related Content: Percentage Formula in Excel (6 Examples)


Similar Readings:


3. Cell Color Based Percentage Calculation with Excel SUMIFS Function

Moreover, we can use the Excel SUMIFS function for percentage calculation based on cell color. Hence, learn the process to compute the percentage.

STEPS:

  • In the beginning, select Define Name from Define Names drop-down under the Formulas tab.

Cell Color Based Percentage Calculation with Excel SUMIFS Function

  • Consequently, a dialogue box will pop out. Here, in the Name section, type ColorCode.
  • Then, in the Refers to section, type the formula:
=GET.CELL(38,sumifs!$D5)
  • After that, press OK.

Cell Color Based Percentage Calculation with Excel SUMIFS Function

Here, the GET.CELL function takes 38 to return the code color, the name of the worksheet is sumifs and Cell D5 is the cell reference.

  • Subsequently, a Name Manager dialogue box will pop out and there press Close.

Cell Color Based Percentage Calculation with Excel SUMIFS Function

  • Now, select cell E5 and type:
=ColorCode

Cell Color Based Percentage Calculation with Excel SUMIFS Function

  • Then, press Enter and use the AutoFill tool to fill the series.

Cell Color Based Percentage Calculation with Excel SUMIFS Function

  • Next, select cell D12. Here, type the formula:
=SUM(D5:D10)

Cell Color Based Percentage Calculation with Excel SUMIFS Function

  • Then, press Enter.
  • After that, select cell D13. There, type the formula:
=SUMIFS(D5:D10,E5:E10,E6)

Here, D5:D10 is the sum range, E5:E10 is the criteria range and E6 is the criteria.

  • Now, press Enter.
  • And then, select cell D14 and type the formula:
=D13/D12

  • Afterward, press Enter and select the ‘%’ icon in the Number group under the Home tab.

  • Eventually, the precise percentage value will appear in cell D14.

Related Content: Calculate Average Percentage in Excel [Free Template+Calculator]


4. Percentage Calculation Based on Cell Color with Excel VBA

Lastly, we’ll apply the Excel VBA code to get our percentage based on cell color. Therefore, follow along to learn the process.

STEPS:

  • At first, select Visual Basic under the Developer tab.

Percentage Calculation Based on Cell Color with Excel VBA

  • Immediately, a new window will pop out.
  • Then, select Module under the Insert tab.

Percentage Calculation Based on Cell Color with Excel VBA

  • As a result, another window will pop out. There, paste the code given below:
Function ColorTotal(CClr As Range, rRng As Range)
Dim sum As Long
Dim color As Integer
color = CClr.Interior.ColorIndex
For Each cl In rRng
If cl.Interior.ColorIndex = color Then
sum = WorksheetFunction.sum(cl, sum)
End If
Next cl
ColorTotal = sum
End Function

Percentage Calculation Based on Cell Color with Excel VBA

  • After that, close the Visual Basic window.
  • Now, select cell D12 and type the formula:
=SUM(D5:D11)

Percentage Calculation Based on Cell Color with Excel VBA

  • Then, press Enter.
  • Again, select cell D13 and type the formula:
=ColorTotal(D5,D5:D10)

Percentage Calculation Based on Cell Color with Excel VBA

  • Next, press Enter.
  • After that, select cell D14 and type the formula:
=D13/D12

  • Then, press Enter and select the ‘%’ icon in the Number group under the Home tab.

  • Lastly, the percentage of the colored cells to the total net sales will appear in cell D14.

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


Conclusion

Henceforth, you will be able to Calculate the Percentage in Excel based on Cell Color with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo