If you've ever driven yourself crazy
searching the internet
for information about different places,
you're in luck because if you have
an Office 365 subscription,
you can automatically get certain key geographic data
in Excel with just an internet connection.
Now this will likely be available
in other versions one day,
but for now, it's just in Office 365.
Plus you'll also need to set English
as your editing language.
And all you have to do is list your locations.
So this can be done in varying levels
from country, state or province,
county, cities or towns, to postal codes.
And also it's important to know that, at this time,
you cannot use individual addresses or major landmarks.
Once you have your locations listed,
you should convert your data to a Table.
Now you don't have to do this,
but it will make it easier
to apply any updates to your places.
Otherwise, you'll have to remember
to select each item every time you refresh
or make a change, and that can take longer
and cause you to miss some locations.
Next, with your geography selected,
go to your Data tab and in the Data Types section,
select Geography.
Do that and you'll see this small icon show up.
It looks a bit like a tri-fold map.
The map icon is what lets you know
that your places are now linked
to an external data source.
If you instead see any question marks,
it means that Excel was not able
to identify a result for that data.
When that happens,
a window will open where you can retype your data
to correct any spelling errors,
add more detail, or select from the options listed.
So what information is there?
So if you click on the map icon,
it'll bring up a card with information
about the location.
Each level of geography has bits of information
that are specific to that level.
For example, the city-town level
displays latitude and longitude information
that is unavailable for other types of geographies.
You may also want to use this card
to spot check your data.
So some locations have names
that are used in multiple places.
You may not always be aware of this
and these need to have additional details
to make sure that Excel selects the one that you want.
For example, in the United States,
there are several towns with the name Springfield.
To make sure you get data for the correct Springfield,
you'll want to include the state abbreviation
when you type the location name.
So back in your data cards,
if you move your cursor to the right of an item,
you see an option where you can add that item to your data.
And if your data is formatted as a Table,
it will automatically be added to all the locations.
If that specific data field
is not available for the location,
you'll see a #FIELD! error.
Often this is because that data field
is just not available for that location type.
So for example, population data
is not available for zip code level locations.
Your list will likely be made up
of common types of locations,
but I've listed examples with varying levels of granularity
so you can see how they can work.
So you can also add this data
by clicking on this small icon here.
It is the top right of your table.
And in this case, because again,
we have varying levels of locations,
you see the data listed for all of them.
However, when you select the field,
you'll see those data fields populate
only for the location types that they apply to.
And if you ever find that you need to refresh your data,
just right-click on any of your location names,
and select Refresh.
Something important to keep in mind
is that this is still a pretty new feature
and doesn't yet work properly
with some of the other Excel tools
like pivot tables, charts, and so on.
So if you want to use the data
in some of these other features,
you'll need to first unlink the data.
So let's make a copy of this.
So unlinking the data types is a two-step process.
You'll first need to copy any data
that you've pulled
and then paste over it as values.
Then highlight all your geography names,
right-click, and in Data Type,
select Convert to Text.
Notice that the map icon is now gone.
This is a signal that that link is broken.
Breaking the link
before converting the other data to values
will cause your data to be lost
and replaced with error messages.
For more tutorials like this,
click on these links. Thanks!