Are you tired of manually calculating percentage changes in your data and looking for a powerful tool that makes the entire process seamless? Well, say no more, as Google has an intuitive and powerful built-in function that makes it easy for users to calculate percentage change in Google Sheets.
This post will give you a deep dive into how you can use the percentage change function in Google Sheets. And guess what? You don’t have to be tech-savvy to figure this out.
Whether you are an investor analyzing stock performance, a data enthusiast, or a business owner tracking sales, today’s guide will provide you with everything there is to know about calculating percentage change in Google Sheets.
Let’s get started, shall we?
Understanding percentage change
Before we show you practical tips on calculating percentage change in Google Sheets, let’s address the elephant in the room: What is percentage change?
Percentage change refers to the difference between two distinct values, divided by the original value and multiplied by 100.
Let’s assume you want to calculate the percentage change between two numbers; you first want to subtract the first number from the second, then divide the result by the first number. To arrive at the percentage change, you then have to multiply the resulting decimal by 100. This will give you the percentage change increase or decrease as a whole number.
Sounds confusing? Here is an example that makes it a bit clearer.
Let’s say a variable increases from 12 to 16; you can calculate the percentage change by simply subtracting 12 from 16 (16-12=4) and then dividing 4 by 12 ((4/12=0.3). To get the percentage change, you then have to multiply 0.3 by 100 (0.3*100=30). To this end, there is a 30 percent increase from 12 to 16.
If you don’t have data to work with, click the above button to open our sample data in Google sheets. Once it is open, go to File > Make a copy to create a duplicate for your own use.
How to calculate percentage change in Google Sheets: Step-by-step guide
Now that you have some understanding of what percentage change is, it’s time to go over the practical guide on how you can calculate percentage change in Google Sheets.
Whether this is your first time trying to do this or something you have done before, rest assured that this guide will simplify the entire process.
More so, Google Sheets has made the entire process seamless, so you won’t have to go through the nerve-wracking process of doing it manually.
For our guide, we will be calculating the percentage change in scores of a group of students in two chemistry exams for the entire semester. Our goal with this is to use percentage change to find out the students that have progressed, those that have regressed as well as how much they have done so.
Here is what our data looks like:
- To calculate the percentage change, we will start off by choosing the cell where we want to show the percentage change. For our example, we will use cell D2.
- Head to the formula bar and type in the following formula:
(This formula subtracts the first value from the second value, then divides it by the first value. It then multiplies the result by 100 to arrive at the percentage change.)
- Press the Enter key on your keyboard. Google Sheets will then automatically calculate the percentage change for students’ Physics scores.
There you have it. From the image above, you can clearly see how each student has either progressed or regressed in their physics score. A negative percentage typically implies that a student has regressed, while a positive percentage shows that a student has progressed.
If you don’t fancy having all the extra decimals, you can tweak them by formatting. And here is how to go about it.
- First, you need to select all the cells containing the percentage changes. In our example, we will select cells D2 to D10.
- On the menu bar, navigate to the Format tab and choose the Number option.
- Scroll all the way to the bottom of the list and select “Custom number format.”
- In the resulting text box, type zero followed by a decimal point and then a few zeros after that. Remember that the number of zeros you type after the decimal determines how many decimals your cell will have. For the purpose of this guide, we decided to go with 0.00, which will give us a percentage change with two decimals.
- Finally, click Apply, and you’re ready for the changes.
The image above gives you a clear picture of how to format percentage changes to two decimals.
If you followed the steps precisely as we detailed above, you’d notice that the percentage change for our sample data has now been formatted to reflect the changes made. Here, take a look at what it looks like after the changes:
Using the TO_PERCENT Function to calculate percentage change in Google sheets
While we have covered the simple way to calculate percentage changes in Google Sheets, we want to show you another way to approach it. Even though this method is a bit complicated, our guide will simplify things further.
Just so you know, the TO-PERCENT function is a brilliant tool you can deploy for calculating percentage changes in Google Sheets. The amazing thing about using this approach over the simple method we covered earlier is that it overcomes some of the weaknesses of the simple approach.
This method helps to automatically convert changes from one cell to the next to the actual percentage. At the end of the day, this method not only makes your task easier but also helps you to understand and interpret the data.
Here, check out the step-by-step guide for calculating percentage change using the TO_PERCENT function.
For this method, we will use the sample data used in the simple method we discussed earlier.
- To calculate percentage change using the TO_PERCENT function, you’ll need to choose the cell where the percentage change appears. Like in our first example, we will use cell D2.
- Now, head to the formula bar and type the following formula:
- Finally, hit the Enter button on your keyboard, and Google Sheets will automatically generate the percentage change for that cell.
- To save you the hassles of repeating the same process for other cells, you can use the auto-fill option, and Google Sheets will automatically generate percentage changes for other cells.
Here is what it should look like:
The illustration above shows how to calculate a percentage change in Google Sheets using the TO_PERCENT formula. It also shows you how you can use the auto-fill option to generate the percentage changes for other cells automatically. Opting for this option will save you from the hassles of repeating the same step for every cell.
The idea of percentage change is very useful, especially if your job or business requires you to keep track of progress and performance. You can keep track of how sales are doing from one month to the next by looking at the percentage change. Not just that, you can also use percentage change to compare the percent gain of two different stock prices over specific periods.
Today’s guide provides you with two ways to calculate percentage change in Google Sheets quickly. Feel free to explore both options and see which is best for you. Also, we showed you how to use the drag fill handle to calculate percentage change for all cells. This option will save you the hassles of calculating percentage changes for each cell.
Have you tried calculating percentage changes using the methods we highlighted in today’s guide? If not, now is a good time to check them and see which works best for you.
You can also check out other comprehensive guides on Google Sheets we have covered. We are sure you’ll find some of our tutorials worth exploring.
Frequently asked questions
What method is the best method to calculate percentage change in Google sheets?
There are several ways to calculate percentage change in Google Sheets, and in today’s guide, we have provided you with two approaches- the simple method, which involves using the =C2-B2)/B2*100 formula and the more advanced method, which involves using the formula =TO_PERCENT((B3-B2)/B2) to calculate percentage change in Google sheets. Feel free to explore any of these methods for your project.
Why is calculating percentage change important?
There are a lot of perks that come with calculating percentage changes. While it makes data analyses easier, it also makes it easy to track progress or performance. Let’s assume you have a business where sales vary monthly, and you want to track changes in sales for every respective month; you can calculate percentage change using Google Sheets to get a clearer picture of how your business has performed in terms of percentage changes in sales across different months.
Who should use percentage changes for data analysis?
If you deal with an insane amount of data or you need to track progress or performance, you need to start using percentage changes. And thankfully, you don’t have to do it manually, as Google Sheets provides you with just the perfect tool to calculate percentage changes quickly and hassle-free.
Other Google Sheets Resources
- How to Calculate Standard Deviation in Google Sheets
- How to Calculate Weighted Average in Google Sheets
- How to Compare Two Columns in Google Sheets (Finding Differences and Matches)
- Google Sheets Conditional Formatting Custom Formula (with Examples)
- How To Strikethrough In Google Sheets (With Examples)
- How To Create Drop-Down List In Google Sheets (With Examples)