Step 1: Decide the Grid Before You Type a Name

Almost every duty roster that works in Excel uses the same shape: one row per employee, one column per calendar date, and a short shift code in each cell. Put employee code and name in columns A and B, designation or post in column C, and the dates of the month from column D onward (D = 1st, E = 2nd, and so on to the 31st). Row 1 holds the month and year, row 2 holds the date numbers, and row 3 holds the weekday abbreviation so that Sundays are visible at a glance.

Freeze panes at cell D4 so that names stay visible while you scroll across dates. Enter the first date as a real Excel date (for example 01-Oct-2026), then fill the next cells with =D2+1, and format the row as 'd' to show only the day number. Use =TEXT(D2,'ddd') in row 3 for the weekday. Because they are real dates, weekend highlighting and month changes work without retyping.

Fix your shift codes before the roster grows. A typical Indian factory, hospital or facility team uses M (morning, 06:00–14:00), E (evening, 14:00–22:00), N (night, 22:00–06:00), G (general shift, 09:00–18:00), O or WO (weekly off), L (leave) and H (holiday). Security agencies running 12-hour posts often use D (08:00–20:00) and N (20:00–08:00). Write the code legend with actual timings in a corner of the sheet; a roster that says 'N' without stating 22:00–06:00 causes disputes at payroll time.

• Rows = employees, columns = dates, cells = one shift code each
• Use real Excel dates in the header row so weekday formulas work
• Define codes with exact timings in a legend on the same sheet
• Freeze panes at the first date cell so names never scroll away

Step 2: Lock the Codes With Data Validation and Colour

Free typing is how 'M', 'm', 'Mor' and 'M ' end up in the same column and break every count. Select the whole roster range (for example D4:AH40), open Data → Data Validation, choose List, and type the allowed codes separated by commas: M,E,N,G,O,L,H. Tick 'Show error alert' so an invalid entry is rejected. Anyone filling the sheet now picks from a dropdown, and your COUNTIF formulas can trust the values.

Next, apply conditional formatting so the pattern is visible without reading each cell. Select the same range, choose Conditional Formatting → Highlight Cell Rules → Equal To, and create one rule per code: light yellow for M, light orange for E, dark blue with white text for N, grey for O, green for L. Add one more rule on the date header using a formula such as =WEEKDAY(D$2,2)>=7 to shade Sundays. A supervisor can now spot three consecutive nights or a week with no off day in seconds.

Protect the structure once it works. Unlock only the roster cells (Format Cells → Protection → untick Locked), then protect the sheet with a password so that headers, legend and formula columns cannot be overwritten. Keep the protection password with the HR head, not with the person who fills the roster.

• Data Validation list: M,E,N,G,O,L,H (or D,N,O for 12-hour posts)
• One conditional-format rule per code, plus a Sunday shading rule
• Unlock only the entry cells, then protect the sheet
• Reject invalid input rather than cleaning it up later

Step 3: Add the Formulas That Make It a Roster, Not a Picture

A roster without formulas is a coloured table; the formulas are what catch mistakes. To the right of the last date, add per-employee totals. If the dates run D4:AH4 for the first employee, use =COUNTIF(D4:AH4,“M”) for mornings, and copy the pattern for E, N, G, O and L. Add a working-days column =COUNTIF(D4:AH4,“<>O”)-COUNTIF(D4:AH4,“L”)-COUNTIF(D4:AH4,“H”), and a night-count column that payroll can use for night allowance. (Type straight double quotes in Excel; they are shown here as typographic quotes.)

Below the last employee, add per-day coverage rows. For the 1st of the month in column D, =COUNTIF(D$4:D$40,“M”) tells you how many people are on morning duty that day; repeat for E and N. Put your required strength beside it (say 6 morning, 6 evening, 4 night) and add a check cell such as =IF(D42<D$45,“SHORT”,“OK”). Conditional formatting on the word SHORT in red gives you a coverage heat map along the bottom of the roster.

Two more checks save you from labour-law trouble. First, weekly offs: =COUNTIF(D4:J4,“O”) over each 7-day block should equal at least 1; flag any employee-week where it is zero, because the Factories Act and state Shops and Establishments Acts require one whole day of rest a week (see weekly off rules). Second, back-to-back rest: an N followed by an M the next day leaves less than eight hours between shifts; a helper formula =IF(AND(D4=“N”,E4=“M”),1,0) summed across the row catches it.

• Per-employee COUNTIF totals for each shift code, offs and leave
• Per-day coverage counts compared against required strength
• Weekly-off check: at least one O in every 7-day window
• Rest-gap check: flag N immediately followed by M

Step 4: Build Rotation Quickly Instead of Cell by Cell

If your team rotates weekly (M → E → N), do not type 31 cells per person. Fill week one by hand for the first employee in each crew, then use a formula in the following weeks that reads the previous week's code and advances it: =IF(D4=“M”,“E”,IF(D4=“E”,“N”,IF(D4=“N”,“M”,D4))). Copy it across the row and down the crew. Convert the formulas to values before publishing so that a later edit does not cascade through the month.

For 12-hour patterns such as 2-2-3 or 4-on-4-off, the simplest approach is a pattern strip: type the repeating sequence (D,D,O,O,N,N,N,O,O,D,D,O,O,O for a Panama cycle) once in a hidden row, then reference it with INDEX and MOD on the day number. Each crew starts at a different offset. If you want ready-made cycles rather than building them, see rotating shift schedule examples and the 12-hour shift schedule template.

Keep a separate 'Changes' sheet with columns for date, employee, old code, new code, reason and approver. Every swap or reassignment gets one line. This is your audit trail when an employee disputes overtime or a client asks why a post was uncovered on a given night.

• Use a nested IF or INDEX/MOD formula to generate rotation, then paste as values
• Keep one pattern strip per cycle and offset each crew
• Log every change on a separate sheet with reason and approver
• Never edit a published month without a line in the change log

Step 5: Print, Share and Version Without Chaos

Set the print area to the roster plus the legend, choose landscape, fit to one page wide, and repeat rows 1–3 on every page (Page Layout → Print Titles). Print the version number and publish date in the footer. Most sites still need a paper copy on the notice board, and the Factories Act requires that the periods of work be displayed; a dated printout is your evidence.

Save each month as its own file with a fixed naming convention such as Roster_SiteA_2026-10_v3.xlsx. When a change is made after publishing, increment the version and circulate the new file; do not overwrite v1. The single most common roster failure in Indian SMEs is not a formula error but two supervisors working from two different versions forwarded on WhatsApp.

If you are building rosters for guards across client sites, the layout changes: posts become rows and the guard name goes in the cell, because the client pays for the post being covered. That method is covered in how to make a duty roster for security guards. A ready-to-use grid for general staff is available as the duty roster format in Excel.

• One file per month per site; version number in the file name and footer
• Print titles so names and dates repeat on every page
• Circulate the latest version only; retire old PDFs explicitly
• For guard posts, use posts as rows and names as cell values

Where Excel Rosters Break, and What to Do About It

Excel handles a single-site team of up to about 30 people with a stable pattern. It starts failing when four things happen together: frequent swaps, leave that is approved in a different file, more than one site sharing people, and attendance that is captured somewhere else. A swap agreed at 22:00 on WhatsApp never reaches the sheet; a leave approved on paper leaves a person rostered on a day they are away; a guard moved from Site A to Site B is present in both rosters. None of these are formula problems.

The other structural gap is that the roster and the attendance record live apart. The roster says N, the register says present, and nobody checks whether the person came at 22:00 or at 01:30. Overtime, night allowance and client billing all depend on that comparison being made every day, not at month end. In Excel that means a VLOOKUP between two workbooks that someone must remember to run.

A roster tool replaces the grid with the same logic enforced automatically: shift templates with timings, a weekly roster builder that is drafted and then published to employees' phones, warnings when someone with approved leave is rostered, shift swaps that require approval, and attendance rules such as late-mark and grace period that read from the assigned shift. Attend Mitra's roster and shift module does this for day, night, rotating, split and midnight-crossing shifts, and the published roster is what the attendance record is validated against. Before you move, read the duty roster glossary entry to align terminology across supervisors, and see the roster software guide for what to check in a trial.

• Excel is fine for one site, one pattern, under ~30 people, few swaps
• Swaps, leave conflicts and multi-site sharing are the usual breaking points
• Roster-to-attendance comparison must happen daily, not at month end
• A roster tool enforces the same rules you built with COUNTIF, automatically