In many cases, we need to analyze and work with data related to cities. Plotting those data is a must in most cases. If you want to learn how to plot city locations and demonstrate different information about that city on a map in Excel, this article can be the ultimate solution for you.
How to Plot Cities on a Map in Excel: 2 Easy Methods
We will plot information related to the population of cities in different states and also in the same state. The Map Chart and the 3D map chart are going to be used to plot the information. But before doing any of the methods, we need to convert the General type of data to a Geographic type of data. The first dataset contains a list of 280 cities in different states. And another list of 62 cities from New York state.
1. Using Filled Map Chart to Plot Cities
A Map Chart is an additional type of chart in Excel that helps you to plot information based on geographic locations.
Example 1: Plotting Cities from Different States
In this example, we will plot 280 cities from different states. And their population information in a single chart using the Map Chart.
Steps
- At first, we need to convert the dataset which is the list of cities from the General data type to the Geographic data type.
- To do this, first, select the range of cells B5:B284, by pressing the Shift+Ctrl+Down Arrow key.
- After selecting the data, go to the Data tab, and from the Data tab, click on the Geographic Data from the Data Types group.
- Then, there will be an Insert Data sign on the corner of the cell. And a Geographic card sign on the left side of each cell.
- Then click on the Insert Data sign and select the Population option from the menu.
- After clicking the Population, the range of cells C5:C284 is now filled with the population value of the cities mentioned in the range of cells B5:B284.
- Now select both the range of cells C5:C284 and the range of cells B5:B284, from the Insert tab, and click on the Maps in the Charts group.
- After clicking Charts, you will notice that the map of the United States of America appears. On that map, the location of each city is highlighted and the population value of the cities is marked with Data Legend.
The more the population of a city, the color will shift towards a darker blue.
Example 2: Plotting Cities from Same State
In this example, we will plot 62 cities from the same states and their population information in a single chart using the Map Chart.
Steps
- At first, we need to convert the dataset which is the list of cities in New York state from the General data type to the Geographic data type.
- To do this, first, select the range of cells B5:B66, by pressing the Shift+Ctrl+Down Arrow key.
- After selecting the data, go to the Data tab, and from the Data tab, click on the Geographic Data from the Data Types group
- Then, there will be an Insert Data sign on the corner of the cell and a Geographic card sign on the left side of each cell.
- Then click on the Insert Data sign and select the Population option from the menu.
- After clicking the Population, the range of cells C5:C66 is now filled with the population value of the cities mentioned in the range of cells B5:B66.
- Now select both the range of cells C5:C66 and the range of cells B5:B66. Then from the Insert tab, click on the Maps in the Charts. group.
- After clicking Maps, you will notice that the map of New York State appears. Because all of the cities in the range of cells B5:B66 are in New York State. On that map, the location of each city is highlighted and the population value of the cities is marked with Data Legend.
- The higher the population of a city, the more the color will shift towards the darker blue.
This is how we can plot different cities on a map from the same state in Excel. Using the Map Chart feature.
Read More: How to Make a Population Density Map in Excel
2. Utilizing 3D Map Chart in Excel
A 3D Map chart is a powerful tool with various types of modification options and information. You can animate, make videos, and plot different types of data in 3D or 2D landscapes.
Example 1: Plotting Cities from Different States
In this example, we will plot 280 cities from different states and their population information in a single chart using the 3D Map Chart.
Steps
- At first, we need to convert the dataset which is the list of cities from the General data type to the Geographic data type.
- To do this, first, select the range of cells B5:B284, by pressing the Shift+Ctrl+Down Arrow key.
- After selecting the data, go to the Data tab, and from the Data tab, click on the Geographic Data from the Data Types group.
- Then, there will be an Insert Data sign on the corner of the cell and a Geographic card sign on the left side of each cell.
- Then click on the Insert Data sign and select the Population option from the side menu.
- After clicking the Population, the range of cells C5:C284 is now filled with the population value of the cities mentioned in the range of cells B5:B284.
- Now select the range of cells C5:C284 and the range of cells B5:B284, from the Insert tab, and click on the 3D Maps in the Charts group.
- Then click on Open 3D Maps.
- Then in the Layer side panel, click on the Add field option below the Location. Choose the City Name field.
- After selecting the option. The map will take us to the USA. Because all our entries are from the USA.
- Also, select the Bubble chart option under Add Layer.
- Then in the Layer side panel, again click on the Add field option below the Size And choose the Population field.
- Then click on the Flat Map option in the Map group. This will switch the map on the left from 3D to 2D.
- After we had made our map 2D, we made some modifications to the map like enabling the Map label. Which actually will show us the names of locations throughout the map.
- After some more minor modifications, our map will look like the one below.
Example 2: Plotting Cities from Same State
In this example, we will plot 62 cities in New York state and their population information in a single chart using the 3D Map Chart.
Steps
- At first, we need to convert the dataset which is the list of cities from the General data type to the Geographic data type.
- To do this, first, select the range of cells B5:B66, by pressing the Shift+Ctrl+Down Arrow key.
- After selecting the data, go to the Data tab, and from the Data tab, click on the Geographic Data from the Data Types group.
- Then, there will be an Insert Data sign on the corner of the cell and a Geographic card sign on the left side of each cell.
- Then click on the Insert Data sign and select the Population option from the menu
- After clicking the Population, the range of cells C5:C66 is now filled with the population value of the cities mentioned in the range of cells B5:B66.
- Now select both the range of cells B5:B66 and the range of cells C5:C66. Then from the Insert tab, click on the 3D Maps in the Charts group.
- Then click on Open 3D Maps.
- Then in the new popup window click on New Tour.
- After clicking the New tour, the 3D map will launch.
- In the window, there will be a side panel.
- In this panel, click on Add Field under the Location.
- In the drop-down menu, select City.
- Next, you will be taken to the New York State location on the map. Because all of our entries are from New York State.
- All of the entry’s locations are now highlighted in orange color bubbles.
- Next, click on the Add Field under the Size option.
- Then from the drop-down menu, select Population.
- After selecting the Population from the menu. The cities are now highlighted with the Bubbles of different sizes to each other.
- The size of the Bubbles depends upon the population value of that particular city.
- After some minor adjustments, our map will look like the below image.
This is how we can plot different cities on a map from the same state in Excel. Using the 3D Map Chart.
Read More: How to Plot Addresses on Google Map from Excel
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to plot cities in Excel” is answered here by using Map Chart and the 3D Map. We used two separate dataset tables to demonstrate these two methods. One table is about the different cities in different states across the USA. Another one is different cities in the same state New York.
For this problem, a workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
Related Articles
- How to Map Data in Excel
- How to Create a Google Map with Excel Data
- [Fixed!] Excel Map Chart Not Working
- How to Create a Map in Excel
- How to Plot Points on a Map in Excel
- How to Make Route Map in Excel
<< Go Back to Excel Map Chart |Â Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
