How to Create a Formula in Excel to Calculate Percentage

Watch the Video – Create a Formula in Excel to Calculate Percentage

Method 1 – Calculate the Percentage Formula to Compare Values

We have a dataset describing the quantities of some products bought and sold. We want to compare the products based on the percentage of sales. We need to calculate the percentage of the quantity bought and sold.

Enter 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 the percentage for other cells.

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, the percentage of sales is maximum for Papaya (95.00%) and minimum for Coconut (56.00%).

Method 2 – Formula to Calculate Percentage from Total Number

Let’s consider the ‘bought quantity’ of products from our previous dataset. We want to find out the percentage of the quantity of each product bought from the total bought quantity.

We have used the SUM function to calculate the total quantities of the products. Then, 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 so that it does not change when dragging or copying the formula for other cells.

This formula calculates the percentage of total values that apply to the same category (e.g., Bought Quantity).

Method 3 – Percentage Formula Based on Condition

3.1. Calculate Percentage Based on Single Criterion

In the dataset below, we can see various products, but “Avocado” is repeated three times. Here, we want to calculate the percentage of sales for “Avocados” only. We have only one criterion, based on which we will create a formula to get the percentage.

Enter “Avocado” in cell G5
Enter the following formula:

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

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

The dataset shows the product “Avocado” and the person “David” multiple times. However, we only want the percentage of “Avocados” sold by “David.” We have two criteria: “David” in cell G4 & “Avocado” in cell G5.

Enter the following formula:

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

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 finds the cell value of G5 in the range C5:C17. Later, it only sums the cell values that 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%

Method 4 – Calculate the Percentage Difference Between Numbers Using a Formula

4.1. Determine the Percentage Difference Between Different Columns

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

Enter the following formula:

=(D5-C5)/C5

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

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

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.

Enter the following formula for the month of February:

=(C6-C5)/C5

Enter the formula for the other cells:

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

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. If we want to comment on the change (whether Profit or Loss), we will create a formula with the IF function. For change <0%, we will comment Loss, and for >0%, we will comment Profit.

This method is good to use when you need to find the percentage difference in the same column and comment on the difference.

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

Steps:

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.
Check expand the selection.
Click Sort.

The data is sorted.
For the minimum value in the range, enter the following formula:

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

This will show you 0% for the minimum value.

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

Method 6 – Apply the AVERAGE Function to Calculate the Average Percentages

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, enter the following formula:

=AVERAGE(C5:C9)

Several Examples of Using Percentages in Excel Formula

1. Calculate Total Amount from Percentage

Case 1: Formula to Add a Percentage

A shop is selling some products, and 5.00% VAT applies to the selling price of all types of products. The final price, including VAT, is 5.00% more than the original price, excluding VAT.

To get the final price, we must create a formula that adds a percentage to the previous price. The formula is below:

=C5+C5*$D$5

This formula calculates the VAT using C5*$D$5 for 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

A shop has sold some laptops, and the price of one laptop ($200) is 5% of the total amount. So, we have 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 Amounts from Percentage and Total Amount

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

To determine the specific amount from each product, we can 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 Percentages

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

Insert a table into your data and then apply the formula:

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

Press ENTER to calculate the output for all the other cells using the formula.

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

In the dataset below, we have the price of some products and a 5% VAT.

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

Enter 105% in cell C5.
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.
Put a checkmark on All from the Paste group and Multiply from the Operation group.
Click OK.