How to handle zero and blank values when calculating percentage change
Calculate percentage increases and decreases, and check why a blank result differs from no change or a drop to zero.
To calculate how much a value increased or decreased, choose Change (%) and set Current as A and Previous as B. The calculation is (current − previous) ÷ previous × 100. Check blank results separately: a missing value or a zero starting point needs a different interpretation from no change.
A blank and a recorded zero mean different things
The fictional table contains previous and current values for the same items. Blank means an empty cell, not text to type. If those values are in separate files, join by a stable item ID first. Matching row positions is not enough to ensure you are comparing the same item.
| Item | Previous | Current |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 60 |
| C | 0 | 50 |
| D | 100 | Blank |
| E | 100 | 0 |
Set Current as A and Previous as B
- Load and select the sheet. Check that Previous and Current use numeric types.
- Open Refine results and add Calculate.
- Choose Change (%) · (A − B) ÷ B × 100.
- Set First value (A) to Current and Second value (B) to Previous. Name the output Change (%) and select Apply.
Reversing A and B changes the baseline, not just the sign. Moving from 100 to 120 is a 20% increase; moving from 120 to 100 is about a 16.67% decrease.
Check growth, decline and the two blank results
| Item | Change (%) | Interpretation |
|---|---|---|
| A | 20 | Increase from 100 to 120 |
| B | -25 | Decrease from 80 to 60 |
| C | Blank | Previous value is zero |
| D | Blank | Current value is missing |
| E | -100 | Actual decrease from 100 to zero |
The output 20 means 20%; the calculation has already multiplied by 100. Keep the percentage sign in the column name and check the number format after saving. Applying Excel's percentage format directly to 20 would display 2,000%.
How can I review the blank results?
Reopen Refine results, add Filter below the calculation, select Change (%) with Is empty, and choose Apply. This shows C and D for review. C starts from zero, so its percentage change is undefined. D is missing its current value. Neither means 0% change.
For an absolute change, add a separate subtraction of Current minus Previous. Item C can have an absolute increase of 50 even though its percentage change is undefined. Negative baselines or return calculations require an agreed reporting convention before interpreting the percentage.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Discussion about blank cells producing -100% or division-by-zero errors — Reddit r/excel
The five fictional records illustrate the zero and blank-value cases raised in the question.
