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.
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
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
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:
Step 4: Select data type
On the ‘Data’ tab, click either ‘Stocks’ or ‘Geography’ (here, we will select ‘Geography’).
Step 5: 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
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.
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...