3 Ways To Plan A Delivery Route With Excel (With FREE Template)

Love them or hate them, Excel spreadsheets are a part of life — and a common tool for managing route planning in a delivery business. 

In this step by step tutorial, we’ll show you the best way to set up an Excel spreadsheet for deliveries, and three ways to plan routes:

1. Delivery route planning with spreadsheets alone.
2. Using Excel and Google Maps route planner to create an efficient route.
3. Using route optimization software.

All three ways use spreadsheets, but two of the tactics use other apps as well.

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

Which spreadsheet app is best for route planning?

There are a few computer programs to choose from when you’re working with spreadsheets. There’s Microsoft Excel, of course, but Numbers (for Mac users) and LibreOffice Calc (open source and free!) are also popular choices. Our personal favorite at Routific is Google Sheets. We're using Google Sheets in our examples, but you can apply the same principles to Excel sheets; they both work almost exactly the same way. 

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 two columns, at the bare minimum. Here's a free Excel template to help you get started.

The two columns should be called “Name” and “Address”. In the “Name” column, put either your customer’s first and last name or their business name. “Address” should contain your customer’s full address, ideally including the street number, street name, city, state or province (if applicable), country, and zip or postal code. This makes it a lot easier for Google Maps to find the right location.

If you prefer, you can split up the address into four 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, which is the first method we suggest below. In our free sample sheet, we’ve added a separate zip code column so you can enjoy the best of both worlds. 

Another option is to use latitude and longitude coordinates, if you have them. To do this, simply create two columns titled Latitude and Longitude instead 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 batches that are easier to work with.

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 takes two steps to sort your addresses by zip/postal code.

1. Freeze the first row:  This turns your column name into a proper header, so it’s always at the top of the sheet.. In Google Sheets, go to View > Freeze  > 1 row. 

Alternatively, hover your mouse over the dark grey line underneath the “1” which marks the first row. Now you can drag a freeze line to include as many rows as you want.

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

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

The same trick will work if you use neighborhood names instead of zip codes.

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 can decide the order in which they will visit each stop and thus when they are able to complete each delivery.

But what if you want a little more control over your delivery routes? You might want to have visibility into where your drivers are while they are on their delivery route. You might want to be able to tell your customers 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, there’s one more step:

3. Organise your stops into groups of 10: If you want to use Google Maps or My Maps, life will be a lot easier if you’ve already sorted your addresses into groups of 10. Again, you can do this using zip codes/postal codes or neighborhood names.

2: Route planning with Excel + Google Maps

Many small businesses use Google Maps as a route planner for deliveries — it’s free, including mobile apps on both Android and iOS, and easy to use. You’ve almost certainly used it yourself for your personal routes, like finding the directions to a new restaurant or checking their real-time traffic updates. 

But you can also use it to build a multi stop route plan! There are some challenges you need to work around, though:

1. Google Maps has a limit of 10 stops (although you can add more using a neat little free tool called More Than 10).
2. Google’s My Maps tool allows up to 100 stops, but you will need to batch them into groups of 10.
3. You can’t optimize routes using constraints like delivery time windows, vehicle load capacities, driver breaks, etc.
4. You’ll need to choose the most efficient order and sort your stops manually. This gets tricky!
5. Google Maps routes show the total drive time, but not stop duration (the time the driver will need at each stop to park and complete the delivery). This can make it hard to know how long routes will really take. 

There’s a workaround for the timing problem: We recommend batching your stops 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. 

If you’re ok with these limitations — let’s get started!

A: Google Maps route planning

Planning a route in Google Maps is pretty easy. Copy and paste in your first address from your spreadsheet, then click Directions to start adding more addresses. Once you’ve added all your stops (or hit the limit), drag and drop the stops around in the list until you’re happy with the resulting route. Now you can easily save or share it. 

💡 You can get more detailed step by step instructions, with pictures, in our article on how to plan a route with multiple stops in Google Maps.  

If you have lots of stops, and you’ve broken them into groups of 10, you can just keep repeating this process in new Google Maps windows until you’re done. Or, you can try Google’s My Maps tool. 

B: Route planning with Google My Maps

Google My Maps is a bit more complex to use, but it does extend your range to 100 stops. So if you need to plan a lot more than 10 stops, this is worth trying. Here’s how to do it:

1. Export your spreadsheet: The first step is to save 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-down menu, scroll down to select CSV. 

In Google Sheets, simply go to File > Download > Comma-separated values (.csv, current sheet).

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

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

3. Tell Google which columns to use for your stop addresses: After you import your file, another box will appear asking you which columns in your spreadsheet will help Google plot a location. Select ‘Address’ — or, if you’ve split the addresses up into separate columns, select all that apply.

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

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

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

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

You’ll  need to look carefully at the map to manually determine an efficient order for your stops. You can drag and stops within a layer to change the route. 

Important note: My Maps uses layers to organize locations, with limit of 10 layers. Within a layer, you can plan a route with up to 10 stops — giving you a total of 100 stops. When you run out of space in one layer, just add a new one.

What if you need a route plan for than 100 stops?

This is where things get tricky. You can definitely use Google Maps to plan a multi-stop route — but as you can probably tell by now, it was never designed to find the shortest route between multiple stops. You’re left with manual sorting. That’s ok when you only have a handful of stops, but it’s really cumbersome once you’re over 20-30 stops. 

It’s also really hard to factor in constraints like multiple drivers, delivery time windows, vehicle capacity, driver breaks, etc. 

This is what route optimization software was made for!

3: Route optimization software

The more stops you have, and the more complex your needs are, the more you’ll need to look beyond Google Maps for route planning and delivery scheduling help.

Route optimization software like Routific automates the entire process of route planning: just upload your spreadsheet and driver information, and our algorithm will calculate the shortest or the fastest route, depending on your preference. It takes delivery time windows and other constraints into account, and allows for multiple drivers. 

With proper route optimization, you can cut planning time from hours to just minutes. No more sorting and batching by zip code! No more eyeballing the distances between stops and manually adding stops into your delivery route!

There are a number of route planning apps on the market, and we have a couple of detailed reviews to help you make a choice:

1. If you're looking for free options to start, check out free multiple stop route planners.
2. If you’re ready to invest in something more powerful, check out the best route optimization software. 

Most of these route planning apps accept CSV file uploads, which means you’re already all set up to try them out. 

Summary

If your delivery operations are small, say less than 50 stops per day, or you're an individual driver, spreadsheets are a good 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.