For small teams, making an employee schedule in Google Sheets is fast and simple. It’s more adaptable than pen and paper, you can easily undo mistakes, and it’s accessible on almost every device.

As your team grows, you can use its advanced functions to manage more complex scheduling situations, like scheduling for multiple locations, handling shift swaps, and ensuring role-based coverage.

In this post, we walk you through how to build a basic employee schedule in Google Sheets, as well as how to use formulas, conditional formatting, and more to create complicated schedules.

The benefits of using Google Sheets for employee scheduling

For simple setups, Google Sheets can be advantageous in multiple ways:

• Making a work schedule in Google Sheets is easier and faster than making one with pen and paper. You can easily undo mistakes, copy and paste values from one cell into another, and use each week’s schedule as a template for future weeks.
• Google Sheets is free to use.
• You can quickly share Google Sheets schedules with employees using a link.
• Google Sheets works on almost every device, so you and your team can access schedules on the go, using your phone, tablet, or desktop.

The disadvantages of using Google Sheets for employee scheduling

Google Sheets does come with some drawbacks:

• Creating a schedule on Google Sheets takes more time than creating one using employee scheduling software.
• It can be difficult to keep track of every employee’s availability, preferences, and time off. You may have to reference multiple tabs and documents while building schedules.
• The most helpful features often require expert knowledge of formulas and functions, and without careful attention at every step, they can break down if there’s a single typo.
• Google Sheets doesn’t offer scheduling features. You can’t notify employees automatically when their schedules change, allow employees to request shift trades, or let team members change their availability on their own.
• While you can see an overview of who made what changes, Google Sheets doesn’t offer context around why changes were made, or whether someone approved them.

For smaller teams with predictable hours and stable schedules, these issues may not be a deal-breaker. But at some point, even small teams run into expensive problems. That’s what happened at Curl Centric:

“The tipping point for us was a payroll error due to not updating the correct tab, making our decision to switch from spreadsheets to scheduling tools. We didn’t move away from spreadsheets because they don’t meet our needs, it’s just as our team has grown and we no longer fit within the functional limitations of a spreadsheet.”

– Akirashanti C. Byrd, CFE, Co-founder, Curl Centric

The experience at KhrisDigital was similar:

“Two writers meant that one sheet was stretched to cover everything without friction. The changes would appear immediately and everyone had the knowledge of where to check, so we decreased the back-and-forth update emails by nearly half… The introduction of a third content vertical was the breaking point. The tab system no longer made sense and writers began to miss work since nobody was reading that many tabs in the middle of the week. But the most frustrating thing was that one incorrect wiping of the past removed all the conditional formatting rules I had constructed over the course of the night.

– Khris Steven, Founder, KhrisDigital

If you’re managing multiple shifts, locations, or frequent schedule changes, employee scheduling software can often be more efficient and cheaper. 

How to make an employee schedule in Google Sheets

Make a schedule for your employees using Google Sheets in seven steps.

1. Create a new file

Open a blank spreadsheet and give your file a name like “Employee Schedule: [date] to [date].” Adding dates in the file name makes it easy to find previous schedules. Google Sheets will automatically save your changes as long as you are connected to the internet.

2. Create a header

• In row 1, drag your cursor horizontally and select the number of cells you’ll need for your header. This will likely be seven or eight — one for employees’ names and five to seven for days of the week, depending on how many days your business operates.
• Click the Merge button on the toolbar.
• Type in the name of your schedule (the header) in this merged cell.
• Make the header stand out. Use font size 14 or 16, add a background color that fits your brand, or add a black border around the cell.

3. Add column titles in row 2

Name column A “Employee.” In the next columns, list the individual days and dates of your schedule. For example, if you’re building a schedule for the week starting Monday through Sunday, name column B “Monday [date]” and column H “Sunday [date].”

If you need to change the size of a column, click on one of its vertical borders and drag it horizontally until it’s the desired size. Alternatively, right click on the column and select Resize column.

4. Add employee names in column A

In column A, list the names of all your employees. If you have multiple locations, you can create separate sheets, one for each location, by clicking on the + icon in the bottom left.

5. List the hours each employee will work

At this point, you can start creating your schedule. For each day, list the times you want each employee to work that day. Adjust the column size so that all timings are fully visible. If someone is on time off, write “Off” on the day they are on leave.

6. Make your schedule visually striking

There’s a lot you can do on Google Sheets for design and style.

• Change the background colors of each row. Click Format > Alternating colors. Google Sheets offers some default styles, or you can use your own. You can also pick a different header color. Once you’ve made the changes you want, hit Done.
• Use bold or italics for important information. For bold font, press Ctrl + B on a Windows PC or Cmd + B on a Mac. For italics, press Ctrl + I or Cmd + I.
• Change the font. On the toolbar, where it says Default, select the dropdown and choose the font you wish to use.
• Add borders to your cells. Highlight the cells you want a border around. On the toolbar, hit the icon that looks like a grid. If you hover over it, it will say Borders. Select the first option called All borders so that the information is clear on screen and in print.
• Center the text to add white space between entries. On the toolbar, look for an icon with horizontal lines — the second to the right after Borders. It will likely be set at left-aligned by default. Click on it and select the second option called Center.

7. Distribute the schedule to your employees

Once your schedule is finalized, share it with your employees. You can:

• Print it and hang it in the break room or another central location.
• Download the file and email it to your employees.
• Create a shared link and send the link to employees.

Ready-to-use employee schedule templates for Google Sheets

Here are some common schedule templates you may find useful.

• 7-day weekly work schedule template: A basic weekly schedule.
• Monthly employee schedule template: A schedule for the entire month that can be printed on one sheet of paper.
• 24 hour schedule template: If your business operates 24 hours a day, use this template to schedule employees weekly across all 24 hours of each day.
• Shift schedule template: If you want to schedule employees for specific shifts, use this template to create a weekly schedule split into different shifts.
• Job schedule template: If your employees work on specific jobs or projects, use this template to specify not only when they’ll work, but also what specific projects they’ll work on.
• Schedule template for multiple positions: If your employees rotate between different roles, use this template to let employees know when they’re working and in what role.
• Daily employee schedule template: If your employees work at different locations each day and their locations aren’t planned in advance, use this template to create a schedule each morning that specifies what jobs they’re working on that day and where.

Advanced employee scheduling in Google Sheets

As your employee headcount grows or the number of locations increases, you’ll need more complex employee scheduling solutions. We look into some advanced functions Google Sheets offers to handle such scenarios.

Calculating hours and tracking overtime

When putting together a schedule, you need to ensure everyone gets assigned the right amount of hours and identify if someone’s gone into overtime.

First, use the COUNTIF formula to ensure everyone gets assigned enough shifts and hours. COUNTIF calculates how many cells in a range match the conditions you set and displays that count in a selected cell.

Calculate number of shifts per week if employees work shifts of the same length

Let’s say you want to confirm that all employees have five shifts in a week.

• Select the cell you want the total weekly hours to appear in, ideally at the end of an employee’s row. So if your schedule ends at column I, select cell I4.
• Type in this formula: =COUNTIF(B4:H4,“Morning”)+COUNTIF(B4:H4,“Afternoon”)+COUNTIF(B4:H4,“Evening”). If the employee is on leave on any day, the formula won’t count that entry as it’s only looking for Morning, Afternoon, or Evening.
• Press enter. This will display the number of shifts that employee has in cell I4.
• Click on the cell again. A circle will appear in the bottom right. Click on that and drag it down to copy the formula to each employee’s row.

Calculate weekly shift duration if employees work different shift lengths

• First, create columns for shift start time, shift end time, and daily total hours. For example, column B4 would show the Monday start time, C4 would show the Monday end time, and D4 would show the Monday hours. Rows 5 through 10 would show this information for the rest of the week.
• Next, select D4 and type this formula: =C4-B4. This gives you the shift duration for Monday.
• Click on D4, then click on the circle and drag across to D10. This automatically calculates daily hours for Tuesday through Sunday.
• In D11, enter this formula to calculate total weekly hours: =SUM(D4:D10). This adds up all daily hours from Monday to Sunday and shows the employee’s total hours for the week.

Then use conditional formatting to flag overtime hours

This function can alert you if someone’s been assigned too many hours by highlighting cells if weekly hours cross a certain number.

• Go to Format > Conditional formatting.
• Choose the cells you want the rule to apply to.
• Where it says Format Cells if…, choose Greater than and enter the maximum amount of shifts or hours an employee can work in the Values or Formula box.
• Choose red in the highlight options under Formatting style and click on Done.

Risks of using Sheets for calculating hours and overtime

• One overwritten formula, wrong cell reference, accidental edit, or typo can mess up the calculations for total hours or overtime without anyone noticing. As a result, an employee might be over-scheduled, under-scheduled, or pushed into overtime because their hours were calculated wrongly.
• Conditional formatting can flag when someone crosses into overtime, but it’s easy for managers to overlook highlighted cells and accidentally approve overtime shifts, which can increase labor costs.

Scheduling software can help avoid these risks by calculating employee hours based on their clock-in and clock-out data. On Buddy Punch, you can also set up overtime rules so that the tool automatically flags it anytime an employee goes into overtime.

Multi-location scheduling

When you’re scheduling your team members across multiple locations, you need to ensure each site has enough people on every shift. But it’s easy to miss the mark when there’s no single view that shows coverage across all locations at once.

Having a different tab for each location’s schedule — with a master overview tab that pulls information for all locations into one place — can address this challenge.

• In the overview tab, use =IMPORTRANGE(“SPREADSHEET_URL”,“SHEETNAME!FIRSTCELL:LASTCELL”).
  • To get the spreadsheet URL, click the URL at the top of the page, press Ctrl + C or Cmd + C, then paste it into the formula.
  • Replace SheetName with your sheet name and the cell range with the cells you want to copy the data from. This will import the data from your chosen spreadsheet.
• Then use the COUNTIF formula to show the headcount per shift, per location. This helps you spot coverage gaps without needing to visit every tab separately.
  • In the cell where you want to display the headcount, type =COUNTIF(LOCATION!FIRSTCELL:LASTCELL,“Morning”). This will show you how many people are assigned to work at a location in the morning.

Risks of using Sheets for multi-location scheduling

• Broken sheet connections can break your master schedule. IMPORTRANGE links your overview tab to other spreadsheets or tabs. If access permissions change or any file is deleted or renamed, the overview tab won’t be able to pull the data accurately. This will leave you at risk of undercoverage.
• Managing multiple tabs or files also means an increased burden of access permission management. Permissions for each tab have to be managed separately, and there’s always the chance managers may accidentally edit the wrong location’s schedule. This can break schedules not only for their own team, but for other teams too.

Buddy Punch simplifies multi-location scheduling by letting you assign locations to shifts, so employees know when they’re working and where. You can easily view the consolidated schedule for different locations in the calendar, without having to manually link different schedules.

Role-based scheduling 

You need to ensure only qualified staff get assigned to certain roles, and that every shift has a minimum number of workers in these roles. Use the named ranges function to make sure that only qualified employees are shown when scheduling.

• Create a tab that contains details about employees’ roles. List their name in column A and roles they’re qualified for in subsequent columns.
• Use data validation to ensure all roles are named consistently.
  • On the toolbar, select Data > Data validation > Add rule.
  • Create a dropdown that includes every role within your organization. Type them all in — for instance, server, host, chef — then select Done.
• In your schedule spreadsheet, go to Data > Named ranges, then select the cells or column where you want to assign roles for each shift.
• Enter the role you need to assign — for instance, server or host — and hit Done. The cells will now show a dropdown. You can select the role when assigning shifts and clearly see who is filling each role.

Risks of using Sheets for role-based scheduling

• A dropdown can limit scheduling choices to qualified employees only, but it can’t account for role hierarchy or task priority. For example, it can’t tell you to use a junior employee for a standard shift and reserve senior staff for roles that need higher skills. That still requires manual oversight.
• If someone gains a certification or changes roles, the roles tab has to be manually updated. This is an ongoing job that can quickly fall through the cracks.

On Buddy Punch, you can use the positions function to identify an employee’s role or job function. You can then assign employees to different shifts based on their positions.

Tracking shift swaps

Regardless of how effectively you build schedules, sometimes, employees will need to trade shifts. Use Google Sheets to build a simple system for shift swapping.

In your scheduling spreadsheet, use a separate tab for swap requests. Include columns like:

• Date of request
• Requesting employee
• Original shift
• Proposed swap with (employee name)
• New shift
• Status (dropdown: pending, approved, declined)
• Manager notes

You can let employees swap shifts and edit the schedule themselves or require a manager to approve the swap and update the sheet. Limiting edit access to managers will keep your sheet protected more strongly.

If a manager needs to approve a swap, add a checkbox column that the other employee — the one who’s accepting a swap — must tick to confirm they’ve seen the change and can work the new shift.

Capture unconfirmed swaps on time

Use conditional formatting to flag shifts that haven’t been confirmed.

• Right click on any cell and select View More Cell Actions > Conditional formatting.
• Under Apply to range, choose which cells you want the rule to apply to. This will be the column with the checkboxes.
• In the Format Cells if dropdown, choose Is equal to.
• Under Value or formula, type in FALSE.
• Under Formatting style, select amber as the highlight color, then hit Done. This will highlight the cell in amber if it hasn’t been checked.

Risks of using Sheets to track shift swaps

• Employees need edit access to submit a swap request or tick the checkbox. This increases the risk of accidental changes to the schedule.
• The schedule also needs to be manually updated for both workers once a swap has been approved. If only one person’s shift gets updated but not the other, there’s nothing to flag the mismatch, and this could lead to double-booked shifts or coverage gaps. Caleb John, Founder and Director of Exceed Plumbing & Air Con, shares what happened when “two technicians ended up assigned to the same after-hours shift on the same night because someone updated one cell without checking the row above it.”

“Nobody caught it until a 10 p.m. callout came in and both technicians showed up to the same job while another suburb had nobody available.” 

Buddy Punch allows employees to trade shifts with each other without exiting the tool. You can choose to let them swap directly without manager approval, or require a manager to approve before a swap is finalized. If an employee can’t make their scheduled shift, they or their manager can also simply open that shift up for cover. You can select who should receive the cover request, and when an employee accepts it, the schedule is updated automatically.

Tracking PTO

When creating shifts, you need to know when employees are on time off so you don’t schedule someone for a shift when they’re unavailable. Use Google Sheets to track approved PTO on a separate tab, then use conditional formatting to mark PTO days on the schedule.

• Start by creating a new tab and name it “Employee PTO requests.” Include:
  • Employee name
  • Leave start date
  • Leave end date
  • Status (applied/approved/rejected)
• When creating the schedule, check the PTO tab for approved leaves. If an employee is on leave on a day of the schedule, type PTO in their row for that day.
• Set up conditional formatting to highlight every cell that contains PTO, so you can clearly see leave days in the schedule.
  • Go to Format > Conditional formatting.
  • Where it says Format cells if…, select Text is exactly and type PTO.
  • Choose red in the highlight options under Formatting style.
  • Hit Done.

Now any cell where you’ve entered PTO will be highlighted, letting you quickly see who’s on leave and when.

Risks of using Sheets for tracking PTO

• Managers need to keep switching back and forth between tabs to accurately reflect PTO in schedules, which can be time consuming and is prone to errors.
• Google Sheets can track PTO requests, but can’t handle the approval process. Requests can easily be missed, delayed, or informally approved without being properly recorded.

In Buddy Punch, employees can set their own availability. Managers can see when employees are unavailable and won’t be able to publish a schedule if it conflicts with someone’s availability. If a manager tries to schedule a shift for when an employee isn’t working, they’ll get an error and will be unable to publish the schedule until it’s fixed.

Schedule smarter with Buddy Punch

Buddy Punch helps you keep schedules running smoothly, with features like:

• Clock-in/out reminders: Automatically remind employees to clock in at the start of their shift or clock out when it ends.
• Limit early/late punches: Prevent employees from clocking in too early or staying clocked in too late to control overtime and labor costs.
• Availability tracking: Let employees update their availability so you can build schedules around when they’re actually able to work.

Contributors

• Akirashanti C. Byrd, CFE, Co-founder of Curl Centric
• Khris Steven, Founder of KhrisDigital
• Caleb John, Founder and Director of Exceed Plumbing & Air Con