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.
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.
We’ll use our favorite example data, a list of Apollo missions. Here it is, loaded into PowerQuery.
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.
Create Data Type
Now some Custom Data Type settings …. Choose Advanced to see all the options.
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.
Load into worksheet
Choose Close and Load to drop the Custom Data Type into a worksheet.
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.
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.
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
