3 Simple Ways to Plan Delivery Routes Using Excel (+FREE Excel Templates)

keyKey Takeaways:
  • Excel Spreadsheets and Google Sheets are small delivery businesses’ most affordable route-planning solutions.
  • Excel route planner minimizes human errors and manual effort involved in traditional route planning methods.
  • It leverages data from Microsoft Excel spreadsheets and promotes error-free, time-savvy, and efficient route planning.
  • It gets easily integrated with advanced route optimization software for enhanced functionality and more optimized logistics operations.
  • Use the free Excel template to import your multiple-stop address data easily.

Believe it or not, Microsoft Excel Spreadsheets or Google Sheets are easily accessible, cost-effective, and far more efficient route planners than manual ones.

We know handling a large mass of data and then sorting and optimizing it for route planning can be daunting. 

However, with tools like Excel and Google Sheets, this becomes manageable and efficient. These platforms support various functions that automate the sorting and filtering of data, ensuring that you can plan routes based on the most logical and time-effective paths. 

Here in this blog, we will walk you through three different ways of planning delivery routes using Excel. Have a look:

  • Using Excel Route Planner
  • Using Route Optimization Software
  • Using Excel and Google Maps

So, without any further ado, let’s understand an Excel route planner, its impact on business, and how it is used to plan the most optimal routes.

What is Excel Route Planner?

Excel route planner is a tool that extracts and leverages data directly from Microsoft Excel spreadsheets to plan routes.

Until now, route planning was a manual process prone to human errors. Also, manual route planning methods are time-consuming. To overcome these challenges, an Excel route planner is used. It is fast, convenient, and error-free.

It extracts data from Excel spreadsheets, such as client names, addresses, contact numbers, service time, time windows, email, notes, and much more. Thus, manual work is eradicated, and all the processes are automated in Excel route planning.

Now, we will look at the steps to plan a route using an Excel route planner.

How to Plan a Route With Excel Route Planner?

Now that you know the meaning and some of the benefits of Excel route planner, let’s go deeper into the topic and look at 4 steps to plan a route using it:

1. Gather data and make an Excel spreadsheet

Gather data and make an excel spreadsheet
    • Firstly, you have to collect all the data required to plan routes for your business.
    • This includes customer names, addresses, phones, email, notes, region, zip code, and time windows.
    • This list can be shortened or extended as per your business requirements.
    • Now, create an Excel sheet and label the columns according to the data you wish to add.
    • For example, if you want to include names, addresses, zip codes, and phone numbers, create four columns and name them accordingly.
    • For example, if you want to add an address column, you can name it anything like “Addresses,” “Address line-1”, or “Detailed address”.

2. Sort data according to your requirements

Sort data according to your requirements
    • Once you have created and filled out the Excel sheet, you must sort the data to organize it.
    • To sort any column, click and drag all the cells containing the data. To maintain column alignment, include the header row.
    • Once the column is selected, go to the “Data” tab.
    • In the “Sort & Filter” group, you can select whether to sort in ascending or descending order.
    • Once you have set the sorting criteria, click the “Sort” button to apply sorting to your data.

To help you get started with Excel route planning, here is a free Excel template. You can modify the columns according to your needs and import them directly into an Excel route planner.

Download the Template for Freecrossline

3. Import the Excel spreadsheet and match the data

Import excel spreadsheet and match data
    • Now that your Excel sheet is ready, it’s time to import the file into your Excel route planner.
    • To do so, open the Excel route planner.
    • Next, click on “Upload File,” select the Excel file from your system, and click on “Open.” You can also drag and drop the file directly into the Excel route planner.
    • Now, match the column names of your sheet with the route planner’s default names. It helps you ensure only the desired data is imported.
    • Then, click on “Done,” and within a few seconds, the Excel route planner will import all the data.

Plan Unlimited Stops in One Go

Bulk import multiple stops in Excel file and automatically plan the precise, accurate and most optimal route.

4. Plan and optimize routes

Plan and optimize routes
  • Now, to plan routes using the imported data, click on ”Next”.
  • In the next step, you will have to choose the driver/s to assign to your planned route.
  • Driver selection
    • Once you select the driver/s, tap on “Optimize and Assign Routes” to move further with the route optimization process.
    • In just a few seconds, the route optimization software will optimize the route and give you the shortest route or fastest route.

    How to Plan Routes Using Route Optimization Software?

    As seen above, planning delivery routes using Excel route planner brings a lot of convenience and ease of routing. There are many route optimization software available in the market that work as an Excel route planner and enable you to import Excel files to plan routes.

    Among them, one of the top Excel route planners is Upper. This route planning solution supports all sorts of delivery businesses and is customizable according to various business requirements.

    The reason you should opt for an Excel route planner is it is fast, dependable, and accurate. Upper empowers you to carry out data-driven route planning by incorporating multiple factors. Whether it’s time windows, traffic conditions, vehicle capacities, or priority stops, Upper’s advanced algorithms and functions handle it all.

    Besides route planning, Upper has various other feature that makes it an all-in-one route management software such as:

      • Route optimization
      • One-click dispatch
      • Proof of delivery
      • API integration
      • Reporting & analytics
      • Quick customer support

    Effortless Routing is Just a Click Away

    Upload your spreadsheet containing multiple stops and automatically plan, optimize, and dispatch error-free routes in minutes.

    How to Plan Routes Using Excel and Google Maps?

    When it comes to route planning, the first thing that comes to our mind is Google Maps, as it is free and easy to use. Google Maps is a suitable route planner for small businesses.

    You can use Google Maps and Excel in 2 ways: Enter stops manually into Google Maps or import the Excel spreadsheet directly into Google Maps.

    The limitations of planning an entire route with Google Maps are that it only allows you to add 10 stops at a time and does not optimize the stops; you can only plan them.

    These limitations were solved when Google launched My Maps. It enables you to add more than 10 stops in a single route. However, the other limitation of route optimization remains intact. To overcome this and start practicing route optimization for your business, you need to look for alternatives to Google Maps.

    Now, let’s look at the steps to plan a route using Excel and Google Maps:

    1. Export your spreadsheet: Save your spreadsheet as a CSV file. In Excel, choose File > Save As, enter a new file name, and select CSV as the file type. In Google Sheets, go to File > Download > Comma-separated values (.csv).

    2. Upload the CSV file: Now, go to “My Maps.” Click “Create a New Map,” name your map, and click “Import” in the top left corner.

    Upload the CSV file

    3. Add the CSV file: Drag and drop the CSV file into the box that appears. You can also select the file from your computer by clicking on the “Select a file from your device” button.

    4. Specify columns for stop addresses: After importing the file, you’ll be prompted to identify the columns in your spreadsheet that contain address information. Select the columns labeled ‘Address’ or choose all relevant columns if the address is split across multiple columns.

    5. Include additional information for stop details: Google Maps will ask you to choose a column to use as the title for each stop. Select the column labeled ‘Name’ or an appropriate alternative like ‘Customer Name’. You can also include additional columns with information such as priorities, parcel location, driver notes, delivery time windows, etc.

    Include additional information for stop details

    6. Plot stops on the map: Once done, click “Finish”. All the addresses from your spreadsheet will be marked on the map.

    7. Create routes: To get directions between the points on the map, click on a stop and then click the arrow icon at the bottom-right of the window.

    8. Add more stops: The selected stop will appear on the left panel. To add more stops, click “Add Destination” and select the desired locations on the map until the entire route is plotted.

    Another constraint of using Google Maps is that you need manual intervention to verify and modify the stop sequence, which can become hectic at times. After all, Google Maps is merely a route planner and not a route optimization software. If your business requires the shortest or fastest route, we suggest using the best route optimization software instead of Google Maps.

    FAQs

    Excel route planner offers various benefits, such as data streamlining, time and fuel savings, and better resource allocation. Its customizable features allow it to handle complex route-planning scenarios, making it a versatile tool for businesses and individuals.

    Yes, Excel route planners can accommodate multiple vehicles or fleet management scenarios. By incorporating vehicle-specific data, such as capacities, fuel efficiency, or driver schedules, you can optimize routes to assign stops to different vehicles while considering constraints like vehicle capacity, load balancing, and driver availability.

    Only some Excel route planners can provide a visual representation of optimized routes. However, some capable Excel route planners like Upper show you a detailed roadmap of optimized routes. Also, when started, they integrate with third-party navigation apps like Google Maps to give turn-by-turn navigation.

    Conclusion

    After considering all three different ways of planning delivery routes using Excel, we are sure that each offers unique advantages and suits different business needs. 

    For instance, if you plan 50 stops per day, then planning routes with Excel Route Planner might be sufficient. However, medium—or large-sized businesses planning more than 100 stops a day might benefit from integrating additional route optimization software along with basic planning.

    Ultimately, the choice of method depends on specific operational requirements and the complexity of the delivery network. However, all of these methods provide robust frameworks for improving logistical efficiency.

Author Bio
Rakesh Patel
Rakesh Patel

Rakesh Patel, author of two defining books on reverse geotagging, is a trusted authority in routing and logistics. His innovative solutions at Upper Route Planner have simplified logistics for businesses across the board. A thought leader in the field, Rakesh's insights are shaping the future of modern-day logistics, making him your go-to expert for all things route optimization. Read more.