top of page
Search

Add geography data to Excel | Tutorial


In seconds you can link an Excel cell to a free database, and then use demographic data in formulae, tables, charts and anywhere else you may find it useful.


The 'Geography' database contains everything from British counties to American states, from Swiss cantons to Chinese provinces, and from the largest of countries to tiny, remote villages.

 

Like Stock (that provides information from stock exchanges) Geography is a 'linked' data types rolled out in October 2020 to Excel 365 users. This builds upon the 'Map' chart type that was introduced in Excel 2016. For details see the "related posts" section below this tutorial.

 

Download Geography workbook

Geography
.xlsx
Download XLSX • 5.39MB

  1. Download and open the above Geography workbook

 

Geography data types

  1. Go to the Victoria worksheet

  2. Select the cell containing, "Victoria"

  3. Choose the Data menu in the ribbon

  4. Locate the Data Types group

  5. Click the Geography button

 

Excel searches the online database for something matching, "Victoria", and it should find more than one. When Excel cannot pick a single place it will present a blue, encircled question mark in the cell, as shown.

 

When the selected cell contains an unresolved ambiguity (as it does here) the Data Selector frame appears showing all the places it might be, starting with the most likely.


In the Data Selector:

  1. Click where it says Victoria, Australia (or image)

  2. Review the Details for Victoria, Australia

  3. Click ← near the top of the Data Selector

  4. Click 'Select' directly below Victoria, Australia

 

The data selector should disappear and the cell redisplayed with a Geography icon. It looks like a fluttering flag, and indicates that the cell contains multiple attributes describing a known place.

 

The attributes (or fields) you reviewed in the Data Selector are also visible in card form. To view the card:

  1. Click the flag icon in the Geography cell

  2. Scroll up and down to view all the attributes

  3. Click away from the card to close

Try different place names in the Geography cell. Because the enter key selects the next cell down, you will need to reselect the Geography cell to see the Data Selector frame.

  1. Find a name that Excel can pick by itself

  2. See if the smallest village you know is listed

  3. Try names no longer in use, such as Burma.

 

Navigating a country's organisational structure

Each country has it's own way organising itself, and for most countries this is visible through the cards.

  1. Set the Geography cell back to Victoria

  2. Reselect the Geography cell

  3. Show the card using [Ctrl] + [Shift] + [F5]

  4. Scroll down to the Country/region attribute

  5. Hover the cursor over, "Australia"

The green highlighting on the Country/region attribute means there's more to see, which will be another card of some type.

 

  1. Click on the green highlighted area for Australia

This shows the Australian card. Note at the top there is a left arrow, the clicking of which will return you to the Victoria card, but we won't do that yet.

  1. On the Australian card, find 'Leader(s) and note there are too many to show on one line.

  2. Hover over the Leader(s) attribute

  3. Click in the Green highlighted area

This card shows an expanded list of Australian leaders. At the top of the Australian leaders card you have the left arrow, again, and you also can see a 'breadcrumb' trail of the cards you've seem.

  1. Click 'Australia' in the breadcrumb trail

 

  1. Make sure you're on the Australian card

  2. Find the Subdivisions attribute

  3. Click to show the subdivisions card

This is similar to the Leaders card, but shows the states and territories. Although there's no green highlighting you can navigate to each state shown by clicking on one of the lines. Please spend some time navigating your way around the cards and then:

  1. Close the card by clicking away from it

 

Using attributes in Excel formulae

  1. Go to the Victoria worksheet

  2. Set Geography to Victoria, Australia

Most of the attributes you've seen can be either: (a) extracted to other cells and columns in your workbook; or (b) used in formula without extraction.


We will start by looking at the writing of formula using the attributes nested up within the Geography cell.

 

Enter the below formula into the Population cell

=Geography.[Population]

Enter the below formula into the Households cell

=Geography.[Households]

Enter the below formula into the Capital City cell

=Geography.[Capital/Major City]

These are the same value as can be seen on the Victoria card, but you will have noted that the Capital City, Melbourne, has been formatted as a Geography cell, with it's own card.

 

Enter the below formula into the topmost of the Leaders range of cells.

=Geography.[Leader(s)]

Instead of a single value, the Leader(s) attribute is an array, and the because Victoria has more than one leader, they spill over into the cells below. To ensure the spillage is into empty cells, the leaders have been presented at the bottom of the column, thereby avoiding the potential for #Spill! errors.


Microsoft have put a lot of work into dynamic arrays over the past two years, and we can expect to hear much more from them over the next two years, and from Excel House, too, as we report on their progress.

 

Enter the below formula into the Image cell

=Geography.[Capital/Major City].[Image]

There are two things of note here. Firstly, you have probably not seen an image connected to a cell in this manner before. And secondly, the dot notation can be used repeatedly to navigate through the countries organisational structure, in much the same was as one can navigate through the cards.


Here we can see that attributes can be Geography items with their own attributes. The dot notation can be used recursively to navigate repeatedly up and down a country's organisational structure.

 

  1. Go to the Table Functions tab

You will see a table containing the three Benelux countries. It seems obvious that to calculate the GDP per capita we could need columns for the GDP and for the population. And you would also expect to see a column for the land area of each country to that the total area of Benelux could be calculated. But none of these columns are present. It's time to review the formulae on this sheet.

 

Select a cell in the GDP per Capita column and note the formula is as follows:

=[@Place].GDP/[@Place].Population

Select the Total Land Area cell and note the formula is as follows:

=SUM(Table1[Place].Area)

Later we shall see how easy it is to extract attributes into columns of their own, and you may be tempted to do that liberally, but it's often unnecessary as attributed live very happily within their 'parent' cell, and over enthusiastic column creation could make your tables uncomfortably wide.

 

Dynamic table of Subdivisions

We now take a further, closer look at dynamic ranges, and will build a dynamic table that changes in size, depending on the selected geography.

  1. Go to the Iceland sheet

  2. Select the, "Iceland", cell

  3. Click Geography in the Data menu

  4. Select the country, if necessary

We now have a cell that's properly formatted with a Geography data type.

 

First, we should review the names ranges that have been set up in the Iceland sheet.


Review the Name Manager screenshot.


There are five that refer to cells in the Iceland sheet. Each of them is a single cell range and they exist below the Geography header, or below one of the four column headers.


These are the cells in which we will be entering formulae.

 

Enter the below formula into the cell immediately below the Subdivisions header

=Geography.[Subdivisions]

This created an array of subdivisions, each of which is a Geography data type.

 

Enter the following formula into the cell immediately below the Population cell.

=Subdivisions#.Population

The hash (#) symbol is important here. It recognises that the cell named subdivision is in fact the top-left cell in a dynamic range, and that means that the formula also returns a dynamic range, which like the subdivisions, spills over to the cells below.

 

Enter the following formula into the Area cell.

=Subdivisions#.Area

Again, the hash symbol is present and like the population, spills over. The cells in the Area column have been formatted to include the, "sq km", suffix, since the unit of measurement is only evident by viewing the geography card (i.e. by clicking on the flag icon in the cell).

 

Enter the following formula into the Density cell

=Population#/Area#

Here we require two hash symbols. If you only have one of the hash symbol then you'll still get a value for each region in the table, but the calculation will use the topmost value in the column.

 

Now the table is complete. In Excel terms it's not really a 'table', but a collection of dynamic arrays, with the advantage is that its size depends on the geography selected.


Try replacing Iceland with different countries, regions or lands - or anything that might be broken down into subdivisions.


You can expect some #Field! errors since not all of attributes are available for all places. You may be surprised, for instance, to find the USA has more divisions than its 50 states suggest, and that the UK divisions not only include England, Northern Ireland, Scotland and Wales (which is surely pretty close to a full list) but all the counties that cover the same area, and many of the towns, too.

 

Extracting attributes


Having earlier cautioned against overuse, it must also be said that Excel has made it very easy to create new cells and columns to house attributes.

 

Extract attributes from Card

  1. Go to Table Functions sheet

  2. Note Netherlands cell below Total Land Area

  3. Show the card for this cell

  4. Hover over the Leaders attribute

  5. Note the Extract Leaders to Grid button to the right of the Green highlighted area.

  6. Click the Extract Leaders to Grid button

This function populates a formula in the cell to the right, that refers to the Leader(s) attribute which, because it's an array and not a single value, spills over into the cells below. Any of the available attributed can be extracted in this way.

 

Add column to Table

  1. Select a country in the table

  2. Note Add column button to right of table, just above headers.

  3. Click the Add column button

  4. Select Birth Rate

Excel create a new column to the right of the table containing the birth rate.

 

Extract column from card in Table

  1. Show the card for Luxemburg in the table

  2. Hover over the Leader(s) attribute

  3. Click the Extract Leaders to grid button

Because we're trying to create a column containing arrays, it's not possible to show each arrays over multiple lines, and so they're presented as a comma-separated string instead.

 

Artificial Intelligence and Ambiguous place names

if you've been playing with Geography types for long, you may have become irritated at the need to resolve ambiguities - but you don't have to. The data selector remembers all unresolved geographies, and when one is selected provides simple navigation to the others on the same sheet.


In this example, "Ashford", has been selected, and the data selector shows that this is just one of five on the same sheet. It provides navigation buttons to more between them, and as each is resolved the next one is presented and the number remaining is reduced.

 

You may have noticed Excel's need for help diminishing. This is due to what Microsoft call artificial intelligence, whereby the decisions you take are remembered, and when patterns emerge, Excel starts making more and more decisions for you.

 

Excel's also finds inspiration in surrounding cells This is most in evidence when place names are in lists.


Go to Local Context


Herein are four lists with, "Georgia" present in both the first and second; and, "Boston", in the third and fourth; but obviously, these are not the same places, which is something Excel will realise from looking at the other items in the same list

 

  1. Select all six countries bordering the Black Sea and click the Geography button

  2. Select all six SE American states and click the Geography button

  3. Select all six Lincolnshire towns and click the Geography button

  4. Select all six Massachusetts towns and click the Geography button

The data selector should not have been presented for any of the above.

  1. View the cards for both 'Georgia's

  2. View the cards for both 'Boston's

  3. View the cards for all of the Massachusetts towns.

You should be pleased to note that Excel has taken different and correct decisions for both 'Georgia's and both 'Boston's.

 

Review


Within this tutorial we have:

  1. Downloaded the accompanying Geography workbook

  2. Turned cells into 'Geography data types' and seen different attribute in card format

  3. Navigated a country's organisational structure by jumping from card to card

  4. Used attributes in formulae, including formulae both within and around tables

  5. Used dynamic ranges to build a table of subdivisions for any given place.

  6. Extracted attributes into cells and columns of their own

  7. Learned how Excel's artificial intelligence can reduce the burden of the data selector.

 

33 views0 comments

Related Posts

See All
bottom of page