Delivery route planning in Excel: how teams do it and where the limit is

Why the daily round is still planned in a spreadsheet

Excel is the starting point for almost every delivery operation that has outgrown the "two couriers and a phone" stage. The reason is simple: the tool is already there, it costs nothing extra, and every employee knows how to use it. In the morning the dispatcher exports addresses from the accounting system, sorts them by district, splits them between drivers — and by nine o'clock the round is ready.

The second argument is flexibility. You can add any column your process needs: cargo type, entrance code, a "call an hour ahead" flag, a note saying "entry from the courtyard, barrier gate". No external system will adapt to the quirks of your operation as fast as an empty column does.

The third argument is that it speaks the same language as everyone else. Customers send orders as spreadsheets, the warehouse hands over shipments as spreadsheets, accounting reconciles closed jobs against a spreadsheet. While volumes stay small, manual planning really is cheaper than automation, and changing a working process just to look modern makes no sense. The question is not whether Excel is bad in itself, but at what volume it stops coping and what a late switch ends up costing.

What a working route sheet looks like

If you plan in a spreadsheet, plan with discipline. A sheet that actually helps, rather than creating the appearance of order, usually holds this set of fields:

• Order number — the same one used in your accounting system, otherwise the evening reconciliation becomes a job of its own.
• Address in normalised form — city, street, building, block, flat or office in a single format. "5 Main St Apt 2" and "Main Street, building 5, apartment 2" are different strings both for sorting and for geocoding.
• Coordinates — latitude and longitude in separate columns. Without them the sheet cannot be handed to any mapping service.
• Time window — start and end of the interval as separate fields, not free text saying "after lunch".
• Service time at the stop — how many minutes you allow for handover, signature, stairs, installation.
• Driver and visit order — the two columns that turn a list into a route.
• Contact and comment — the recipient's phone number and everything the courier should know in advance.
• Status and actual time — filled in at the end of the day; this is your future statistics.

It pays to split the data across three sheets: an address directory with coordinates and service norms (filled in once and reused), the sheet for the current day, and a facts sheet that collects the results. That way you are not retyping the same rows every morning — known addresses are pulled in automatically and only new ones need manual work.

Techniques that save the dispatcher time

A spreadsheet has several mechanics that noticeably speed up the morning allocation. They do not solve the routing problem, but they remove routine work and a share of the typing errors.

• Data validation and drop-down lists for drivers, zones and order types — they eliminate the typos that later make a filter miss half the rows.
• Lookups against the directory (VLOOKUP or XLOOKUP) — coordinates, zone and standard service time are pulled in automatically for any address seen before.
• Conditional formatting — highlight jobs with a narrow window, overdue ones, and those missing coordinates or a phone number. The eye catches the problem before departure rather than after.
• A pivot table — a quick answer to how many stops and how many planned minutes each driver has been given. It is the only way a spreadsheet can show you an uneven workload.
• Sorting by "zone + window start" — a crude but workable heuristic: group by geography first, then order by time inside each group.
• Export to CSV — so you can push the stop sequence into a mapping service and see what the plan looks like on a map.

It is also worth setting a rule for file versioning: one file per day with the date in its name, and one person responsible for edits. Half of the morning arguments in a dispatch office are not about the route at all — they are about who has the current version of the file open.

Where the hard limit sits

Excel stores and filters data brilliantly, but it knows nothing about roads and it does not choose the visit order for you. Sorting by district is grouping, not routing. The final sequence of stops is still built by a person, and that is where the constraints appear — the kind no macro and no amount of tidiness can fix.

• Combinatorics. The number of possible visit orders explodes with every extra stop. At ten addresses an experienced dispatcher can still pick a sensible sequence; at twenty they take the first workable option rather than the best one.
• Time windows. As soon as half the jobs come with intervals, geographic grouping starts to fight with the timetable. Holding both address proximity and slot boundaries in your head at once is close to impossible.
• Constraints. Weight, volume, restricted zones, driver qualification, a city-centre permit, a mandatory return to the depot — every condition rules out a set of options, and by hand you have to remember them all.
• Replanning. Any change during the day — a cancellation, an urgent order, a broken-down van — devalues the morning plan. You cannot rebuild the whole sheet in ten minutes.
• No feedback loop. The spreadsheet does not know where a driver is right now or what has already been completed. Until the courier calls, the plan and reality live separate lives.

The practical guideline is simple: while a day holds a handful of drivers and a few dozen addresses without hard intervals, a spreadsheet holds up. Once time windows, constraints and daily changes on the fly appear, the quality of the plan stops depending on the data and starts depending on who happens to be on dispatch duty that day.

The hidden cost of manual planning

Excel looks free because its cost never appears as a separate budget line. In reality that cost is spread across several items, and each of them can be measured.

• Dispatcher hours. The daily allocation eats time every single morning, and that time does not scale: twice the orders means twice the manual work.
• Extra mileage. The gap between a "workable" sequence and a genuinely short one is paid for in fuel, vehicle wear and paid driver hours.
• Dependence on one person. The logic of the allocation lives in a single employee's head. Their holiday or resignation is an operational risk, not an HR matter.
• Version errors. Two open files, a lost edit, yesterday's sheet sent to a courier — routine failures that produce failed deliveries out of nowhere.
• No data to manage by. Actuals go into the sheet manually and not always, so there is nothing to calculate real service time, on-time share or the reasons for missed visits from.
• Nothing to show the customer. Answering "where is my order" means phoning the courier.

That last point deserves a note of its own: it is the one that most often triggers the switch. Internal inconvenience is tolerated for years, but a customer's demand to see the status themselves simply cannot be met with a spreadsheet.

When to move on and what changes

The signals that a spreadsheet is exhausted usually arrive as a bundle: the morning allocation takes more than an hour, orders now come with time windows, drivers call the dispatcher about every change, nobody can explain in the evening why three stops were not closed, and customers are asking for live status.

Make the move gradually, without breaking everything in a single day:

• Clean up your data. Normalised addresses, coordinates and realistic service times per stop are what you will carry from the spreadsheet into any system.
• Record your current numbers. How long planning takes, mileage per shift, stops per driver, the on-time share. Without a baseline you will not know what changed.
• Keep Excel as an exchange format. A spreadsheet remains a convenient way to receive orders from a customer and to export data — what changes is not the exchange format but the place where the route is built.
• Start on part of the flow. One city, one team, one week running in parallel with the usual process.

Here is what replaces the manual allocation: orders are received and assigned to drivers in a single screen, the visit order is calculated by an algorithm that accounts for delivery windows and constraints, routes are visible on a live map, the driver works in a mobile web interface with photo reports and checklists without having to install an app, and the customer follows statuses in a personal account. In itlogist, route optimization is built on the OR-Tools engine — Routing in itlogist.

None of the data has to be retyped: the system exchanges data with 1C, AmoCRM, Bitrix24 and Excel. Rollout takes 7 days, with no long-term contracts, and the product is designed for teams of 5 to 100 field staff — meaning you can test the change on a live order flow rather than on paper.