Director SumProduct
Share this content
Tags:

Creating the new data types in Excel

In the next episode of his data types series, Liam Bastick walks us through a six stage process to create the new Stocks and Geography data types.

25th Aug 2020
Director SumProduct
Share this content
Spreadsheet icon
istock_200degrees_aw

Last time we introduced two new data types that allow Excel to do even more: it can recognise real-world concepts, beginning with Stocks and Geography. 

We’ve seen how useful these data types are, but how do you set them up?  Assuming you can access these new rich data types, it’s as easy as counting to six…

To create these data types, there is a simple six-stage process.  It works similarly for both Stocks and Geography.  For this walkthrough illustration, let’s use the Geography rich data type.

Step 1: Input data

Input data

That’s right; just type some text into cells. In this example, country names have been typed in – and note the deliberate spelling of “Brasil” (depending upon what language you speak!).  You may also have typed province names, territories, states, cities or other geographical categories.  Similarly, if you require stock information, you could type company names, fund names, ticker symbols, and so on.

Step 2: Create a table

Create a Table

Although it's not required, it is recommended you create an Excel Table. This is so ranges may be extended readily and easily later, should you wish. Select any cell in your data and go to Insert -> Table in the Ribbon or use the keyboard shortcut CTRL + T.  This will make extracting online information easier later on.

Step 3: Select cells

At this point, select the cells that you wish to be converted to the Geography data type:

Select cells

Step 4: Select data type

Data type

On the ‘Data’ tab, click either ‘Stocks’ or ‘Geography’ (here, we will select ‘Geography’).

Step 5: Icons appear

Icons appear

If Excel finds a match between the text in the cells and the online sources, it will convert your text to either the Stocks or Geography data type (as selected). You will know immediately if they have been converted since they will have the icon for Stocks and the icon for Geography.

Here, all text has been recognised – including “Brasil” now renamed “Brazil”. This type of data matching is known as “fuzzy matching”, where text is amended to be consistent with how it is recognized by the rich data type.

Step 6: Add a column

Step 6

Click the ‘Add Column’ button, and then click a field name to extract more information, such as ‘Population’ (pictured).

If you see the question mark symbol instead of an icon, then Excel is having difficulty matching your text with data in Microsoft’s online resources.  Go back and review your data.  Correct any spelling mistakes and when you press ENTER, Excel will do its best to find matching information.  If this does not work, click the question mark symbol and a ‘Selector’ pane will appear.  Search for data using a keyword or two, choose the data you want and then click ‘Select’.

In part three of this series I'll offer some tips for when you start working with these two new data types. 

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.