Browse 554 attributed reviews, viewable separately for our classroom and online training
The new Excel data types will make a big difference to your life
Part two of a four-part series of blogs

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.

  1. New Excel data types will make a big difference to your life
  2. Creating and using data types (this blog)
  3. The built-in data types available (and those coming soon)
  4. 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:

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:

Place names

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:

Making 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:

Header rows

In our case, the header row contains the column title Place.

 

Your table will be formatted:

Formatted table

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:

Place names selected

It doesn't matter whether you select the column header or not.

 

Go to the list of Excel data types:

Excel data types

Click on the arrow to see a full list of the data types available.

Choose in this case Geography:

Geography data type

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:

Geographical places 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:

Adding latitude column

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:

Typing in column formula

The syntax is as shown here.

Instead of using the column name, you could just use a cell reference instead:

Adding by cell reference

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:

Add column icon

Click on this icon to add any column.

You can click on this to add (for example) the longitude of each place:

Adding longitude

You can choose which field you want to add.

To get this:

Added longitude

Excel adds an additional column to your table.

 

Resolving problems

What happens when Excel can't find an item in a data type?

Resolving unmatched item

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:

Data selector ribbon

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:

Finding Marple

That looks more like it! Click on the correct place in the list offered.

 

Excel will (in this case) replace Rose Hill with Marple.

This blog has 0 threads Add post