How to Plan Routes with Excel and Optimisation Software?
How to Plan Routes with Excel and Optimisation Software?
Published on 7 April 2022 • Reading time: 8 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?
- How to optimise routes from the data in your spreadsheet?
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.).
The advantages and disadvantages of planning routes with Excel.
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.
Comparison Table — Excel vs. Dedicated Route Optimisation Software:
| Criteria | Excel | Route Optimisation Software |
| Cost | Low or none (often already available). | Initial investment, but fast return on investment. |
| Ease of use | Requires solid knowledge of formulas and macros. | User-friendly interface with support and assistance. |
| Route optimisation | Manual, based on user experience. | Automatic, using advanced algorithms that factor in multiple constraints. |
| Handling unexpected events | Reactive, requires manual adjustments. | Proactive, with automatic re-calculation of routes in case of disruptions. |
| Route visualization | Limited, needs external tools. | Integrated mapping with real-time tracking. |
| Scalability | Suitable for small businesses with simple needs. | Designed to handle large volumes and complex constraints. |
| Analysis and reporting | Basic, requires advanced skills to build reports. | Detailed reports generated automatically for performance tracking. |
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.
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.
Importing an Excel file into the AntsRoute route optimization software.
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 carried out 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 :
Optimising routes in AntsRoute from an Excel file.
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 scheduling of optimized routes in AntsRoute from an Excel file.
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 with Quickbooks, Zoho, but also the famous Sage suite, which explains in this page how to export data in Excel format.
☝️ Good to know: To further increase efficiency, our technical teams have developed direct integrations between AntsRoute and some business software such as Odoo, WooCommerce or Zapier.
Frequently Asked Questions (FAQs)
By using Excel, you can organize your data: customer name, service location, scheduled date and duration of the appointment, etc. Once you’ve gathered all this information, you need to build routes—often for several delivery drivers or field agents. With Excel, route planning has to be done manually, which quickly becomes a real headache… Manually created routes are inefficient and lead to higher fuel costs and wasted time.
That’s why experts have developed route optimization software to automate the creation of these routes using an Excel file. Using this kind of tool saves time for operations managers and mobile teams. In short, your business reduces costs and boosts productivity.
Absolutely! Adopting route optimization software is cost-effective in the short term. A solution like AntsRoute automatically optimizes routes by reducing the number of kilometers driven and minimizing travel time, which boosts productivity and cuts costs. Additionally, you save a lot of time when planning routes for your mobile teams. Simply import your Excel file, and AntsRoute takes care of building the routes based on all your constraints and those of your customers. AntsRoute is available starting at €17 per month per vehicle. From the very first months of use, your company will see significant savings.
If you believe that optimizing your routes with Excel—a free tool—is the best method for your business, we recommend trying AntsRoute free for 7 days. You just might change your mind!
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!
Free 7-day trial | No credit card required
Contenu
- 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
- ☝️ Another option: you list your customers in a Google Sheets file
- Case 2: You list the tasks to be carried out in an Excel file
- ☝️ Another option: You manage the tasks with Google Sheets
- Case 3: Your in-house software enables you to export data in Excel or Google Sheets format
- Frequently Asked Questions (FAQs)
