Using stocks and other data types in Excel

Q. Can you tell me how to use the linked data types in Microsoft Excel?

A. You can convert text in Excel to linked data types. This is referred to as “linked” data types because the data is connected to an online data source. Once the data is linked, this connection will remain, and the data can be updated at any time. This is a great feature in Excel because it can extract many types of data for you and bring it into Excel. For example, if you were to link to a list of public companies, you could instantly extract information about each company’s stock price, market cap, shares outstanding, etc. If you were to link a list of countries, you could immediately obtain information about the countries’ leaders, population, capital, etc.

There are hundreds of different data types to which you can link your data. Just to name a few examples: currencies, food, movies, and activities. Most of the linked data types are only available to users with Microsoft 365. You can view the different linked data types available to you by clicking Data on the Ribbon. The linked data types that are available will appear in the Data Types group. Although you may only see a few data types listed in this group, choosing the option for the Automatic data type will link your data to many possible data types that you do not see within that group.

You can use the example Excel tables and video located at the bottom of this article for using the linked data type feature. Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

To use linked data types, start by creating a list of your data in an Excel spreadsheet. It is not required, but it is recommended that the data be within an Excel table. To convert a data range to a table, click anywhere within the data and click Ctrl+T. The first example in the accompanying Excel workbook and video contains a list of companies, as shown below on the left. After creating the list of companies, select the entire list of companies, go to Data, and click Stocks within the Data Types group. The resulting list appears below on the right.

Notice the icons that appear to the left of each company. The company names that have properly converted to a linked data type for stocks will display the building icon. Some of the company names display a question mark, which means additional help is required to identify the right company. Click the cell that contains the first question mark. The Data Selector will open on the right side of the screen. You may be given multiple suggested results, and you should select the one you are looking for. See the screenshots below. Once you make your selection, the company will be properly linked. Repeat these same steps for all companies that contain a question mark until all are properly linked.

Now that all the companies in the list are properly linked, you can pull in a wealth of information about each company. Simply click anywhere within your table of companies, then click the Add Column icon (the icon would say Insert Data if the data was not in a table). Choose what stock information you would like added to the table, and Excel will add that information for each company in a new column in the table. See the screenshot below for the final stock table for this example.

Let’s go through another example using geographic data. The process is very similar to what we just did. In this example, create a table containing data about various states of the United States of America or open the accompanying Excel workbook to access this table. The table is shown in the screenshot below.

Next, select the entire list of states, go to Data, and click Geography within the Data Types group. The resulting list appears below. Note that all states were properly converted to a linked data type and display the map icon.

Next, click anywhere within your table of states and click the Add Column icon. Choose what geographic information you would like to include in the table. See the screenshot below for the final states table for this example.

Lastly, let’s link some data about diet and exercise. This example is not business-related but demonstrates the power of the artificial intelligence being used in these linked data types.

Let’s suppose you would like to keep track of your diet and exercise but do not want to have to look up the calories, fat, carbs, etc., for everything you eat or how many calories you burned for every exercise you do. You can now keep a log of your diet and exercise in Excel, and with a few clicks, you can extract lots of information about your diet and exercise.

See the screenshot below for an example of a daily log of a person’s diet and exercise. Notice that modifiers, such as amount and weight, were added to the text to get even more precise information about each piece of data.

Next, select the food items in the first table, go to Data, and click Food within the Data Types group. Then select the exercise(s) in the second table, go to Data, and click Activity within the Data Types group. The resulting list appears below.

You could have chosen the data type Automatic for each of these as well, and it would have worked. The automatic data type will attempt to automatically recognize the type of data and prepare the content.

Next, click anywhere within your table of food and click the Add Column icon. Choose what dietary information you would like to include in the table. Do the same for the table of exercise. See the screenshot below for the final diet and exercise tables for this example.

You can view a video demonstration of the Data Types feature being used in this article below.

— By Kelly L. Williams, CPA, Ph.D.