Microsoft Excel update will take spreadsheets to the next level

Microsoft Excel
(Image credit: Shutterstock / 200dgr)

Microsoft is rolling out a major update to its spreadsheet software Excel that extends the supported pool of data types far beyond just numbers and text.

In previous updates, the firm introduced dynamic arrays and array formulas and also Bing-assisted stock and geography data types, which introduced an element of intelligence to cells.

However, according to a Microsoft blog post, the next Excel upgrade will take this expansion a step further, by allowing users to create their own custom data types.

These data types can be structured and deployed in such a way that a single cell can host deep and contextual information about a certain topic (e.g. a specific product or location), which is accessed via a scroll-over menu.

Custom data types are also connected to live sources, meaning the information stored in Excel spreadsheets need no longer be static and the likelihood of error is reduced.

Microsoft Excel update

The significance of the upcoming Excel update is at first a little challenging to grasp but, as Microsoft explains, it’s about expanding the ways in which data can be utilized by adding a measure of dynamism and intelligence.

“Up to this point, the Excel grid has been flat: it’s two dimensional. Not all data is flat though, and forcing data into that 2D structure has its limits. With data types, we’ve added a third dimension to what you can build with Excel,” said Microsoft.

“Data types are rich, going beyond numbers and strings - just one cell value can contain a deep collection of hierarchical data, references to other data types, images and intelligent actions. They are also connected and can point to live services providing data that can be refreshed at any time.”

The user group that stands to gain the most from the new features appears to be customers of Microsoft Power BI, the company’s data visualization and business intelligence service. Any company data published to Power BI will be connected automatically to Excel, making this the fastest and simplest way to take advantage of the new custom data types. 

However, data types can also be created from scratch using Power Query. When importing data into an Excel spreadsheet, users will now be prompted to transform the information into a data type, which can draw live data from a range of sources - files, databases, online services etc.

Microsoft is also working on a selection of more than 100 pre-configured data types, powered by knowledge engine Wolfram Alpha. These data types will allow users to, for example, keep track of stock prices, log nutritional information and draw down chemistry data.

Excel data types driven by Power BI are available immediately to Microsoft 365 customers that subscribe to a Power BI Pro plan and Power Query data types are in the process of rolling out for all customers. Wolfram Alpha data types, meanwhile, are currently only available in preview for members of the Insider Program.

Joel Khalili
News and Features Editor

Joel Khalili is the News and Features Editor at TechRadar Pro, covering cybersecurity, data privacy, cloud, AI, blockchain, internet infrastructure, 5G, data storage and computing. He's responsible for curating our news content, as well as commissioning and producing features on the technologies that are transforming the way the world does business.