formulastreak

Percentage change, percent of total and running totals

Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release

Short answer

Percentage change is =new/old-1 (format as %), and it's not meaningful when the old value is 0 or negative. Percent of total is =C2/$C$12, with the total cell locked. A running total is =SUM($C$2:C2) filled down: the start stays put, the end moves.

Growth, shares and running totals are three of the most common things a report shows. Each is a one-line formula once you know the pattern.

Percentage change

Change is new ÷ old − 1: =C2/B2-1. Format the cell as a percentage. (C2-B2)/B2 is the same thing.

Revenue by month · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 9.2%
ABCD
1Month20252026vs 2025
2Jan$18,400$20,1009.2%
3Feb$17,200$19,85015.4%
4Mar$21,000$23,40011.4%
5Apr$19,800$22,90015.7%
6May$22,500$21,700-3.6%
7Jun$24,100$26,80011.2%

Round the value, not just the look

A 0% format shows 0.0756 as 8% but still calculates with 0.0756. To change the value, round it: ROUND(3.14159, 2) is 3.14, ROUNDUP(2.01, 0) is 3 and ROUNDDOWN(2.99, 0) is 2. Functions nest, inside first: ROUND(AVERAGE(F2:F13), 1) averages, then rounds to one decimal.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = 2.4
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusAvg qty2.4
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

Percent of total

Divide each value by the total, and lock the total with $ so it doesn't slide when you fill down.

March household budget · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = 46.5%
ABCDE
1CategoryTypeBudgetActualShare
2RentFixed$1,200.00$1,200.0046.5%
3GroceriesVariable$450.00$512.4017.4%
4UtilitiesFixed$160.00$148.756.2%
5TransportVariable$120.00$96.504.6%
6Phone & internetFixed$65.00$65.002.5%
7Eating outVariable$150.00$203.105.8%
8SubscriptionsFixed$38.00$44.971.5%
9GymFixed$40.00$40.001.5%
10GiftsVariable$60.00$25.002.3%
11SavingsFixed$300.00$300.0011.6%
12Total$2,583.00

Running total

An expanding range adds everything from the first row to the current one: =SUM($C$2:C2). Only the start is locked.

Revenue by month · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = $20,100
ABCD
1Month202520262026 to date
2Jan$18,400$20,100$20,100
3Feb$17,200$19,850$39,950
4Mar$21,000$23,400$63,350
5Apr$19,800$22,900$86,250
6May$22,500$21,700$107,950
7Jun$24,100$26,800$134,750

Check yourself

One real exercise from the “Ranges and functions” unit. Checked on our server; no account needed.

Show how far each category was over or under budget in E2: Actual (column D) minus Budget (column C). Positive means overspent, negative means under budget. Fill down to E11.

Select E2 and type = to start its formula (in the cell or the formula bar)
ABCDE
1CategoryTypeBudgetActualOver/under
2RentFixed$1,200.00$1,200.00
3GroceriesVariable$450.00$512.40
4UtilitiesFixed$160.00$148.75
5TransportVariable$120.00$96.50
6Phone & internetFixed$65.00$65.00
7Eating outVariable$150.00$203.10
8SubscriptionsFixed$38.00$44.97
9GymFixed$40.00$40.00
10GiftsVariable$60.00$25.00
11SavingsFixed$300.00$300.00

Common mistakes

  • Dividing by the new value instead of the old one.

  • Forgetting the $ on the total, so every row below divides by an empty cell (#DIV/0!).

  • Calling a change from 2% to 3% “1% growth”: it's 1 percentage point, or 50% growth.

Questions

How do I show a decrease as negative?+

new/old−1 is already negative for a decrease. Use a format like 0.0%;-0.0% or conditional formatting to colour it.

What if the old value is 0 or negative?+

Then a percentage change isn't meaningful: dividing by 0 gives #DIV/0!, and growth from −400 to +300 would show as −175%. Finance reports show "n/m" (not meaningful): =IF(B2<=0,"n/m",C2/B2-1).

Reading is step one. Now make it stick.

Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.

Practise it now