Custom Data Types in Excel 365

How to use the new custom data types in Excel 365 and create your own custom data type. Watch the video below:

Custom Data Types in excel

We have learnt how to work with text, numbers and formulas in Excel. Next we learnt how to use array formulas like Filter. We also introduced the stock data type in the stock-history function. Today we will explore many connected data types like countries, cities, movies, people, etc.

What are the linked data types available in Excel 365? Linked data types connect to sources of data, such as Bing, Wolfram Alpha, Power BI, etc. Here is a list of linked data types in Excel:

Linked Data TypeDescription
StocksTo insert a stock price into Excel, we first convert text in a cell or range into the Stocks data type. Then we can extract details like the stock price, change in price, and so on.
CurrencyExchange rates
GeographhyGet data about country/region, city, state, administrative division, county, township, municipality, district, shire, province, territory, zip code
CityGet data about city, neighborhood, US ZIP Code
UniversityGet data about tuition fess of a University like Harvard
ChemistryDisplays information about chemical, element, isotope, mineral
YogaMakes info available about yoga pose, yoga position, yoga sequence, yoga prop
FoodGet info about food, food type, food type group, nutrient
ActivityActivity formula calculates calories burned while doing a physical activity for specific time.
MovieCan display details about a movie like cast or revenue generated at the box office.
PlantWe can get info about plants like rose or lily.
PeopleDisplays info about persons like the president of the US.
AnimalDisplays info about dog breed like beagle, for example
AnatomyWe can learn about the anatomy of the ear, eye, etc.
MusicProvides data about music act, music work, musical instrument, music album
MedicalWe can get data about diseases like rabies, etc.
AutomaticDepending on the data in a cell or range Excel can detect the subject and provide data about countries, media, activity, geography, stocks, language, etc
Custom Data Types

How to use linked data types:

  1. In an Excel worksheet, enter specific identifying text into cells or cell.
  2. Once we select a data type from the data tab as shown in the video, Excel looks for a match between the cell values and the database provider.
  3. If there’s a match, we’ll see a data type icon in the cell to indicate it was converted successfully. If you see a Question mark icon and the Data Selector pane opens, Excel needs our help to choose the right data type.
Custom Data Types
Custom Linked data Types

Using Power Query we can create our own linked data types quickly and easily. Watch the video to learn how to create a custom data type regarding the customers called MyCustomers. We can also update our connected data automatically with a click on ‘Refresh’.