Step-by-Step Guide: Developing Custom Data Types in Microsoft Excel

Quick Links

• Import the Data
• Choose the Data Columns
• Create the Data Type
• Use Your Data Type

If you often take advantage Microsoft’s Data Type feature when including data in your sheets, but you wish a specific category would come along, there’s an easy solution. You can create your own data type in Microsoft Excel!

When Microsoft introduced its Data Type feature, you only had a few options like geography and stocks . The options then grew to include things like food, cities, plants, and more. But maybe you have a particular data type you’d like to see, and that’s where custom data types come in.

The data type creation feature is only available to Microsoft 365 subscribers.

Import the Data

In this tutorial, we’ll show you how to create a data type by importing data from a web source. But you can also use data from a text or CSV file or a spreadsheet. For our example, we’ll import something fun: a list of Angry Birds video games from Wikipedia .

Go to the Data tab and choose your import choice from the Get & Transform Data section of the ribbon. In our case, we select “From Web.”

Enter the URL for the website, click “OK” and wait a moment for Excel to connect. You may be asked to select the level for the URL you enter. If so, make your choice and click “Connect.”

Choose the Data Columns

When the Navigator window opens, you’ll see the list of options found from your source on the left. You can click to view each one in the Table View tab on the right. If you would like to use more than one, click the Select Multiple Items checkbox at the top of the list. After selecting the data, click “Transform Data.”

Next, the Power Query Editor window appears. This is where you’ll choose the columns of data you want to use for the data type. You can choose multiple columns by holding Ctrl as you click each one.

Create the Data Type

When you finish choosing the columns, go to the Transform tab and click the Structured Column drop-down arrow on the right side of the ribbon. Pick “Create Data Type.”

Now you can choose how you want the data to display. Enter the Data Type Name you want to use. In the “Display Column” drop-down menu, choose which column should display in your sheet for the data type.

If you want to remove columns you selected or arrange them differently, click “Advanced.” Move columns you don’t want from Selected Columns to Available Columns using the Remove button. To rearrange the order, use the Move Up and Move Down arrows on the right.

When you finish, click “OK.” The Power Query Editor displays once more with your data in a single column. The other columns you selected are condensed into this one column.

Next, you’ll load the data type into an Excel table. Go to the Home tab and click “Close & Load” on the left side of the ribbon.

You’ll then see the data type column you selected in your Excel sheet. For our example, it’s the Game column.

Use Your Data Type

You’ll notice the data type icons next to each item in the list and you can insert other pieces of data the same way as Excel’s built-in data types . Click the Add Column icon and choose the piece of data to insert. It automatically displays in a column to the right.

Continue clicking the icon and selecting more data to insert as you wish.

To display the data card, click the icon next to one of the items. You’ll then see the data card for that item pop open.

Using data types, you have a convenient way to insert data into your spreadsheet. And if you want to take that data to the next level, check out how to use data type templates .

Also read: