Whether you’re a business owner or student, Excel calculate percentage is an essential skill. Learn how to calculate percentage increase or decrease in values. Profit and loss, interest rates, statistics, discounts, comparing quantities, and tax calculation all require us to calculate percentages. We will look at the various ways of calculating percentages and how to calculate a percentage increase or decrease between values.
What’s the Easiest Way to Calculate Percentages in Excel?
A percentage is calculated by dividing a number by a hundred. A percentage is a fraction of 100, calculated by dividing the numerator by the denominator and multiplying the result by 100. A percentage is a number or ratio expressed as a fraction of 100. The symbol % denotes it.
For instance, in a survey, if the opinion on a critical issue is taken from 50 people and amongst them, 20 are for it, and 30 are against it, the percentage of people who are for an opinion is calculated as (20/50)*100= 40. Excel offers formulas and functions to calculate percentages. There are many formulas to calculate percentages, but it depends on what you want to achieve.
What is the Percentage Formula in Excel?
The formula to calculate a percentage in Excel is (part/total)*100. By default, Excel automatically calculates the percentage when you apply the correct formatting to the cell in which you want the results to appear. You do not have to multiply the resulting fraction by 100 since Excel does this automatically when the percentage formatting is applied to a cell.
A simple example of how to calculate percentage in Excel takes at least two values. One value denotes the total, or the outcome, of a work or activity. Another value denotes a part, or a subgroup, of the total value.
To calculate the percentage in Excel, follow these steps:
- Enter the item names, total items manufactured, and the number of faulty items in the columns as shown in the example below.
- Input the formula to calculate the percentage of items that work, excluding the faulty ones.
- In the above example, calculate the percentage of items that work using the formula =C2/B2.
- The result is displayed in column D. Then apply the Excel percentage formula by selecting Home → Numbers Group → Choose Percentage from the drop-down menu.
- You can increase or decrease the decimal point and make the percentage a whole value.
Column D displays a rounded percentage of items that are working and not faulty.
Other Methods for Calculating Percentages in Excel
Let’s look at the other methods for calculating percentages in Excel.
Calculate the Percentage from Values at the End of the Table
Sometimes, you have the total at the end of the column, and you will have to calculate the percentage of the individual items to the total. In the example below, the percentage formula in Excel is given as =B2/$B$5.
Let’s look at the reason why an absolute reference is included. Column B has the value of a specific group of people, and the total is returned at the end of the column. We are interested in calculating the percentage of each type of people to the total and returning the percentage in the next column, C.
To calculate the percentage, take the individual value divided by the total. The total remains the same in all calculations. Therefore, keep it static by referencing it using a $ symbol, making it an absolute reference.
Related: Create an Absolute Reference in Excel – Beginner’s Guide
Select Home → Numbers → Percentage, and set the cell format to contain a percentage value.
Increase or decrease the decimal point and make the percentage a whole value.
Calculate the Percentage from Values in Multiple Rows
When you have several rows of data and you wish to pull out values based on particular criteria, and total those up, you need the SUMIF function. The SUMIF function adds up numbers relating to given criteria. We can then divide that number by the total to calculate a percentage.
In the above example, use the SUMIF() function to find the sales percentage in different branches (our criteria). The formula to find the percentage of sales in the branch North is =SUMIF(B2:B9, G2, D2:D9)/$D$10. The SUMIF() function totals the cells specified by a given condition or criteria.
In this instance, the SUMIF() function takes three arguments, range, criteria, and sum_range. Here, the range where the SUMIF() function is applied at B2:B9, the condition or the criteria here is to select the sales that happened only in the ‘North’ branch.
The function identifies sales of two products at the ‘North’ branch, calculates the total sales in the ‘North’ Branch, and divides it by the overall sales.
You can use SUMIF() functions to find the total sales in different branches, North and South.
How to Calculate a Percentage Increase in Excel
Percentage increase or decrease is used for comparison. This formula calculates the percentage increase or decrease between two values. For instance, if you have a value x that changes over time, and you need to find out the percentage of the difference between the old and the new values, you can use the percentage increase formula.
Percentage Increase = (new_value – old_value) / old_value
If the result from the formula gives a positive value, it indicates that the new_value is more than the old_value, and if it is a negative value, it indicates that the new_value is less than the old value, in other words, the percentage change between two values has decreased.
Calculate Percentage Increase or Decrease in Two Columns
The below example shows the percentage change comparing the previous month’s and current month’s expenses. The relative difference can be a positive or negative value, which means that the expense could have increased or decreased compared to the current and previous month’s expenses.
The steps to calculate the percentage difference between two numbers are as follows:
- Enter the formula =(C2-B2)/B2 in cell D2.
- A decimal value is returned as a result.
- Select Home → Styles Group → Percent to convert the decimal number to a percentage.
- Drag the formula to the cells below until the end of the table to apply the formula.
Calculate a Percentage Increase or Decrease Between Two Numbers
You can compare two numbers within the same column, find the difference, and display the percentage difference between the two values. For instance, in the example below, the table shows the growth of a plant in cm.
You want to compare the percentage increase or decrease in the plant’s growth from when it was planted. The formula to calculate is =(B3-$B$2)/$B$2. The plant’s initial height is given in cell B2, and every other value has to be compared with this initial one. When cell B2 is going to be static, use the absolute reference and lock it with the $ symbol.
In C2, the formula cannot be applied as it is in week 1, and no significant difference can be found. In cell C3, enter the formula as =(B3-$B$2)/$B$2. From week to week, the growth is compared, and the result is displayed.
Then, change the cell’s format to percentage so that it displays the percentage difference between the two values in comparison.
Frequently Asked Questions
1. Is there another way to calculate a percentage in Excel?
Percentages can be calculated using the shortcut key to format a cell to contain a percentage value by selecting the cell, then pressing CTRL+SELECT+%. Other methods to format the cell are:
Method 1 – Select from the drop-down menu, select the settings on the Number group, and set the format.
Method 2 – Select the % symbol and apply the format.
Method 3 – Open the settings and set the percentage format.
2. What is the formula to calculate the percentage of the total?
The general formula to calculate the percentage is to divide the sum by the total number of values. Here, in the example below, the percentage of the total population that is male can be found using the formula, =(B2/$B$6)*100.
Final Thoughts
We encounter various situations where percentages are useful to our work. Being able to calculate percentages is a fundamental skill within Excel. Percentages are vital for any work involving profit and loss, interest rates, statistics, discounts, comparing quantities, and tax
calculation. It’s important to make sure calculating percentages is something you are confident with as it’s so valuable to your Excel skillset.
As Excel spreadsheets are used in almost every profession, learning shortcuts and mathematical functions is helpful for confidently navigating various work situations. Check out our courses in Excel and Microsoft Office Applications to learn more about Excel functions and formulas. You can enroll in all of these courses on our website, and you will earn micro-credentials on their completion.
