Auto-Flag Double-Booked Workers
in Your Excel Shift Schedule

You manage a 40-person shift roster. Your schedule lives in Excel — dates in column A, shift names in column B (Morning / Afternoon / Night), worker names in column C. It works, mostly. But twice last quarter, someone got scheduled for two shifts in a single day. You caught the first one because a worker called in confused. You didn't catch the second one until 5:45 a.m.

Manually scanning 200 rows for duplicates before every posting cycle is where errors hide. Excel can do this for you — automatically, on every row, the moment you paste in a new name.

A Conflict Alert column (column D) that instantly shows ⚠ DOUBLE-BOOKED if any worker appears twice on the same date, or ✓ OK if the row is clean. It requires no macros, no add-ins — just one formula you copy down the column.

Set Up Your Schedule Table

Your schedule table needs at minimum three columns. If yours already has them under different names, note the column letters — you'll substitute them in the formula below.

Row 4 above is the problem row — Rodriguez is already on Morning that day. The formula will flag both row 2 and row 4.

Enter the Core COUNTIFS Formula

Click cell D2 and type this formula, then press Enter:

=IF(COUNTIFS($A$2:$A$200, A2, $C$2:$C$200, C2) > 1, "⚠ DOUBLE-BOOKED", "✓ OK")

How it works — reading left to right:

• COUNTIFS($A$2:$A$200, A2, …)Count every row where column A equals this row's date. The $ signs lock the range so it doesn't shift when you copy the formula down.
• … $C$2:$C$200, C2)AND column C equals this row's worker name. COUNTIFS only counts rows where both conditions are true simultaneously.
• > 1If the count is greater than 1, this worker appears more than once on this date — a conflict.
• "⚠ DOUBLE-BOOKED"The text displayed when a conflict is found. You can change this to any flag you want.

Wrap With IFERROR to Handle Empty Rows

If column A or C has any blank cells, the basic formula can return an error instead of silently skipping. Wrap it with IFERROR so blanks display a dash instead:

=IFERROR(
  IF(COUNTIFS($A$2:$A$200, A2, $C$2:$C$200, C2) > 1,
     "⚠ DOUBLE-BOOKED",
     "✓ OK"),
  "—")

The IFERROR(formula, "—") wrapper catches any formula error and displays — instead. This keeps the column clean even if you have header rows, subtotals, or gaps in your data.

Now copy cell D2 and paste it down through D200 (or however many rows your schedule uses). Every row with a double-booked worker will immediately show ⚠ DOUBLE-BOOKED.

Extend With INDEX/MATCH to Identify Which Shift Conflicts

The basic formula tells you that a conflict exists. This advanced version tells you which other shift the worker is already assigned to — so you can resolve it immediately without scrolling.

=IFERROR(
  IF(COUNTIFS($A$2:$A$200, A2, $C$2:$C$200, C2) > 1,
     "⚠ Also on: " & INDEX($B$2:$B$200,
       MATCH(A2 & C2, $A$2:$A$200 & $C$2:$C$200, 0)),
     "✓ OK"),
  "—")

What the INDEX/MATCH addition does:

• A2 & C2Concatenates the date and worker name into a single lookup key, e.g. '44026Rodriguez, M'. This lets MATCH search for a combined match.
• $A$2:$A$200 & $C$2:$C$200Builds the same combined key for every row in the range. MATCH finds the first row where both date and name match.
• INDEX($B$2:$B$200, …)Returns the shift name from column B at the matched row — so you see exactly which shift is the other booking.

With this formula, a flagged row will now show: ⚠ Also on: Morning— telling you Rodriguez is already on Morning shift that day, and you're about to double-book her on Afternoon.

Add Conditional Formatting to Highlight Entire Rows

The formula does the detection. Conditional formatting makes conflicts impossible to miss — the entire row turns red the moment a conflict is flagged.

1. Select your entire data range — e.g. A2:D200
2. Go to Home → Conditional Formatting → New Rule
3. Choose "Use a formula to determine which cells to format"
4. Enter this formula:

   =$D2="⚠ DOUBLE-BOOKED"

   The $D2 locks column D but lets the row number change — so every row checks its own D column.
5. Click Format, choose a red fill, click OK.

Now any row where column D contains the conflict flag will turn red across all four columns — instantly visible even when you're scrolling fast.

Here's what your schedule looks like before and after applying the formula. The conflict rows (2 and 4 — Rodriguez on 7/14) are immediately surfaced.

Both rows flagging Rodriguez on 7/14 are now visible without any scrolling or manual scan. You can sort by column D to push all conflicts to the top before posting the schedule.