How to create a staff roster in Google Sheets

Written by Roster Flow. Published 20 June 2026.

The thing that makes Google Sheets good for a roster is also the thing to be careful with: everybody can open the same file. Nobody works from last week’s attachment, and a change shows up on every phone within seconds. But a file the whole team can open is a file the whole team can read, so the layout below keeps the roster people need to see apart from the rates and notes they do not. It follows the same idea as the weekly roster in Excel: one row for every shift, with the totals and the grid built from those rows, so nothing has to be added up by hand.

Before you open a new sheet

Have these ready, because a roster built without them is a roster built twice:

• The team list, with each person’s role, employment type and agreed hours
• Availability for the week, and any leave already approved
• What you expect the week to look like: bookings, deliveries, events and the busy hours of each day
• The rostering rules in the award or agreement, such as minimum engagement, breaks and the time between shifts
• A Google account for yourself, and the email address each team member will use to open the roster

Use two files, not one

Most roster sheets start as one file and end up with pay rates, phone numbers and private notes sitting a click away from the whole team. Start with two instead. The manager’s file holds everything. The team’s file holds only the week’s grid, pulled across from the manager’s file by a formula, so it is always up to date and never holds anything it should not.

In the manager’s file, rename the first tab Team, then add tabs called Shifts and Week with the plus button at the bottom left. On the Shifts tab, put the headings in row 1. The formulas below assume date in column A, day in B, name in C, role in D, start in E, finish in F, unpaid break minutes in G, hours in H, label in I and cost in J. On the Team tab, put name in A, role in B, employment type in C, agreed hours in D, hourly rate in E and rostered hours in F.

Enter times Sheets can add up

Select columns E and F and choose Format, then Number, then Time. Type times with a colon, 6:30 or 18:30, and Sheets stores them as a fraction of a day, which is what lets one be taken from another. A time typed as 6.30 is just a number, and 6.30pm is text, and neither will add up. Format column A as a date, and column G as a plain number of minutes.

Fill whole columns with one formula

In Excel you write a formula in the first row and drag it down. In Google Sheets, ARRAYFORMULA lets one formula in row 2 fill the whole column, including rows you have not added yet. Put this in H2 to work out the paid hours of every shift:

=ARRAYFORMULA(IF(E2:E="","",MOD(F2:F-E2:E,1)*24-G2:G/60))

The IF leaves a row blank until it has a start time. Finish minus start gives the length of the shift as a fraction of a day, and MOD(…,1) fixes the shift that finishes after midnight, where the finish is earlier in the day than the start. Multiplying by 24 turns the fraction into hours, and the break comes off in hours, not minutes. Format column H as a number with two decimal places.

Two more fill the day and the label. In B2, =ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"ddd"))) turns each date into Mon, Tue and so on, so the day can never disagree with the date. In I2, =ARRAYFORMULA(IF(E2:E="","",TEXT(E2:E,"h:mm")&" to "&TEXT(F2:F,"h:mm"))) builds the label the grid will show. One rule comes with array formulas: type nothing else in those columns below the formula, or Sheets shows an error saying the result would overwrite data.

To estimate the cost of each shift, put =ARRAYFORMULA(IF(C2:C="","",H2:H*VLOOKUP(C2:C,Team!A:E,5,FALSE))) in J2. It looks up each person’s rate on the Team tab. Treat the total as an estimate: most awards pay weekend, evening, public holiday and overtime hours at different rates, and a single rate per person will understate them. The weekend and public holiday hours calculator splits out those hours, and the award pay rates pages show the rates the award sets.

Total each person’s week

On the Team tab, put this in F2 and copy it down for each person, so their rostered hours come straight from the shift rows:

=SUMIFS(Shifts!H:H,Shifts!C:C,A2)

The total changes the moment a shift is added, moved or deleted. If you want to check a week by hand, the work hours calculator does the same sum, and the decimal hours converter turns 5 hours 15 minutes into 5.25.

Build the week grid

On the Week tab, put the names down column A from row 2 and Mon to Sun across row 1 from column B. This formula, in B2 and copied across and down, lists each person’s shifts for the day:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Shifts!$I:$I,Shifts!$C:$C=$A2,Shifts!$B:$B=B$1)),"")

FILTER finds the shifts that match the name and the day, TEXTJOIN puts them in one cell, and IFERROR leaves the cell empty on a day off instead of showing an error. Because the grid matches on the day name, keep one file per week: when the week is done, choose File, then Make a copy, name the copy for the next week and change the dates. The formulas come across with it.

Build the checks in

A sheet can warn you before the roster goes out. Use Format, then Conditional formatting, choose Custom formula is, and pick a fill colour you will notice:

• Too many hours. On the Team tab, apply =F2>D2 to F2:F, so a total above the person’s agreed hours stands out. For a full-time employee, the National Employment Standards set a maximum of 38 hours a week plus reasonable additional hours.
• Shifts too short. On the Shifts tab, apply =AND(H2<>"",H2<3) to H2:H if the award you checked sets a three hour minimum engagement. Use the figure from your own award.
• Double booking. Apply =AND($C2<>"",COUNTIFS($C$2:$C,$C2,$A$2:$A,$A2)>1) to C2:C to highlight anyone rostered twice on the same date, then check whether it is a genuine split shift or a mistake.
• Names that do not match. Select C2:C, choose Data, then Data validation, add a rule and pick Dropdown (from a range) with Team!A2:A, so a shift can only go to somebody on the team list.

Finally, protect the formula columns so nobody types over them by accident: select the column, choose Data, then Protect sheets and ranges, and limit who can edit it.

Share the roster without sharing everything

Protecting a range stops people changing it. It does not stop them seeing it, and anyone who can open a file can read every tab in it. That is why the team gets a second file. Create a new spreadsheet called something like Team roster, and in A1 of its first tab enter:

=IMPORTRANGE("paste the manager file’s link here","Week!A1:H40")

The first time, Sheets asks you to allow access between the two files. After that, the team file shows the Week tab and updates when the manager’s file changes, with no rates, phone numbers or notes in it. Then share it:

• Click Share, add each team member’s email address and set them as Viewer, so they can read the roster but not change it
• Leave General access as Restricted, rather than Anyone with the link, so a forwarded link does not open the roster to strangers
• Avoid File, Share, Publish to web for a roster, which puts a copy online for anybody who finds the address
• In the manager’s file, choose Tools, Notification settings, then Edit notifications to get an email when something changes, and use File, Version history to see who changed what

People open the roster in the Google Sheets app on their phone, which shows the grid well enough for checking a week. For the noticeboard, freeze the top row and the names with View, then Freeze, and print the Roster tab in landscape with the scale set to fit to width. Put the week and the date it was published in the top cell, so an old printout is obvious.

Google Sheets or Excel?

Both will run a small team’s roster well. The difference is mostly in where the file lives and who works on it.

Where a shared sheet starts to strain

A good sheet can run a small team for a long time. The strain shows in the same places for most businesses. Availability and swap requests arrive by message and have to be typed in. Somebody asks why they cannot see the roster because they signed in with a different Google account. Hours actually worked are written somewhere else, and at the end of the week they are retyped into payroll. None of that is a formula problem, which is why a better formula does not fix it.

If that sounds familiar, the guide to moving off a spreadsheet covers the move. Roster Flow keeps what works in this layout, one record per shift and totals that add themselves up, and adds what a shared sheet cannot: availability and leave from staff phones, the roster published to everybody at once, clock ins that become timesheets, and approved hours sent to Xero, MYOB or a CSV file.

A quick checklist

• Start and finish are formatted as time and typed with a colon
• Hours use MOD, so shifts past midnight add up
• Weekly totals come from the shift rows, not typed in
• Checks flag long weeks, short shifts, double bookings and unknown names
• Pay rates stay in the manager’s file, and the team sees only the roster
• The team file is shared with named people as viewers, not with anyone who has the link
• The printout shows the week and the date it was published

Sources

• Google Docs Editors Help: ARRAYFORMULA
• Google Docs Editors Help: IMPORTRANGE
• Google Docs Editors Help: conditional formatting in Google Sheets
• Fair Work Ombudsman: hours of work

Back to the blog