How to use the new custom data types in Excel 365 and create your own custom data type. Watch the video below:
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 Type||Description|
|Stocks||To 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.|
|Geographhy||Get data about country/region, city, state, administrative division, county, township, municipality, district, shire, province, territory, zip code|
|City||Get data about city, neighborhood, US ZIP Code|
|University||Get data about tuition fess of a University like Harvard|
|Chemistry||Displays information about chemical, element, isotope, mineral|
|Yoga||Makes info available about yoga pose, yoga position, yoga sequence, yoga prop|
|Food||Get info about food, food type, food type group, nutrient|
|Activity||Activity formula calculates calories burned while doing a physical activity for specific time.|
|Movie||Can display details about a movie like cast or revenue generated at the box office.|
|Plant||We can get info about plants like rose or lily.|
|People||Displays info about persons like the president of the US.|
|Animal||Displays info about dog breed like beagle, for example|
|Anatomy||We can learn about the anatomy of the ear, eye, etc.|
|Music||Provides data about music act, music work, musical instrument, music album|
|Medical||We can get data about diseases like rabies, etc.|
|Automatic||Depending on the data in a cell or range Excel can detect the subject and provide data about countries, media, activity, geography, stocks, language, etc|
How to use linked data types:
- In an Excel worksheet, enter specific identifying text into cells or cell.
- 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.
- 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.
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’.