Function Problems

How to Fix Excel Geography Data Type Not Recognizing Major Cities

Steve K Oct 9, 2026 869 views

Question details

Users experience issues where the built-in Geography data type in Excel fails to identify and populate data for well-known major cities while successfully recognizing smaller locations.

How to Fix Excel Geography Data Type Not Recognizing Major Cities

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to use the Geography data type to pull geographic data (such as population, coordinates, or regional data) for a list of cities.
Observed behavior: Excel returns an error or fails to link the text to its geographic database for certain major cities, resulting in incomplete datasets and disrupting distance calculations or mapping.

Before you start

Ensure your computer is connected to a stable internet network, as the Geography data type requires online access to query its database. Also, check your dataset for any accidental typographical errors or hidden spaces before proceeding.

Solution 1Recommended

Use Standardized Spellings and Contextual Names

Adding regional context or using alternative standardized spellings helps Excel's underlying database correctly identify the location.

The Geography data type relies on a specific online database that may have strict spelling requirements or outdated naming conventions. If a city is not recognized, providing more geographic context often resolves the ambiguity.

Add state or country information

Instead of typing just the city name (e.g., 'Paris'), type the city followed by a comma and the state or country (e.g., 'Paris, France' or 'Paris, Texas').

Apply the Geography data type

Select the updated cell, navigate to the Data tab on the Excel ribbon, and click 'Geography' in the Data Types group.

Review the Data Selector pane

If Excel still cannot find a match, click the question mark icon that appears next to the cell to open the Data Selector pane on the right. Search for alternative spellings or historical names for the city.

Quick Tip: Using a concatenated helper column (e.g., =A2 & ", " & B2) to combine city and country columns can speed up recognition for large datasets.

Solution 2

Utilize a Geocoding Service or Add-in

Bypass the built-in feature's limitations by using a dedicated geocoding service or Excel add-in for reliable coordinates.

Solution 3

Create a Manual Lookup Database for Missing Cities

Maintain a custom coordinate and data list for the specific cities that Excel fails to recognize.

Free Microsoft Office alternative

Manage Complex Data and Lookups Seamlessly in WPS Office

If you are frustrated by inconsistent Excel features or expensive subscription costs, WPS Office provides a powerful, free alternative. It easily handles complex formulas, custom lookup tables, and large geographic datasets without the heavy resource usage of Microsoft Office.

1. Download WPS Office: Visit the official WPS website and click the 'Download WPS Office Free' button to get the installer.
2. Install the Software: Run the downloaded setup file and follow the quick on-screen instructions to install the suite on your device.
3. Open Your Spreadsheet: Launch WPS Spreadsheets, navigate to your files, and open your existing .xlsx document to continue analyzing your geographic data seamlessly.

Fully compatible with Microsoft Excel (.xlsx) file formatsLightweight architecture ensures fast performance with large datasetsFree to use with a familiar, easy-to-navigate spreadsheet interfaceRobust support for XLOOKUP, VLOOKUP, and custom mapping formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel recognize small towns but miss major cities?

Excel's Geography data type relies on a specific online database (powered by Bing/Wolfram). Inconsistencies, missing data updates, or strict spelling requirements in that specific database can cause it to miss major cities while successfully matching smaller ones.

Can I manually add a city to Excel's Geography data source?

No, you cannot directly edit or add to Microsoft's built-in Geography data source. However, you can create a custom lookup table in a separate worksheet containing your missing cities and reference it alongside the built-in feature using lookup formulas.

Do I need an internet connection to use the Geography data type?

Yes. The Geography data type requires an active internet connection to query the online database and pull in geographic details such as population, location, and coordinates.

Are there alternative ways to calculate distances between cities in spreadsheets?

Yes. Instead of using the built-in Geography data type, you can use a geocoding API via Power Query, install a dedicated mapping add-in from the Office Store, or import a static CSV database containing city latitudes and longitudes to calculate distances using the Haversine formula.

Excel & Data Problems