Skip to content
FitRank

Scheduling basics

Automated employee scheduling in Excel: how far it goes and where it breaks

By Yaseen Khatib, , 4 min read

Automated employee scheduling in Excel: how far it goes and where it breaks

Most teams start scheduling in Excel, and for a good reason: everyone has it, everyone can read a grid, and it costs nothing extra. The question is how much of the work Excel can actually do for you, and at what point you are the automation. Here is an honest map, from the parts that work well to the parts that do not.

What Excel automates well

All of that is in the free employee schedule template on this site, with weekly and monthly sheets, so you do not have to build it.

Filling the grid automatically

The step people mean by automated scheduling is the fill: Excel picking who works when, rather than you. Excel can do a version of this with the Solver add-in. You model one binary cell per person per shift per day, a constraint per slot that the cells add up to the number of people needed, a constraint per person that the cells do not exceed their maximum, and a zero for every day a person is unavailable, then ask Solver to minimise unfilled slots or the spread of hours.

It works for small teams. The standard Solver that ships with Excel accepts up to 200 variable cells (Microsoft support: define and solve a problem by using Solver). Six people, three shifts a day and seven days is 126 variables, which fits. Ten people is 210, which does not, before you have added a single rule about rest between shifts or who can take which shift. Beyond that you are into paid Solver editions, VBA, or writing the fill yourself.

If you want the fill without the model, the free automatic schedule generator does the small-team version in your browser: people, shifts, unavailable days and a weekly maximum in, a filled week out, with a CSV you can paste into the template.

Where it breaks

What to use instead, honestly

For a shift grid with swaps, time off and notifications, a staff scheduling app is the normal next step after Excel, and there are many. Which word to search for matters more than it should; we explain why in staffing vs scheduling.

FitRank is not a shift grid, and we would not sell it to you as one. It is for the last two problems on the list: it ranks your people for an open task or role with rules in code, a probability for every typed answer and a reason that cites the facts behind it, and it records what the manager did with each suggestion. It is in pilot with a small number of companies, it was built for project and services work rather than hourly rosters, and a manager makes every final call. If that is the problem you have, read how FitRank decides. If your problem is Tuesday night coverage, start with the template above; it is free and it will take you further than you expect.

More from the blog

Be one of the first teams to staff with FitRank

We're taking on a small number of pilot companies. Pre-register for early access, or talk to us about an annual licence.