Percentage of Total and Percentage Change in Excel (Without #DIV/0! Errors)

Percentage of Total and Percentage Change in Excel (Without #DIV/0! Errors) featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

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
  1. Percentage of total
  2. Share of a group
  3. Percentage change
  4. When last month was zero or blank
  5. Change in percentage points vs percent
  6. Where people go wrong
  7. Practice
  8. Continue learning

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.

Advertisement

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 region

Percentage change

=(C2 - B2) / B2         ' new minus old, divided by old
=C2 / B2 - 1             ' same thing, shorter

Sales 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.

Advertisement

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.

Advertisement

📎 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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong

Leave a Comment