Route Optimization in Excel
Optimize driving routes using Microsoft Excel and MapPoint

Route Optimization Map

Whether you're showing real estate to clients, making customer service calls, or running a delivery service, there's a significant opportunity to save time, reduce costs, and conserve fuel by optimizing the order of stops on a driving route. Unfortunately, route optimization software can be prohibitively expensive and overly complex for many situations.

An economical alternative is the route optimization feature available in Microsoft MapPoint. MapPoint is a mapping and route planning tool that can optimize routes based on minimizing driving time.

In cases where multiple routes require optimization, the functionality of MapPoint can be accessed through Microsoft Excel. For instance, multiple routes with stops listed in Excel can be optimized by running MapPoint in the background, using the Excel addin CDXZipStream. CDXZipStream works entirely within the familiar environment of Excel and provides a simple, easy-to-use interface that returns optimized route data directly to the worksheet.

Please view the video below to see how it works:

Route Optimization in Excel

Now let's take a look at an example that illustrates how route optimization using Excel can be a valuable decision-making tool for a supplier in a large urban area.

Example: Route Optimization for a Restaurant Supplier

A produce supplier provides organic fruit and vegetables to premium urban restaurants on a daily basis. Currently, there are five delivery vans each running a fixed route to approximately 15 restaurants. These five delivery routes have already been optimized, but with the addition of three new clients the routes must be re-optimized. The goal of this calculation is to determine which routes should include the new clients in order to keep delivery time to a minimum. This calculation is critical since the current routes are already very full and the addition of these clients may require adding a delivery vehicle at significant cost.

For each of the five current routes, CDXZipStream can calculate the minimum driving time after adding each of the new clients. A total of 5 x 3 = 15 iterations are performed to determine which combination has the shortest driving time. Assumptions include a 15 minute stop at each restaurant in order to unload the produce. Total time must be less than 8 hours to conform to union requirements.

We can develop a matrix showing the current and new optimized delivery times calculated by CDXZipStream, after adding each of the new locations to an existing route.

Optimized Delivery Route Table

Based on these driving time calculations, Routes 3, 4 and 5 can add at least one new location while maintaining delivery time within the 8-hour union requirement, and purchase of a new delivery van is not necessary at this point. The shortest calculated delivery times for these cases are highlighted. Although not shown here, there are also other iterations that could be performed, such as showing the effect of adding two locations to a single route, but for this particular case that was not possible due to the constraints of vehicle size. It is also interesting to note that with locations 2 and 3, the best choice for the route was not the one that would have been chosen based on geographic proximity. Especially with city driving where there are complexities ranging from natural barriers such as rivers, to one-way streets, choosing routes based on geographic proximity may not yield the best result.