How to Use Excel for Basic Route Optimization (with Templates)

Many delivery managers still rely on Excel or Google Sheets for route planning. And it makes sense — spreadsheets are flexible, accessible, and free. While they don’t match specialized software, with the right approach, Excel can handle basic route optimization for small businesses.

1. Why Use Excel for Route Optimization?

For startups or small logistics operations, Excel is an excellent low-cost tool to begin optimizing routes. According to Statista, Excel remains the most widely used business analytics tool in 2025. With a few formulas, it can calculate distances, optimize stops, and even visualize data.

2. Setting Up Your Spreadsheet

Start by creating a simple table with these columns:

• Stop ID – Sequential numbers for each address
• Address – Full street and city
• Latitude / Longitude – Optional but useful for precise mapping
• Delivery Window – Time restrictions if applicable
• Estimated Duration – Time spent per stop

You can geocode addresses for free using tools like MapDevelopers Batch Geocoder or Google Sheets' built-in Apps Script for Google Maps API.

3. Calculating Distances

To calculate distances between stops, use Google Maps Distance Matrix API or manually with Excel formulas. Here’s a simplified example using latitude/longitude:

=ACOS(SIN(RADIANS(Lat1))*SIN(RADIANS(Lat2)) + COS(RADIANS(Lat1))*COS(RADIANS(Lat2))*COS(RADIANS(Long2)-RADIANS(Long1))) * 6371

This formula estimates kilometers between two coordinates using the Haversine method.

Pro Tip:

Save distance calculations in a matrix format — each row and column representing stops — so you can analyze total route distances quickly.

4. Using Excel’s Solver Add-In

Excel’s Solver is a powerful optimization tool built into Microsoft 365. It can find the best route order by minimizing total distance or time. Configure it as follows:

1. Activate Solver from File → Options → Add-ins → Excel Add-ins → Solver Add-in.
2. Create a column for “Sequence” representing the order of stops.
3. Set the objective to minimize total distance (sum of distance between consecutive stops).
4. Apply constraints (each stop appears only once).

Although Excel can’t perfectly solve a Traveling Salesman Problem for large datasets, it works efficiently for routes under ~15 stops.

5. Visualizing Routes with Google My Maps

Export your Excel or Google Sheet as a CSV, then import it into Google My Maps. This gives you a visual representation of your route and helps drivers understand their sequence more clearly.

Recommended Columns for Export:

• Stop ID
• Address
• Latitude / Longitude
• Stop Sequence

Color-coding stops or using markers for priority deliveries adds clarity for drivers.

6. Free Excel Template

Here’s a simple layout for your route planner:

| Stop ID | Address              | City       | Distance (km) | Sequence | Time Window |
|----------|---------------------|-------------|----------------|-----------|--------------|
| 1        | 123 Elm St          | Austin, TX  |                | 1         | 8:00–9:00 AM |
| 2        | 88 Greenway Blvd    | Austin, TX  |                | 2         | 9:00–10:00 AM |
| 3        | 200 Main St         | Austin, TX  |                | 3         | 10:00–11:00 AM |

Once filled, apply Solver to find the optimal order based on shortest distance or least travel time.

7. Pros & Cons of Excel Routing

Pros

• Free and accessible
• Great for small businesses
• Customizable with macros and scripts

Cons

• No live traffic or predictive data
• Time-consuming for large datasets
• Manual address cleaning required

8. When to Move Beyond Excel

As your business scales, Excel quickly becomes inefficient. Automated solutions handle complex constraints and traffic data much faster. Research from ScienceDirect (2025) shows AI-based routing tools reduce total delivery times by up to 25%.

That’s why many companies combine manual control with expert-assisted solutions like Last Mile Routes, where human specialists and AI work together to design optimal, driver-ready plans.