How to Create a Route Map in Excel (Two Methods)

Method 1 – Utilizing Google Maps

In this approach, we will explore creating a route map using Microsoft Excel and Google Maps.

Steps

  • Prepare Your Data:
    • Create a dataset containing information about the locations you want to include in your route map. For our example, we’ll use a dataset of 10 people.
    • The names of these individuals are listed in column B, and their detailed address information is spread across columns C, D, E, and F.

  • Save Your Data as a CSV File:
    • Open your Excel file.
    • Click on the File tab, then select Save As.
    • In the Save As window, choose a suitable name for your file (e.g., Make a Route Map).
    • Change the file format to CSV (MS-DOS) (*.csv).

Utilizing Google Map to Make Route Map

  • Check the CSV File in Notepad:
    • Right-click on the saved CSV file and open it in Notepad.
    • Verify that the information is formatted correctly according to your requirements.

Utilizing Google Map to Make Route Map

  • Create a New Map Using Google My Maps:
    • Open a web browser and search for Google My Maps.
    • Click on the Create A New Map option.

    • Rename the map according to your preference (e.g., Make Route Map).

Utilizing Google Map to Make Route Map

  • Import Your CSV File:
    • Select Import and then choose Select a file from your device.

    • In the dialog box that appears, locate and open your Make a Route Map.csv file.

    • Wait for the file to load into the website.
  • Position the Placemarks:
    • A small window will appear, asking you to choose the columns to position the placemarks. Select all columns except for the Name column.
    • Click Continue.

Utilizing Google Map to Make Route Map

  • Set Marker Titles:
    • Another window will prompt you to choose the title for your markers. Select the Name column as the marker title.
    • Click Finish.

Utilizing Google Map to Make Route Map

  • Trace Locations on the Map:
    • After a brief processing time, you’ll see all the locations traced on the map.

Utilizing Google Map to Make Route Map

  • Add Directions:
    • Click on the marker (represented by a beaker icon) and select the Direction to here option.

Utilizing Google Map to Make Route Map

    • A new layer called Route will be added below the current layer.

Utilizing Google Map to Make Route Map

    • Select the second place on your route.

Utilizing Google Map to Make Route Map

    • Click Add Destination and add subsequent spots to complete the route.
  • Completion:
    • Your route map is now complete.
    • This method allows you to create a route map directly within Excel using Google Maps.

Read More: How to Create a Google Map with Excel Data


Method 2 – Creating a Route Map with a Scatter Chart

In this method, we’ll use a scatter chart to create a route map in Excel. Unlike the previous approach, we’ll work with coordinates to represent locations. Follow the steps below:

  • Gather Coordinates:
    • Obtain the coordinates for each location. You can find these coordinates from our previous map or by searching directly on Google Maps.

    • Right-click on a marker to view its coordinates at the top of the list.
    • Copy the coordinate value.

  • Paste Coordinates into Excel:
    • Paste the copied coordinate values into an Excel worksheet (use Ctrl+V).
    • Repeat this process for all the coordinates.

  • Select the Data Range:
    • Highlight the range of cells containing the coordinates (e.g., G5:H14).
  • Create a Scatter Chart:
    • Go to the Insert tab.
    • Click the dropdown arrow next to Scatter (X, Y) or Bubble Chart and choose Scatter with Smooth Lines and Markers from the Charts group.

Applying Scatter Chart to Make Route Map

    • The scatter chart will appear.
  • Customize Data Labels:
    • Remove unnecessary chart elements, leaving only the data labels.

Applying Scatter Chart to Make Route Map

    • Double-click on any data label to open the Format Data Labels side window.
    • Under Label Options, uncheck all options except Value From Cells.
    • Specify the range of cells (B5:B14) containing the corresponding person’s names.

Applying Scatter Chart to Make Route Map

  • You will see all the points got the corresponding person’s name.

Applying Scatter Chart to Make Route Map

  • Enhance the Chart:
    • To make the chart more meaningful, select it and go to Fill & Line options.
    • Choose Picture or Texture Fill.
    • Insert an image of the map that covers the area of interest, making the chart resemble a real map.

Applying Scatter Chart to Make Route Map

  • Customize Points:
    • Modify the appearance of the blue points on the chart according to your preference.

Applying Scatter Chart to Make Route Map

  • Completion:
    • Your route map is now ready.

Applying Scatter Chart to Make Route Map

Things You Should Know

  • Keep in mind that this method relies on an image of the map, so the scale may not match the actual geography precisely.
  • Use this chart as a prediction tool for understanding a person’s location and their neighbors, but exercise caution and verify information when needed.

Read More: How to Plot Addresses on Google Map from Excel


Download Practice Workbook

You can download the practice workbooks from here:


Related Articles


<< Go Back to Excel Map ChartExcel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo