How to Calculate Percent Change in Google Sheets

How to Calculate Percent Change in Google Sheets
How to Calculate Percent Change in Google Sheets – Sheetaki

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.

Example spreadsheet for calculating percent change in Google Sheets

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:

A Real Example of Calculating Percent Change in Google Sheets

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:

Percent Change = (Second Value – First Value) / First Value * 100
Understanding the formula is only the beginning. The next step is figuring out how to implement it in Google Sheets. You’ll learn how to do it in the next section.

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.

Once you have a copy of the spreadsheet, perform the steps below.
  1. Our objective for this activity is to calculate the percent change in product sales between 2021 and 2020.
    Example dataset to calculate percent change
  2. Let’s start by computing for the percent change of the first product. Select cell B5.
    Select cell B5
  3. 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.
    Type in the formula for calculating percent change

  4. After typing the formula, press the Enter key on your keyboard. Your spreadsheet should update accordingly:
    Calculating percent change in Google Sheets

    Great! You just calculated the percent change of the first product’s sales in two years.
  5. 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:
    Use the autofill feature to copy the formula for calculating percent change
  6. 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:

=(B3-B2)/B2*100

You can also use this one, which gives you the same result:

=TO_PERCENT((B3-B2)/B2)

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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like