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.

Calculate Percentage formula in Excel to compare values

  • 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%).

Read More: How to Apply Percentage Formula in Excel for Marksheet 


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.

Calculate percentage in Excel from total number

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

Read More: How to Calculate Percentage of a Number in Excel 


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 “Avocadoin cell G5
  • Enter the following formula:
=SUMIF(C5:C17,G4,D5:D17)/SUM(D5:D17)

SUMIF formula to calculate percentage in Excel from single criterion

 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 Avocadoand the person Davidmultiple times. However, we only want the percentage of Avocadossold by David.” We have two criteria: Davidin cell G4 &Avocadoin cell G5.

  • Enter the following formula:
=SUMIFS(D5:D17,B5:B17,G4,C5:C17,G5)/SUM(D5:D17)

SUMIFS formula to calculate percentage in Excel from multiple criteria

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%

Read More: How to Calculate Percentages Based on Conditional Formatting 


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.

Calculate percentage difference in different columns in Excel


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.

Calculate percentage difference in same column in Excel

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.

Sort data by smallest to largest

  • The Sort Waring box will pop up.
  • Check expand the selection.
  • Click Sort.

Sort Warning dialog box

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

Calculate percentage in Excel based on ranks


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)

AVERAGE formula to calculate average of percentage in Excel

Read More: How to Calculate Average Percentages in Excel 


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.

Calculate total amount from percentage

Read More: How to Add 15 Percent to a Price in Excel


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.

Total amount from percentile amount

Read More: How to Calculate Total Percentage in Excel


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.

Calculate specific amount from percentage and total amount

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


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.

Calculate percentage with structured reference in Table


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.

Sample dataset to increase column by percentage

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.

Paste special option in context menu

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

Paste Special dialog box

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

Increase column to specific percentage

Read More: How to Add Percentage to Price with Excel Formula 


Download Practice Workbook

You can download the practice book from the link below.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo