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

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

### 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*).

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

### 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%**

**Read More:** How to Calculate Percentage Based on Conditional Formatting

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

**Read More:** How to Calculate Average Percentage in Excel

## 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*.

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

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

**Read More:** How to Calculate Total Percentage in Excel

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

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

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

**Read More:** How to Create a Formula with Structured References in Excel

## 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%*.

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

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

You can download the practice book from the link below.

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

**Related Articles**

- How to Calculate Percentage Complete Based on Dates in Excel
- Why Are My Percentages Wrong in Excel?
- How to Calculate Absenteeism Percentage in Excel
- Convert Number to Percentage Without Multiplying by 100 in Excel
- How to Calculate Productivity Percentage in Excel
- Percentage Showing as Thousand in Excel