How to Create a Formula in Excel to Calculate Percentage

Whether you’re analyzing sales data or tracking student grades, percentages are an essential part of any data analysis. Excel makes it easy to calculate percentages with its built-in formula capabilities. In this article, we’ll walk you through the basics of percentages and show you how to create a formula in Excel to calculate percentage. By the end of this article, you’ll have the knowledge you need to use Excel to quickly and accurately calculate percentages for any data set.

Watch Video – Create a Formula in Excel to Calculate Percentage

Basics of Percentage Formula

A percentage is a way of expressing a number as a fraction of 100. It is represented by the symbol “%”.

To create a formula in Excel to calculate a percentage, you can use the following formula:

= (part/whole) * 100

Here, “part” represents the value you want to find the percentage of, and “whole” means the total value or quantity. The formula divides the part by the whole to get the decimal value, then multiplies it by 100 to convert it to a percentage.

For example, let’s say you want to calculate the percentage of students who passed an exam out of the total number of students who took it. If there were 75 students who passed and 100 students in total, you can enter the formula “= (75/100) * 100” into an Excel cell to get the result of 75%.

This is just a general scenario, but there is no fixed formula in Excel to calculate percentage. It will change as per the situation.

How to Create a Formula in Excel to Calculate Percentage: 6 Suitable Examples

To calculate the percentage, you will need to analyze and observe the scenario you are dealing with. Properly understanding the term and analyzing the situation will help you to calculate the percentage. In this section, we will discuss 5 suitable and real-life examples of how to create a formula in Excel to calculate percentage.

1. Calculate Percentage Formula to Compare Values

In the image below, we have a dataset describing the bought and sold quantities of some products. We want to compare the products based on the percentage of sales. So, we need to calculate the percentage from the bought and sold quantity. For this, we have applied the following formula in cell E5.

=D5/C5

Here, dividing the sold quantity (D5) by the bought quantity (C5) will get you the fraction of sales, and converting the cell format to percentage from the Home tab will show you the percentage.

Drag the Fill Handle tool to calculate percentage for other cells also.

Note: For each case, we will use the Percentage format to show the percentage.

After you calculate the percentage for each item, you can compare the products. For our dataset, we can see that the percentage of sales is maximum for Papaya (95.00%) and minimum for Coconut (56.00%).

2. Formula to Calculate Percentage from Total Number

One of the common ways to calculate percentage is based on the total number/values. For example, let’s just consider the bought quantity of the products from our dataset. We want to find out the percentage of the bought quantity of each product from the total bought quantity. We have used the SUM function here to calculate the total quantities of the products first. Later, we created the following formula in Excel to calculate the percentage.

=C5/SUM($C$5:$C$14)

For, range $C$5:$C$14 we have used the absolute reference to lock the range lest it should change with dragging or copying the formula for other cells.

This formula to calculate percentage of total values is applicable for the same category (i.e. Bought Quantity).

3. Percentage Formula Based on Condition

In this segment, we have modified the dataset a little bit by adding Sales Person with each product. We will create a percentage formula based on both single and multiple criteria.

3.1. Calculate Percentage Based on Single Criterion

In the dataset below, we can see that there is a variety of products, but “Avocado” is repeated 3 times only. Here, we want to calculate the percentage of sales only for “Avocado”. So, we have only one criterion here based on which we will create a formula to get the percentage.

We have put the criterion “Avocado” in cell G5 and entered the following formula.

=SUMIF(C5:C17,G4,D5:D17)/SUM(D5:D17)

idea: Formula Breakdown

The SUMIF function adds the cells specified by a given condition or criteria. So, it checks for the cell value of G4 in the range C5:C17 and sums the corresponding cell values in similar rows in the range D5:D17.

SUMIF(C5:C17,G4,D5:D17) = 82+85+90 = 257
SUM(D5:D17) = 610
SUMIF(C5:C17,G4,D5:D17)/SUM(D5:D17) = 257/610 = 0.4213 = 42.13%

3.2. Calculate Percentage Based on Multiple Criteria

We have used the SUMIF function as we have considered a single criterion previously. But the SUMIFS function can consider multiple criteria and sums cell values according to the specified criteria or conditions.

The dataset holds the product “Avocado” and the person “David” multiple times. But we only want the percentage of “Avocado” sold by “David” over the total products. So, we have two criteria now: “David” in cell G4 & “Avocado” in cell G5.

Apply the following formula to calculate percentage in Excel based on multiple criteria.

=SUMIFS(D5:D17,B5:B17,G4,C5:C17,G5)/SUM(D5:D17)

idea: Formula Breakdown

The SUMIF function adds the cells specified by a given condition or criteria. So, it checks for the cell value of G4 in the range B5:B17 and also finds the cell value of G5 in the range C5:C17. Later it only sums the cell values which are common for the two criteria G4 and G5.

SUMIFS(D5:D17,B5:B17,G4,C5:C17,G5) = 82
SUM(D5:D17) = 610
SUMIFS(D5:D17,B5:B17,G4,C5:C17,G5)/SUM(D5:D17) = 82/610 = 13%

4. Calculate Percentage Difference Between Numbers with Formula

We can consider two cases to calculate the percentage difference between numbers. The numbers can either be in the same column or in a different column.

4.1. Determine Percentage Difference Between Different Columns

For some products, we have the Wholesale Price in one column and Retail Price in another column from which we want to calculate the percentage of profit. For this, we need to subtract the Retail price from the Wholesale price and divide the difference by the Wholesale price.

So, use the following formula to determine the percentage difference between different columns.

=(D5-C5)/C5

Here, D5 is the Retail price and C5 is the Wholesale price.

4.2. Calculate in Same Column to Find Percentage Increase or Decrease and Predict on Percentage

Sometimes we may need to calculate the percentage difference in the same column and for specific scenarios, we may also require to comment on the change. This dataset is a similar scenario.

We have sales data over the year in a single column from which we will calculate the percentage difference. We will find the change in sales in a month compared with the previous month. Apply the following formula for the month of February as for January there is no previous month to compare.

=(C6-C5)/C5

Copy the formula for the other cells also. You can see that for some months, the result is positive and for some months it’s negative. The positive value indicates Profit and the negative value indicates Loss. So, if we want to comment on the change (whether Profit or Loss), we will create a logical formula with the IF function. For change <0%, we will comment Loss and for >0% we will comment Profit.

The formula used here is:

=IF(D6<0%,"Loss","Profit")

So this method is applicable where you need to find the percentage difference in the same column and comment on the difference.

5. Use PECENTRANK.INC Function to Calculate Percentage Based on Rank

The PERCENTRANK.INC function returns the rank of a value in a dataset as a percentage of the dataset. Now we will use this function to create a formula based on rank. We have sales records over the year. Considering the minimum value as 0% and the maximum value as 100%, we will create a formula to calculate the percentage for the intermediate values by applying the PERCENTRANK.INK function. As our task is based on minimum and maximum values, we need to sort the data by value first of all.

Select the range of data with values >> go to the Home tab >> click the dropdown of Sort & Filter under the Editing group.
Select Sort Smallest to Largest from the dropdown menu.

The Sort Waring box will pop up.
Keep checkmarks on expand the selection.
Click Sort.

Now your data is sorted.
For the minimum value in the range, apply the following formula.

=PERCENTRANK.INC($C$5:$C$16,C5)

This will show you 0% for the minimum value.

Copy the formula down and the percentage will keep increasing with the increase in the value.
For the maximum value, it will show 100%.

6. Apply AVERAGE Function to Calculate Average of Percentages

Suppose we have a dataset that represents the sales growth percentages of a company over a period of five years.

To calculate the average sales growth percentage over these five years using the AVERAGE function, apply the following formula.

=AVERAGE(C5:C9)

The formula will calculate the average of the percentages and return the average growth percentage of the company over these five consecutive years.

Several Practical Examples to Use Percentages in Excel Formula

We use percentages in our day-to-day life for various purposes. In this section, we will discuss some real-life examples of using percentages with Excel formulas.

1. Calculate Total Amount from Percentage

As you have learned to calculate the percentage, you can also calculate the total from the percentage.

Case 1: Formula to Add a Percentage

Let’s say, a shop is selling some products and 5.00% VAT is applicable to the selling price of all types of products. So, the final price including VAT is 5.00% more than the original price excluding VAT.

So, we need to create a formula to add a percentage to the previous price to get the final price. We have applied this one below.

=C5+C5*$D$5

This formula calculates the VAT using C5*$D$5 this portion and adds the VAT to the original price C5 to get the final price including VAT.

Case 2: Total Amount from Percentile Amount

Let’s say, a shop has sold some laptops where the price of one laptop ($200) is 5% of the total amount. So, we have got the partial amount and percentile amount. We need to find the total sales from this data. If we want to calculate the total amount from this percentile amount, we can use the formula below.

=C5/D5

Here, dividing the partial amount by the percentile amount will get you the total amount.

2. Determine Specific Amount from Percentage and Total Amount

To determine a specific amount from the percentage and total amount, we can consider the list with some products stated below. The dataset describes the total sales from these products and the percentage of sales for each product.

So, to determine the specific amount from each product, we can just multiply the total sales by the percentage amount of the specific product.

=$D$5*C5

This will find you the sales from a specific product.

How to Create a Formula with Structured References to Calculate Percentage in Excel

Structured reference is a special type of cell reference that is used in Excel tables. This reference is designed to make it easier to work with and analyze data in tables. It allows you to refer to table elements, such as columns, rows, and totals, using descriptive names instead of traditional cell references.

Our dataset has Wholesale prices and Retail prices of some products from which we will calculate the Percentage of Profit. For this, we need to subtract the wholesale price from the retail price and then divide the difference by the wholesale price.

So, insert a table to your data and then apply the formula. We don’t need to type the formula after inserting the table. Just select the cell and the structured reference will automatically appear in the formula bar.

=([@[Retail Price]]-[@[Wholesale Price]])/[@[Wholesale Price]]

Apply the formula for the first cell and by pressing ENTER it will calculate the output for all the other cells using the formula. In fact, the formula is applied here for the described columns.

How to Increase or Decrease an Entire Column by a Percentage Without Formula in Excel

To increase or decrease an entire column by a percentage without a formula, we will use the Paste Special Command feature. For example in the image below, we have the price of some products on which 5% VAT will be applicable.

To get the price including VAT, we need to increase the entire column by 5%.

We need to increase the value by 5%, so put 105% in another column where we will insert the updated price.
Copy the column values which we need to increase.
Click the first cell (with 105%) where we want to paste values.
Right-click on the mouse >> click Paste Special.

The Paste Special dialog box will pop up.
Here, put a checkmark on All from the Paste group and Multiply from the Operation group.
Click OK.

As a result, your values will increase to 105%.

Frequently Asked Questions

1. Are there any built-in functions for percentage calculations in Excel?

Yes, Excel provides several built-in functions for percentage calculations, such as PERCENTILE and PERCENTRANK. These functions offer additional capabilities and flexibility in handling percentage-related calculations.

2. How can I use conditional formatting to highlight specific percentage ranges?

Conditional formatting in Excel allows you to apply formatting rules based on certain conditions. You can use it to highlight specific percentage ranges by selecting the cells containing the percentage results, choosing the conditional formatting option, and setting the desired rules to define the formatting based on percentage thresholds (i.e. greater than or less than a specific percentage)

3. Can I use conditional statements or logical operators in percentage formulas?

Yes, you can use conditional statements or logical operators in percentage formulas in Excel. For example, you can use the IF function to check a condition and calculate different percentages based on the result. This allows you to perform calculations based on specific criteria or conditions.

Takeaways from this Article

Percentages can be easily calculated in Excel using simple formulas. The formula structure involves dividing the part by the whole and multiplying by 100.
When calculating percentage changes, subtract the old value from the new value, divide the result by the old value, and multiply by 100 to get the percentage increase or decrease.
Cell references can be used in percentage formulas, allowing for flexibility and easy updating of values.
You can calculate percentages of subtotals by multiplying the subtotal by the desired percentage, or calculate subtotals of percentages by dividing the percentage value by 100 and then dividing the result by the percentage.
Use logical operators and conditional statements in formulas to perform calculations based on specific criteria or conditions on percentages.
Excel provides built-in functions such as PERCENTILE and PERCENTRANK that offer additional capabilities for percentage-related calculations.

Download Practice Workbook

You can download the practice book from the link below.

Creating a Formulato Calculate Percentage.xlsx

Conclusion

In conclusion, this article serves as a comprehensive guide on how to create a formula in Excel to calculate percentage. It covers the formula structure for percentage calculations, calculating percentage changes, utilizing cell references, and leveraging built-in functions.

The article also demonstrates the use of calculating percentages of subtotals and incorporating logical operators and conditional statements. Additionally, it highlights the AVERAGE function for calculating the average of a series of percentages.

By following the provided instructions and examples, readers can confidently perform percentage calculations and gain valuable insights from their data in Excel.

Get FREE Advanced Excel Exercises with Solutions!