How to Calculate Percentage Change in Excel (and Google Sheets)
1. What is percentage change?
Percentage change tells you how much a value has increased or decreased compared to a previous value.
[ \text{Percentage Change} = \frac{\text{New Value} - \text{Old Value}}{\text{Old Value}} \times 100 ]
- New Value – the figure you are measuring (e.g., sales this month).
- Old Value – the reference figure (e.g., sales last month).
- A positive result means growth; a negative result means decline.
Quick sanity check
| Old | New | Formula in Excel / Sheets | % Change |
|---|---|---|---|
| 120 | 150 | =(B2-A2)/A2*100 |
25 % |
| 85 | 68 | =(B3-A3)/A3*100 |
–20 % |
Note – If the old value can be 0, protect the division (see Section 5).
2. Simple two‑period calculation
When you only need to compare one earlier value with a later one, the classic formula works:
=(B3 - B2) / ABS(B2) // Excel & Google Sheets
| Month | Sales | Formula (C3) | % Change |
|---|---|---|---|
| Jan | 1,200 | — | — |
| Feb | 1,500 | =(B3-B2)/ABS(B2) |
25.00 % |
Format column C as Percentage to see “25 %”.
Tip: Lock the base cell with $ (e.g., =$B$2) only when you want every row to compare to the same reference.
3. Year‑over‑Year (YoY) growth
YoY growth is the same calculation applied to annual data.
| Year | Revenue |
|---|---|
| 2022 | 125 000 |
| 2023 | 158 000 |
Formula (cell C3):
=(B3 - B2) / B2
Result: 0.264 → format as Percentage → 26.4 %.
Using XLOOKUP for non‑contiguous years (Excel 2021/365 only)
If the prior year isn’t directly above the current row, pull it with XLOOKUP:
=(B2 - XLOOKUP(A2-1, A:A, B:B, 0)) / XLOOKUP(A2-1, A:A, B:B, 0)
A2= current year (e.g., 2023)B2= current revenue- The
XLOOKUPfinds the previous year’s revenue.
Google Sheets does not have XLOOKUP; use INDEX/MATCH or VLOOKUP instead.
4. Calculating a whole column at once
Google Sheets – ARRAYFORMULA
=ARRAYFORMULA(IF(ROW(B2:B)=2, "Change", (B2:B - B1:B) / B1:B))
| Month | Sales | Change (decimal) |
|---|---|---|
| Jan | 1,200 | Change |
| Feb | 1,500 | 0.2500 |
| Mar | 1,350 | -0.1000 |
| Apr | 1,800 | 0.3333 |
Format Change as Percentage → 25 %, –10 %, 33.33 %.
Excel 365 – dynamic arrays
=LET(prev,B1:B, cur,B2:B, IF(SEQUENCE(ROWS(cur),1,1,1)=1,"Change",(cur-prev)/prev))
The result spills automatically and can be formatted as a percentage.
5. Guarding against zero or negative bases
Dividing by zero returns #DIV/0!. Wrap the denominator in a test:
=IF(A2=0, "N/A", (B2-A2)/A2) // Excel & Google Sheets
| Old | New | Formula | Result |
|---|---|---|---|
| 0 | 150 | =IF(A2=0,"N/A",(B2-A2)/A2) |
N/A |
| 20 | 30 | same | 0.50 (50 %) |
You can also use IFERROR for a shorter fallback:
=IFERROR((B2-A2)/A2, "N/A")
Both approaches work in Excel (365/2021 or later) and Google Sheets.
6. Formatting & visual cues
| Task | Excel steps | Google Sheets steps |
|---|---|---|
| Apply % format | Home ► % button or Ctrl+Shift+% |
Format ► Number ► Percent |
| Show two decimals | Home ► Increase/Decrease Decimal | Format ► Number ► Percent ► Decrease decimal places |
| Conditional formatting (green > 0, red < 0) | Home ► Conditional Formatting ► New Rule → “Cell Value > 0” (green) and “< 0” (red) | Format ► Conditional formatting → “Greater than 0” (green) / “Less than 0” (red) |
| Add sparklines | =SPARKLINE(C2:C10, {"charttype","column"}) |
Same syntax works in Sheets |
These visual tools make trends instantly recognizable.
7. Common mistakes to avoid
| Mistake | Why it’s wrong | Correct approach |
|---|---|---|
| Dividing by the new value | Gives the inverse of the real change | Divide by the old value ((New‑Old)/Old) |
| Forgetting the percentage format | Shows a decimal (0.25) that can be misread | Apply the % number format or multiply by 100 |
Using absolute references ($A$2) when copying down |
Every row compares to the same base | Use relative (A2) or mixed ($A2) references as needed |
| Including blanks or zeros in the denominator | Produces #DIV/0! or misleading zeros |
Filter blanks out or wrap the formula in IF/IFERROR |
| Calculating change on cumulative totals | Understates the true period‑to‑period change | Compute change on the individual period values before they are summed |
8. FAQ
What is the formula to calculate percentage change in Excel?
Use =(New‑Value‑Old‑Value)/Old‑Value. Multiply by 100 or format the cell as Percentage to display the result as a percent.
How do I calculate percentage change for a range of values?
In Google Sheets, wrap the row‑by‑row formula in ARRAYFORMULA; in Excel 365, use a dynamic‑array formula (e.g., LET with SEQUENCE). Both spill the results automatically down the column.
What should I do if the base value is zero?
Test the denominator first: =IF(Old=0, "N/A", (New‑Old)/Old). You can also use IFERROR((New‑Old)/Old, "N/A") to return a friendly placeholder instead of #DIV/0!.
Can I use this calculation in Google Sheets?
Yes. The same arithmetic (=(B2‑A2)/A2) works in Google Sheets. For advanced look‑ups, replace Excel‑only functions (XLOOKUP) with INDEX/MATCH or VLOOKUP.
How can I display the result as a percentage with two decimal places?
Select the result cells, then apply the Percentage number format. In Excel, press Ctrl+Shift+% and click Increase Decimal twice; in Google Sheets, choose Format → Number → Percent and then Decrease decimal places twice. The cell will show, for example, 12.34 %.