Shift Schedule Template in Excel: 3 Grids You Can Copy

Monday, 08:40. You open last week’s file, wipe the names out of it and start over, and by row 14 the same waiter is closing on Friday and opening on Saturday while the total at the bottom reads 41.5 hours against a 38-hour contract. What follows is three Excel grids, filled in rather than described, plus the four formulas that catch that kind of mistake before your team does.

What does a shift schedule template in Excel actually need?

Four things, and none of them is formatting.

• One row per person, with their contract hours in a column of their own
• One column per day, seven of them, and no merged cells anywhere
• A single-letter shift code in every cell, days off included
• A small lookup table listing each code and the paid hours it stands for

That lookup table is the part most people skip, and skipping it is why so many downloaded templates end up abandoned by week three. Type 09:00-17:00 straight into a cell and Excel reads it as text. Nothing adds up. Every total then has to be counted by hand, which defeats the point of opening a spreadsheet in the first place.

Codes fix it. Here is the set used in the first grid below, sized for a kitchen and floor running two services a day. If you are starting from an empty workbook rather than an inherited one, the step-by-step Excel schedule guide covers the build itself.

Drop that table into cells B20 to E25, codes in column B and paid hours in column E. One formula then does all the counting, assuming the seven day columns run C to I and the first person sits in row 4:

=SUMPRODUCT(COUNTIF(C4:I4,$B$20:$B$25),$E$20:$E$25)

COUNTIF returns how many times each code turns up in that person’s week. SUMPRODUCT multiplies every count by its paid hours and adds the results together. Drag it down and each row totals itself, permanently. Change a single cell from E to S and the number moves the same second, which is the whole reason to bother with codes instead of typing times.

Template 1: the weekly grid for a small team

The grid below belongs to a 40-cover bistro in Ixelles, eight on payroll, closed Mondays. Contract hours sit in the last column on purpose, right next to the calculated total, so the gap is impossible to miss.

Four rows are over contract: Lotte by half an hour, Sofia by two and a half, Mathis by two, Théo by three and a half. Nobody built that on purpose. It accumulates one favour at a time, and it only becomes visible when the contract column sits next to the total column. Add up the Hours column and the week costs 270.5 paid hours of labor.

Read it down instead of across and the coverage picture appears. Friday evening has Nadia, Karim, Lotte, Inès, Théo, Sofia and Mathis all on a shift that touches dinner service: seven people for 40 covers, which is generous. Wednesday dinner has four. That imbalance is the useful output of a template, more useful than the totals in many cases, and it is invisible in any version of the file where days are merged across columns for prettiness.

Two habits keep this grid alive past the first month. Never delete a person’s row when they leave, archive it on a second sheet, because payroll will ask about February in June. And keep one file per week rather than one tab per week, named with the ISO week number so the sort order stays honest.

Template 2: the 24/7 three-shift week

Continuous cover is a different problem. You are no longer filling people’s weeks, you are filling the operation’s hours, and the grid has to start from the demand side. A care home in Ghent we work with runs 21 carers across three shifts, seven days, and the sheet begins with this block rather than with names.

Run the arithmetic before you write a single name, because it decides whether the roster is even possible. Mornings need 40 shifts a week, afternoons 33, nights 21: 94 shifts in total. At 8 paid hours for M and A and 8.5 for N, that is 320 plus 264 plus 178.5, so 762.5 hours of cover every week. Divide by the 38-hour Belgian average and you need 20.07 full-time equivalents. Twenty-one carers covers it with almost no slack, which is exactly why one flu week in Ghent turns into a scramble.

Underneath the requirement block go the 21 name rows, same structure as template 1. Then, at the bottom of each day column, a check row that compares what you rostered against what you need:

=COUNTIF(C4:C24,"M")

Repeat it for A and N, put the requirement numbers in the three rows directly below, and wrap the pair in a comparison so the sheet tells you rather than making you count:

=IF(COUNTIF(C4:C24,"M")=C31,"ok","check")

Conditional formatting on that cell, red when the word is check, is the single highest-value thing you can add to a spreadsheet roster. At Shyfter, the files we get sent for review almost never have it, and roughly half of them are short-staffed on at least one shift in the week the manager considered finished.

Rotation patterns are a separate question from the template itself. If you need the day-by-day sequences for four teams, the rotating shift schedule templates cover Panama, DuPont and 4-on-4-off with the headcount maths worked out.

How do you turn the grid into hours and labor cost?

Hours are half the answer. The bistro’s 270.5 hours mean nothing until they carry a rate, so the third template is a small sheet sitting beside the grid, pulling each total across and multiplying it by a loaded hourly cost.

The rates above are illustrative, loaded figures rather than gross pay, so swap in your own. The student line is genuinely lower though, and not by accident: student contracts in Belgium carry a reduced solidarity contribution instead of full social security contributions, within a contingent of 650 hours a year since 2025. Go past that contingent and the cheap line becomes an ordinary one, mid-quarter, with no warning from the spreadsheet.

Now the number that matters. Take the week’s revenue, put it in its own cell, and divide:

=SUM(E4:E11)/B14

At 21,000 EUR of revenue that week, 6,472.50 of labor is 30.8 percent, sitting at the bottom of the range we walk through in the guide to labor cost percentage. Take the identical grid into a rainy week at 18,000 EUR and the same roster is 36.0 percent. Nothing about the schedule changed. That is the structural weakness of planning in a spreadsheet: the cost is fixed on Monday and the revenue only shows up on Sunday.

Two rules to run past the totals before the file goes out. Théo’s 41.5 hours is a compliance question rather than a budget one, and the answer moves with the country. In Belgium it eats into the reference period, and into the compensatory rest that comes with it. In the United States anything past 40 hours in a workweek is paid at one and a half times the regular rate under the Fair Labor Standards Act. Sofia is the row that bites hardest though: 22.5 hours against a 20-hour student contract, and part-time overruns are the first thing a social inspector opens.

Where does an Excel shift schedule template stop being enough?

For one site and one manager, it holds up well. It falls over at the second site.

A supermarket franchise in Antwerp running three stores had three files, three managers and one shared pool of weekend staff. Nobody was doing anything wrong. Somebody was still rostered in two stores on the same Saturday afternoon roughly twice a month, and the only way anyone found out was a phone call at 14:10. No spreadsheet catches that, because no spreadsheet knows the other spreadsheet exists.

The other break is quieter. A spreadsheet records what you planned; it has no idea what happened. Nobody clocked in inside Excel, nobody swapped a shift inside Excel, and the version that got emailed on Thursday as the final one was not the version pinned to the wall on Friday. The gap between planned and worked hours is where payroll corrections come from, and it is the reason time tracking and the roster have to share one database rather than two files. Once that is true, the staff scheduling side stops being a grid you maintain and starts being a plan your team can read on their phones, with double bookings refused at the point of entry instead of discovered at 14:10.

Before you send any spreadsheet roster out, three checks earn their time back:

• Every total compared against its contract column, not eyeballed
• Every day column compared against a required-headcount row
• One named version, one owner, and no file called final

Our read from the field is that most teams get about eighteen months out of a spreadsheet roster. It stops working at a predictable moment: the second site, the first labor inspection, or the first month where a manager spends more time reconciling hours than planning them. If any of those is close, book a demo and bring the file you use now; the fastest conversation is the one where we look at your actual grid.

Frequently asked questions

Does a shift schedule template in Excel handle night shifts that cross midnight?

Not if you type clock times into the cells, because Excel reads 22:00 to 06:30 as a negative duration and returns an error. Use a shift code instead and store the paid hours as a plain number in your lookup table, which is what the N code does in template 2. The spreadsheet never needs to know the shift crosses a date boundary; it only needs to know the shift is worth 8.5 hours.

How many hours should a shift schedule template allow per person?

Set the ceiling from the contract, not from the legal maximum, and put the contract figure in a column right beside the calculated total. In Belgium the reference is a 38-hour weekly average, so a full-timer landing on 38.5 has already started building compensatory rest. Part-time contracts deserve more attention than full-time ones, because a student on a 20-hour contract who works 22.5 is the overrun an inspector notices first.

Can I use Google Sheets instead of Excel for this template?

Yes, and the collaboration is better because everyone reads one live copy rather than emailing versions around. Every formula in this article works unchanged, COUNTIF and SUMPRODUCT included. What you still will not get is a clock-in record, an absence feed or a rule that refuses a double booking, so the ceiling arrives at the same point.

How do I stop two people being scheduled for the same shift?

Inside one sheet, a headcount check row per day catches it: count each code down the column and compare it with the number you actually need. Across several sheets or several sites, there is no reliable spreadsheet answer, because neither file can see the other. That is the specific point where a shared roster stops being a convenience and starts being the only way to know.