Microsoft Excel Logo

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.

Note: 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.”

On the Data tab, 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.”

Enter the URL for your data source

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.

Select columns in the Power Query Editor

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.

Choose a name and column

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.

Arrange the columns using the up and down arrows and the Remove button

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.

Power Query Editor with created data type

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.

Data type loaded in Excel sheet

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.

Data type data added

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.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »