How to make a rota in Excel: a weekly rota step by step

You do not need software to run a rota for six people. You need a sheet laid out so that nobody has to think about it, one formula that adds the hours, and the habit of sending it out on the same day every week. This is the weekly rota we give away as a template, built from a blank workbook step by step. If you would rather skip the building, the finished file is at the end.

Lay the sheet out before you type a single time

Staff down the side, days across the top. That is the layout people read fastest, because it answers the two questions they have: which row is me, and what am I doing on Thursday. Give each day two columns, In and Out, so that a shift is a start and a finish rather than a code you have to decode. Codes have their place, and they get their own section below, but for a cafe, a shop or a small restaurant where hours vary from day to day, times are what the team actually needs to see.

1. Column A is the name, column B the role. The role column earns its keep the first time you need to see whether anyone can close on Saturday.
2. Columns C to P are Monday to Sunday, two per day. Put the day name in row 4 and merge it over its pair (select C4:D4, then Merge and Centre). Row 5 holds In and Out under each day.
3. Column Q is Hours, one number per person, and the formula for it comes in step 3.
4. Row 1 is the title with the week it covers. Row 2 is one line of instructions for whoever fills it in after you: type 09:00, leave a day blank for a day off.
5. Freeze panes at C6 (View, Freeze Panes) so the names stay put when the sheet scrolls sideways on a laptop.

Six people fit on one screen. Twelve still do. Past twenty, a weekly grid with times is hard to read and it is worth looking at the monthly and code-based templates instead.

Enter times as times

Type 09:00 rather than 9-5 or 9am. Excel stores a time as a fraction of a day, so 09:00 is 0.375 and 17:00 is 0.708, and that is the only reason a formula can subtract one from the other. A cell that says 9am in text looks the same to you and is invisible to the arithmetic.

• Select C6:P11 and set the format to hh:mm (Format Cells, Custom). Then 9:00 shows as 09:00 and a stray 9:00 pm shows as 21:00, which is what you meant.
• A shift that runs past midnight is typed as it happens: In 22:00, Out 02:00. The formula in the next step handles the fact that 02:00 is smaller than 22:00.
• Leave both cells blank for a day off. Do not type Off, a dash or a zero: a blank is the one value the formula and the eye agree on.
• Colour the pair when it holds a shift. In the template the blue fill is static; if you want it to appear on its own, add a conditional formatting rule on C6:P11 with the formula =C6<>"" and a light fill.

One formula for the hours, and what to do about midnight

The hours for one day are the finish minus the start, times 24. That works until somebody finishes at 02:00 after starting at 22:00, when the subtraction gives minus twenty hours. The fix is to add a day whenever the finish is earlier than the start, and to ignore any pair where one of the two cells is empty:

=IF(AND(ISNUMBER(C6),ISNUMBER(D6)),(D6-C6+IF(D6<C6,1,0))*24,0)

Read it from the inside out. D6-C6 is the shift as a fraction of a day. IF(D6<C6,1,0) adds a whole day when the finish is before the start, so 22:00 to 02:00 becomes 0.167 of a day rather than minus 0.833. *24 turns the fraction into hours. ISNUMBER on both cells stops a half-filled pair from inventing fifteen hours out of one blank.

Q6 holds seven of those added together, one per day, with the cell references moving two columns each time: C6 and D6, then E6 and F6, and so on to O6 and P6. Type the first term, copy it six times with the letters changed, and check it against a week you have already counted by hand. Then drag Q6 down to Q11, and put =SUM(Q6:Q11) in the Total row underneath.

Two honest limits. The formula counts the time between In and Out, so an unpaid break is either subtracted in the formula (-0.5 at the end of each term for a half-hour) or, more readably, given its own column. And it counts hours rather than money: overtime rates, night premiums and bank holidays are exactly what a spreadsheet is bad at, and you will be adding them up by hand at the end of the month.

Check the week before it goes out

A rota that adds up is not yet a rota that works. Three checks take a minute each and catch most of what comes back to you on Monday.

• Heads per day. Under the table, put =COUNTA(C6:C11) in Monday's In column and copy it across every second column. It counts the people starting each day. If your busy hour is midday, =COUNTIFS(C6:C11,"<="&TIME(12,0,0),D6:D11,">"&TIME(12,0,0)) counts who is actually on at twelve.
• Hours per person. Conditional formatting on Q6:Q11: greater than 40 in red, less than a part-timer's contracted hours in amber. The sheet will not tell you why, but it will stop you sending a 52-hour week to a student.
• Rest between shifts. A 22:00 finish followed by a 07:00 start is nine hours of rest, and nothing in the grid will flag it. Read the rows once, left to right, for anyone who closes and then opens.

Codes instead of times for fixed patterns

If everyone works the same twelve-hour shift, typing 07:00 and 19:00 four hundred times a month is a waste of a Sunday evening. The other layout uses one letter per person per day, D for a day shift, N for a night, O for off and H for holiday, with a small shift table under the grid that says what each letter means: start, finish and hours. A person's hours are then a COUNTIF per code multiplied by that code's hours, and coverage rows under the grid count how many D and how many N are on each day, turning red below the number you need. Change the start time in the shift table once and every hours cell in the sheet follows.

That is the layout of the monthly, 4 on 4 off and night shift templates, and the one to use for care homes, security and anywhere that runs a fixed shift pattern.

Print it, send it, and keep one copy true

1. Name the file by the week: Rota w/c 28 Sep.xlsx. Next week is a copy of the sheet (right-click the tab, Move or Copy, Create a copy) with the times cleared and the names kept, or last week edited if it mostly repeats.
2. Fit it to one page: Page Layout, landscape, Fit to 1 page wide. A rota that spills onto a second sheet is a rota with Sunday missing on the wall.
3. Send a PDF, keep the file. The PDF in the group chat and the printout on the wall are copies; the workbook is the one you change. When something changes after it has gone out, reissue the whole PDF with a version in the name rather than describing the change in a message.
4. Same day, same time, every week. The team plans their life around when the rota appears. A rota that comes out on Thursday one week and Sunday the next is what the team will remember about it, and it has nothing to do with the spreadsheet.

Download the finished template

The sheet described above, with the six-person sample week filled in and the formula in place, is the weekly staff rota template on our templates page. The same page has a two-week version with a fortnight summary, the monthly code grid, a 4 on 4 off rota and a timesheet. The files open in Excel, Google Sheets and LibreOffice; every formula is written to work in all three, and there are no macros.

When the spreadsheet stops being enough

For one site and a handful of people, the sheet above is a complete answer. It stops being one when the rota exists as a photo in a group chat, when swaps are agreed by text and land in your head, and when the month's hours are added up on the last evening of the month. The six signs are in a separate article. Two things to try before you decide anything: the rota maker builds a week in the browser and downloads it as an Excel file with no sign-up, and InShift keeps the published rota on everyone's phone, takes swaps as requests, counts the hours as shifts are published, and still gives you the spreadsheet: “Print” in the “Planner” has “Download Excel” for whoever wants the file. See what it does.