Bringing AI to Spreadsheets: The Power of Excel 365’s Linked Data Type

Microsoft Excel 365 is an innovative tool in the field of data management and analysis. Its ‘Linked Data Types’ feature stands out from the rest, as it connects cells to live data from credible sources like Bing and Power BI. This feature is a game-changer for data analysis, providing dynamic insights into a wide range of topics, from stocks and currencies to geographical locations and organizational data. Whether you’re keeping track of your stock portfolio’s performance, studying population trends, comparing exchange rates, or managing organizational data, Excel’s Linked Data Types take your data analysis to the next level. In this article, we present the 3 most important data types, as well as their use cases and best practices.

Geography Data Type

In order to add location information to your Excel sheet, you need to convert the text into the Geography data type. After this, you can utilize an additional column to extract relevant details related to that data type, such as GDP, Total Population, or unemployment rate.

How to set the Geography Data Type

• Type some text in cells, such as countries, states, areas, or city names.
• Select the cells containing Geographical location names.
• With the cells selected, go to the Data tab and then click Geography.

• If Excel finds a match between the text in the cells and online sources, it will convert your text to the Geography data type. You’ll know they’re converted if they display a specific Geography icon.

• Select one or more cells with the Geography data type. An “Insert Data” button will appear. Click that button, and then click a field name to extract more information, like “Population.”
• Click the “Insert Data” button again to add more fields. If you’re using a table, type a field name in the header row to create a new column with the related data.

• Perhaps the best feature is data visualization because a color-coded map can be produced with almost a click of a button, both on world~ and country maps. With this, you can create perfect infographics of your existing data in a geographical breakdown in seconds. As usual, go to the Insert tab, then choose the Maps chart types.

Geography Data Type Use Cases

1. Educational Research: Students and educators can use the geography data type to pull in data for research projects. For instance, if a student is doing a report on various countries, they can input the country names and then pull in data like population, GDP, area, etc., directly in Excel.
2. Business Analysis: Companies with an international presence can use this data type to analyze various geographic locations where they operate. They can pull in data like population, GDP per capita, or area and use this data to inform business strategies.
3. Travel Planning: Travel agencies or individuals planning trips can use the geography data type to gather information about various destinations, such as population density or average temperature.
4. Real Estate: Real estate companies can use the geography data type to analyze housing markets in different regions. They can input a city or region name and then pull in data like population or area to aid their analysis.
5. Data Visualization: The geography data type can be used to enhance data visualization in Excel. For example, you could create a chart that compares the population of different countries or cities.
6. Public Policy / Nonprofit Work: Organizations that work in public policy or social work could use the geography data type to gather data about different regions to inform their work. For instance, a nonprofit working on water access could use data about country populations and areas to help identify regions where their work might be most needed.

Currency Data Type

Excel 365 offers a convenient Linked Data Type called the Currency Data Type. It enables effortless access and comparison of exchange rates from different countries.

How to set the Currency Data Type

• Enter the currency pair in a cell using the following format: From Currency / To Currency, with the appropriate ISO currency codes. For instance, type in “USD/EUR” to obtain the exchange rate from one United States Dollar to Euros.
• To convert the selected cells into a data type, stay on the cells and navigate to the Data tab. From there, choose the Currencies data type. If there’s a match between the currency pair and the data provider, the text will be converted into a data type, and an icon of Currencies will be visible in the cell. However, if Excel faces difficulty in matching the text with data, you might want to double-check for any errors and retry the process.
• In order to gather additional details from the Currencies data type, simply choose one or multiple converted cells and click on the Insert Data button that appears or use the shortcut Ctrl/Cmd+Shift+F5. A list of all available fields will then appear, allowing you to select the ones you wish to add to a new data column. For instance, the Price field displays the exchange rate for the currency pair, while the Last Trade Time field shows the quoted time of the exchange rate.

• After collecting all the necessary currency data, you can apply it to your calculations and formulas. It’s important to keep your data current, so you can click on Data > Refresh All to get the latest quote.

Currency Data Type Use Cases

1. Currency Conversion: If you’re dealing with financial data from different countries, you can use the Currency Linked Data Type to convert monetary values from one currency to another. For example, if you have sales data in US Dollars and need to report those figures in Euros, this data type can help.
2. Financial Analysis: This data type can be useful in conducting financial analysis involving multiple currencies. For instance, you might use it to compare the relative values of investments in different currencies or to understand how changes in exchange rates might impact your business or investments.
3. International Business Operations: Companies with international operations often deal with multiple currencies in their day-to-day business. The Currency Linked Data Type can help track and calculate costs, revenues, or profits in various currencies.
4. Travel Planning: For personal use, if you’re planning a trip to a foreign country, you can use the Currency Linked Data Type to get a sense of exchange rates and help budget your travel expenses in the local currency.
5. Education and Research: It can be used for educational purposes or research, especially in economics or finance, where understanding currency exchange rates and their fluctuations over time is key.

Stock Data Type

Excel 365’s Stocks Data Type is a powerful tool that transforms a stock ticker into an interactive entity, providing rich information such as price, change, and currency. This AI-powered feature utilizes data from trusted sources like Refinitiv and Nasdaq, making it easier for users to track their personal investments. With the ability to stay within Excel and access the latest stock prices, trading volume, and other financial information, users can save time and be more efficient. The Stocks Data Type has also been expanded to include bitcoin, bonds, international currencies, and extended-hours pricing information. Users can now access company information, such as industry and company descriptions, in addition to previously available data like mutual funds and stock indexes.

How to set the Stock Data Type

1. Enter the data: Start by typing the stock ticker symbols or company names in individual cells in Excel.
2. Convert to Stocks Data Type: After you’ve entered your data, select the cells and then click on the “Data” tab in the Ribbon. In the “Data Types” group, click on the “Stocks” button. Excel will convert your selected data into the Stocks data type. You’ll know that your data has been converted if you see an icon of a building in the cells.
3. Add Data: To pull in data related to the stock (like price or volume), click on one of your converted cells to show the card icon on the top right of the cell (it looks like a small rectangle). Click the card icon to open the data card. The card will show a list of fields that you can add to your worksheet. Click on the field you want to add, and Excel will add that data to a new cell in your worksheet.
4. Refresh Data: The data pulled in by Excel isn’t updated in real-time, but you can easily refresh the data at any time by right-clicking a cell with the Stocks data type and selecting “Data Type” > “Refresh”.

Stock Data Type Use Cases

1. Personal Investment Tracking: You can use the Stocks data type to track the performance of your personal investment portfolio. By entering the ticker symbols for the stocks you own, you can pull in data such as current price, volume, and other relevant information for each stock.
2. Financial Analysis: If you’re conducting a financial analysis of different companies, you can use the Stocks data type to pull in financial data for each company. This could include information such as market capitalization, earnings per share, and other financial metrics.
3. Market Research: If you’re conducting market research, you could use the Stocks data type to monitor the performance of companies in a particular industry or sector. This could help identify trends or patterns in the market.
4. Investment Planning: If you’re planning to invest in stocks, you can use the Stocks data type to compare different potential investments. By pulling in data for each potential investment, you can compare their performance and other key metrics to help make your investment decisions.
5. Educational Purposes: If you’re teaching or learning about the stock market, the Stocks data type can be a great tool to visualize and understand real-time market data.
6. Data Visualization: You can use the stock data type to create dynamic charts and graphs. For example, you could create a chart that tracks the price of a particular stock over time.

Conclusion

Microsoft has been expanding Excel’s capabilities to work with more complex types of data beyond traditional text and numbers. The introduction of dynamic arrays and array formulas was a significant step in this direction, as was the introduction of the Stocks and Geography data types, which are connected and can contain live, rich sets of information about objects like stocks, currencies, cities, and countries. These data types were also expanded to include user-defined types, allowing users to connect to their own data for analysis.