Microsoft is fully reforming Excel with the ability to support custom real-time data types. Excel users have been using Microsoft’s spreadsheet tools to import, organize, and analyze data for decades, but the basic data types have always been limited to text and numbers. Although Microsoft has previously added dynamic arrays and some custom inventory and geographic data types, the company is now updating Excel so that people can import their own data as custom data types.
This means that you will be able to process data in Excel in a new way, and hope that there are no many troubles that exist today. For example, you can import the data type of Seattle, and then create a formula that references that single cell to extract information about the population of Seattle.
These data types work by stuffing a set of structured data into a single cell in Excel, which can then be referenced by the rest of the spreadsheet. The data can also be refreshed to stay current. For example, if you are studying the periodic table of elements, you can create a cell for each element and then easily extract individual data from it.
Microsoft has introduced more than 100 new data types into Excel for Microsoft 365 individual or family subscribers. Thanks to data from the Wolfram Alpha service, Excel users will be able to track inventory, obtain nutritional information for diet plans, and more. The Insiders program is currently available for Office beta testers.
The most powerful place of these custom data types is obviously the companies that rely on Excel every day. Microsoft is using its Power BI service as a connector to introduce data sources into Excel data types in business, so that companies can connect various data. This may be hierarchical data, or even references to other data types and images. Companies can even convert existing units into linked data types, making data analysis easier.
However, Power BI is not the only way this feature works. After importing the data into Excel, you can now use Power Query to convert it to a data type. This may include information from files, databases, websites, etc. You can clean up the imported data and then convert it to the type of data to be used in the spreadsheet. If you have extracted the data using Power Query, it is easy to refresh the data from its original data source.
In practice, the key to how all this works is how Power BI and Excel handle the raw data entered by the enterprise. Excel and Power BI will need to competently detect patterns in the data, apply its logic to structure the data, and ultimately convert it into content usable in a spreadsheet. Although Excel users can clean up the data they are working on, this is usually easier said than done.
These new Power BI data types will be available in Excel for Windows to all Microsoft 365 / Office 365 subscribers who also have Power BI Pro service plans. The Power Query data type is also being introduced to subscribers. On the consumer side, the Wolfram Alpha data type is currently available in a preview by Office Insiders and will be available to all Microsoft 365 subscribers soon.