Scheduling basics
Automated employee scheduling in Excel: how far it goes and where it breaks
By Yaseen Khatib, , 4 min read

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
- Hours from shift codes. Keep a Shifts sheet with each code's start, end and hours, and a SUMPRODUCT over COUNTIF gives each person's hours for the week from the codes you typed. Night shifts that cross midnight are a MOD away.
- Coverage. A COUNTIF per shift per day tells you how many people hold each shift each day, so an empty Tuesday night shows up as a zero before the week starts.
- Validation. A drop-down list of codes stops typos such as a lowercase n that no formula will count.
- Highlighting. Conditional formatting turns a zero in coverage red and a person over their hours orange. This is where most of the value is: the sheet does not decide anything, but it makes every problem visible.
- Dates. One month-start cell can drive every day header and the weekend shading of a monthly grid.
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
- Rules multiply. Availability and a maximum are easy. Minimum rest between a night and the next morning, no more than N nights in a row, two qualified people on every shift, fairness of weekends over a quarter: each one is another block of constraints, and together they stop fitting in a sheet anyone else can maintain.
- One file, many hands. The schedule is a shared document that several people edit, and Excel's answer to that is a copy with a date in the filename. There is no reliable record of what was published, when, and what changed after.
- No history of decisions. The sheet shows the schedule, not why it is this way. When someone asks why they got four nights again, the answer is in the manager's head.
- No reasons. Even a Solver fill gives you a grid, not an explanation. It cannot say this person was chosen because they have the skill and are free on the start date, and that one was left out because they are on leave.
- Skills are a search. The moment the question changes from who is free to who is right for this work, Excel offers filters on a column of skill names, and the column is as good as the last time somebody updated it.
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.