3 Ways To Plan A Delivery Route With Excel

January 27, 2020

Love them or hate them, spreadsheets are a part of your life especially now that you’re running a small delivery business.

Spreadsheets were first developed for accounting and bookkeeping tasks in order to replace the old ways using pen and paper. They very quickly permeated into many different parts of the business, and today, spreadsheets are used pretty much in any context where tabular lists are built, sorted, and shared.

You might use spreadsheets to create budgets, maintain a list of your customers, or even plan your delivery routes.

spreadsheets meme
excel meme
excel meme for route delivery

A quick search on the web shows us how many people are in the ‘I loathe spreadsheets’ camp. If that’s you, we hope you might be able to suspend your hate for just a moment, because in small business, spreadsheets can be your friend.

In this blog, we’ll show you three ways to plan your routes:

  1. Planning Routes with Excel (spreadsheets alone)
  2. Planning Routes with Google Maps
  3. Planning Routes with 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 favourite here at Routific is Google Sheets

Formatting Your Spreadsheet

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.

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.

How to plan a route with excel-3

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.

 

Method #1: Planning Routes with Excel (spreadsheets 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 freeze the first row so that the name of your columns remain up top even after sorting.

Go to View > Freeze > 1 row

How to plan a route with excel-8

Next, select the column where your zip/postal codes are. Then, go to Data > Sort sheet by Column 5, A → Z

How to plan a route with excel-9

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.

 

Method #2: Planning Routes with 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.

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.

You’ll first need to start by exporting your file. We recommend saving the file 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-down menu, scroll down to locate and select CSV. In Google Sheets, simply go to File > Download > Comma-separated values (.csv, current sheet).

How to plan a route with excel-2

Next, go 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’.

How to plan a route with excel-5

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

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.

 
use google maps to plan your delivery route in excel

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’.

google maps to plan a delivery route

After you do this, all the addresses in your spreadsheet will be plotted on the map, like so:

How to plan a route with excel

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.

route planning with google maps

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.

How to plan a route with excel-11

It’s worth noting that while Google Maps is great when you have a small number of deliveries to make, there are some limitations when you’re trying to plan routes for deliveries.

With Google Maps:

  • Your routes need to be 10 stops or less
  • You can only plan for 1 driver at a time
  • 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)

The last point might need some more explaining.

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 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.

Read more: Google Maps and Route Optimization - What’s the Difference?

Method #3: Planning Routes with 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 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 eight software platforms for you, including a number of free multiple stop route planners. The great thing about these route planning apps is that most of them accept spreadsheet uploads, which means you’re already all set up to try them out.

The biggest value-add the route planning apps bring to the table is determining the best order in which you should make your deliveries. 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.

In Summary

Our goal is to make life a little easier for small delivery businesses, so we hope after reading this blog, you might come to hate spreadsheets a little less.

Spreadsheets can help organize your data, and get you set up to use all kinds of software, including routing software, more seamlessly.

I hope this article helped you figure out some tips and tricks to delivery planning routes with excel. If you're looking for more content on how to make route planning easier, check out Manual Route Planning Is Hurting Your Delivery Business 👇

Manual route planning ebook CTA image 2

It’s one of our favourite books from the Routific library. Enjoy!