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.
Calculate a Percentage Increase or Decrease
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:
=(C2-B2)/B2
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:
=(C3-B3)/B3
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.
Automatically Format the Percentage
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.
=TO_PERCENT((C2-B2)/B2)
Remember to place the entire original formula in parentheses when using the TO_PERCENT function.
Manually Format the Percentage
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.
Alternative Formula for a Percentage Change
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:
=(C2-B2)/B2*100
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.
=(C3-B3)/B3*100
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.