Topic 3 of 15, EHL Maths & Excel final (AP)
%
Share % of grand total
When the exam asks for each row's contribution to the total, divide the row by the grand total, and lock the grand total with $ before you fill down.
When the exam asks for it
- “% of total”, “share”, “contribution”, “proportion of the grand total”
Formulas
Share of total
=individual total / $grand total$Example in C2, then fill down
=B2/$B$14Lock column and row
$B$14 (Cmd+T on Mac, F4 or Fn+F4 on Windows)Worked example
C2fx
=B2/$B$14| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Room nights | % | ||
| 2 | January | 300 | 6.0% | =B2/$B$14 | |
| 3 | February | 280 | 5.6% | =B3/$B$14 | |
| 4 | March | 320 | 6.4% | =B4/$B$14 | |
| 5 | April | 380 | 7.6% | =B5/$B$14 | |
| 6 | May | 420 | 8.4% | =B6/$B$14 | |
| 7 | June | 450 | 9.0% | =B7/$B$14 | |
| 8 | July | 560 | 11.2% | =B8/$B$14 | |
| 9 | August | 600 | 12.0% | =B9/$B$14 | |
| 10 | September | 480 | 9.6% | =B10/$B$14 | |
| 11 | October | 400 | 8.0% | =B11/$B$14 | |
| 12 | November | 330 | 6.6% | =B12/$B$14 | |
| 13 | December | 480 | 9.6% | =B13/$B$14 | |
| 14 | Total | =SUM(B2:B13) → 5,000 | 100% | ||
| 15 | |||||
| 16 | Percentage = Individual Total / $Grand Total$ | ||||
| 17 |
Sheet1
⬇ Download practice ExcelExample, Practice and Answers sheets with working formulas. Free.
Step by step
=SUM(B2:B13)grand total in B14: 5,000 room nights=B2/$B$14in C2: 300 / 5,000 = 6.0%- Fill down to C13B2 changes to B3, B4 … but $B$14 stays; the shares add up to 100%
- Format column C as Percentage0.06 is shown as 6.0%
⚠ Exam traps
- Without $, filling down turns B2/B14 into B3/B15. B15 is empty, so you get #DIV/0!.
- Lock only the grand total, not the row value: B2 must change, $B$14 must stay.
- Check: all shares together must give 100% (small differences come from rounding).
Practice question
Rooms 450, F&B 150, Other 150. What is the share of Rooms?
Show answer
✓ 450 / 750 = 60%.
Stuck on this topic before your exam? We explain it one-to-one on your own Excel files, live online. Short crash courses before the EHL Maths & Excel final are possible on weekdays and weekends.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.