There’s a cool feature in Excel for Microsoft 365 that not many people know about. Linked Data Types allow you to access data from a range of subject areas such as finance, geography, nutrition, and media directly in Excel.
Do you need to include the latest exchange rates, company stock prices, or city population figures in your spreadsheet? If so, then this introduction to Excel linked data types is for you.
What are linked data types?
Did you know that Excel has access to data on hundreds of subjects like company data, countries and cities, food, animals, and tons more? A feature called linked data types was introduced in 2018 that allowed you to use real-time stock prices or geographical data in your spreadsheet directly without setting up a data connection or feed of any sort.
The range of data available via linked data types has grown considerably over the last couple of years and now includes data across 19 categories.
- Activities
- Anatomy
- Animals
- Chemistry
- Cities
- Currencies
- Foods
- Geography
- Locations
- Medical
- Movies
- People
- Plants
- Space
- Stocks
- Structures
- Terrain
- Universities
- Yoga
Linked data types make it easy for you to complete common day-to-day tasks in Excel such as using real-time exchange rates in your financial calculations or referencing nutritional information in a recipe database that you’ve created.
Some common use cases include:
- Currency conversion – if you have spreadsheets that need to convert currencies, then it makes sense to use the Currencies data type as it will always pull through the most recent exchange rate
- Stock Market price analysis – you can quickly do some basic price analysis using the Stocks data type.
- Recipe analysis -you can create recipes and summarise various nutritional aspects based on the information pulled from the Foods data type.
- Fitness Planner/Tracker – you can create a fitness plan using data from the Activities data type and track actual progress in terms of calories burned.
- Fun stuff – you can create quizzes using any of the data sources, or favorite movie lists.
Microsoft has created several templates that demonstrate some of the data types. You can check them out here.
Once you’ve seen how easy it is to use linked data types, you’ll find lots of use cases for them, so let’s take a look at an example.
How to use linked data types
The best way to see how to use linked data types is with an example.
Let’s take a slightly contrived example where I want to create a shortlist of cities in Texas which would be the best location for a small business I want to start there. There are obviously a number of factors to consider that are specific to the nature of the business, but on a general level, I’m interested in a place with high median incomes, low crime levels, and a low unemployment rate. I can get all of this information in a few minutes using linked data types in Excel.
First, I need to create a list of cities in Excel:
Now I need to tell Excel that these cells have cities in them and that I want to retrieve the linked data for each city. To do this, highlight the range of cells and click on Data, and from the Data Types option, select Cities.
Excel will now connect through to the data source (in this case its Wolfram) and if the cities are recognized, the cell type will be converted to linked data type cells (a small city icon indicates the type of linked data). There was one city that returned more than one result, so Excel flagged it and prompted us to select which one should be used.
We now have a column of cells that have been converted to linked data type. All we need to do now is select which data we want to retrieve from the Cities data source. To do this, we should first convert the list of cities into a Table (select a cell in the list and click CTRL-T) – converting to a table makes it quicker and easier to work with the linked data types.
Now, if you select a city in the table and click on the icon, the data card will be displayed – this shows all the available columns for that particular data type.
For this example, I’m going to select a number of fields relating to economic and crime indicators to build up the following table:
Note that the unemployment rate for Arlington couldn’t be retrieved. This is usually due to either missing data in the data source, or a data type mismatch (e.g. the source may say “N/A” which is text, whilst Excel is expecting numbers).
Finally, I’m going to add some conditional formatting to make it easier to pick out some of the highlights from this data, and make it easier for me to shortlist the preferred locations in Texas. Based on the highlighted cells, it looks like Frisco and Plano would be good places for my shortlist.
A few other things to note about using linked data types:
- You can refresh the data in the linked data cells by selecting a cell and clicking on Data > Refresh All
- You can see a preview of the data for a particular cell by clicking on its icon, or selecting the cell, right-clicking and selecting ‘Show Data Type Card’
- You can get data from a linked data cell by referencing the source cell and the field to retrieve, e.g. if you typed the following into a cell, you’d get the population value for whichever city is in B3 : =B3.[city population]
How to create your own linked data types
When people see how linked data types work in Excel, one of the first things they say is “This is great, but can I also create my own data types?”. The answer is “sort of”.
Microsoft has added a feature to Power Query which allows you to create your own data type based on a selection of data columns. It’s explained pretty well here. This example gets some data from a table in a Wikipedia article, but you can get data from any supported data source (e.g. flat file, database, Azure).
The process involves the following steps:
- Get data from a source into Power Query
- Perform any clean-ups or other transformations in Power Query (optional)
- Select the columns you want to be available in the linked data type
- Create the linked data type, specifying the main display column
- Load the linked data type column into Excel from Power Query
- Use the data!
The result is that you can get data from an external source into Excel so that it behaves like a linked data type. The screenshot below shows the US President linked data type from the Microsoft example.
The problem is that you can’t re-use this connection outside of the workbook that you create it in. This means that it’s not as useful as the other standard linked data types.
In essence, this process simply imports data from a source in a collapsed format, and provides you with the ability to view details via the data card, or expand the table to include the extra columns.
A more complete solution would involve creating a linked data type that can be accessed by any user in your organization. A great example would be if you could create a linked data type that contained information about your company’s products (e.g. Product Name, Description, Cost Price, Sales Price, SKU, etc). Users could then retrieve information about a list of products by using the ‘Company Products’ linked data type. Of course, the creation of the linked data type would need the involvement of your IT Admin to make sure that it is available to all the relevant subscribers to the organization’s Excel for Microsoft 365 account.
Summary
Linked data types allow you to access a wide range of public information directly from within Excel, and to include it in your spreadsheets. If you need to include common information like stock prices, geographic stats, food nutrition figures, and other general knowledge, then using linked data types will save you a lot of time.
Although you can create your own custom linked data types, the feature is somewhat limited at the moment, and we’ll hopefully see more progress from Microsoft in this area in the near future.
If you need a quick and easy way to import data from SQL Server into Excel and perform updates that you can save back to SQL, then check out SQL Spreads.
Leave a Reply
No comments yet. Be the first!