How to Calculate Percentage in Excel

Excel is a tool for storing and managing a large volume of data. It uses rows and columns to store the data. This tool is used for the analysis of data to get insights from it. Generally, this is used by financial professionals for accounting purposes, but it can be used by anyone. Data analysis can be performed in many ways like visual analysis using graphs, mathematical or trend analysis using formulas, etc. Let’s learn how can we calculate a percentage of numbers in Excel. 

In this tutorial, we’ll look at a few methods for quickly and accurately calculating percentages in Excel, as well as some fundamental percentage formulas that will eliminate any guessing from your numbers.

Percentage Basics

The Latin word per centum, which means “by the hundred,” is where “percent” originates. The percentage means, the proportion per hundred (out of 100). It is represented with the symbol ‘%’.

(Part/Whole)*100 = Percentage

For example, if you had ₹2000 and spent ₹500, How much did you spend percentage-wise?

Simple maths =500/2000*100 gives you the result, which is 25%.

How to Calculate a Percentage in Excel (Basic Method)

Excel automatically multiplies the resultant fraction by 100 when the Percentage format is added to a cell, so you don’t have to do it yourself when calculating a percentage.

The fundamental Excel method for calculating percentages is

Part/Total = Percentage

Now let’s look at how you can use actual data to apply the Excel percentage calculation.

Suppose you have some student data in columns A, B and C. Follow the procedures below to determine the percentage of marks obtained.

1. In column D2, enter the formula =C2/B2, and then duplicate it across as many rows as necessary.
2. To show the resulting decimal fractions as percentages, select the Percent Style button (Home tab > Number group) OR Press CTRL+SHIFT+% on the keyboard.

Fundamentals to Calculate Percentage in Excel

Percentage formula in Excel: Percentage can be calculated using the form la = part/whole. For Example, if you’re trying to apply for a discount, you want to reduce a particular amount by 10%. The formula is Price* 1- Discount%. (Consider the “1” as a substitute for 100%).

Calculating percentage is a mathematical operation that involves multiplying the fraction by a hundred.

For example, you have six subjects in your school and you are asked to write an exam for each subject, and every subject has 100 marks to score. The total marks for all the subjects sum up and give 600. And if you score 498 marks out of 600. Now you have to calculate the average of your scores. The average is calculated by dividing your score by the total number of marks and multiplying the fraction by 100 as shown below:

(498/600)*100 = 83

Percentage of Numbers using Excel Formulas

For example, suppose we want to calculate 20% of 200. Type the following formula into an Excel cell,

= 20% * 200

Example 1:

Output:

We can also reference other cell values instead of using direct numerical values. Suppose we have a cell containing the salary of an employee, and we want to calculate pf contribution by him, that is 12% of his salary.

Example 2: The A2 cell contains salary, and we want the pf contribution amount in the B2 cell

Solution:

Use cell name in place of number whose percentage is required.

Output:

How to Automatically Calculate Percentages in Excel

There are a set of Built-in features and functions to implement the mathematical operations. Percentage Calculator is one of them.

Now let’s consider an example where you can calculate the percentage of students’ Marks.

To calculate the Marks obtained use the formula “=(B2+C2+D2)”

Now for calculating the percentage use the formula “=(E2/F2)”

You can use the “%” symbol to convert the values into percentages.

Calculating Percentage of Total in Excel – Examples

Let’s look at some more instances that will make it easier for you to quickly calculate a percentage of a total in Excel for various data sets.

Example 1. If the Total is at the End of the Table in a Cell

The only difference between the percentage formula in this situation and the one we just reviewed is that the cell reference in the denominator is an absolute reference (with $).

Use the following method to determine percentages of the total if, for instance, some values are in column B and their sum is in cell B7:

=B2/$B$7, Because you want to keep the denominator at B7, input $B$7 as an absolute cell reference.

In the below example, the Percentages of Total column has a percentage style with two decimal places visible.

How to Calculate Percentage of Variation in Excel

A percent change formula is probably the one you would use the most frequently out of all the Excel formulas for determining percentages.

Excel Formula for Percent Increase / Decrease

The general formula is as follows, to determine the percentage difference between two numbers, X and Y:

Percent Change = (Y – X) / X

It’s crucial that you properly identify which value corresponds to X and which to Y when using this formula with actual data.

For example, yesterday you had 100 Sticky Notes, and now you have 120, meaning that now you have 20 Sticky Notes more than before, which is a 20% increase.

Considering the above example, the Percentage of Variation Formula in Excel can be re-written as:

Percentage of Variation = (New Price – Old Price) / Old Price 

Example 1. Calculating the month’s percent difference between 2 columns

Suppose you have last month’s inventory in column B and this month’s inventory in column C. Use the below formula to calculate the Percentage Change/Variation

=(C2-B2)/B2

Follow these steps to determine the percent difference between two columns

• In column D2, enter the formula =(C2-B2)/B2, Click the Percent Style button on the Home tab to convert the decimal number to a percentage
• Then duplicate the formula across as many rows as necessary

As a result, the formula calculates the percentage of change in this month (column C) compared to last month (column B). Here, negative percentages (percent decline) are formatted in red, while positive percentages (percent increase) are formatted in black.

Example 2. Calculating Percentage Difference Between Two Numbers

If you have a single column of numbers, such as column C, which shows quarterly sales, you can use the following formula to determine the percentage difference between the previous quarter and the current quarter:

=(B3-B2)/B2

Here, negative percentages (percent decline) are formatted in red, while positive percentages (percent increase) are formatted in black.

How to Increase or Decrease a Number Using the Excel Percentage Formula

To increase an amount by a percentage, use this formula:

= Amount * (1 + %)

For example, to increase the value in cell A1 by 50%, use the formula:

=A1*(1+50%)

To decrease an amount by a percentage:

= Amount * (1 – %)

For example, to reduce the value in cell A1 by 50%, use the formula:

=A1*(1-50%)

In our example, if A2 is your current Profit and B2 is the percentage you want to increase or reduce that amount by, here are the formulas you’d enter in cell C2:

Increase by percentage:

=A2*(1+B2)

Reduce by percentage:

=A2*(1-B2)

Hopefully, this extensive overview on Calculating Percentages in Excel has given you a general idea of how to calculate percentages in Excel along with different ways to calculate percentages in Excel. I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!

FAQs on How to Calculate a Percentage of a Number in Excel

Q1: What is Excel Formula to calculate Percentages?

Answer:

The easiest percentage formula is “(part/total)*100. This formula is used without the (*100). This is because when the percentage format is selected, the resulting number automatically changed to percent.

Q2: What is the Excel Formula for Percentage Change?

Answer:

Below is the Formula for calculating the Percentage change :

“(New value -original value)/original value” This formula calculates the percentage change(increase or decrease) . A positive percentage indicates an increase and a negative percentage indicates decrease.

Q3: How can we remove Percentage Errors?

Answer:

The percentage Errors can be removed with the help of the IFERROR function.

Q4: What is the Formula for calculating the percentage of the total?

Answer:

The formula for calculating the percentage of the total is” (part/total)”.