Proper Spreadsheets

Templates › Clubs

Volunteer Rota

Made for anyone organising volunteers.

Keep a list of volunteers with their skills and availability, plan dated shifts, see which shifts still need someone and how many shifts each person has done. The summary carries a chart that redraws itself as you type.

Of the 18 columns, you fill in 13 and the other 5 work themselves out, and all 10 figures on the summary do too. There is room for 680 rows of your own.

£4.99one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

Already have a spreadsheet of your own? Check it for mistakes, free.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic. What the film shows, in order:
  1. A new row is typed into the Rota tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the Summary tab change to include the new row.
  4. The chart, Rota hours by role, redraws with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 680 rows. Open any tab to see its columns.

Volunteers

9 columns

One row per volunteer: what they can do, when they are free, and a running count of their shifts. Ready for 80 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
NameType any text.
ContactType any text.
Main SkillPick one: Front desk, Kitchen, Driving, Setup, Admin or First aid.
Other SkillsAnything else they are happy to help with.Type any text.
AvailabilityPick one: Weekday mornings, Weekday afternoons, Evenings, Weekends or Any time.
ActiveTick it when it applies.
Shifts DoneFills itself in.

Worked out from this row's Name, using Volunteer, Confirmed and Date on Rota.

The formulaIF(ISBLANK([name]), "", COUNTIFS([Rota!volunteer], [name], [Rota!confirmed], "Yes", [Rota!date], "<="&TODAY()))
Hours DoneFills itself in.

Worked out from this row's Name, using Hours, Volunteer, Confirmed and Date on Rota.

The formulaIF(ISBLANK([name]), "", SUMIFS([Rota!hours], [Rota!volunteer], [name], [Rota!confirmed], "Yes", [Rota!date], "<="&TODAY()))
Shifts Coming UpFills itself in.

Worked out from this row's Name, using Volunteer and Date on Rota.

The formulaIF(ISBLANK([name]), "", COUNTIFS([Rota!volunteer], [name], [Rota!date], ">"&TODAY()))

Rota

9 columns

One row per shift. Leave Volunteer empty until someone is on it. Ready for 600 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
DateType a date.
ShiftPick one: Morning, Afternoon or Evening.
RolePick one: Front desk, Kitchen, Driving, Setup, Admin or First aid.
HoursType a number.
VolunteerPick one of the Name entries on Volunteers.
ConfirmedChoose Declined if they cannot do it; the shift then shows as needing cover.Pick one: Yes, Not yet or Declined.
StatusFills itself in.

Worked out from this row's Date, Volunteer and Confirmed.

The formulaIF(ISBLANK([date]), "", IF(ISBLANK([volunteer]), "Unfilled", IF([confirmed]="Declined", "Needs cover", IF([confirmed]="Yes", "Confirmed", "Awaiting reply"))))
Days AwayNegative means the shift has passed.Fills itself in.

Worked out from this row's Date.

The formulaIF(ISBLANK([date]), "", [date]-TODAY())
NotesType any text.

Summary

10 figures

The state of the rota at a glance.

FigureWorked out as
Upcoming shifts with nobody on

Worked out from Status and Date on Rota.

The formulaCOUNTIFS([Rota!status], "Unfilled", [Rota!date], ">="&TODAY())
Upcoming shifts needing cover after a decline

Worked out from Status and Date on Rota.

The formulaCOUNTIFS([Rota!status], "Needs cover", [Rota!date], ">="&TODAY())
Upcoming shifts awaiting a reply

Worked out from Status and Date on Rota.

The formulaCOUNTIFS([Rota!status], "Awaiting reply", [Rota!date], ">="&TODAY())
Upcoming shifts confirmed

Worked out from Status and Date on Rota.

The formulaCOUNTIFS([Rota!status], "Confirmed", [Rota!date], ">="&TODAY())
Shifts in the next 7 days still unfilled

Worked out from Status and Date on Rota.

The formulaCOUNTIFS([Rota!status], "Unfilled", [Rota!date], ">="&TODAY(), [Rota!date], "<="&(TODAY()+7)) + COUNTIFS([Rota!status], "Needs cover", [Rota!date], ">="&TODAY(), [Rota!date], "<="&(TODAY()+7))
Active volunteers

Worked out from Active on Volunteers.

The formulaCOUNTIF([Volunteers!active], TRUE)
Shifts done this year

Worked out from Confirmed and Date on Rota.

The formulaCOUNTIFS([Rota!confirmed], "Yes", [Rota!date], ">="&DATE(YEAR(TODAY()),1,1), [Rota!date], "<="&TODAY())
Volunteer hours given this year

Worked out from Hours, Confirmed and Date on Rota.

The formulaSUMIFS([Rota!hours], [Rota!confirmed], "Yes", [Rota!date], ">="&DATE(YEAR(TODAY()),1,1), [Rota!date], "<="&TODAY())
Most shifts done by one personSee the Volunteers tab for each person's count.

The largest Shifts Done on Volunteers.

The formulaMAX([Volunteers!shifts_done])
Active volunteers with no shifts done yet

Worked out from Active and Shifts Done on Volunteers.

The formulaCOUNTIFS([Volunteers!active], TRUE, [Volunteers!shifts_done], 0)