Hands-On Excel

Hands-On Excel Challenges Percentages

Excel percentage formula: percent of a total

Divide each amount by the total and lock the total: =B2/$B$7. Then click %. Excel keeps 0.5 in the cell and shows it as 50%.

StarterAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

05 · Percentages · Starter

Percent of a total

What share of the budget does each category take? In C2:C6, divide each amount by the total in B7 and fill down. Then click % to show the shares as percents.

A share is part ÷ total: =B2/B7. Every row divides by the same total, so lock it as $B$7 before you fill down. Don't multiply by 100: the % button does that for you.

budget.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · Percentages

The formula

A share is the part divided by the whole. If rent is 1,200 of a 2,400 budget, =B2/B7 gives 0.5. Format the cell as a percent and it reads 50%.

=part / total

Don't multiply by 100

Excel stores percents as fractions: 50% is 0.5. The % format multiplies by 100 only when it shows the number. So if you write =B2/B7*100 and then click %, the 50 is shown as 5000%. Pick one: keep the fraction and use the % format (best, because other formulas can use it directly), or multiply by 100 and leave the cell as a plain number.

Lock the total before you fill down

Every row divides by the same total, so the reference to it must not move. Fill =B2/B7 down and the next row becomes =B3/B8: B8 is empty, and you get #DIV/0!. Write =B2/$B$7 instead. The absolute reference challenge covers the $ in detail.

Common mistakes

FAQ

How do I calculate a percentage of a total in Excel?
Divide the part by the total, for example =B2/$B$7, then format the cell as a percent with the % button. The $ signs keep the total fixed when you fill the formula down.
Why does my percentage show 5000% instead of 50%?
The formula multiplies by 100 and the cell also uses the % format, which multiplies by 100 again for display. Remove the *100 from the formula.
Why do I get #DIV/0! when I copy a percentage formula down?
The reference to the total moved down with the formula and now points at an empty cell. Lock it with dollar signs, as in $B$7.
How do I calculate percent change in Excel?
Subtract the old value from the new one and divide by the old one: =(C2-B2)/B2, then use the % format.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Percent of a totalPercentages Starter · 3 min
  6. 06 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  7. 07 Pass or fail?IF Core · 3 min
  8. 08 Count the ones that matchCOUNTIF Core · 3 min
  9. 09 Add up with two conditionsSUMIFS Core · 3 min
  10. 10 Look it upVLOOKUP Core · 3 min
  11. 11 Find the right bracketVLOOKUP approximate match Core · 3 min
  12. 12 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  13. 13 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  14. 14 Catch the error, not the bugIFERROR Core · 3 min
  15. 15 Split first and last namesLEFT, MID + FIND Core · 3 min
  16. 16 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy