Create a staff roster in Excel with Microsoft Copilot

Yes — with an important caveat about what “build” means. Microsoft Copilot in Excel is grounded in the data in your worksheet. It reads the table you point it at and responds to natural-language prompts in a side pane: it can analyse your data, surface trends, add formula columns, sort and filter, highlight cells with conditional formatting, and create charts or PivotTables. That makes it excellent at shaping and checking a roster.

What it is not is a from-scratch constraint solver. Copilot won’t reliably invent a fair weekly schedule that balances everyone’s availability, hours, and skills the way a purpose-built AI rostering engine does — that’s a different class of problem. The realistic workflow is: you set up the structure and the rules, Copilot helps you populate and pressure-test it, and you keep final judgement. Used that way, it genuinely saves time.

Two things have to be in place before Copilot can touch your roster:

That second point does most of the heavy lifting. A formatted table gives every column a clear header and a defined range, which is exactly what Copilot needs to understand “hours”, “role”, or “Monday” when you mention them in a prompt. Skip it and Copilot will either ask you to format the data first or give unreliable answers. If you’re on a plan without the Copilot add-on, use the ChatGPT-in-Excel approach instead — it works on any Excel version because the AI lives outside the spreadsheet.

Start with a clean roster layout on one worksheet — one row per employee, one column per day — then convert it to a table.

Keep each day column consistent — either a shift code (AM, PM, OFF) or a time range (9:00–17:00), never both. Then add a second small table for your shift definitions so hours can be calculated rather than typed:

Select each block and press Ctrl+T to convert it to an Excel table. Give the tables sensible names (Table Design → Table Name) like Roster and Shifts — Copilot references named tables far more reliably than “the data in columns B to I”.

Open the Copilot pane from the ribbon (the Copilot button on the Home tab). With your cursor inside the Roster table, describe what you want in plain English. Copilot works conversationally, so you can refine across several prompts rather than getting everything in one go.

Copilot will propose changes — new formula columns, filled cells, or a suggested layout — and show you a preview before applying anything. Treat its first pass as a draft. Because Copilot isn’t a true scheduling optimiser, expect to nudge it: it may over-allocate a popular shift or miss a coverage gap on a quiet day. That back-and-forth is normal, and it’s still faster than dragging cells by hand. If you’d rather start from a template with fairness logic already built in, our free AI roster generator drafts a balanced roster you can export to a spreadsheet.

This is where Copilot in Excel is genuinely strong. Once a draft roster exists, use the pane to check and polish it:

Copilot can also create a quick chart of hours per employee or a PivotTable summarising cost by role — useful when you’re forecasting labour costs from a roster. Always eyeball the result: Copilot applies exactly what you ask, so a vaguely worded prompt produces a confidently wrong answer. A 30-second human scan for fairness and fatigue still matters.

Here’s the hard limit, and it’s the most important part of this guide. Copilot is grounded in your spreadsheet — it has no live knowledge of Australian Modern Awards. It does not know the current base rates, casual loading, weekend and public-holiday penalties, evening loadings, minimum-engagement rules, or mandatory break requirements for your industry. If those figures aren’t already in your workbook (correctly), Copilot cannot apply them, and it can’t tell you when a roster breaches the law.

This is exactly the gap that RosterElf’s automatic award interpretation closes: it applies the correct rates and penalties as you build the roster, so cost and compliance are calculated live rather than checked after the fact. If you want to keep working in a spreadsheet for now, our award rates Google Sheets tool gives you a rates reference you can pull real figures from — but the numbers still need confirming against Fair Work each financial year.

The two most common ways to bring AI into an Excel roster are Microsoft Copilot and ChatGPT. They solve the problem from opposite directions:

In practice, many managers use both: ChatGPT (or our AI roster generator) to draft a balanced first version, then Copilot inside Excel to total hours, highlight overtime, and format it for the team. Copilot’s advantage is that it never leaves your spreadsheet; ChatGPT’s is that it works on any plan and is stronger at generative first drafts.

Copilot makes a genuinely useful assistant for a staff roster in Excel — as long as you use it for what it’s good at. Structure your data as tables, let Copilot draft, analyse, and format, and keep a human eye on fairness and coverage. But remember its ceiling: it can’t optimise a complex schedule, it can’t interpret awards, and it can’t turn approved hours into pay.

Once your roster drives payroll, penalties, and compliance, a spreadsheet — Copilot-assisted or not — starts to carry real risk. That’s the point where purpose-built rostering software with live AI rostering, award interpretation, and payroll integration pays for itself, because the compliance and cost calculations that Copilot leaves to you are handled automatically.