Find Latitude and Longitude for Each City in Excel
November 09, 2018 - by Bill Jelen
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.
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).
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.
In E2, use =C2.Longitude. Copy the D2 and E2 formulas down for all cities.
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:
- Select D2:E999
- Press Ctrl + C to Copy
- Right-click and choose the Paste Values icon to convert the latitude and longitude to values.
- You can now delete the extra columns B & C.
You can now sort your data in a North-to-South order. Sort by Latitude descending
Or - to sort west to east, sort by Longitude ascending
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.
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
