With Excel’s new rich data type feature, this is no longer the case. We now have multiple data fields inside one cell! When you think of data types you might be thinking about text, numbers, dates or Boolean values, but these new data types are quite different. They are really connections to an online data source that provides more information about the data. At present there are two data types available in Excel; Stocks and Geography. For example, with the new stock data type our cell might display the name of a company, but will also contain information like the current share price, trading volume, market capitalization, employee head count, and year of incorporation for that company. The connections to the additional data are live, which is especially relevant for the stock data. Since data like the share price is constantly changing, you can always get the latest data by refreshing the connection. Caution: The stock data is provided by a third party. Data is delayed, and the delay depends on which stock exchange the data is from. Information about the level of delay for each stock exchange can be found here. The data connection for the geography data type is also live and can be refreshed, but these values should change very infrequently.
Video Tutorial
Converting Text To Rich Data Types
Whichever data type you want to use, converting cells into a data type is the same process. As you might expect, the new data types have been placed in the Data tab of the Excel ribbon in a section called Data Types. At present the two available data types fit nicely into the space. But you can click on the lower right area to expand this space, which seems to suggest more data types are on their way!
You will need to select the range of cells with your text data and go to the Data tab and click on either the Stock or Geography data type. This will convert the plain text into a rich data type. Warning: You can’t convert cells containing formula to data types. You can easily tell when a cell contains a data type as there will be a small icon on the left inside the cell. The Federal Hall icon indicates a Stock data type and the map icon indicates a Geography data type.
Right Click Data Type Menu
The right click menu has a contextual option which is only available when the right click is performed on a cell or range containing data types. This is where you’ll find most commands related to data types.
Automatic Data Type Conversion
If you want to add to your list of data type cells, all you need to do is type in the cell directly below a data type and Excel will automatically convert it to the same data type as above.
Correcting Missing Data Types
Excel will try and convert your text with the best match, but it might not always find a suitable match or there might be some ambiguity. When this happens, a question mark icon will appear in the cell. You can click on this icon to help Excel find the best match.
Click on the question mark icon and this will open the Data Selector window pane. You can use the search bar to try and locate the desired result and then click on the Select button to accept one of the options presented.
Changing Data Types
In case Excel made the wrong choice, you can also change a data type. You right click on the data and choose Data Type then Change from the menu. This will also open the Data Selector window pane.
Currency & Cryptocurrency Data
With the stock data type, you can get more than just stocks from a company name. The stock data type can also be used for currency and cryptocurrency conversions.
Currency Exchange Rate Pairs
You can convert pairs of currencies into data types to get exchange rates.
Cryptocurrency Exchange Rate Pairs
You can convert cryptocurrency/currency pairs into data types too. Exchange rates between cryptocurrencies are currently not supported.
Country, State, Province, County & City Data
The geography data type supports most types of geographies. Countries, states, provinces, counties and cities will all work. The fields available for each type of geography will vary. For example, a country, state, province, or county might have a capital city field, but this field will not be available for a city.
Data Cards
There is a pretty cool way to view the data contained inside a cell that’s been. When you click on the icon for any data type a Data Card will appear. This is also accessible through the rick click menu. Right click then choose Data Type from the menu then choose Data Card.
Data cards will show all the available data for that cell and may also include a picture related to the data such as a company logo or a country’s flag. Each piece of data displayed in the card also comes with an Extract to grid button that becomes visible when you hover the mouse cursor over that area. This will extract the data into the next blank cell to the right of the data type. In the lower right corner of the card, there’s a flag icon. This allows you to report any bad data that might be displayed in the card back to Microsoft.
Extracting Data
The data cards are nice for viewing the data inside a cell, but how can you use this data? There are Extract to grid buttons for each piece of data inside the data cards, but these will only extract data for one cell. What if you want to extract data from many cells?
When you select a range of cells containing data types, an Extract to grid icon will appear at the top right of the selected range. When you click on this, Excel will show a list of all the available data that can be extracted. Unfortunately, you’ll only be able to select one piece of data at a time. A handy feature with extracting data, is that Excel takes care of the number formatting. Whether you’re extracting a percent, number, currency, date or time, Excel will apply the relevant formatting.
Dot Formulas & FIELDVALUE Function
Extract is probably not the best word to use for the Extract to grid button. If you check the contents of the extracted data, you’ll see it’s a formula reference to the data type cell.
Dot Formula Notation
In fact, you don’t need to use the extract to grid button to create these formulas. You can write them just like any other Excel formula. The above formula will return the population of Canada. The above formula will give us a density calculation and return 3.7 people per square kilometer. Thankfully, Excel’s formula IntelliSense will show you all the available fields when you create a cell reference to a data type cell followed by a period. This makes writing data type formulas easy! Note: When the field name contains spaces, the field reference needs to be encased with square braces. Formulas only support one dot. The above formula will return the capital of Canada, which is geographical data. But the above formula won’t return the population of the capital city of Canada. To get this information, you would need to extract the capital, then copy and paste the result as a value, then convert that value to a data type, then extract the population.
FIELDVALUE Function
The FIELDVALUE function was introduced with data types and can be used to get the exact same results from a data type cell. This new function has two arguments:
Value is a reference to a data type.Field is the name of a field in the data type.
Warning: The FIELDVALUE function does not have IntelliSense for the available fields in a data type.
The previous example to return the population of Canada can be written as above using the FIELDVALUE function. The previous example to return the population density can be written as above using the FIELDVALUE function.
New FIELD Error Type
A new #FIELD! error has been added to Excel for the dot formulas and FIELDVALUE function. This error can be returned for several reasons.
The field does not exist for the data type.The value referenced in the formula is not a data type.The data does not exist in the online service for the combination of value and field.
In the above example, Ireland is returning a #FIELD! error for the abbreviation because this data is missing online.
Refreshing Data
How do you refresh data? Using the Refresh and Refresh All commands found in the Data tab will refresh any data types you have in your spreadsheet. This means you can use the keyboard shortcuts Alt + F5 (refresh) and Ctrl + Alt + F5 (refresh all) to refresh data types. Using the Refresh command will refresh all cells in the workbook that are the same data type as your active cell. The Refresh All command will refresh all cells of any data type. Warning: Refresh All will also refresh all other connections in the workbook such as pivot tables and power queries. There is also a refresh method that is proprietary to data types. If you select a cell with a data type, you can right click and choose Data Type from the menu then choose Refresh. This will refresh all cells in the workbook that are the same data type as the selected cell and won’t refresh any other connections like pivot tables or power query.
Unlinking Data
If you no longer want your data connected, you can unlink the data by converting it back to regular text. The only way to do this is to right click on the selected range of data then choose Data Type from the menu and select Change to Text.
Warning: Linking and unlinking data might change your data. When you convert text to a data type and then convert it back to text, you might not get your original text back exactly as it was. It’s best to create a copy of any text data before converting to a data type so you always have the original text.
Using Tables With Data Types
You don’t need to use Excel tables with data types, but there is a nice bonus feature with tables when extracting data. Excel will create the column headings for you. Note: You can create an Excel table from the Insert tab with the Table command or using the keyboard shortcut Ctrl + T. Learn more about Excel tables: Everything You Need to Know About Excel Tables The Extract to grid button will appear anytime the active cell is inside a table containing data types. The active cell does not need to be on a cell containing a data type. When there are two or more columns with data type inside a table, the Extract to grid button will be based on the right most column. Unfortunately, this means you can’t extract from any other columns.
Using Sort & Filter With Data Types
Sorting and filtering data types has a nice feature that allows you to sort and filter based on any field even if it hasn’t been extracted. Note: Turn on the sort and filter toggles for any table or list by using the Ctrl + Shift + L keyboard shortcut. When you click on the sort and filter toggle of a column of data types, you will be able to select a field from a drop down at the top of the menu. This will change the field which sorting and filtering is based on. For example, we could select the Year incorporated field and sort our list of companies from oldest to newest, or filter out any company incorporated after the year 2000.
Finding Data Types In A Workbook
Data types are visually distinct because of the icons in cell, so distinguishing between a regular cell and a data type cell is easy. But what if you want to locate all the data type cells in a large workbook? Visual inspection might not be practical. Since data types are not supported by earlier versions of Excel, the easiest way to locate them in a workbook is with Excel’s check compatibility feature. Note: Check compatibility can be found in the File tab ➜ Info ➜ Check for Issues ➜ Check Compatibility.
The compatibility checker will list out all the features in the workbook that are not backwards compatible with prior version of Excel. To find any data types, you can look for the words “This workbook contains data types…” in the summary. Each summary will also have a Find hyperlink that will take you to the location and select those cells. Note: Selecting only Excel 2016 from the Select versions to show button will reduce the results in the compatibility checker and make finding the data types easier.
Conclusions
With rich Data Types you can now get more from your data without leaving Excel to use your favourite search engine. You can quickly and easily get current financial data, currency rates, cryptocurrency values and geographical data with a couple clicks. This is a very powerful feature in Excel you need to start using, and with the hint of more data types to come, will become even more useful.
