Microsoft Excel Adds Support for Live Custom Data Types

Microsoft Excel to add support for custom live data type, allowing users to import their own data as a custom data type. A single cell value can now have a live connected set of information you can pull into your decision-making process, without needing to go back and forth with the original source to find more information. You can also leverage the card view to see more information on that data type and navigate to related information. Users will also be able to use the Power BI data ecosystem to create data types.

You can create your own data types, connecting directly to any data you want on Excel, Microsoft announced in a blog post. With Power Query, you can build queries and transforms to get the exact shape of data you're looking for. When you import data into your spreadsheet, you'll have the option to transform that data into a data type. To create the custom data type, you can use any source of organisation data available within the Get Data menu – files, databases, online services, and more.

Organisation data types from Power BI will also be available on Excel. Data types can use information from your company and organisation, leveraging Power BI as an ideal source of authoritative data. The data ecosystem of Power BI includes 140+ data sources, centralised and controlled access, row-level security, and Microsoft Information Protection (MIP) sensitivity labels.

You can refresh your data types to get the latest information from Power Query and Power BI.

It is possible to discover Power BI tables through the data types gallery, after which you can convert cells into data types. Once the cells have been converted into linked data types, you can extract additional information to work within your spreadsheets. If you enter a product code or name, for example, you'll see additional information such as a description, price, and available inventory – all within the card view. You can build invoice in Excel using connected and trusted data coming directly from Power BI.

Data types will also reduce the risks of errors, as per Microsoft. You don't have to copy paste, and can directly import. For example, you can import the data type for a city, and use a formula to reference the population =A1.population. If the data type updates, the results in the formula will update too. Instead of seeing a static number, you can also see the formula and know where the number came from.

Microsoft is also adding over 100 new data types into excel for Microsoft 365 Personal or Family subscribers. Users will be able to do things like track their nutritional information, research potential colleges, monitor stocks, learn chemistry, and more, using information from Wolfram Alpha's online service. This will be available for preview to Office beta testers in the Insiders programme.

The Power Query data types are rolling out and will be available in the coming weeks for all Microsoft 365/ Office 365 subscribers in the coming weeks. Power BI data type will be available for those who have a Power BI Pro service plan.

October 30, 2020