Excel Latitude and Longitude with Geography Data Type

Cities stored in Column A can provide their Latitude and Longitude automatically when Excel recognizes them as Geography data types. This works well for operators who need coordinates for delivery zones, branch maps, service areas, or location-based reports.

The problem

A city name in Column A is only text until Excel converts it into a linked Geography data type. A linked data type connects the cell to online geographic information and displays a small map icon beside the city. Excel can then retrieve fields such as Latitude and Longitude from that city record. Microsoft documents this workflow for city, country, state, and province names.[4][5]

This issue came up in the r/excel community, where the goal was to place each city’s Latitude in Column B and Longitude in Column C automatically. The original Excel community question shows the map icon already appearing beside the city names.

The solution

First convert the cities in Column A to the Geography data type:

1. Select the city names in Column A.
2. Open the Data tab.
3. Choose Geography from the Data Types section.
4. Check that Excel adds a map icon beside each recognized city.
5. Resolve any ambiguous matches by selecting the cell and choosing the correct location from the card or suggestion menu.

Once Excel recognizes the cities, use these formulas:

• In B2, enter =A2.Latitude
• In C2, enter =A2.Longitude

The dot after A2 tells Excel to retrieve a field from the linked geography record. Latitude returns the north or south position. Longitude returns the east or west position. Excel supports this dot notation for formulas that reference Geography data types.[2][4]

Copy both formulas down for the remaining rows. Excel will adjust A2 to A3, A4, and so on.

This is usually the clearest approach because each output column has one purpose. Column B contains Latitude. Column C contains Longitude. Operators can inspect or adjust either field without changing the other.

A second approach can return both fields with one dynamic-array formula. A dynamic array is a formula result that automatically fills neighboring cells. Enter this in B2:

=FIELDVALUE(DROP(A:A,1),{"Latitude","Longitude"})

FIELDVALUE retrieves one or more fields from a linked data type. DROP(A:A,1) removes the first row of Column A, which is useful when row 1 contains headers. The array {"Latitude","Longitude"} requests the two fields in that order. Microsoft defines the FIELDVALUE syntax as FIELDVALUE(value, field_name) and the DROP syntax as DROP(array, rows, [columns]).[1][3]

The combined formula can populate two columns and multiple rows from one cell. However, the separate formulas are more robust for most business workbooks. They are easier to troubleshoot, easier to explain to staff, and less likely to fail because another value is occupying the spill area.

How to apply it

1. Put one city name per row in Column A. For reliable matching, include a state, province, or country when a name is common. For example, use Puebla, Mexico instead of only Puebla when the workbook includes locations from multiple countries.
2. Select the city range, such as A2:A100.
3. Go to Data, then choose Geography under Data Types.
4. Confirm that the map icon appears beside each recognized city.
5. Click a cell with an uncertain match and select the correct location from Excel’s suggestions.
6. Add Latitude as the header in B1 and Longitude as the header in C1.
7. Enter =A2.Latitude in B2.
8. Enter =A2.Longitude in C2.
9. Fill both formulas down through the list.
10. Check several results against the city cards. This catches spelling errors and cities with the same name.
11. Convert the range into an Excel table with Insert, Table if the list will grow. Tables make it easier to keep formulas and headers aligned as new cities are added. Microsoft recommends creating a table when working with geographic data, although it is not mandatory.[8]
12. If a formula returns an error, inspect the source cell in Column A. The city may still be plain text, the location may be ambiguous, or the required field may not be available for that record.

Do not assume this is the same as Google Sheets. Excel’s map icon and dot formulas depend on Excel’s linked data type feature. A workbook opened in another spreadsheet application may not preserve the same behavior.

Keep a practical fallback for important operational files. If coordinates drive route planning, delivery pricing, or customer commitments, store a reviewed copy of the coordinates as values after validation. Linked data types use an online data source, so a business process should not depend on an unverified match.

Implementation angle

This pattern can become a controlled location-enrichment step for a sales, delivery, or inventory workbook. An automation partner could move the city list into Zoho Creator, use Make or n8n to validate locations and synchronize approved coordinates, then return a clean table for Excel reporting. Claude can also help generate the Latitude and Longitude formulas, check for ambiguous city names, and flag rows that did not populate correctly.

Book an AI implementation engagement with Creator Scripts to turn this location process into a reliable workflow.

Frequently asked questions

How do I get latitude and longitude from a city name in Excel?

Convert the city cells to the Geography data type from the Data tab. Then enter =A2.Latitude and =A2.Longitude in separate columns, replacing A2 with the cell that contains the city. Fill the formulas down the list.

Why is my Excel latitude or longitude formula returning an error?

Check that Excel converted the city name to the Geography data type and shows a map icon in the cell. If the city name is unclear, add a state, province, or country and select the correct match. The requested field may also be unavailable for that location.

Can Excel fill latitude and longitude for a whole list of cities?

Yes. Enter the latitude and longitude formulas beside the first city, then fill them down for the other rows. You can also use a dynamic-array formula, but the cells where results should appear must be empty.