You make a staff rota in Excel with a table that has your staff in the rows and the days of the week in the columns. In each cell you put the shift. With a few formulas you add up each person’s hours and see whether they’re over or under their contracted hours. Don’t fancy building it yourself? Download our free Excel rota for hospitality or retail.
How to make a staff rota in Excel in 7 steps
1. List your staff
Create a “Staff” sheet with each person’s name, role (for example front of house, kitchen or till) and contracted hours per week. You’ll use this sheet later to check the rota adds up.
2. Create a weekly rota
Add a new sheet for each week and name it after the week number, such as “Week 44”. Put the names in column A and Monday to Sunday in the columns next to it. Group staff by role, so you can quickly see whether each area is covered.
3. Use shift codes
Don’t type out the full times every time, use short codes. Create a “Key” sheet with three columns: code, times and number of hours. For example: E = 09:00–17:00 = 8 hours, L = 17:00–23:00 = 6 hours. In the rota you only type the code.
4. Count the hours per person
You can look up the hours for each shift with VLOOKUP. If the code is in cell B3, this formula returns the number of hours:
=IFERROR(VLOOKUP(B3,Key!$A:$C,3,FALSE),0)
Add up the hours for the week with =SUM() and put the total at the end of each row.
5. Check contracted hours and cover
Put that person’s contracted hours next to the weekly total. Subtract one from the other and you’ll see straight away whether someone is working over or under their hours. With conditional formatting you can turn that number red as soon as someone goes over. At the bottom of each day, use =COUNTA() to count how many people are working. That shows at a glance whether a day is short-staffed.
6. Add availability and holiday
Ask your team to tell you in good time when they can’t work, for example every Thursday for the following week. Shade those days grey before you start planning. Put holiday on a separate sheet, so you don’t forget it the following week.
7. Share the rota
Save the rota as a PDF and share it in the group chat, or pin a printout up in the staff room. Put the week number and date in the file name, so nobody looks at an old version.
Tips for keeping your Excel rota up to date
Work in one file.
Don’t copy a new file every week, add a sheet instead.
Process changes straight away.
Then share the rota again and say what has changed.
Agree how shift swaps work.
For example: swapping is fine, but only once you’ve approved it.
Keep old weeks.
That way you can look back at how much someone worked.
Planning for a specific sector? Read scheduling staff in Excel for hospitality or for retail.
Download a free Excel rota
Don’t want to start from a blank file? We’ve made a free Excel staff rota with the rota and hours count already built in. Download it for hospitality or retail.
When does Excel become too small?
Excel works well for a small team with a fixed rota. It gets tricky once the rota changes every week. You’ll notice it at moments like these:
Someone doesn’t turn up.
Lisa’s shift moved from Friday to Saturday, but she was still looking at the old rota.
You put someone on who can’t work.
Tom said he couldn’t do Saturday, but the note fell behind your desk.
A pile of extra hours catches you out.
Emma stayed an hour longer every busy evening. A few months later she’s 40 hours over.
The payslip is wrong.
You typed an 8 instead of a 6 and paid two hours too much.
Sound familiar? Then you’re spending more time on Excel than on your rota.
What can a staff rota app do that Excel can’t?
With a staff rota app like Tiemdo, your team does part of the work itself:
Availability:
your team enters it in the app themselves, before a deadline you set.
Rota:
as soon as you publish, the rota is on your team’s phones. So there’s always one version.
Drop-outs:
if someone drops out, you open the shift up and colleagues respond themselves.
Hours:
your team clocks in, the hours worked sit next to the rota and you approve them in one go.
Wage costs:
you see what each day’s staffing costs while you’re still planning.
Rota planning costs £1 per active member of staff per month, or £2 with clocking in and time tracking. You can try it free for 30 days, no credit card needed. Curious how a system like this works? Read How does staff rota software work?