When you want to know how much your sales went up or your bills went down, you can calculate a percentage difference in Google Sheets.
With a simple formula, you can see the percentage increase or decrease between your values. You can also format the result as a percentage or whole number, automatically or manually.
To calculate the difference between two values as a percentage, you’ll use a basic formula. The syntax is
(new_value - original_value)/original_value or the new value minus the original value with the result divided by the original value.
For an example, we want to find the percentage difference in our sales from January to February. We can see that some sales went up while others went down. You would use this formula:
The result is -0.25 or negative 25 percent. The formula accounts for decreases by placing a negative symbol in front of the result.
For a positive result, we’ll use our next product and the same formula with updated cell references:
This time our result is 0.08 or 8 percent.
If you prefer to display your results as percentages rather than decimals, you can do this automatically with a slight change to the formula or manually using the tools in Google Sheets.
Google Sheets offers a handy function for converting numbers to percentages. The function is TO_PERCENT and the syntax is
TO_PERCENT(value) where the argument can be a number or cell reference.
By adding the function to the beginning of the percentage formula which becomes the value, you’ll see your result automatically formatted for you.
Remember to place the entire original formula in parentheses when using the TO_PERCENT function.
If you’ve already entered the percentage formulas into your sheet and don’t want to change them, you can simply format the results as percentages.
Select the cell(s) containing the results and do one of the following:
- Click the Format as Percent button in the toolbar.
- Choose “Percent” in the More Formats drop-down list in the toolbar.
- Select Format > Number > Percent in the menu.
There is another formula you can use that’s similar to the first. This one multiplies the result by 100, thus providing you with a formatted decimal number. You can use this if you don’t necessarily need to display the percent symbol.
Using our same examples as above, you would use this formula:
Now rather than showing our result as -0.25, it displays as -25.00 or negative 25 percent.
The same works for our second example giving us a positive result of 8.00 or 8 percent.
Calculating the percentage difference between your values can give you a quick and clear picture of how numbers change. For more, take a look at how to make negative numbers red in Google Sheets.
- › How to Convert Dollars, Text, Percent, and Dates in Google Sheets
- › 4 Ways to Ruin Your Smartphone’s Battery
- › This Is How Steve Jobs Killed Adobe Flash
- › What Do “FR” and “FRFR” Mean?
- › How to Make Your Facebook Account More Private
- › 10 Things Blocking Your Wi-Fi Signal at Home
- › The Origins of Ctrl+C, Ctrl+V, Ctrl+X, and Ctrl+Z Explained