📎 This article includes 1 downloadable practice file ↓
Two percentage questions come up in every report: “what share of the total is this?” and “how much did it change?”. Both are one-line formulas, but small mistakes — a missing $, dividing by the wrong number, a zero last month — produce wrong or ugly results.
In this article
Percentage of total
=I2 / SUM($I$2:$I$201)Format the column as Percentage (Ctrl+Shift+%). The total is locked with $ so it stays put as you copy down. Faster on big sheets: put the total in one cell (say I203) and divide by $I$203.
Share of a group
=SUMIFS(I:I, B:B, "North") / SUM(I:I) ' North's share of all sales
=I2 / SUMIFS($I$2:$I$201, $B$2:$B$201, B2) ' this order's share of its own regionPercentage change
=(C2 - B2) / B2 ' new minus old, divided by old
=C2 / B2 - 1 ' same thing, shorterSales went from ₹2,00,000 to ₹2,30,000: (2,30,000 − 2,00,000) ÷ 2,00,000 = 15% growth. A fall from 2,30,000 to 2,00,000 is −13.0% — percentage change isn’t symmetrical, because the base differs.
When last month was zero or blank
=IF(B2 = 0, "New", C2 / B2 - 1)
=IFERROR(C2 / B2 - 1, "n/a")Growth from zero is undefined, not “infinite %”. Showing “New” is clearer than a #DIV/0! error or a huge number.
Change in percentage points vs percent
Margin moving from 12% to 15% is a change of 3 percentage points (15% − 12%), or a 25% increase in the margin (15 ÷ 12 − 1). Reports often confuse the two — label which one you mean.
Where people go wrong
| Mistake | Result |
|---|---|
Total not locked (SUM(I2:I201)) |
Every row divides by a shrinking range |
| Dividing by the new value | Understates growth — always divide by the old (base) value |
| Multiplying by 100 and formatting as % | Shows 1500% instead of 15% |
| Averaging percentages | Wrong unless the bases are equal — recompute from totals |
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.