How to plan routes with Excel and optimisation software?

Published on 7 April 2022 • Reading time: 6 min read

If you manage a small team of delivery drivers or field workers, it is tempting to use a spreadsheet like Excel or Google Sheets to organise their routes. You are already proficient with this type of tool, which also requires no initial investment. However, some unexpected constraints or a few extra customers can make your carefully prepared schedules unworkable…
Did you experience similar difficulties? These practical tips and advice should help you to plan the routes of your mobile workers efficiently, using a spreadsheet and route optimisation software.

Table of contents

• Does Excel do the job for planning routes?
  • The advantages and limits of Excel for planning routes
  • Excel and Google Sheets help to plan routes, but not to optimise them
• How to optimise routes from the data in your spreadsheet?
  • Case 1: You list your customers in an Excel file
  • Case 2: You list the tasks to be completed in an Excel file
  • Case 3: Your in-house software enables you to export data in Excel or Google Sheets format

Planning delivery or service routes using an Excel spreadsheet is an effective first step. To improve productivity, companies can combine this method with the use of optimisation software.

Does Excel do the job for planning routes?

When your company developed or re-internalised its delivery or field service management activities, Excel was an easy and quick solution to implement. But you spend more and more time adjusting your tables and formulas… and that’s not surprising!

The advantages and limits of Excel for planning routes

The software such as Excel or Google Sheets are very useful for listing and organising multiple data such as :

• The customer identification details (company name, company identification system, client number, name of manager, etc.).
• Their address or geographical location.
• Their contact details (e-mail, telephone, etc.).

These spreadsheets also include valuable features for linking cells, and therefore information used in several processes (orders, deliveries, invoicing, etc.). Things get more complicated when you have to manage :

• Irregular or unusual events (holidays, traffic jams, last-minute cancellations, etc.).
• A temporary or long-term increase in the number of workers.
• Different skills among employees (licenses, authorizations, more technical operations, etc.).

Do you manage a team of 2 or 3 drivers who each complete very regular routes? Why not, Excel might be enough. In any other case, the use of a route optimisation software in addition to your usual spreadsheet can save you a lot of time and money.

Excel and Google Sheets help to plan routes, but not to optimise them

Excel and Google Sheets can help you to organise your routes, by summarising all the relevant information available. But these tools do not help you to optimize your trips, i.e. to make the most of the logistics resources available. These systems, while useful, are simply not designed to do that.
In the best case, you are sure to have scheduled tasks for every customer to be delivered or visited. In the worst case, you are using far more resources than necessary:

• Number of kilometres unnecessarily travelled.
• Customers not served or overtime.
• Excessive fuel consumption.
• Increase in driver fatigue and stress.
• Increase in customer complaints, etc.

By associating a route optimisation software with your usual spreadsheet, you can achieve a new and much more ambitious objective: organise your resources in an optimal way, taking into account all the business constraints. In other words, software like AntsRoute finds the best possible solution for each route to meet the needs of your customers and your teams, while improving profitability.

How to optimise routes from the data in your spreadsheet?

To optimise your delivery or field service operation routes from the information summarised in your Excel or Google Sheets tables, the AntsRoute team has developed several solutions.

Importing an Excel file into the AntsRoute route optimisation tool.

Case 1: You list your customers in an Excel file

Many managers continue to manage their customer file with Excel. At the same time, they plan their routes “by hand”, using a navigation application such as Google Maps or Waze. If you are working with this method, there is no doubt about it: adopting a route optimisation solution in addition to Excel is going to save you a lot of time and energy!

To summarise in simple terms, optimisation software considers and calculates every possible scenario for you and then selects the best one. Instead of taking hours to predict what would happen if you assigned a particular task to a given worker, the software determines the most effective options in just a few minutes, thanks to powerful algorithms.
Then, once all these calculations have been made, AntsRoute gives you a complete roadmap for each field worker, available on the mobile application allowing to use the Waze or Google Maps navigation system.

To optimise your routes effortlessly with AntsRoute, simply import your customer database in .XLS, .XLSX. or .CSV format by following these instructions, or by contacting our support by phone.
Of course, if you later need to re-export your routes to Excel for invoicing, for example, the data can be transferred just as easily in the other direction!

Another option: you list your customers in a Google Sheets file

If your company uses the Google Workspace office suite (formerly G Suite), the transfer procedure is essentially the same. The only difference is that before exporting your Google Sheets file to AntsRoute, you must first connect these two applications with Zapier. There is no need to know anything about computer language with this no-code solution, and our support service will help you at every stage!

Validating data contained in an Excel file on AntsRoute.

Case 2: You list the tasks to be completed in an Excel file

Some companies go even further in their use of Excel, and include in the tables all the data relating to the tasks to be carried out:

• Dates or deadlines for delivery or field service operation.
• Type of services or deliveries ordered.
• Number and date of order.
• Expected duration.
• Time slot of passage.
• Comments about delivery, etc.

Again, you can optimise your routes by importing into AntsRoute :

• Either a list of tasks with a fixed date.
• Or tasks with a deadline for completion.

Depending on your business constraints, you can also transfer as many custom fields as you need into the AntsRoute software and application, in the order and form you want:

• Items to be selected from a list
• Checkboxes
• Numbers
• Text fields, etc.

If you’d like to find out how to plan routes using Excel and optimisation software, you can download our free template file.

Another option: You manage the tasks with Google Sheets

The import of a list with deliveries or visits built with Google Sheets is also possible, with some minor adjustments. The Help Centre is there to guide you step by step.

The construction of optimised routes with AntsRoute software.

Case 3: Your in-house software enables you to export data in Excel or Google Sheets format

Are you using customised software developed to meet your company’s specific needs? No problem, as you can normally export information in spreadsheet form.
In the same way, most business software includes this type of useful feature. This is the case of Quickbooks, Zoho, but also of the famous Sage suite.

Good to know: To further increase efficiency, our technical teams have developed direct integrations between AntsRoute and some business software such as Odoo or Must-G5.

The best way to measure the benefits of our route optimisation software is to try it out! Just try it out in addition to your usual spreadsheet, free of charge and without obligation, by subscribing here!