3 Ways To Plan A Delivery Route With Excel

Love them or hate them, Excel spreadsheets are a part of your life—especially now that you’re managing a delivery business. In this post we’ll show you how to use Excel as a route planner to find the best routes.

In this blog, we’ll show you how to format your Excel spreadsheet and three ways to plan routes:

1. How to plan delivery routes with Excel (spreadsheets alone)
2. How to use Excel & Google Maps to plan delivery routes up to 2000 stops
3. Using route optimization software

All three ways use spreadsheets, but you’ll notice two of the tactics leverage the use of software as well.

Before we start route planning, we need to make sure your spreadsheet is set up properly.

Getting started

There are a few computer programs to choose from when you’re working with spreadsheets. Perhaps the most well known is Microsoft Excel. There are also other programs like Numbers (for Mac users) and LibreOffice Calc (open source and free!). Our personal favorite here at Routific is Google Sheets. We're using Google Maps in our examples, but you can apply the same principals to Excel; both software function almost identically. 

How to format your Excel file for delivery route planning

To plan your routes using Excel, Google Sheets, or any other spreadsheet program, you’ll need to include some basic data. We suggest starting off by creating at least 2 columns, at the bare minimum. Here's a spreadsheet template to help you get started.

The two columns should be called “Name” and the second one should be called “Address”. In the “Name” column, you can put either your customer’s first and last name or their business name. The “Address” should contain the full address of your customer which ideally includes the street number, street name, city, state or province (if applicable), country, and zip or postal code.

If you prefer, you may also split up the address into 4 separate columns:

• Street Address
• City
• State/Province
• Zip/Postal Code

There are benefits to doing this if you want to be able to sort your addresses by zip or postal code (more on this below).

Another option is using lat-long coordinates. To do this, simply create two columns titled Latitude and Longitude in lieu of the Address column.

Now that your spreadsheet is set up correctly, we can get on with the route planning.

Three ways to plan routes with spreadsheets

1: Route planning with Excel alone

Spreadsheets allow you to sort your data in a way that makes sense to you. When you’re dealing with a long list of addresses, delivery companies often try to break the list up into smaller, more easily digestible batches.

One popular method is to sort by zip or postal code. This allows you to split a large geographic area up into delivery zones so your drivers aren’t wasting time driving all across the city. By assigning each zone to a dedicated driver, they are able to focus on a specific area of the city, get familiar with the route and its customers, and complete more deliveries per route in a shorter amount of time.

It’s two steps to sort your addresses by zip/postal code. First, let’s 

1. Freeze the first row:  This is so that the name of your columns remain up top even after sorting in the next step. In Google Maps, go to View > Freeze  > 1 row

2. Sort by zip/postal code: Select the column where your zip/postal codes are. Then, go to Data > Sort sheet by  A → Z. This will sort your selected column, in this case column E, in numerical order.

Voila! This will bucket all your customers with the same zip or postal code together, making it easier for you to manage.

Many delivery businesses will assign these smaller batches of routes to drivers, and leave it up to the drivers to decide how to plan their day. In this case, drivers often will decide the order in which they will visit each stop and thus when they are able to complete each delivery.

But, you may want to have a little more control over your delivery routes. You might want to have visibility in regards to where your drivers are while they are on their delivery route. You might want to be able to tell your customers more specifically when to expect their deliveries, so they aren’t waiting around all day. Or, you might want to be the one who determines the order of stops to ensure your driver isn’t wasting any time or fuel on the road. If that’s the case, please read on.

2: Route planning with Excel + Google Maps

There are a number of free online services that can help you plot a route for your drivers.

The first service that comes to mind is probably Google Maps - it’s free and extremely easy to use – and many small businesses use Google Maps as a route planner for deliveries.

You can manually enter multiple stops into Google Maps or you can also plot a route with multiple stops on Google Maps by uploading a spreadsheet.

Many of you will be familiar with Google Maps’ basic Route Planner. Many of you will have used this for your personal routes like finding the directions to a new restaurant. You can also use it to build a multi stop route plan. The challenge with this option is that you can only plan routes with a maximum of 10 stops. If you want to plan routes with more stops, Google offers another option called My Maps. You can upload up to 2000 stops at a time. 

Now you might be thinking, “Thousands of stops?! Perfect! What else could I ask for?” Well, before you start jumping for joy, there are some trade offs we should go over first. 

One of the big ones is that you won’t have any idea how long a route will take your driver to complete. 

To get a better understanding of timing, we recommend batching routes into morning and afternoon routes. This will give you a bit more control over when deliveries are completed. While it takes a bit more time to set up driver routes like this, it will help you to provide a better delivery experience to your customers. 

Limitations of Google’s My Maps route planning:

• You don’t know how long routes will take to complete for your drivers to complete
• You can’t optimize routes using constraints like delivery time windows, vehicle load capacities, driver breaks, etc.
• You need to eyeball and manually determine an efficient order for your stops (this gets tricky when you need to factor in things like the constraints mentioned above)

If you’re OK with the limitations listed above then this section is for you! 

Let’s get started.

1. Export your spreadsheet: We recommend saving your export as a CSV. The easiest way to do this in Excel is to select File > Save As. In the dialog box, type a new name for the file and in the “Save as Type” drop-downmenu, scroll down to locate and select CSV. In Google Sheets, simply go to File > Download > Comma-separated values (.csv, current sheet).

2. Upload csv file to http://mymaps.google.com/: Click the red button that says ‘Create a New Map’, name your map, and in the top left-hand corner, click ‘Import’.

3. Drag and drop your CSV file into the box that appears, or click the blue button to select a file from your computer.

4. Tell Google which columns to use for your stop addresses: After you import your file, another box will appear asking you to identify the columns in your spreadsheet that will help Google plot a location. You should select ‘Address’ or if you’ve separated the address into separate columns, select all that apply.

5. Tell Google to include additional info into the stop details: Next, Google Maps asks you which column should be used as a title for each stop. You should select the column you’ve titled ‘Name’ or in the example below, ‘Customer Name’. You can also include as many additional columns with info to help your drivers complete deliveries. For example, you may want to include driver notes, delivery time windows, etc.‍

6. Plot your stops onto the map: Click “finish” and all the addresses in your spreadsheet  will be plotted on the map, like so:‍

7. Build your delivery routes: To get directions between the points on the map, click on a stop and then click on the arrow icon that appears on the bottom-right of the white window.

8. The stop you’ve selected will appear on the left panel. From there, you can continue to add more stops by clicking ‘Add Destination’ and then selecting the stops on the map until you’ve plotted out the entire route.

You’ll  need to eyeball and manually determine an efficient order for your stops. This gets tricky when you need to factor in constraints like multiple drivers, delivery time windows, vehicle capacity, driver breaks, etc. 

This is one of the biggest limitations of Google Maps. 

Often, Google Maps is confused with being a route optimization tool. It can definitely be used for planning a route with multiple stops. The distinction here is that while Google Maps is a tool that can be used to find the shortest route between multiple stops, it was never designed to find the optimal order of those stops in your route. The person planning the routes will need to plot addresses in Google Maps, and spend the time manually determining the most efficient order to serve them in. If you tell Google what order those stops should go in, you’ll get the best results possible for which roads to take; but you can’t ask it to provide the stop order for you.

This is where route planning software comes in handy.

3: Route optimization software

As you can see, there’s still a ton of manual work when it comes to plotting a route on Google Maps. The more stops you have, and the more complex your needs are, the more you’ll need to look elsewhere for route planning and delivery scheduling help.

So, if Google Maps isn’t working out for you, what do you do?

There are a number of route planning apps on the market that could work for your business, but they vary greatly in functionality and in price.

To help make things a little easier for you, we’ve actually gone ahead and reviewed the best route optimization software for you. If you're looking for free options to start, check out this review of free multiple stop route planners. The great thing about these route planning apps is that most of them accept excel file uploads, which means you’re already all set up to try them out. Nowadays there are even mobile apps for delivery routes that will let you upload spreadsheets!

The biggest value-add the route planning apps bring to the table is determining the best order in which you should make your deliveries, particularly when you have a large volume of stops and/or multiple routes. Optimization algorithms go to work for you, choosing either the shortest or fastest route to multiple destinations, and most of the apps allow you to plan routes for multiple drivers at the same time.

No more sorting and batching by zip code! No more eyeballing the distances between stops and manually adding stops into your delivery route!

Some of the route planning apps will also factor in the constraints we talked about earlier, such as delivery time windows, vehicle load capacities, and driver breaks. This kind of software has been proven to save businesses hours in planning and driving time, and helping businesses – big and small – increase delivery capacity by adding more stops per route.

Summary

Our goal is to make life a little easier for small delivery businesses. If your delivery operations are small, think less than 50 stops per day or you're an individual drivers, spreadsheets probably are the right choice! But if your delivery team is making more than 50 stops per day, we recommend exploring route planning software. It helps delivery teams save hours each week managing deliveries while simultaneously building a 5-star delivery experience for your customers; something spreadsheets and  Google Maps were never built for.