Excel Sheet for Employee Schedule: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can build a fully functional employee schedule in Excel using a simple grid layout with dates across the top and employee names down the side, then add conditional formatting and formulas to track hours and flag conflicts. This article explains the core structure, step-by-step setup, common pitfalls, and when a dedicated scheduling app might be a better fit.

Why Excel Is Still a Popular Choice for Scheduling

Excel remains a go-to tool for small businesses, restaurants, and retail teams because it is already installed on most office computers, requires no monthly subscription, and offers complete control over the layout. Unlike paper schedules or group chats, an Excel sheet can be edited instantly, shared via OneDrive or email, and reused week after week. It also lets you calculate total hours automatically, which saves time during payroll. However, Excel has no built-in shift-swap approval, no automatic conflict detection, and no mobile app designed for employees to check their shifts—those limitations matter for larger teams.

Basic Structure of an Employee Schedule Sheet

The standard layout is a table where each row is an employee and each column is a day of the week. A typical setup looks like this:

  • Column A: Employee name
  • Columns B–H: Monday through Sunday (or date headers like 6/1, 6/2, etc.)
  • Column I: Total weekly hours (calculated with a SUM formula)
  • Column J: Notes (e.g., “requested off,” “training”)

In each day cell, you type the shift times, such as 9:00 AM – 5:00 PM or simply 9-5. For hours calculation, it is better to store start and end times in separate columns, but for a quick visual schedule, a single text entry works. If you want to calculate hours automatically, you need separate columns for start and end times, or you can use a helper column that parses the text—though that is more complex.

How to Calculate Total Hours Automatically

To calculate weekly hours, you need start and end times in separate cells. For example, assume columns B and C hold Monday’s start and end times, D and E hold Tuesday’s, and so on. With employee names in A2:A20 and times in B2:G2 (start times) and C2:G2 (end times), enter this formula in H2 to get total hours for the week:

=SUM((C2-B2)+(E2-D2)+(G2-F2)+(I2-H2)+(K2-J2)+(M2-L2)+(O2-N2))*24

But that formula assumes a specific layout with alternating start/end columns. A cleaner approach is to use a separate area for each day: put start time in one column and end time in the next. For instance, with Monday start in B2 and Monday end in C2, Tuesday start in D2 and Tuesday end in E2, and so on through Sunday (columns N and O), the formula becomes:

=(C2-B2)+(E2-D2)+(G2-F2)+(I2-H2)+(K2-J2)+(M2-L2)+(O2-N2)

Format the result cell as a number with 2 decimals, then multiply by 24 to convert the Excel time fraction into hours. For example, if Monday is 9:00 AM to 5:00 PM, the difference is 8 hours, and the sum of all days gives total hours. If you prefer a time format (like 40:00), format the cell as [h]:mm.

Using Conditional Formatting to Spot Conflicts

One of the most useful Excel features for schedules is conditional formatting. You can highlight overlapping shifts or double-booked employees. For example, to highlight any cell where the same employee has two shifts on the same day, you would need a more complex setup, but a simpler rule is to highlight weekend days in a different color or to flag any cell that contains the word “OFF” in red.

To highlight cells that contain “OFF”, select the entire schedule range (e.g., B2:H20), go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, type OFF, and choose a red fill. To highlight weekends, select the columns for Saturday and Sunday, then use Home > Conditional Formatting &strong> New Rule > Use a formula with a formula like =WEEKDAY(B$1)=7 (assuming row 1 contains actual dates). This is a quick visual aid, not a true conflict detector—Excel cannot automatically tell you if two employees are scheduled at the same time on the same day unless you write a complex formula or use Power Query.

Common Problems and How to Fix Them

Several issues come up repeatedly when people build schedules in Excel:

  • Time formatting errors: Typing “9-5” instead of “9:00 AM” makes calculations impossible. Use a consistent format like 9:00 AM or a 24-hour clock.
  • Overnight shifts: If an employee works 10 PM to 6 AM, a simple subtraction gives a negative number. Use a formula like =IF(C2<B2, C2+1, C2)-B2 to handle overnight shifts.
  • Accidental overwrites: Employees may accidentally delete formulas. Protect the formula cells by selecting them, going to Format Cells > Protection, checking “Locked”, then protecting the sheet with Review > Protect Sheet (leave the input cells unlocked first).
  • Version confusion: If you use XLOOKUP or FILTER, remember they require Excel 2021 or Microsoft 365. Older versions need VLOOKUP and IFERROR.

Excel vs. Dedicated Scheduling Software

Excel is free (if you already have Office) and flexible, but it lacks features that many businesses need. Dedicated tools like When I Work, Deputy, or Homebase offer:

  • Employee self-service mobile apps
  • Shift swap and time-off requests
  • Automatic conflict detection (e.g., double-booking, overtime)
  • Integration with payroll and time clocks
  • Cloud-based real-time updates

On the other hand, Excel gives you total control and no per-user fees. For a team of 5–15 people, Excel is often sufficient. For 20+ employees with complex shift rules, a dedicated app saves hours of manual checking. A hybrid approach is to use Excel for planning and then export to a shared calendar or PDF for employees.

Cost and Safety Considerations

Excel itself is part of Microsoft 365 (subscription, typically $6–$10 per user per month for business plans) or a one-time purchase of Office 2021. If you already have it, the incremental cost is zero. Free alternatives like Google Sheets offer similar functionality with easier sharing, but they lack some advanced Excel features like Power Query. When sharing schedules via email or cloud, be careful with personal data—employee names and work hours are considered personal information in many jurisdictions. Avoid putting full addresses or social security numbers in the schedule. Also, always keep a backup copy of the file, especially before running any macros.

Frequently Asked Questions

Can Excel automatically avoid overlapping shifts?

Not easily. Excel has no built-in conflict detection. You can write a complex array formula or use conditional formatting to highlight cells where two employees have the same time on the same day, but it requires a well-structured layout and a good understanding of formulas. For real-time conflict alerts, a dedicated scheduling app is much simpler.

How do I make a schedule that repeats every week?

Create a template with the formulas already in place, then save it as an Excel template (.xltx). Each week, copy the template, clear the shift cells, and add the new dates. You can also use the SEQUENCE function (Microsoft 365) to generate a row of dates automatically, but the simplest method is to drag the fill handle across the date row.

What is the best way to share the schedule with employees?

For read-only viewing, export the sheet as a PDF and email it, or upload to a shared drive. For interactive editing, save the file to OneDrive or SharePoint and share a link with edit permissions. If employees need to see it on their phones, consider using the Excel mobile app, but note that editing on mobile is clunky. Many businesses print a paper schedule and also post a PDF.

Can I track employee availability in the same sheet?

Yes. Add a separate worksheet called “Availability” with employee names and days of the week, and mark available times. Then use conditional formatting on the schedule to highlight any shift that falls outside the availability range—this requires a formula that compares the shift start time to the availability start time. It is doable but takes time to set up. A simpler approach is to visually compare side by side.

Does Excel work for shift swaps?

Only manually. An employee can edit the sheet to swap shifts, but you lose an audit trail unless you use version history (File > Info > Version History). For a more formal process, use a shared Excel file with comments or a separate “Swap Requests” sheet where employees log requests and managers approve them.

What about overtime calculations?

You can add a column that sums hours per employee, then use an IF formula to flag anything over 40. For example, with total hours in I2, use =IF(I2>40, I2-40, 0) to show overtime hours. Remember that overtime rules vary by jurisdiction and employment contract—Excel can calculate numbers, but you must apply the correct legal thresholds.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.