How to make a shift schedule or work rota in Excel

Organizing a team’s shifts seems simple until someone swaps a day off, a night shift runs into a morning shift, or nobody notices there aren’t enough people on Saturday afternoon. A well-designed shift schedule in Excel solves most of these problems, because it calculates hours and flags gaps without you having to check everything by eye.

You’ll create a weekly rota with shift codes, drop-down lists to fill it in without mistakes, automatic colors, hour totals per person, daily coverage checks and, finally, protection so nobody changes it by accident.

Set the rules before you open Excel

The schedule will only be useful if the rules are clear from the start. Write down these decisions:

• Shift types and their hours: morning, afternoon, night, split…
• Short codes for each shift, one or two letters long.
• Hours each shift counts for, including breaks if they’re deducted.
• Minimum staff needed on each shift and day.
• Limits: maximum weekly hours and minimum rest between shifts. These values depend on labor law and any applicable agreement, so confirm them.

Create the shift code table

In a separate area of the sheet (for example, from M1 to O6), create a table with the codes. The whole schedule will rely on it:

If the hours of a shift change later, you only need to edit this table and every total will update.

If you’d rather calculate hours from start and end times

You can put the start and end time in two columns and calculate the duration. The night shift ends the next day, so a simple subtraction would give a negative value. This formula fixes it (start in P2, end in Q2):

=MOD(Q2-P2,1)*24

Build the schedule layout

1. In B1, enter the date of the Monday of that week.
2. In A4:A9, enter the team members’ names (Employee 1, Employee 2…).
3. In C3, enter =$B$1 and in D3, enter =C3+1. Copy D3 across to I3 to get all seven days.
4. Apply the custom format ddd d to C3:I3 to see “Mon 6”, “Tue 7″… When you change the date in B1, the whole week updates.
5. Keep column J for total hours and row 11 for daily coverage.

With a few shifts already assigned, it looks like this:

Fill it in with drop-down lists

Typing codes by hand leads to mistakes: a lowercase “m”, an extra space or a letter that doesn’t exist. A drop-down list prevents this:

1. Select the shift range, C4:I9.
2. Go to Data › Data Validation.
3. Under Allow, choose List.
4. Under Source, enter =$M$2:$M$6 and click OK.

Each cell now shows an arrow with the valid codes, and Excel rejects any other value.

Calculate hours and coverage with formulas

Hours per employee

In J4, this formula looks up the hours for each code in the row in the shift table and adds them up. It works in every version of Excel:

=SUMPRODUCT(SUMIF($M$2:$M$6,C4:I4,$O$2:$O$6))

Copy it down to J9. If you want to know how many night shifts each person works, use:

=COUNTIF(C4:I4,"N")

People per shift and day

In C11, count how many people are on the morning shift that day and copy the formula across to I11. Repeat in the next rows for afternoon and night by changing the letter:

=COUNTIF(C$4:C$9,"M")

Automatic warnings

To spot anyone over the weekly limit (40 hours in the example), add this in K4:

=IF(J4>40,"Check","OK")

And to find anyone coming off a night shift and starting a morning shift the next day, which almost never meets the minimum rest, use a helper column with:

=IF(AND(C4="N",D4="M"),"Not enough rest","")

Add color with conditional formatting

A color-coded schedule can be read at a glance. To color each shift:

1. Select C4:I9.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Equal To.
3. Type M and pick a color. Repeat with A, N, O and V.

You can also shade the weekend. Select C3:I9, create a new rule using the option to use a formula, and enter:

=WEEKDAY(C$3,2)>5

With argument 2, Monday is 1 and Sunday is 7, so the rule marks Saturday and Sunday. Do the same in the coverage row to highlight in red any day with fewer people than needed.

Protect the schedule before sharing it

A shared rota gets changed sooner or later: someone deletes a formula or swaps their shift without telling anyone. Protect it on two levels.

Protect the sheet

1. Select the cells that can be edited (C4:I9 and the date in B1).
2. Open Format Cells › Protection and clear Locked.
3. Go to Review › Protect Sheet and set a password.

That way the formulas and the code table stay fixed, and only the shifts can be changed.

Protect the file

Sheet protection doesn’t stop anyone from opening the workbook. If the schedule includes personal data or you’re going to email it, it’s best to require a password to open it. You can do that with our free tool to password-protect spreadsheets, without hunting for the option in each Excel version. And to pin the schedule on a notice board, convert it to PDF: nobody can edit it and it looks the same on any device.

Common mistakes when scheduling shifts in Excel

• Typing hours by hand in each cell: if a shift changes, the totals stop adding up. Always calculate from the code table.
• Subtracting times for the night shift: a simple subtraction gives a negative number or #####. Use MOD as explained above.
• Copying a new sheet by hand every week: duplicate the sheet and change only the date in B1; the days recalculate on their own.
• Not checking coverage: a schedule with correct hours can still leave a shift empty. Always look at the coverage row.

Conclusion

A good shift schedule in Excel is built on a code table, drop-down lists to fill it in without mistakes, and a few formulas that add up hours, count people and flag conflicts. Add colors so it reads quickly and protect it before sharing. With that foundation, each new week will take you just a few minutes.

Frequently asked questions