BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Soon you will be able to display information from almost any common dataset in your Excel worksheets automatically. For now your access will probably be limited to stocks and geographical data, but many more data types (including your own custom ones) should be available soon.
- New Excel data types will make a big difference to your life
- Creating and using data types (this blog)
- The built-in data types available (and those coming soon)
- Power BI datasets as Excel data types
Posted by Andy Brown on 22 December 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating and using data types
For this page, I'll use the example of the Geography data type:

If you don't have this in your version of Excel, you either need to wait until it's rolled out to you or change your update channel.
Setting up your worksheet
Start by typing something into a block of cells containing (in our case) geographical information:

When I first tested this, I tried out successively smaller places. I was expecting this feature to fail after Manchester, but to my surprise it even recognised the village of Hadfield, where the Wise Owl head office is.
Optionally, make this into a table:

With any cell in the list selected, either choose the option shown or press Ctrl + T.
If you have any sense, your list will have a header row, so tick the relevant box:

In our case, the header row contains the column title Place.
Your table will be formatted:

You don't actually need to make a list of data into a table to use the new data types, but since Microsoft recommend this, who am I to quibble?
Applying a data type
Now select the cells containing your (here) place names:

It doesn't matter whether you select the column header or not.
Go to the list of Excel data types:

Click on the arrow to see a full list of the data types available.
Choose in this case Geography:

As I keep saying, you may well not have this available, as I'm on a preview channel in Excel.
Excel shows that it's recognised your places by an icon:

The picture shows that each place has been recognised.
Adding columns to your table
You can now choose to display additional information in (at least) three different ways. One way is to create a new column whose name is one of the data type's field names:

Just adding a column to your table called Latitude will automatically show the latitude corresponding to each place.
A second way is to add a formula to reference the value in the first column:

The syntax is as shown here.
Instead of using the column name, you could just use a cell reference instead:

Whether you use the cell reference or column name is immaterial: you get a list of the available field names in either case.
The third (and probably easiest) way to add a column is using this icon, which appears at the top right of your table:

Click on this icon to add any column.
You can click on this to add (for example) the longitude of each place:

You can choose which field you want to add.
To get this:

Excel adds an additional column to your table.
Resolving problems
What happens when Excel can't find an item in a data type?

I live in Rose Hill, a tiny part of a town called Marple - not surprisingly Excel can't find this.
In cases like this, you can click on the question-mark tool shown selected above:

None of the suggested place names is actually correct, so I'll search for a new one.
You can use the search box to find the place in question:

That looks more like it! Click on the correct place in the list offered.
Excel will (in this case) replace Rose Hill with Marple.
- New Excel data types will make a big difference to your life
- Creating and using data types (this blog)
- The built-in data types available (and those coming soon)
- Power BI datasets as Excel data types