How to Make a Work Schedule in Excel in 10 Steps + a Better Alternative

Making a work schedule in Microsoft Excel or Google Sheets can be easier and faster than making one with pen and paper. And, built-in sheets and the equations functionality can help with related tasks like time tracking.

These solutions are typically cheaper than dedicated scheduling software, though the cost benefit analysis becomes more nuanced when you factor in how much time you spend making a work schedule in Excel and the features you’re not getting by using Excel (like simple to navigate shift swapping and auto-populated repeating schedules).

Here are some of the drawbacks to be aware of when making work schedules in Excel:

• You need to reference multiple pieces of information, including general employee availability, preferred employee availability, time off, operational hours, and more. This info doesn’t live in your spreadsheet, so you’ll have to reference other docs or files while making your schedule. Compare this tedious cross-referencing with using scheduling software, where this information is readily available.

• It’s a pain managing past schedules in Excel. Creating weekly schedules (let alone daily schedules) can quickly consume storage space in Excel. Plus, without a robust naming and organizational system, it’s difficult to locate individual Excel files (and therefore records). Sometimes as a business owner you need to quickly reference past schedules to see when someone was working — this isn’t easy in Excel.

• The most helpful scheduling features often require advanced knowledge of Excel, and therefore are time-consuming for beginners (e.g., linking schedules across multiple locations while having it be that selecting an employee from a dropdown makes them unavailable at other locations).

• Spreadsheets become harder to manage the bigger they are, so Excel is inefficient for large rosters. You’ll spend more and more time in your Excel spreadsheet creating your schedule.

• Other project management functions (such as clocking in and out, communicating with employees, and letting employees cover for each other) simply aren’t available through Excel.

For some teams, these issues are not a deal breaker; for other teams, it’ll be cheaper and more efficient to use scheduling software.

In this post, we’ll begin with a step-by-step guide to creating a schedule in Excel, including how to add sheets for keeping track of roles and how to keep a running total of how many hours each employee is scheduled to work every week. At the end, we’ll include a link to download a free template for an Excel weekly work schedule. But we’re also going to talk about a better method for small businesses who want to make a work schedule — using scheduling software that eliminates the headaches of spreadsheets and manual scheduling.

You can try Buddy Punch’s scheduling software for free for 14 days. Buddy Punch makes it easy to create schedules with features like a drag-and-drop calendar, auto-scheduling, employee availability, shift swapping features, and more. Plus, Buddy Punch is also an employee time clock app that your team can use to track hours worked and that you can use to keep employees accountable, track time worked, calculate overtime, PTO balances, and issue payroll.

How to Make a Work Schedule in Excel in 10 Steps

Follow these step-by-step instructions to make your own work schedule without having to look up additional Excel tutorials.

These are general instructions, which of course you can modify. We’re trying to show you the most basic (fastest) way to make a schedule.

If you want to follow along, you can make a copy of our Excel work schedule or download it as an Excel file.

Step 1: Open File

Open Excel. Save the file, and keep saving after each step to avoid losing your work.

Step 2: Create Header

Select cells visible in Row 1 and click the “Merge & Center” button, then type in the name of the schedule. You can also use the borders tool to select “All Border” to make this header visually distinct from the rest of the schedule.

Step 3: Create Shifts

If you have a set number of recurring shifts every week, you can use abbreviated names as above. Otherwise you can put time ranges for the week’s shifts (e.g., “Monday 8:00-10:30”). In the row below, make note of the date in the cell corresponding to the first shift on that day of the week. This helps both with recordkeeping and readability.

Step 4: List Employees by Site

In column A, row 4, enter the name of your first work site, then highlight the row (left-click the number at the left of the screen) and select “top border.” Bold the site name to make the delineation clearer by highlighting the cell and clicking the bold button or by using the keyboard command ‘control-B’.

Beneath this site, manually enter the names of employees who can work there. Leave two blank rows and repeat the process for each additional site.

Step 5: Create Roles Sheet

Create a new sheet by clicking the “+” button at the bottom left of the screen. Rename this and the first sheet by double-clicking and typing an indicative name (e.g., “Schedule” for the first and “Employee Roles” for the new one). Within the new sheet, make a list of all employee names and make note of their roles in subsequent columns. That way you can easily refer to employee roles within the same Excel file without cluttering the schedule screen.

Step 6: Equations for Tracking Assigned Hours

In the column to the right of your final shift, we’re going to calculate the total number of hours an individual employee is scheduled to work at each site. If you’d like to make this section visually distinct from the rest of the schedule, give the “Site Hours” column a left border.

In the cell corresponding to your first employee of your first site, type in “=SUM(”, select all the cells in the row between their name and Total Hours column, and press enter (alternately, you can type in the cell values in the format LeftmostCell:RightmostCell, e.g. B5:P5).

The cell should update, displaying a value of zero (numbers have been added to the example to demonstrate how the equation will update once hours are assigned). Click and hold the square at the bottom right of this cell and drag it down to the row of the last listed employee for that site. This should automatically fill the cells with the corresponding equation for their row. Copy the cell with the equation, paste it into the cell corresponding to the first employee of the next site, and repeat the process.

While this gives the total number of hours an employee is working at a specific site, some employees may work at multiple sites. Therefore, use the column to the right of Site Hours to calculate their total hours:

If an employee works at multiple sites, in the Total Hours column, type “=SUM(”. You’ll then need to click on each cell corresponding to their site hours, separating each click with a comma. In our example, Bruce works at all four sites, so the equation for Site 1 is “=SUM(Q5,Q13,Q21,Q30).”

Repeat this process for each site the employee works at so that you can view their total hours no matter which site you’re currently viewing (but be careful about copy-and-pasting, as Excel may automatically shift the formula unless you add “$” into the formula, e.g. “$Q$5”).

Alternately, use the equation “=” and click the Total Hours cell for site one. This method, however, is the most likely to propagate errors throughout the document.

Step 7: Equation for Tracking Employees per Shift

In the row below the last employee for the first site, type “=COUNTIF” into the cell in the column for the first shift, then select the cells in the column corresponding to all the possible employee shifts (in the example, B5:B9). Add a comma, then type, with quotation marks included, “>0”. This tells Excel to count all the cells with a value greater than zero, which in this case is equivalent to the number of people who have been assigned to that shift. Hit enter, then drag the equation across the row.

Repeat this process for each site. The “Personnel” rows will display the total number of people assigned to each shift in each location.

Step 8 (Optional): Track Roles

If you only have one or two roles to keep track of (e.g., a shift manager), you can add a row for roles using the same method as tracking personnel. However, rather than adding all employees, add only the employees capable of filling that role. If the employees aren’t next to each other on the spreadsheet, however, you may need to add the functions together.

For example, Bruce and Barbara’s rows at site 2 aren’t adjacent, so it was necessary to use the formula “=COUNTIF(B13,“>0”)+COUNTIF(B16,“>0”)” to prevent Excel from returning an error.

Another tool for keeping track of roles is color coding. For example, it’s possible to click on the cells of employees and add highlighting associated with their role (such as a light blue highlight for managers). Highlighting the entire row is also possible. Either way, be sure to use a colorblind-safe palette, as failing to do so could render schedules inaccessible to some employees and managers.

Step 9: Assign Hours to Each Team Member

Input the number of hours an employee is assigned for each shift. Note you need to use numerals, not words or letters; otherwise, the Excel equations will malfunction. Once hours are assigned, the totals will update automatically, letting you see whether employees have the correct number of hours and whether there’s an equitable distribution of work in your employee schedule.

In the above example, most of the managers are stretched thin. While Bruce never exceeds 40 hours a week at one site, he’s scheduled at multiple sites for a total of 80 hours! Meanwhile, Barbara is assigned to both day and night shifts at Site 2, which adds up to a 60-hour work week. This schedule needs to be re-balanced to keep overtime to a minimum, and it would also be advisable to hire or train more managers.

Step 10: Distribute the Schedule to Employees

Once you’ve created a work schedule with Excel, you need to distribute it to your employees. Options include email, sharing it via Microsoft Office, or manually adding the shifts to a shared calendar.

If you’d rather not start from scratch, you can download the Excel schedule template we created for this guide. This is a Google Sheets file but you can use it to download an Excel file.

But as you saw, creating a work schedule this way, comes with some serious drawbacks.

• It’s a lot of manual work. Before you even make your schedule, you need to create your scheduling template. This involves fidgeting around with columns, rows, formulas, and more. Plus, as your team changes or your needs change, you’ll have to adapt your template.

• It’s difficult to share your schedule. You want to share your work schedule with your team, but having your employees open up Excel spreadsheets on their phone or tablet or even some computers isn’t really intuitive or easy to reference. Plus, if you accidentally change permissions, your employees may not be able to see the schedule or they’ll be able to edit their own schedules.

• Spreadsheets don’t help you write better schedules faster. A good work schedule factors in employee availability, employee preferences, operational needs (like is it a busy sales week, is there a national holiday to consider), and any approved time off requests. Spreadsheets don’t really help with this, so you’ll need to use different tools to keep track of all the above.

Again, these issues may not be a dealbreaker for you. If they’re not, try using an Excel work schedule and see if it’s sufficient for your team. But if you’re like most businesses we talk to, you’ll need a scheduling solution with more helpful features, which is what we cover next.

Are you more of a visual learner? If so, you can try our tool for free or schedule a one-on-one demo.

Buddy Punch: User-Friendly Scheduling, Shift-Swapping, and More

Buddy Punch is a cloud-based scheduling software suite that’s compatible with Windows, Mac, Android, and Apple. It offers a robust scheduling feature with a calendar-based interface that makes it easy to create reusable templates, notify employees of their schedules, and allow employees to trade shifts.

Buddy Punch’s scheduling features let you significantly cut down on how much time it takes to create a work schedule.

In addition, its time clock lets employees clock in from their own devices, and comes with tools for:

• Accountability. Tools like geofencing and IP address locking ensure employees are at work when they clock in.

• Tracking PTO. You can enter leave accrual policies into Buddy Punch and it will automatically calculate time off based on hours worked.

• Overtime Alerts. Employees and managers will be notified whenever employees are approaching overtime.

• Record-keeping. Generate timesheets and reports that are exportable into multiple formats.

• Payroll. Integrate Buddy Punch with existing payroll software or export hours directly into its payroll menu.

We’ll begin by covering the scheduling features before diving into its time tracking functionality.

Scheduling Features

Buddy Punch offers a centralized, uncluttered scheduling tool with reusable templates that can account for variables like employee role, location, and department. You can also easily alert employees to their shifts, and even let employees arrange for substitutes when they can’t make their shift.

Drag-and-Drop Calendar

Buddy Punch has an intuitive calendar interface with customizable drag-and-drop shifts. All you have to do is click on the shift to pull up editable fields.

Fields such as shift location, roles, and departments can be selected via dropdown menus. Other fields that can be easily customized in the same base menu are employee(s) and shift length — no equations, extra sheets, or color coding necessary.

Sharing and Notifications

By integrating with other platforms, Buddy Punch lets you easily alert employees to their shift schedules via email and push notifications. This is a distinct step up from Microsoft Excel, which doesn’t allow for easy communication among team members.

Shift Trading

When you use Excel, if an employee can’t make a shift, they’ll need to notify you (and possibly co-workers), the team will need to communicate back and forth to adjust the schedule, you’ll need to edit the schedule, and then you’ll need to send it back out again.

With Buddy Punch, the centralized self-service portal lets employees arrange for their own shift trades. It even alerts managers to these trade requests, ensuring everyone is kept in the loop as the employee work schedule is updated in real-time.

Time Tracking Features

Buddy Punch is an all-in-one time tracking solution for ensuring adherence to carefully crafted schedules. Because it’s compatible with most technology, employees can log in from a kiosk or their own devices. While it’s possible to track hours using Excel, Buddy Punch also has tools for:

• Accountability. Geofencing and IP Address Locking ensures employees are at their work sites when they sign in.

• Time Off Accrual. Buddy Punch lets you automate leave accrual based on employee work hours.

• Overtime Monitoring. If employees are approaching overtime, they and their managers will receive an alert.

• Records & Reports. Buddy Punch lets you generate reports with a single click, and they can be exported in multiple formats.

Accountability

Buddy Punch offers multiple options for ensuring employees are reporting their hours accurately, including GPS, geofencing, and IP Address Locking.

GPS and Geofencing

Whether for safety or accountability, geofencing and GPS tracking features let you know whether your employees are where they’re supposed to be (a feature Excel notably lacks).

The GPS only activates when employees clock in at their start times, preserving their privacy. It allows managers to view a log of punch-in and punch-out locations, and also enables live tracking of employees during the workday.

Geofencing is a less invasive option to ensure employees are only clocking in once they’ve reached their worksite. In short, it lets employers set a physical radius around a work site, and employees must be within that radius to clock in. If they aren’t, they’ll receive an error message prompting them to try again when they’ve arrived at work.

IP Address Locking

Unlike Excel, Buddy Punch can restrict employees from clocking in if their IP address doesn’t match ones managers have designated as work IP addresses. This is another way to ensure employees are at work when they say they are.

Automatic Punch-Outs

Automatically safeguard against employees forgetting to sign out with Buddy Punch. If employees haven’t logged out after their shift has ended, Buddy Punch will log them out, reducing human error and the need for correcting time sheets.

Track and Calculate Time Off

Buddy Punch automatically calculates leave based on hours worked, via rules managers specify during set-up. These rules can be customized for individual employees, by role, or by department to ensure compliance with company policies.

Both managers and employees can easily check balances via Buddy Punch. When employees request leave via their self-service portals, managers can easily update schedules in the same menu rather than having to juggle Excel spreadsheets.

Monitor Overtime

Rather than having to keep track of overtime manually, managers get alerts as soon as employees start clocking too many hours. Employees also get these alerts, giving them the chance to log out and saving everyone the hassle of accounting for unplanned overtime.

Records & Reports

Buddy Punch lets managers check records for individual employees via the self-service dashboard. It also enables the easy export of reports for tax and recordkeeping purposes in Excel, CSV, PDF, or print.

Payroll

Many people who do schedules with Excel also do payroll with Excel, but Buddy Punch can streamline the process, saving time and reducing human error. It can integrate with other popular payroll software such as QuickBooks, or you can use Buddy Punch’s Payroll software. Employees’ hours are imported directly into this menu, where Buddy Punch calculates wages (including overtime wages) based on hours worked. It can also handle:

• Federal, state, and local taxes. Buddy Punch automatically files these taxes and sends documents like W2 forms to employees.

• Expenses and Bonuses. Keep track of everything from tips to mileage, and incorporate non-hourly bonuses into payroll.

• Direct deposit. Employees indicate their preferred payment method in their self-service portal, and if they choose direct deposit, Buddy Punch will automatically disburse the funds once payroll is submitted.

If you’d like to learn how Buddy Punch can help your business, schedule a demo or try it free today!