Steeple Sheets / Free guides

How to make a church rota in Google Sheets, step by step

You can build a useful church rota in Google Sheets in under an hour. Here is how, with the formulas to copy.

1. Set up three tabs

• Volunteers: names in column A, starting in A2. Phone numbers in column C if you want them.
• Rota: dates down column A, jobs across row 1.
• Notes (optional): swap rules and contacts.

2. Add the dates

On the Rota tab, type your first Sunday in A2. In A3 type =A2+7 and fill down for as many weeks as you need (52 rows gives you a year). Put job names in B1 to G1, for example Welcome, Reader, Prayers, Sound, Refreshments, Coffee.

3. Add drop-down names

Select B2:G53. Open Data, Data validation, Add rule. Choose Dropdown (from a range) and enter =Volunteers!$A$2:$A$60. Now each cell has a list of your volunteers, and typos are gone.

4. Highlight double-bookings

With B2:G53 still selected, open Format, Conditional formatting. Under Format cells if, choose Custom formula is and enter:

=AND(B2<>"",COUNTIF($B2:$G2,B2)>1)

Pick an amber fill. Any name that appears twice on the same Sunday now lights up.

5. Count how often each person serves

On the Volunteers tab, in B2, enter =COUNTIF(Rota!$B$2:$G$53,A2) and fill down. Sort by this column now and then to spot anyone doing too much.

6. Show gaps

On the Rota tab, in H2, enter =COUNTBLANK(B2:G2) and fill down. Any number above zero is a job still to fill.

7. Share it safely

• Give edit access to one or two people only. Everyone else gets view access.
• Freeze the top row and first column (View, Freeze) so headings stay visible on phones.
• To print, use File, Print and choose Fit to width.

Using Excel instead?

The same formulas work. Drop-downs are under Data, Data Validation, List, and the double-booking rule goes in Conditional Formatting, New Rule, Use a formula.