How to Pull Geographical Data (County/City) in Excel?

Pull Geography data in excel

In this tutorial, we’ll learn how to pull geographical data in Excel. Getting geographical data of any country, region, or city using the traditional method of manual ‘Google’ or ‘Bing’ searching or ‘Wikipedia’ is time-consuming as well.

Also, directing the way to possible errors while copy-pasting of these data points. Multiply these efforts when pulling geographical data of multiple locations.

Excel helps us fetch and display the geographical data with an add-in.

In this tutorial, we will guide you step-by-step, so you can easily fetch Geographical Data from any region around the globe.

Also read: Genuine Ways to Recover Unsaved and Corrupted Excel Files

What Geographical Data can be fetched in Excel?

Excel’s built-in ‘Stock Market’ tool supports many data parameters which can be fetched directly. These parameters are listed below:

01. Abbreviation02. Admin Division 1 (State/province/other)03. Agricultural land (%)
04. Area05. Armed forces size06. Birth rate
07. Calling code. 08. Capital/Major City09. Carbon dioxide emissions
10. County/Region11. CPI12. CPI Change (%)
13. Currency code14. Electric power consumption15. Fertility rate
16. Forested area (%)17. Fossil fuel energy consumption18. Gasoline price
19. GDP20. Gross tertiary education enrollment (%)21. Image
22. Infant mortality23. Largest city24. Latitude
25. Leader(s)26. Life expectancy27. Longitude
28.Market cap of listed companies29. Maternal mortality ratio30. Minimum wage
31. Name32. National anthem33. Official Language
34. Official name35. Out of pocket health expenditure (%)36. Physicians per thousand
37. Population38. Population: Income share fourth 20%39. Population: Income share highest 10%
40. Gross primary education enrollment (%)41. Population: Income share highest 20%42. Population: Income share lowest 10%
43. Population: Income share lowest 20%44. Population: Income share second 20%45. Population: Income share third 20%
46. Population: Labor force participation (%)47. Subdivisions48. Tax revenue (%)
49. Time zone(s)50. Total tax rate51. Unemployment rate
52. Urban population
All Data Options of Geographical Data Points using Excel

The important point to note over here is, that all these Data parameters are only available for the County level geographical data points and not all are applicable on Region or City level.

For instance, City – Geographical data parameters that are supported are Abbreviation, Area, Capital/Major City, Country/region, Image, Largest city, Leader(s), Name, Population, and Time zone(s).

Steps to Pull Geographical Data in Excel

Let’s get started with using the ‘Geography’ tool in Excel to get the Geographical details of any region.

Step 1. Organize the Data – Country, Region or City name

Type a country, state, province, territory, or city name into each cell, for which you want to fetch the Geographical details.

You may also enter data points in a table for multiple geographical locations.

Geography Data in Excel - Sample data
Geography Data in Excel – Sample data

In our example above, we are finding Geography Data of these countries and cities:

India, USA, Germany, Tokyo, Paris – we have purposely used both countries and cities to illustrate a better example in this tutorial. You may also notice that we are using a short form abbreviation for the United States of America. All this is done to show the capabilities of the Excel built-in Geography tool.

Step 2. Select Location and convert to Geography

  • Select the cells with Location name or names to get data.
  • Navigate to ‘Data‘ in the above tools tab (Home tab)
Converting location names to Geography in Excel
Converting location names to Geography
  • Click on ‘Geography‘ to convert the input location names to Geography. [as shown below, the Excel will automatically convert and map the data to the most accurate understanding of Excel tool]
Converted to Geography in Excel
Converted to Geography

If converted successfully, you will see a map-like symbol just before the entered name/s in the same cell.

Unable to convert to Geography in Excel

In case, after following the above step, your input name wasn’t converted to Geography, a question mark sign appears instead of a map sign.

It’s likely because Excel is having issues matching your text with data. Possible solutions to this are:

Correct any spelling mistakes if you have made or try to be more specific. Or, click on the info symbol to reveal a side pop-up where you will be presented with the option to select the closest matching search results for which Excel will do its best to find matching information

Step 3. Select the Geography parameter for Data

  • Excel supports various Geography data parameters to select from, which are shown in the above table here.
  • To select particular data parameters, select all the inputted names (converted to geography) and on hovering over them you will be presented with a symbol of data (of somewhat like a book)
  • Click on the icon and select the parameter you want to fetch data of.

To see all of the fields available, click the geography icon or select the cell and press ‘Ctrl+Shift+F5‘.

Pro Tip: Set ‘Column Width’ to ‘AutoFit Column Width’ as this sets column width as per the data value inside that particular column, hence, benefiting us in full data value visibility returned by a function.

Selecting Geography data parameter
Selecting Geography data parameter

Final Sheet with all the required data. [You may choose any number of data parameters as per your requirement]:

Geography Data in Excel
Geography Data in Excel


#FIELD! – means that Excel was unable to fetch the geographical data of that particular data set. In this case, you may notice the reason for it, it’s pretty obvious how can cities have an Official Name (Tokyo, Paris) hence, #FIELD!. Again, how can a country have a country/region data set hence again unavailability of data?

#SPILL! – simply means out of bound. Which is resultant when a large number of values are returned. Here, in our example it’s evident in Time zone(s) for the United States and Germany because both these countries have multiple time zones and all can’t be fit in a single cell.

##### – the data value is relatively large (long) and doesn’t fit in that cell size. Increase column width or set it to auto to render this error in the data set.


That’s It! You can get Geographical data in various parameters easily by following the above step-by-step procedure now, in your Excel sheets.

We hope you learned and enjoyed this lesson and we’ll be back soon with another awesome Excel tutorial!