Sometimes we need to create a route map of our visiting place to cover them as early as possible. There are several types of applications to get it. Additionally, we can also make it using Excel. In this context, we will show you two different ways how to make a route map in Excel. If you are curious to know about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook and CSV file for practice while you are reading this article.
2 Easy Methods to Make Route Map in Excel
To demonstrate the method, we consider a dataset of 10 people. The name of those people is in column B and their detailed address information is in columns C, D, E, and F respectively.
1. Make Route Map Utilizing Google Maps
In this approach, we are going to use Microsoft Excel and Google Maps to make our route map. The steps of this approach are given below:
📌 Steps:
- First, click on the File tab. Then click on Save As.
- As a result, the Save As window will appear on your device.
- Set a suitable name for your file and change the file format to CSV(MS-DOS)(*.csv). In our case, we set the title as Make a Route Map.
- After that, right-click on the file and open the file in Notepad to check every information come according to the required pattern.
- Then, launch a web browser on your device and search for Google Maps. Here, we are using Google My Maps which is a feature of Google Maps.
- Click on the Create A New Map option on that page.
- A map window will appear. Rename the map according to your desire. We set our map name as Make Route Map.
- Then, select the Import > Select a file from your device option.
- A dialog box called Open will appear.
- Now, select the Make a Route Map.csv file and click on Open.
- Within a while, the file will load into the website. Then, a small window will appear and ask you to choose the columns to position the placemarks. We choose all columns except Name.
- Finally, click on Continue.
- Another window will appear and ask you to choose your markers title. This time we select the Name column as the title of our markers.
- Click on Finish.
- It will take a while, then you will see all the locations are traced on the map.
- Now, click on the marker Beaker and select the Direction to here option.
- A new layer will add below the current layer. Rename it Route.
- Then, select the second place.
- After that, click on the Add Destination. A new box will come and add the 3rd spot.
- Similarly, add all the spots to complete the route.
- Our route map is completed.
Finally, we can say that our method worked perfectly and we are able to make a route in Excel.
Read More: How to Create a Google Map with Excel Data (With Easy Steps)
2. Applying Scatter Chart to Make Route Map
In the following method, we will create a scatter chart to make a route map in Excel. Here, we need a different type of information, their coordinates. The procedure explains below step by step:
📌 Steps:
- First, get all the coordinates of the locations. As you see, our previous map shows us the coordinates of the locations so we easily gathered them from there.
- Besides it, you can simply search for the location on Google Maps.
- After that, right-click on the marker. You will see the coordinates at the top of that list.
- Click on the coordinate value with the mouse to copy it.
- Then, press ‘Ctrl+V’ to paste the value into the Excel worksheet. Similarly, get all the values of coordinates.
- Now, select the range of cells G5:H14.
- In the Insert tab, select the drop-down arrow of Insert Scatter(X, Y) or Bubble Chart and choose the Scatter with Smooth Lines and Markers option from the Charts group.
- The chart will appear in front of you.
- After that, uncheck all the Chart Elements except the Data Labels.
- You can see that the dots are showing the value of the Y-axis. So, we are not able to understand anything from the chart.
- Double-click on any data labels.
- As a result, a side window will appear entitled Format Data Labels.
- Now, select the Label Options and uncheck all the options, and select only the Value From Cells.
- A small dialog box called Data Label Range will appear.
- Then, select the range of cells B5:B14 and click OK.
- You will see all the points got the corresponding person’s name.
- To show the more convenient, select the chart and select the Fill & Line option.
- From the Fill options, choose the Picture or texture fill option.
- After that, click the Insert option and choose the picture of the map covering their area to show the chart as a real map.
- Similarly, you can click on the chart blue points and modify them according to your desire.
- Our route map is ready.
Thus, we can say that our method worked perfectly and we are able to make a route in Excel.
💬 Things You Should Know
In this process, we just insert an image of the map to look our chart more meaning full. As the scale of that image is not match the actual map, every point may not show the accurate location of that person. So, don’t rely blindly on this chart. However, this chart will provide you with a prediction, about a person’s location and his neighbors. That may help you in many cases when you need any information about any specific person.
Read More: How to Create a Map in Excel (2 Easy Methods)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to make a route map in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!