Linked data types are a cool use of AI, allowing Excel to recognize and link data to external sources automatically. This integration streamlines workflows and empowers users with up-to-date, contextual information on entities like geography, stocks and more.
To follow along with this demo, crack open a new workbook. You need to be on Excel 365 to have access to linked data types.
What data types are possible?
This feature is possible with the following data types:
- Stocks
- Currencies
- Geography
You can also create custom data types for your own organization with the help of Power BI.
Preparing the data
To utilize custom data types, simply start typing the desired data points to gather more information instantly. Converting the source to a table is also recommended, according to the documentation.
Example 1: Geography
Starting with the Geography data type, I’ll list the three Benelux countries and convert them into a table:
The intentional typo in this table serves to showcase the AI-powered features of these data types. To witness this in action, navigate to the Data section on the ribbon and choose Geography under Data Types:
Notice the map icon on the left of each country – this signifies the custom Geograpy data type. Click on the dropdown icon next to your data to access additional attributes for each country, like Area.
As a Table format, you can instantly add a new column. You now have the flexibility to include various elements, such as adding an image to a cell. For instance, you can utilize this feature to insert the geographic entity’s flag, even though it’s currently labeled as “Image” in the system.
Adding rows to your custom data type is a breeze. Let’s include a larger country, like the United States. Thanks to AI, your custom data type will readily recognize “USA:”
Example 2: Stocks
This also works with stocks. Simply type in some companies, and Excel will do its best to look up the correct entity using stock tickers:
You can explore various attributes of these stocks, but here’s something odd—when I choose “Open,” I realize that the values are listed in Euros. After a closer look at the ticker, I realize these are the shares traded in Frankfurt.
The AI erroneously linked the term “Disney” to the Frankfurt-based stock instead of the correct New York-based one. To rectify this, try inputting “Walt Disney” which should update the stock ticker to the accurate New York exchange.
Now, although we have the correct ticker and opening price, there’s still an issue: the displayed stock price is formatted in Euros, even though the real units are now US dollars. To resolve this, you should copy the formatting from another cell and apply it to the Disney stock cell.
Subject matter expertise is crucial for using AI due to its potential to make assumptions and exhibit naivety.
Once you specify the desired entities, their attributes are automatically updated every five minutes. For more information on refresh rules, refer to the documentation.
Keep in mind that the Stocks data type lacks historical data, providing only the latest attribute values. If you need historical stock price data, consider using the STOCKHISTORY() function.
How is this AI? Why does it matter?
Linked data types exemplify AI by leveraging natural language processing (NLP) to interpret user queries, extracting data from unstructured sources using AI algorithms, mapping and classifying information, providing real-time data updates, and integrating data from various sources. In particular, linked data types use a knowledge graph, which involves mapping and classifying different types of data, associating relevant information with appropriate entities.
Compare your experiment in linked data types to the solution workbook below:
How have you used linked data types in Excel? Have you had issues with Excel recognizing the correct entity to link to? Let me know in the comments.
Leave a Reply