Excel Custom Data Types
In This blog series I will be writing about some of the recent new features in Excel and some of the best resources for learning about them. I recently wrote about some of the new features in Excel on LinkedIn, some of these new features include:
Data Types and Custom Data Types
Let
LAMBDA
Unhide Multiple Cells
PDF Connector in Power Query
Dynamic Arrays
New Dynamic Array Formulas
Today I want to focus on Custom Data Types in Excel and provide some great resources for learning Custom Data Types
Custom Data Types
Excel has created new data types over the last year or so and some of these embedded data types include stock and geography data types. In addition to pre-build data types one can create custom data types using your own data. A data type in Excel allows a single cell to contain a rich set of information within the cell. A great article by Microsoft on The evolution of Excel and how Data Types work can be found here:
Once you understand how data types work and how they can be used a lot of new opportunities open up in Excel as you can create your own data types in Excel or using your organization data from Power BI. You can create custom data types using your organizational data for example product data, employee data, location data, etc can all become customer data types in Excel. Excel houses all the data in one cell but allows you to have access to multiple pieces of data and to display the pieces of data embedded in the cell via a data card or to pull out the data elements via an Excel formula and use throughout your workbook.
The image below shows an address list and the embedded data for Joe Smith is displayed as a card. The list was created in Power Query as a custom Data Type with about one minute of work. The card contains individual elements to include name, address, city, state and zip.
Custom Data Type with Data Card
Excel Resources for Custom Data Types
First lets start by listing a few videos that do a great job of demonstrating how Power Query can be used
The first is an introduction of data types by John MacDougall. This is a great overview about rich data types.
Next is a video by Oz Du Soleil on his Youtube channel Excel on Fire. Oz has a unique style that is always engaging as he often tells a story as he explains how to do something in Excel.
The next video I recommend watching is by Bill Jelen who is known as Mr. Excel. He talks about data types and the journey excel went through to get us to the point of being able to create our own data types.
In addition to the above videos a few posts that have good content on custom data types are listed below.
Gasper Kamensek has a great article on the Excelunplugged.com website that takes about the new data types in Excel and why they are a game changer
The next article is by John MacDougall and accompanies the video displayed about giving an overview of Rich Data types
The next article is by Mynda Treacy and focuses on creating custom data types in Excel the first two articles focused more on data types in general. The blog can be found at the following location:
Conclusion
In conclusion I would encourage you to take the time to do your own search on Excel data types. The videos and posts above only scratch the surface of what you can do in Excel with rich data types. As more blogs become available on how to use custom data types I will update this post. With that being said I encourage you to go out there and play with data types and see how you can incorporate them in the work you are doing in Excel. Stay tuned for my next post on another exciting new function/feature that has recently come out in Excel. Leave your comments below.