Simon Hurst expands his ExcelZone series to explore the revolutionary new Excel feature that Microsoft did their best to conceal.
In the run-up to Christmas we will be uncovering a radical change in the way that Excel works.
With Excel 2010, Microsoft surreptitiously undermined 30 years of spreadsheet development. Although there had been many significant changes in the capabilities of spreadsheets before 2010, the cell remained at the heart of the spreadsheet, with data being entered, and calculations performed, cell-by-cell.
It could be argued that Lotus Improv, and the subsequent development of pivot tables, had already moved spreadsheets away from purely cell-based calculations. But Improv was marketed as a separate product and ended up being a short-lived experiment, while Excel PivotTables had a very specific use in summarising and presenting data.
The 2010 changes introduced two features that liberated almost all types of Excel calculation from their dependence on individual cells. Both of these features were sneaked into Excel ostensibly as part of the external data tools.
The Power Pivot add-in included a data model that allowed tables of data, including tables within Excel worksheets, to be displayed in separate tabs with the possibility of performing column-based, rather than cell-based, calculations using an extensive set of new functions: Data Analysis Expressions or DAX. Significant as this was, it was not the most important of the new features.
Excel 2010 also saw the introduction of another new add-in, Power Query. Disguised as a tool for acquiring and 'shaping' data, Power Query was actually a cover for the introduction of a radical new approach to performing almost any type of standard Excel calculation on the contents of Excel worksheets.
The next stage in Microsoft's Machiavellian plot was to promote the Power Query tools from an optional add-in to an integral part of Excel as they took their place in the Get & Transform group of the standard Excel Data Ribbon tab. Even this wasn't enough – they were then included as part of a completely free new application: Power BI desktop.
ExcelZone has already covered the basics of the Power Query and Get & Transform tools. The forthcoming series will concentrate on applying the tools to common worksheet-based Excel problems, many of which are Any Answers favourites.