Learning how to calculate percentage difference in Google Sheets is useful for making comparisons by having a better grasp of proportion and quantity.
Table of Contents
Percentages are a great way to make calculations easier. Working with parts of 100 is significantly simpler than working with thirds, twelfths, or any different denominators in fractions.
There are two common ways to use percentages:
- By proportion
- By total
The first application of percentage is to calculate by proportion. This is to also work out the amount of progress or percentage of a target is achieved.
For example, you are a student who is given the task of finishing this literature assignment by the end of spring break. Moreover, to keep track of your progress, you can create a simple tracker by using percentages as a mechanism.
As you can see, with this table, we can easily track the progress of pages you read for each book.
The second application of percentage is to calculate by total. This can also be used to compare an amount against the total amount.
For example, you are a sales manager and need to evaluate your team’s performance for the year-end review. One of the key performance indicators is the number of sales each member sold in a year.
To easily assess each performance, we can create a simple table to compare everyone’s achievements.
By using this table, we can see that Nat has outperformed his peers and deserves to be recognized for his contributions. This also helps you give objective feedback on who and why they are qualified to get a promotion or a salary increment.
Furthermore, now that we have a better understanding of how to use percentages in Google Sheets, we can calculate percentage differences.
Anatomy of Calculating Percentage Difference in Google Sheets
The percentage difference between the two values is calculated by:
= ((Value 1 - Value 2)/ Value 2)* 100
For example, if you got 70 marks for Math midterms in June and 80 marks in Dec, we can use the following formula to calculate the percentage difference:
Percentage Difference = ((80 – 70)/ 70)* 100
Percentage Difference = 12.5%
This also shows a 12.5% improvement in your math test, justifying the effort you put into studying paid off.
To insert the formula into Google Sheets, it would look like this:
A Real-Life Example of Calculating Percentage Difference in Google Sheets
Example 1:
Imagine you manage a retail store selling various items. By the end of the year, you are tasked to analyze the sales trend of the store.
A way to assess sales is to calculate the percentage difference of sales for each month. This also helps to identify the sales trends and see which months have higher sales.
Besides, it also helps you to estimate the amount of inventory to purchase, which avoids stocks getting obsolete and prevents cash flow issues.
Moreover, you may make a copy of the spreadsheet using the link I have attached below.
How to Calculate Percentage Difference in Google Sheets
- First, click on the cell you want to calculate the percentage difference in. In this example, it is D6.
- Begin your formula with an equal sign
=.
- To calculate the percentage difference between the two months, we use the total sales for February and subtract it from the total sales for January. Then, the difference is divided by the total sales of February.
- The amount in cell D6 is shown in the form of a fraction. To show the difference as a percentage, we will times the amount by 100. However, this does not show the symbol percentage.
- To show the percentage symbol, we can format the cell as a percentage by clicking the percentage icon on Google Sheets’s on the toolbar. When using this method, we can skip the previous step.
- Another way is to add the
TO_PERCENTfunction in front of the formula. This function also helps to convert a provided number into a percentage.
- Once you insert the formula to all the months, your sheet will look something like this.
Example 2:
Another application for percentage difference is calculating the percentage difference of actual outcome compared to an estimation.
Picture yourself as a financial controller. At the end of each year, you need to compare the actual cash flow and the forecasted cash flow. Any difference of more than five percent would need to be investigated and reported to the chief financial officer.
This can also easily be done by using the formula to calculate the percentage differences.
- First, we select E6 to insert the formula in.
- Begin your function with an equal sign
=, followed by the name of the function,TO_PERCENT. Don’t forget to add an open parenthesis(.
- We then select the actual amount, which is C6, minus it with the forecasted amount, D6. The difference will be divided by the forecasted amount.
- Once you input all line items with the formula, your sheet will look like this.
The table shows that Revenue 2 and Cost of Goods Sold 1 have both exceeded the five percent difference limit. Both items would need to be investigated and reported to the chief financial officer.
There you go! These are some simple and practical ways to help you calculate and apply percentage difference in different real-life scenarios. 🙌🏻
Don’t forget to check out other cool functions in Google Sheets to enhance and simplify work for your everyday use!
Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
