Shiftto

Making a Shift Table in a Spreadsheet: Where It Works and Where It Stops

Published: October 12, 2026 / Last updated: October 13, 2026

Most people start a shift table in a spreadsheet, and for a small group that is enough. Here are some tips for building it and the point where it gets out of hand.

Two sheets

Use one sheet for availability input and one for the assignment table. Putting both in one sheet means respondents and the organizer edit the same cells, and one of them tends to wipe out the other's input.

  • Availability sheet: people in rows, time slots in columns, with ○△× entered
  • Assignment sheet: time slots in rows, roles in columns, with the names of assigned people

Protect ranges on the availability sheet so each person can edit only their own row.

Building the availability sheet

Put the time slots in the first row (the start times, such as 10:00 and 11:00) and the names in column A. Use dropdowns so a cell can only be ○, △ or ×. If people can type freely, you get “◯”, “o” and “OK” in different forms and the counting formulas break later.

Don't leave unanswered cells blank; set the default to “not yet” so you can see at a glance who has finished. Fill the name column with the list of participants in advance and have each person fill in their own row.

Finding short slots with formulas

Next to the assignment sheet, add a column that counts people per slot with COUNTA and compare it with the required headcount. Conditional formatting that turns short slots red lets you see shortages without scanning the table.

You can also use COUNTIF per person to check that nobody is in two roles at the same time and that nobody has too many shifts. Building these check columns is the most work at the start.

What to checkExample function
People per slotCOUNTA(range of cells holding assigned names)
Slots below the required headcountConditional formatting: red if count < required
Shifts per personCOUNTIF(whole assignment range, name)
Nobody placed in a × slotReference the availability sheet and warn on ×
Nobody in two roles at onceCOUNTIF per slot to count duplicate names

Accidents with shared editing

If everyone has edit access, someone will overwrite another person's row or delete a cell containing a formula. You can make the sheet view-only and collect input through a form, but then copying form answers into the table becomes the new chore.

Everyone being able to see others' answers is another concern. Some people find it hard to write that they can't come.

You can roll back from the edit history, but working out who changed what and when falls to the organizer. After the deadline, switch the availability sheet to view-only so answers don't change in the middle of assigning.

Where it stops working

As a rough guide, around 10 people, 20 slots and one or two roles rarely causes trouble. Past 20 people, three or more roles, or events spanning two or more days, trial and error on assignments gets hard to do in your head.

That is because every time one person's answer changes, you have to recheck the headcount, the number of shifts per person and consecutive work all over again.

The other boundary is how you assign. Spreadsheet functions tell you that something is short, but they don't work out who to place where to fix it. A person has to hunt for that by eye, which takes longer as the group grows.

Making the table resilient to change

Shifts change even after they are decided. Rather than typing names directly, use a dropdown that picks from the participant list, which prevents counting errors from spelling variations. Color changed cells or leave a date in a change-log column so you can check when someone says they were never told.

When printing it out to hand around

On the day, paper is sometimes easier to read than a smartphone. For printing, it helps to prepare a per-person table and a per-time table separately. The per-person table lets each person find their own duties, and the per-time table lets headquarters find gaps. The tricks are to keep to a column count that fits on one page and to write role names instead of relying on color alone, so it reads in black and white.

Combining it with Shiftto

You can leave only the collecting and assigning to Shiftto. Results export to CSV, so you can load them into a spreadsheet, shape them into your usual table and hand them out. There is also a print page. No sign-up, and it is free.

The split is: let it tally the answers and assign, and keep your existing table for the final layout and distribution. You don't need to switch everything at once.

Related articles

See all articles