When dealing with spreadsheets on Google Sheets, you may have to compare two values and find the proportion at which the new value differs from the older one. This is important for situations like knowing how good your company’s sales are, if your expenditure has gone up/down, how much better your students have performed, and more.
If you’re looking for ways to find proportional differences between two values, Google Sheets has an easy way to determine percentage changes and that’s what we’ll explain in this post.
Google Sheet percentage change formula: What is it?
The formula to determine the change in percentage terms in Google Sheets is to_percent(). With the to_percent() formula, you provide values in the brackets in various manners, including simple ways like direct numerical values or cell references. Let’s explore them all and see how to use the to_percent() formula easily.
Google Sheet percentage change formula: How to use it
For calculating percentage changes, you only need to use one function inside Google Sheet: to_percent(). Inside the brackets “()“, you’ll need to provide the values and the mathematical formula to get the desired change in percentage form. Since you’re comparing two values to find their proportional difference, the to_percent() function will have this formula inside the brackets – (Value 2 – Value 1) / Value 1.
- To explain the to_percent() function, we’ve created a sheet to compare the marks of students between Exam 1 and Exam 2. In this sheet, you can use the to_percent() to check whether their performance has improved over the two exams.
- If you have created a sheet like this, click on cell D2 to calculate the percentage difference in marks for student A in exams 1 and 2.
- In this cell, type =to_percent((C2-B2)/B2), since cell C2 hosts Student A’s marks from Exam 2 and B2 hosts from Exam 1. After typing this function, press the Enter key on your keyboard.
- You will now see the percentage change between C2 and B2 inside cell D2.
- In most cases, you’ll see a Suggested autofill that shows similar results for the rows in the spreadsheet.
- If not, you can duplicate the function to work with values listed in other rows (in this case, rows 3, 4, and 5). To do that, click on the blue square dot at the bottom right corner of cell C2 and drag it downwards till C5.
- When you do that, the cells under column C will show the percentage changes between the two values in their respective rows.
- Depending on the two values you provided, the percentage change may include negative values and decimals. The negative values denote a decrease from Value 1 to Value 2, which is indicated by a – sign.
- As for decimals, you can choose to keep them, remove them, or edit the number of values that are shown after the decimal point by formatting the cells accordingly. In the example below, the percentage changes in Column C are all assigned in decimal numbers.
- To change their formatting, select all the concerned cells in Column C first, click on the Format tab from the top toolbar, and go to Number > Custom number format.
- You’ll now see the Custom number formats box on your screen. Since the cells currently have values of 0.00%, you can remove the decimals by entering 0% in the text box at the top. If you want more values after the decimal point, add more zeroes after the decimal value in 0.00% to get your desired results. Once you add a preferred format, click on Apply.
- You’ll now see the new values inside Column C in your spreadsheet.
That’s all you need to know about calculating percentage changes in Google Sheets.
