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
Download and open the above Geography workbook
Geography data types
Go to the Victoria worksheet
Select the cell containing, "Victoria"
Choose the Data menu in the ribbon
Locate the Data Types group
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:
Click where it says Victoria, Australia (or image)
Review the Details for Victoria, Australia
Click ← near the top of the Data Selector
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:
Click the flag icon in the Geography cell
Scroll up and down to view all the attributes
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.
Find a name that Excel can pick by itself
See if the smallest village you know is listed
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.
Set the Geography cell back to Victoria
Reselect the Geography cell
Show the card using [Ctrl] + [Shift] + [F5]
Scroll down to the Country/region attribute
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.
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.
On the Australian card, find 'Leader(s) and note there are too many to show on one line.
Hover over the Leader(s) attribute
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.
Click 'Australia' in the breadcrumb trail
Make sure you're on the Australian card
Find the Subdivisions attribute
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:
Close the card by clicking away from it
Using attributes in Excel formulae
Go to the Victoria worksheet
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
Enter the below formula into the Households cell
Enter the below formula into the Capital City cell
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.
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
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.
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:
Select the Total Land Area cell and note the formula is as follows:
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.
Go to the Iceland sheet
Select the, "Iceland", cell
Click Geography in the Data menu
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
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.
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.
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
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.
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
Go to Table Functions sheet
Note Netherlands cell below Total Land Area
Show the card for this cell
Hover over the Leaders attribute
Note the Extract Leaders to Grid button to the right of the Green highlighted area.
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
Select a country in the table
Note Add column button to right of table, just above headers.
Click the Add column button
Select Birth Rate
Excel create a new column to the right of the table containing the birth rate.
Extract column from card in Table
Show the card for Luxemburg in the table
Hover over the Leader(s) attribute
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
Select all six countries bordering the Black Sea and click the Geography button
Select all six SE American states and click the Geography button
Select all six Lincolnshire towns and click the Geography button
Select all six Massachusetts towns and click the Geography button
The data selector should not have been presented for any of the above.
View the cards for both 'Georgia's
View the cards for both 'Boston's
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.
Within this tutorial we have:
Downloaded the accompanying Geography workbook
Turned cells into 'Geography data types' and seen different attribute in card format
Navigated a country's organisational structure by jumping from card to card
Used attributes in formulae, including formulae both within and around tables
Used dynamic ranges to build a table of subdivisions for any given place.
Extracted attributes into cells and columns of their own
Learned how Excel's artificial intelligence can reduce the burden of the data selector.