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.
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
How to Calculate Percentage in Excel Based on Cell Color: 4 Methods
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)
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.
- Now, select the range of cells you want to work with.
- Then, select Filter from the Sort & Filter drop-down in the Editing group under the Home tab.
- After that, select the drop-down icon beside the Net Sales.
- Then, select the Green colored box in the Filter by Cell Color options like it’s shown in the following picture.
- 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.
- And then, select cell D13 and type the formula:
=D12/F4
- 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 Complete Based on Dates in Excel
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:
- First, select Define Name from the Define Names drop-down under the Formulas tab.
- 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.
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.
- Now, select cell E5 and type:
=ColoredNumber
- Then, press Enter and use the AutoFill tool to fill the series.
- Consequently, it’ll return the color codes.
- Next, select cell D12. Here, type the formula:
=SUM(D5:D10)
- 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: How to Calculate Percentage Based on Conditional Formatting
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 the Define Names drop-down under the Formulas tab.
- 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.
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.
- Now, select cell E5 and type:
=ColorCode
- Then, press Enter and use the AutoFill tool to fill the series.
- Next, select cell D12. Here, type the formula:
=SUM(D5:D10)
- 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.
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.
- Immediately, a new window will pop out.
- Then, select Module under the Insert tab.
- 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
- After that, close the Visual Basic window.
- Now, select cell D12 and type the formula:
=SUM(D5:D11)
- Then, press Enter.
- Again, select cell D13 and type the formula:
=ColorTotal(D5,D5:D10)
- 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
Download Practice Workbook
To practice by yourself, download the following workbook.
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
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Calculate Percentage of Month 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
