Excel and Google Sheets how-to
Calculate percentage change
Sales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Change |
| 2 | Jan | 400 | 500 | 25% |
| 3 | Feb | 500 | 450 | -10% |
| 4 | Mar | 250 | 300 | 20% |
| 5 | Apr | 320 | 368 | 15% |
| 6 | May | 800 | 600 | -25% |
Sample data. Sales by month, the formula in D2 is copied down and the column is formatted as a percentage.
The formula
Click the first Change cell (D2) and type:
=(C2-B2)/B2It takes the new value minus the old one, then divides by the old one: (500 - 400) / 400 = 0.25. Format the column as a percentage (the % button on the Home tab in Excel, or Format, Number, Percent in Google Sheets) and it shows 25%. Copy it down for the other months.
- C2 is the new value, this year.
- B2 is the old value you compare against, last year.
A decrease comes out negative
February went from 500 to 450, so the same formula gives -10%. A minus sign means a drop; no separate formula is needed for decreases.
The brackets matter
Leave them out and the division happens first:
=C2-B2/B2That is 500 - (400 / 400) = 499, not a percentage at all. A shorter version that needs no brackets gives the same 25%:
=C2/B2-1When the old value is 0
Growth from 0 cannot be worked out as a percentage, so the formula shows #DIV/0!. Show a blank instead:
=IF(B2=0,"",(C2-B2)/B2)For January it still shows 25%; for a month where last year was 0 the cell stays empty.
Add or take off a percentage
To raise 400 by 10%, multiply by 1 plus the percentage:
=B2*(1+10%)That gives 440. To take 10% off, use 1 minus the percentage: =B2*(1-10%) gives 360. You can type 10% straight into the formula.
Compare every month with the first one
Dollar signs lock the cell you compare against, so it stays put when you copy the formula down:
=(C3-$C$2)/$C$2February this year against January this year: (450 - 500) / 500 = -10%. Copied to March it gives -40%. These formulas work in every version of Excel and in Google Sheets.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Sales Tracker and Dashboard 2027Log every sale and see revenue, top products and top customers on a dashboard.
- Monthly Budget TrackerBudget vs actual, bills and savings.
- Investment Portfolio Tracker (Canada)Holdings, adjusted cost base and income across several accounts.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.