How to spot the limits of Excel for planning technicians

Excel is usable for planning technicians for as long as the rules of the plan match the real operation. A technician-by-day grid is quick to create. Its reliability, however, depends on whether the spreadsheet recognises an overlap, works with absences and lets you trace which change applies.

• Excel can display, filter and calculate a plan. You have to describe the operational rules to it with formulas, validations and a way of working.
• The limit is not set by the number of technicians. What decides is the number of concurrent orders, changes and people who write into the plan.
• The version history of an online file helps when restoring changes, but it does not replace the history of a specific order.
• This article serves as a spreadsheet audit. For choosing a tool there is a separate comparison of ServisBook and Excel.

What Excel is genuinely good at

Excel is a quick way to create an overview. You put technicians in rows and days in columns, and adapt the layout to your own habits. Changing the structure does not require a vendor, and common totals or filters can be added directly in the file.

The spreadsheet also calculates well. It can add up days worked, count team staffing and use conditional formatting to distinguish weekends, holidays or invalid values. If you need a ready-made basis for shifts, use the free shift planning template.

Excel's strength is freedom. At the same time, that is its limitation: the spreadsheet does not know what a technician, an order or an absence means until you translate that logic into its structure and rules.

Four checks that need a rule of their own

1. Overlapping assignments

Two entries of the same name are not automatically a conflict for Excel. The control formula has to know whether you plan by days, half days or hours and where the starts and ends of orders are stored. After adding a new column or changing the layout, you need to verify that the check still covers the whole range.

2. Holidays, sickness and public holidays

Absences have to be in the same plan or in a clearly linked source. If they are kept in another file, the check depends on whether the planner opens it and whether they are working with the current version. The list of public holidays has to be maintained in the same way; an empty spreadsheet does not know their calendar on its own.

3. Qualifications

A free technician is not necessarily a suitable technician. A list of qualifications can be kept on a separate sheet and checked against the type of work. The rule, however, also has to allow for the validity of qualifications and for the fact that a company may name qualifications differently depending on the trade.

4. Changes to the plan

Online Excel offers co-authoring and version history. That helps find out what the file looked like earlier, but it is not an automatic timeline of a single order. A phone agreement, the reason for a move, an attachment and a technician's note stay outside it unless you create your own procedure for them.

What the spreadsheet should remember and what a person should

If any of these questions is answered from memory or from another unlinked file, the spreadsheet is not the single source of the plan. It may still work, but its reliability depends on the availability of a specific person.

Auditing your own spreadsheet

Go through the following points over your current file:

1. Enter a model overlap for one technician and verify that the spreadsheet visibly flags it.
2. Change the range of the plan or add a technician, and check that the rules have extended to the new row or column as well.
3. Verify a holiday and a public holiday without opening another file.
4. Find a technician for a specific type of work using only the data in the plan and the linked lists.
5. For one moved order, trace who made the change, when and why.
6. Ask a second person to describe next week from the spreadsheet without follow-up questions to the original planner.

The result is not a score. Each step that cannot be done simply shows a rule that is for now held by a person outside the spreadsheet. You can then decide whether to add it to Excel, simplify the procedure or move that part of the work elsewhere.

When Excel is still enough

Excel serves well if one person manages the plan, there are few changes and someone keeps checking the spreadsheet's rules. It also remains suitable for fixed schedules in which appointments almost never move and qualifications do not differ between orders.

There is no reason to replace it either where the company does not plan people in time, or where planning already lives inside its own business system. The spreadsheet can continue to work as a calculation and reporting tool even if the operational plan itself is created elsewhere.

What does not belong in a planning spreadsheet audit

Keeping order records is a different task from checking capacity. It deals with the customer, status, attachments, work logs and a traceable history; it is described in detail in the article Order records in Excel.

Agreeing an appointment with the customer is a separate task as well. The spreadsheet can show free time, but the rules of communication, offering several options and confirming the appointment have to be handled by your own procedure or by a tool designed for appointment offers.

What to do after the audit

First fix the rules that are missing from the spreadsheet and designate one binding place for the plan. If it turns out that even then the planner has to do most of the checks by hand, compare your requirements with the planning board and capacity and skills.

The decision should be based on your own operation. The direct differences, the price and the situations in which not to buy ServisBook are summarised in ServisBook vs Excel.

Frequently asked questions