How to create your own data type in Microsoft Excel

If you often take advantage of Microsoft's Data Type feature when including data in your sheets, but you want a specific category to appear, there is an easy solution. You can create your own data type in Microsoft Excel!!

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

Note: The data type creation feature is only enabled for Microsoft subscribers 365.

Import the data

In this tutorial, We will show you how to create a data type via 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 option from the Get and Transform Data section of the ribbon. In our case, that appears and “From Web”.

Enter the URL of the web portal, click on “To accept” and wait a while for Excel to connect. You may be prompted to select the level of the URL you enter. If so, make your choice and click on “Connect”.

Choose the data columns

When the browser window opens, you will see the list of alternatives found in your source on the left. You can click to view each one in the Table View tab on the right. If you want to use more than one, click the Choose multiple items check box at the top of the list. After choosing the data, click on “Transform data”.

Then, the Power Query Editor window appears. This is where you will choose the data columns you want to use for the data type. You can select multiple columns by holding down the Ctrl key while clicking each one.

Create the data type

When you finish selecting the columns, go to the Transform tab and click the Structured Column drop-down arrow on the right side of the ribbon. Choose “Create data type”.

Now you can select how you want the data to be displayed. Enter the name of the data type you want to use. In the drop-down menu “Show column”, choose which column should be displayed on your sheet for data type.

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

When I finish, click on “To accept”. The Power Query Editor is displayed once more with your data in a single column. The other columns you selected are condensed into this single column.

Then, will load the data type into an excel table. so you can follow the same steps to insert the remaining totals and copy the formula to the other cells in the totals row “Close and load” Go to the Formulas tab and click.

Later you will see the data type column you selected in your excel sheet. For our example, is the Game column.

Use your data type

You will notice the data type icons next to each item in the list and you can insert other data in the same way as Excel's built-in data types. Click on the Add column icon and choose the data you want to insert. Automatically displayed in a column to the right.

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

To show the data card, click on the icon next to one of the items. Then, you will see the data card for that item open.

With the 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, see how to use data type templates.