Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we need to perform route optimization in Excel to better represent the data. But there are no direct methods to do so. In this article, I will show you 2 suitable methods to perform route optimization in Excel. Hence, read through the article to learn more and save time.
However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences.
How to Perform Route Optimization in Excel: 2 Suitable Methods
In this tutorial, I will show you how to perform route optimization in Excel. Here, I have used 2 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset includes the Names of the customers and Locations with Zip codes.
1. Sort by Zip Code to Optimize Route in Excel
First of all, I will use the Sort feature to optimize routes in Excel. However, the process is quite easy and simple. In this part, I will sort the Zip Code of the locations in order to optimize the given routes. Hence, follow the steps below in order to complete the operation properly.
📌 Steps:
- Initially, create another column and insert the Zip codes of the location.
- Then, select the dataset headings and go to the View tab.
- Next, select the Freeze Panes However, you have to freeze them in order to keep the heading at the top after the sorting process.
- After that, select the Zip Code column and move to the Data tab.
- Now, select the sort sheet by A → Z from Sort & Filter command.
- Afterward, check to Expand the selection and press Sort.
- Finally, you will get your optimized route because it is better to start the route from the first location and end it serially.
2. Combine Excel and Google Maps for Route Optimization
Furthermore, you can make the optimization better with the help of Google Maps. In this part, I will combine Microsoft Excel and Google Maps in order to perform route optimization. However, this method will show you not only the route and distance but also the traffic of the route. Moreover, it will show you multiple routes so that you can choose according to your preference. Hence, read through the below part to understand the process.
📌 Steps:
- Firstly, open the active worksheet and hit the F12 key to open the Save As dialog box.
- Secondly, select .CSV from the Save as type and click on Save.
- Thirdly, press OK from the dialog box. Here, the workbook contains multiple worksheets, but you have to save all sheets individually to .CSV.
- Fourthly, go to the following link and click on +CREATE A MAP.
- Afterward, hit the Import button from the top left corner.
- Now, drag and drop the CSV file into the box. However, you can Select a file from your device.
- After that, select the columns which contain location and press Continue.
- Similarly, select the column which contains the title for the locations and hit Finish.
- However, it will show all the locations on the map.
- In addition, select a stop and click on the arrow icon from the bottom right corner of the window.
- Add all the destinations from the map.
- Lastly, you will get your desired route optimization.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow to perform route optimization in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- How to Solve Linear Optimization Model in Excel
- Excel Optimization with ConstraintsÂ
- Schedule Optimization in Excel
- How to Perform Multi-Objective Optimization with Excel Solver
- How to Optimize Multiple Variables in Excel
- How to Calculate Optimal Product Mix in ExcelÂ
- Mean Variance Optimization in Excel
- How to Make Price Optimization Models in Excel
- How to Solve Network Optimization Model in Excel
<< Go Back to Optimization in Excel | Solver in Excel | Learn Excel
