Creating your own custom data types in Excel

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating your own custom data types in Excel
Excel provides a few built-in data types (organisations, stocks, currencies and geography), but you can also add your own, as this blog shows

Posted by Andy Brown on 14 July 2025

In this blog

Creating the source data
Uploading the data
Transforming the data
Loading the data into Excel
Using custom data types

Excel contains 4 built-in data types, which I blogged about recently:

The 4 data types available by default in Excel.

However, you can also add your own - here's one for the 6 main cast members of the long-running Friends TV series:

The list of each friend's sexual parnters comes from this rather sad Reddit thread!

The rest of this blog shows how you can create and use a data type like this.

If you want to follow along you can download the Excel workbook I used here.

Creating the source data

The first thing to do is to create an Excel workbook containing the data columns which you want to comprise your custom data type:

The file you can download above includes a picture of each Friend, but it appears you can't load these pictures into Power Query (you can see I tried below).

Uploading the data

In a new or existing Excel workbook, choose to load some data:

I've chosen to get data from an Excel workbook, but you can choose virtually any data source.

Find and select your saved Excel workbook (don't forget to close it first!), then choose the worksheet containing your data:

The right-hand side will show a preview of your data.

Click on this button at the bottom right corner of the dialog box:

Click on this button to transform your data before loading it.

Transforming the data

You now need to make any changes to your data:

I double-clicked on the first column name to rename it as shown here.

It's also clearer to remove any columns you don't want to be part of your data type:

I've removed the Picture column from the Excel workbook, since this wasn't imported.

Select the first column, hold down the Shift key and click on the fourth column to select the columns making up your data type:

These are the columns our data type will include.

Now choose this option on the ribbon:

Click on the Transform ribbon tab.

Choose to create a data type based on the selected columns of data:

Click on this icon (note that this only appears in Excel - if you're using Power BI Desktop, you won't see it).

Choose which is the main column for your data type:

Here I've gone for the Friend column as being the main one.

Loading the data into Excel

You should now have a single column of data:

The other columns are there, but are just hidden.

You can now choose to load this data type into Excel:

Select this option from the File menu (you'll need to return to the Home tab first).

Using custom data types

You can only use custom data types within an Excel table. One way would be to show the details for any card:

Click on any data card symbol to show the details for that row (in this case, for Phoebe).

You can also choose to insert other data type columns:

Click on the icon shown to choose another column to display (the icon only appears when you click in your table).

This would give you a second column showing who played each friend:

You could add additional columns n the same way.

For a full list of everything you can do with custom data types in Excel (there's not that much more than shown here), see this Microsoft tutorial.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS

EXCEL BLOGS

Creating the source data

Uploading the data

Transforming the data

Loading the data into Excel

Using custom data types

Head office

London

Manchester