You can write a simple formula to calculate percent change in Google Sheets.
It’s essential to be aware of percent changes regarding finances or business transactions. This factor alone could help you determine whether you’re gaining or losing your money or other resources. Furthermore, percent changes can give you insights on how gradual or sudden a change was over time.
Computing for percent changes manually can be very tedious and perplexing. But thanks to Google Sheets, you don’t need to worry anymore. Even though you have to perform it in thousands of numerical datasets, you can easily and quickly do it using a simple formula.
In this guide, we’ll demonstrate how to write a formula that can help you calculate percent change in Google Sheets. Let’s get started!
A Real Example of Calculating Percent Change in Google Sheets
Take a look at the example spreadsheet below. The spreadsheet contains the annual sales of a company over its first two years.
Consider the case where we want to determine the percent change of sales for each product. By writing the correct formula, we can get the following result:
As you can see, Google Sheets was able to compute the percent change in each product’s sales between two years. Let’s look at how it’s done in the succeeding sections.
Formula for Calculating Percent Change in Google Sheets
Before you can calculate percentage changes in Google Sheets, you must understand its formula first. The standard way of computing the percent change between two values is to get the difference of the first and second numbers. Afterward, divide the result by the first number and multiply it by 100.
Here’s the exact formula for computing percent change:
How to Calculate Percent Change in Google Sheets
Let’s do a simple activity using our example earlier for you to learn how to calculate percent change in Google Sheets. First, click the link below to generate a copy of the spreadsheet we’ll use.
- Our objective for this activity is to calculate the percent change in product sales between 2021 and 2020.
- Let’s start by computing for the percent change of the first product. Select cell B5.
- Next, recall the percent change formula we discussed from the previous section. We’ll implement this formula in our example spreadsheet: Percent Change = (Second Value – First Value) / First Value * 100Now, let’s fill in the variables on our formula with the actual values in the dataset. Let the cell reference of 2021 sales record be First Value, and then 2020 be Second Value. To achieve this, type in this formula:
=(B3-B2)/B2*100
Be guided by the image below.
- After typing the formula, press the Enter key on your keyboard. Your spreadsheet should update accordingly:
Great! You just calculated the percent change of the first product’s sales in two years. - This time, let’s use the autofill feature of Google Sheets to compute the percent change of the other products’ two-year sales. Select cell B5, then click the blue box found at the bottom of the cell and drag it across the succeeding cells. If you’ve done it correctly, your spreadsheet should already look like this:
- Using the formula, we calculated the percent change of all the products’ first and second-year sales. Based on the spreadsheet, we can draw the following conclusions:
- There’s a 24-percent increase in the sales of smartphone products
- There’s an 18-percent decrease in the sales of tablets
- There’s a 29-percent increase in the sales of laptops
You see how easy that is? With just a simple formula, we’re able to determine the percent change of two numerical values in Google Sheets.
Using the TO_PERCENT Formula to Calculate the Percent Change
When using the standard percent change formula, you’ll notice that toward the end, you will need to multiply the result by 100 to convert it into its percentage form. There’s actually an alternative way to do this. And that’s with the help of the TO_PERCENT function.
The TO_PERCENT function allows you to instantly convert a numerical value into a percentage. You can also utilize this function when calculating percent changes in your datasets. So, aside from this formula:
You can also use this one, which gives you the same result:
While both formulas yield similar results, the latter is easier to remember and is less likely to lead you to mistakes.
There you have it—the answers on how you can calculate percent change in Google Sheets. With these tricks, you no longer need to bring up your calculator just to manually find out the percent changes in your datasets.
If you want to learn more awesome spreadsheet techniques, then you should check out our other articles about Google Sheets.
You can also subscribe to our newsletter to receive more useful articles like this one.