How to make a staff rota in Excel
Building a rota in Excel takes about ten minutes once you know the layout. Most of the frustration comes from two decisions made badly at the start — what goes in the cells, and what the codes mean.
Summarise with AI
Key takeaways
- Put names down the left and Monday to Sunday across the top, then freeze the panes so names stay visible as you scroll
- Decide once whether cells hold shift times or hours as a number — mixing the two is what breaks the totals
- =SUM() across each row gives weekly hours per person; a total row underneath gives cover per day
- Agree a shift-code legend (OFF, AL, BH, SICK, TOIL) or people will invent their own
- Excel will not check the 48-hour average or the 11-hour rest rule — those stay your job
Rather not build it?
Our free UK rota templates already have the grid, the formulas, the shift codes and the print setup — weekly, monthly, rolling and blank.
Get the free templates1. Set up the grid
Names down the left, days across the top. Specifically:
- Column A — employee name
- Column B — role, so you can see skill cover at a glance
- Columns C to I — Monday to Sunday
- Column J — total hours
- Column K — notes
Start the week on Monday. It is how UK rotas are normally written, and it lines up with how weekly rest is counted rather than cutting a week in half.
Then freeze the panes — click cell C2 and use View → Freeze Panes. Without it, scrolling to Sunday loses the names and you end up counting rows with a finger on the screen.
2. Decide: times or hours?
This is the decision that causes most of the trouble later, and it is worth two minutes now.
| Approach | Cell contains | Trade-off |
|---|---|---|
| Shift times | 09:00–17:00 | Clearest for staff. Totals will not calculate — it is text. |
| Hours | 8 | Totals work. Nobody knows when to turn up. |
| Both | Time visible, hours in a hidden column | Best of both, and twice the typing. |
What does not work is mixing them in one column. =SUM() ignores text, so a column of "9-5" entries totals zero — and a zero that looks like a calculation is worse than no total
at all.
3. Add the totals
In the Total column, on the first staff row:
=SUM(C2:I2)
Copy it down every staff row. Then add a total row underneath the grid with =SUM(C2:C20) under each day column.
The row totals tell you what each person is working. The column totals are the more useful half — they show cover per day, which is where you spot that Saturday has three people and Tuesday has one.
4. Agree the shift codes
Put the legend on the sheet. If you do not, people invent their own, and six weeks later nobody can tell whether "H" meant holiday or a half day. A workable set:
- OFF — day off
- AL — annual leave (counts towards the 5.6 weeks)
- BH — bank holiday
- SICK — sickness absence
- TOIL — time off in lieu
Coding leave rather than leaving a blank turns the rota into an absence record as well as a plan — which matters more since statutory sick pay became payable from day one in April 2026.
5. Make it print properly
Landscape orientation, then Page Layout → Scale to Fit → Width: 1 page. Leave Height on Automatic. Without this, Sunday lands on a second sheet, which is the most common complaint about a printed rota and the easiest to fix.
What Excel will not do for you
A spreadsheet is a good planning tool and a poor compliance tool. Three things it cannot check:
The 48-hour average
Working time is capped at a 48-hour average over 17 weeks. Nothing in the grid warns you when someone drifts over it.
The 11-hour rest rule
An 11pm finish and a 7am start is a breach. Excel will let you type it, and a swap agreed by text will never reach the file at all.
What was actually worked
The rota records the plan. Late finishes and covered shifts are invisible — and minimum wage turns on hours worked.
None of that means the spreadsheet is wrong for you. It means the checks stay a person's job — our rota laws guide sets out what to check, and the minimum wage checker shows what unpaid time at the edges of a shift does to someone's effective rate.
If your cover repeats on a fixed cycle rather than changing each week, a rolling rota template saves rebuilding it — and our shift patterns guide explains how 4-on-4-off and continental cycles cover the week. A spreadsheet also cannot prove what was actually worked, which is what working time and minimum wage records require — and it has no way to tell anyone a shift changed, covered in changing a rota.
Or skip the spreadsheet entirely
Build the rota, catch a rest-break breach before you publish, and let staff swap shifts without anyone rebuilding the grid.
This guide explains how to build a rota in Excel and summarises the UK working time rules a spreadsheet cannot check for you. It is not legal advice — check GOV.UK or take advice before relying on it for a specific case. It does not constitute legal, HR, or professional advice and should not be relied on as a substitute for advice specific to your business, workforce, or circumstances.
Excel rota questions
- Put employee names in column A and roles in column B, then Monday to Sunday across columns C to I. Enter either shift times or hours in each cell, add
=SUM(C2:I2)in a Total column and copy it down, then freeze the top row and first two columns so names and days stay visible. - Pick one and stay with it. Times ("09:00–17:00") are clearer for staff reading the rota; hours as a plain number let the totals calculate. If you want both, put the time in the cell and the hours in a hidden column — mixing them in the same column means
=SUM()silently ignores the text entries. - Almost always because the cells contain text rather than numbers. "9-5" and "09:00–17:00" are text, so
=SUM()counts them as nothing. Either enter hours as numbers, or accept that the total column will not work and remove it rather than leave a misleading zero. - Set the orientation to landscape, then Page Layout → Scale to Fit → Width: 1 page. Without that, Sunday usually ends up on a second sheet, which is the single most common complaint about printed rotas.