A weekly roster template in Google Sheets lets you plan a full week of shifts for your team on one screen, with drop-downs for each day, an automatic count of week offs and leave, and a check that warns you when someone does not get their proper days off. This guide explains a free template that does exactly that, using two tabs: Shift and Roster.
It is for shop and restaurant managers, call-centre and support leads, hospital and hostel supervisors, and any small business that works in shifts. You will see how the shift list is set up, how the roster and the Shift Wise Count read it, the COUNTIF and conditional formatting rules behind the checks, and how to adapt it to your own team.
🎥 Watch the Video Tutorial
Open the Free Weekly Roster Template
The link opens a view-only copy. Use File > Make a copy to save your own editable version to Google Drive.
What the Weekly Roster Template in Google Sheets Includes
| Part | Where | What it does |
|---|---|---|
| Shift list | Shift tab | Five shifts plus WO (week off) and L (leave), with start time, end time and working hours |
| Roster grid | Roster tab | Employee names down the side, Monday to Sunday across the top, a shift drop-down in every cell |
| WO and Leave columns | Roster tab | Count of week offs and leave days for each employee |
| Week-off check | Roster tab | Highlights an employee’s row in red when they do not have exactly two week offs |
| Shift Wise Count | Below the roster | How many people are on each shift, week off and leave for each day |
The Shift Tab
This tab is the master list of everything that can appear in the roster. It has four columns: Start Time, End Time, Working Hours and Shift.
| Shift | Start Time | End Time | Working Hours |
|---|---|---|---|
| 08:00 – 17:00 | 8:00 AM | 5:00 PM | 9 |
| 10:00 – 19:00 | 10:00 AM | 7:00 PM | 9 |
| 12:00 – 21:00 | 12:00 PM | 9:00 PM | 9 |
| 13:00 – 22:00 | 1:00 PM | 10:00 PM | 9 |
| 15:00 – 00:00 | 3:00 PM | 12:00 AM | 9 |
| WO | Week off | ||
| L | Leave | ||
You can change the timings, add a night shift or rename the codes. Whatever is in the Shift column becomes an option in the roster drop-downs.
The Roster Tab
Employee names go in column B, and the seven days run from Mon to Sun with the date under each day. The week should start on a Monday; the sample week runs from 25-Dec-23 to 31-Dec-23. Each cell has a drop-down with the shifts, WO and L. Week offs show in green and leave in purple, so the pattern is easy to read.
The WO and Leave columns at the right count each person’s week offs and leave days. For example, Cora Parsons has WO 2 and Leave 0, while Aleyna Leblanc has WO 2 and Leave 2 (leave on Tuesday and Friday).
The Two-Week-Off Check
The template has a conditional formatting rule: if an employee does not have exactly two week offs in the week, their whole row turns red. That makes it hard to publish a roster where someone works seven days or gets three days off by mistake.
The Shift Wise Count
Below the roster, the Shift Wise Count shows how many employees are on each shift, on week off and on leave for every day. It is the fastest way to spot a day that is short-staffed on the evening shift or has too many people off.
Worked example: on Monday the counts are 8 (08:00 – 17:00), 4 (10:00 – 19:00), 6 (12:00 – 21:00), 4 (13:00 – 22:00), 2 (15:00 – 00:00), 2 WO and 2 L. Together that is 8 + 4 + 6 + 4 + 2 + 2 + 2 = 28, which matches the 28 employees in the roster (rows 5 to 32). Every day’s column should add up to your headcount; if it does not, a cell has been left blank.
Formulas Behind the Counts
The template is ready to use, but these are the formulas you would use to build or check the same counts. The roster occupies C5:I32 with names in column B; the Shift Wise Count labels sit in B38:B44.
| What | Formula | Meaning |
|---|---|---|
| Week offs for one employee | =COUNTIF(C5:I5,"WO") |
Counts WO across Monday to Sunday in row 5 |
| Leave for one employee | =COUNTIF(C5:I5,"L") |
Counts L in the same row |
| People on a shift on a day | =COUNTIF(C$5:C$32,$B38) |
Counts how often the label in B38 appears in Monday’s column |
| Red row check | =$J5<>2 |
Custom formula: true when the WO count is not 2 |
To add the red-row rule to a copy of your own, select the roster rows, go to Format > Conditional formatting, choose Custom formula is, enter the formula and pick a red fill. Drop-downs are set under Data > Data validation with the criteria Dropdown (from a range) pointing at the Shift column on the Shift tab. If your drop-downs feel slow, see how to speed up Google Sheets drop-downs.
Useful Variations
- Total hours per employee: count the shift cells and multiply by 9, since every shift in the sample is 9 hours.
- One week off instead of two: change the 2 in the conditional formatting rule to 1.
- Minimum staffing alert: add a rule on the Shift Wise Count that turns a cell orange when it drops below your minimum.
- New shift: add a row on the Shift tab and a matching label in the Shift Wise Count.
How to Use It with Your Own Team
- Open the free template and choose File > Make a copy.
- On the Shift tab, adjust the shift timings and codes to match your business.
- On the Roster tab, type your employees’ names in column B.
- Enter the Monday date for the week you are planning.
- Pick a shift, WO or L for each person and each day from the drop-downs.
- Fix any row that turns red, then check the Shift Wise Count for each day.
- Share the sheet with your team as view-only so everyone sees the final roster.
Real-World Use Cases
- Retail store: a manager covers opening and closing hours with the 08:00 and 15:00 shifts and keeps weekends balanced.
- Customer support desk: a team lead uses the Shift Wise Count to make sure late shifts are never left with one person.
- Restaurant or café: an owner plans week offs so every employee gets two days and busy days stay fully staffed.
Best Practices
- Publish the roster a few days before the week starts so people can plan.
- Rotate late shifts fairly; scan each column so the same people are not always on 15:00 – 00:00.
- Record approved leave as L before assigning shifts, so you plan around it.
- Keep last week’s copy (duplicate the tab) as a record of who worked when.
- Check local labour rules on rest days and working hours. The template’s two-week-off check is a helper, not legal advice.
Limitations
This template plans one week at a time and does not track actual attendance, overtime or payroll. It does not stop someone being given a late shift followed by an early one. For monthly duty planning or attendance, look at our Employee Shift Schedule in Google Sheets, the Nurse Duty Roster Calendar, the dynamic shift scheduler with ARRAYFORMULA or the Weekly Attendance Sheet.
Related templates: Employee Shift Schedule in Google Sheets and Nurse Duty Roster Calendar in Google Sheets on NextGenTemplates.
If you are new to spreadsheets and want a guided path through formulas like COUNTIF and tools like conditional formatting, the learning paths on NextGenTemplates Academy are a good place to start.
Frequently Asked Questions
Is this weekly roster template free?
Yes. The link above opens a view-only Google Sheet; use File > Make a copy to get your own editable version.
What do WO and L mean?
WO is a week off and L is leave. Both are options in the drop-down alongside the five shifts.
Why has an employee’s row turned red?
Because they do not have exactly two week offs that week. Add or remove a WO until the row returns to normal.
Can I change the shift timings?
Yes. Edit the Shift tab. The values in its Shift column feed the drop-downs, so update the Shift Wise Count labels to match.
How many employees can the roster hold?
The sample has 28 employees. You can add rows; just extend the drop-downs, the WO and Leave formulas and the count ranges.
Does the week have to start on Monday?
The layout runs Monday to Sunday, so enter a Monday date as the first day of the week.
Wrapping Up
A weekly roster template in Google Sheets takes the guesswork out of shift planning: pick shifts from a drop-down, let the sheet count week offs and leave, and fix anything that turns red before you share it. Make your own copy and plan next week’s roster in a few minutes.
Open the Free Weekly Roster Template
📅 Last updated: October 2026
