How to Add Data Types in Excel

Excel's Data Types feature transforms simple text, like the name of a country or a stock ticker, into a powerful, interactive connection to a huge online database. This lets you pull detailed, up-to-date information directly into your spreadsheet without ever leaving the application. This article will show you exactly how to use Excel’s built-in data types and how to create your own custom ones from your company’s data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Are Excel Data Types?

Think of Data Types as a way to make your cells "smarter." Normally, if you type "Microsoft" into a cell, Excel just sees it as a word. But when you convert that cell to the "Stocks" data type, Excel recognizes it as the company Microsoft (MSFT) and links it to a wealth of real-time data.

Once converted, a small icon appears next to your text, and you can instantly extract related information into other cells. For a stock like Microsoft, you could pull in its current price, market capitalization, P/E ratio, or its number of employees. For a country like "Japan," you could pull its population, GDP, capital city, or official language. This elevates your spreadsheet from a static list of values into a dynamic report that can be refreshed with the latest information.

Excel has two main categories of these smart data types:

• Linked Data Types: These are the built-in types powered by online sources like Bing and Wolfram. They cover categories like Stocks, Geography, Currencies, Chemistry, and even Movies or historical figures. The data for these is pulled from the web and can be refreshed.
• Custom Data Types: You can create your own data types from your organization's data using Power Query. For example, you could turn a list of your products into a "Product" data type, complete with its SKU, price, inventory level, and supplier, all neatly packaged into a single cell.

Using Data Types eliminates the need for endless VLOOKUPs or manual data entry to consolidate information, making your reports more powerful and easier to manage.

How to Use Excel's Built-in Data Types

Getting started with the standard Linked Data Types is incredibly straightforward. Let's walk through an example using geographic locations.

Step 1: Enter Your Data in a List

First, type your list of items into a single column. For an optimal experience, format this list as an Excel Table. To do this, simply select any cell in your list and press Ctrl + T (or go to Insert > Table). This is not strictly required, but it makes handling the data much easier. For our example, we'll list a few countries.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Select a Data Type from the Ribbon

Highlight the cells containing the text you want to convert. Then, navigate to the Data tab on Excel's ribbon. In the "Data Types" group, you'll see a gallery of options like Stocks, Currencies, and Geography. Since we have a list of countries, click on Geography.

Excel will begin working to connect to its online service and match your text with a known geographical location. Once successful, you'll see a small map icon appear to the left of each country's name.

Handling Potential Mismatches

Sometimes, your input might be ambiguous. For instance, "Washington" could refer to the state or the D.C. capital. If Excel is unsure, a question mark icon will appear. Clicking on it opens the "Data Selector" pane on the right side of your screen, where you can select the correct entry.

Step 3: Extract Rich Information from the Data Type

Here's where the magic happens. Select one or more of your newly converted cells. A small "Insert Data" button (it looks like a small grid with a plus sign) will appear to the top right of the selection.

Click this button to see a dropdown list of all the available "fields" you can extract. For our Germany entry, you could choose to see its population, GDP, capital city, or area. Clicking on an item from this list, like "population," will automatically place that data into the next empty column.

This allows you to quickly build out a rich dataset without ever copying and pasting information from an external website.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Accessing Data with Formulas

You can also pull this information using formulas, which is often more efficient for larger tables. The syntax is simple: just reference the cell containing the data type, followed by a period and the name of the field enclosed in brackets.

For example, if "Germany" is in cell A2, you can get its population with this formula:

=A2.[Population]

Or its capital city with:

=A2.[Capital/major city]

As you type the period after the cell reference, Excel will even provide an auto-complete list of available fields, making it easy to discover what data is available.

How to Create Your Own Custom Data Types

While the built-in types are useful, the ability to create your own is a game-changer for internal business data. By using Power Query, you can transform a table of your product list, employee directory, or project status into a reusable data type. Let's create a custom "Product" data type.

Step 1: Get Your Data into the Power Query Editor

First, make sure your source data is structured as an Excel Table. Let’s say we have a table named ProductData with columns for ProductName, SKU, Category, Price, and StockLevel.

Select any cell within this table. Go to the Data tab and click From Table/Range. This will open the Power Query Editor with your data loaded in.

Step 2: Define and Create the Data Type

Inside the Power Query Editor, decide which columns you want to package into your new data type. You can select one, several, or all of them. Press Ctrl + Click to select multiple columns.

Next, go to the Transform tab in the Power Query ribbon. In the "Structured Column" section, click the Create Data Type button.

Step 3: Configure Your Custom Data Type

A dialog box will appear. Here, you can give your new data type a name, like "Product Info," and choose which column should serve as the primary "Display Column." The display column is the main value that will be shown in the cell back in Excel. It's best to choose a unique identifier, like ProductName or SKU. Let's select ProductName.

Click OK. Power Query will now collapse your selected columns into a single column, named "Product Info" (or whatever you called it), that contains your new custom data type. Notice the small grid icon next to the column header.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Load Your New Data Type Back into Excel

With your data type created, go to the Home tab in the Power Query Editor and click Close & Load. Power Query will load the results into a new worksheet in your workbook. This new table will contain your custom data type column.

Step 5: Use Your Custom Data Type

Your custom data type works just like the built-in ones. You can click on a cell in the "Product Info" column, use the "Insert Data" button to extract fields like SKU or Price into new columns, or write formulas to retrieve specific data points:

=B2.[Price]

The best part? If you update the original source data table and refresh your Power Query connection (by going to Data > Refresh All), all the information in your custom data type will update automatically.

Tips for Working with Data Types

• Refreshing is key. For built-in types like Stocks and Currencies, the data can become outdated. Go to the Data tab and click Refresh All to get the latest information from the web. Custom data types are refreshed the same way.
• Availability matters. Linked Data Types and the ability to create new ones are exclusive features for Microsoft 365 subscribers. If you have an older, perpetual license version of Excel, you won't have access to them.
• Check your organization settings. Certain Data Types or features may be controlled by your organization's IT admin. If you don't see them, you may need to check your organizational policies.

Final Thoughts

Excel's Data Types are a seriously powerful tool for turning a static spreadsheet into a dynamic and connected reporting hub. By converting simple text into interactive data points, whether from the web or from your own business analytics, you can spend less time looking up information and more time analyzing it.

Building effective reports starts with having all your data connected and accessible. While Excel Data Types are fantastic for bringing external context into your sheet, combining data from various business platforms like Shopify, Google Analytics, and Salesforce can still feel like a slow, manual process. At Graphed, we built our platform to solve exactly that challenge. We connect directly to all your critical marketing and sales tools, so you can stop downloading CSVs and start building live dashboards by simply describing what you want to see. Instead of wrestling with spreadsheet imports, you just connect your accounts once and use plain language to get instant answers. You can try Graphed to create real-time, cross-platform reports in seconds.