Custom Data Types are great in Excel 365

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Microsoft has done a clever thing, combining two important Excel features into a new and really useful addition – Custom Data Types.

Excel 365 for Windows/Mac already has Stock and Geography data types to drop live information into workbooks.  It’s now possible to make your own Custom Data Types (CDT) that work the same way. 

Excel Custom Data Types 409x296 - Custom Data Types are great in Excel 365
Microsoft’s example of a custom data type made from a customer database.

Custom Data Types combine two Excel features

Excel 365 for Windows has Power Query to import data from a wide range of sources and update them automatically.  Power Query usually drops data into a worksheet that can be searched with Vlookup/Xlookup or similar functions.

Linked Data Types bring information in from Microsoft servers – most commonly stock, share, fund, index prices and exchange rate details.

Custom Data Types puts the two together.  Power Query gets the data into Excel and manages into the format you need.  Now it can output that data as a Custom Data Type for more elegant and manageable display.

Custom Data Types in action

Here’s a simple step-by-step example of Custom Data Types in action.

image 292x296 - Custom Data Types are great in Excel 365

We’ll use our favorite example data, a list of Apollo missions.  Here it is, loaded into PowerQuery.

image 1 473x209 - Custom Data Types are great in Excel 365

Any PowerQuery source is available for Custom Data Types, including automatically refreshing connections.

Optionally, move the main or key field to the left side.  That’s the field you’ll lookup with – a customer name, product name or code etc.  In our example, it’s the Mission name.  This isn’t necessary but we’ve found it handy.

Transform any fields that are necessary.  Add any calculated fields etc.

Select all the columns/fields you want in the Custom Data Type.

Then go to Transform | Structured Column | Create Data Type.

image 3 473x233 - Custom Data Types are great in Excel 365

Create Data Type

Now some Custom Data Type settings …. Choose Advanced to see all the options.

image 4 - Custom Data Types are great in Excel 365

Data Type name – give the CDT a name

Display Column – select any column to be the main display field.

Select the columns to appear in the CDT or move to the left ‘Available Columns’ to hide.

Click OK and the Custom Data Type appears in PowerQuery.  ‘Created data type’ is now in the Applied Steps.  Choose the cog icon to reopen the Data Type settings, if necessary.

image 5 473x248 - Custom Data Types are great in Excel 365

Load into worksheet

Choose Close and Load to drop the Custom Data Type into a worksheet.

image 6 473x256 - Custom Data Types are great in Excel 365

The list of rows appears.  The Query appears in the Queries and Connections pane (as usual) so you can reopen the connection and tweak the data source or CDT.

Click on the icon at right of the CDT list to display the available fields.  Choose one or more to add to your table.

image 7 473x243 - Custom Data Types are great in Excel 365

The Data Card with all the details can appear with the Ctrl + Shift + F5 shortcut or right-click and choose Show Data Type Card.

Custom Data Types are live and refresh

The CDT list and fields update whenever the data connection is refreshed.

We added the last Gemini mission and two post-Apollo missions to the data source, chose Data | Refresh All and like magic the custom data types are updated.

image 9 473x296 - Custom Data Types are great in Excel 365

The bad news … for now

Custom Data Types are only available in Excel 365 for Windows with high-end Microsoft 365 Enterprise plans E3 or E5.

It’s also in current Insiders track releases, seemingly regardless of Microsoft 365 plan.

Let’s hope this nice feature is extended to all Microsoft 365 customers.  It has uses far beyond the corporate ones that Microsoft imagines.

Fix Excel when Stock and Geography data types go missing
More linked data types coming to Excel 365
Stock and Geography Data Types coming to Office for Mac

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address