MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find Latitude and Longitude for Each City in Excel


November 09, 2018 - by Bill Jelen

Find Latitude and Longitude for Each City in Excel

Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography?

If you have the new features, read on. If you don't do a search for how to join the Office Insiders program.

First, make a copy of your city data. The Data Type operation is a bit destructive and will change the text in the city cells. Make a copy first so your original cities can be used later.

Make a copy of the data
Make a copy of the data

Select the range of cells containing cities. From the Data tab of the Ribbon, click on Geography (shown below in Portuguese…your computer will show your default language).

Select the cells and click Geography
Select the cells and click Geography

Each cell should gain a Map icon. If any cells have a circled question mark instead, you might have mis-spelled the city. Select that cell and use the pane on the right side of the screen to do a search. Note: This can also happen if there are two cities with the same name, such as Uniontown Ohio or Sutter Illinois. In the selection pane, they will show you the county for the two Sutters and you can choose.

Say that your first city data type is in C2. Add a formula in D2 of =C2.Latitude.


The Formula AutoComplete offers the available fields
The Formula AutoComplete offers the available fields

In E2, use =C2.Longitude. Copy the D2 and E2 formulas down for all cities.

Excel returns the coordinates for each city
Excel returns the coordinates for each city

You might think you are done, but those formulas in columns D & E need the data type cells to remain in order to keep working. Follow these extra steps:

  1. Select D2:E999
  2. Press Ctrl + C to Copy
  3. Right-click and choose the Paste Values icon to convert the latitude and longitude to values.
  4. You can now delete the extra columns B & C.
Convert the formulas to values
Convert the formulas to values

You can now sort your data in a North-to-South order. Sort by Latitude descending

Sort north to south
Sort north to south

Or - to sort west to east, sort by Longitude ascending

Sort west to east
Sort west to east

You can also filter the data. To find all cities along the Gulf of Mexico, filter by Longitude, looking for everything less than -82.43.

Filter by location
Filter by location

Download Excel File

To download the excel file: /find-latitude-and-longitude-for-each-city-in-excel.xlsx

The new Geography data types in Excel allow you to add meta-data about each city.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Rules for lists: no blank rows, no blank columns, one cell headers, like with like"

Title Photo: Cody Black on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.