I’m going to look at three Excel features that not everyone would necessarily consider to be essential elements of learning the fundamentals of Excel.

Dynamic Arrays

Many new Excel features just extend Excel’s capabilities, but Dynamic Arrays have the potential to change how almost any Excel calculation is performed. They can also have an effect on formulas that were created before their introduction and are often created inadvertently, sometimes generating an unexpected error message. For all of these reasons, it seems important to cover Dynamic Arrays as part of the foundations of Excel.

For example, what used to be a simple demonstration of the importance of understanding the use of dollar signs in a cell reference can now be simplified by using a Dynamic Array formula instead. Here we are fixing the reference to cell B1 using the dollar signs so that we can copy a formula in cell B4 down to B8:

However, in the new era of Dynamic Arrays, we could just enter a single, possibly more intuitive, formula into cell B4:

Our Dynamic Array formula automatically ‘spills’ into our other cells and has the additional benefit that it can only be edited in the cell in which it was entered, greatly reducing the possibility of introducing inconsistency by changing the formula in just one of the cells.

It’s also important to understand that, at the moment, Dynamic Array formulas cannot create, or even be part of, Excel Tables. This means that, if we intended to keep on adding rows to our amount column, we might be better off using our original formula and turning our range of data into a Table so that our formula column would automatically be copied down to any new rows in a way that would not happen with our Dynamic Array formula.

Although Dynamic Arrays have been generally available for several years now, many Excel users are yet to discover them. In a recent poll I saw on an Excel training website, 90% of respondents said that they had either never heard of Dynamic Arrays (59%) or had heard of them, but never tried using them (31%). A few of the comments concerning Dynamic Arrays also suggested the importance of covering them at an early stage: several users reported that they had come across #SPILL! errors but hadn’t realised what had caused them before learning about Dynamic Arrays.

PivotTables

Although covering everything that PivotTables and Power Pivot can do would take a substantial course of its own, a general understanding of how PivotTables work can be useful to many Excel users. PivotTables are often seen as a complex and advanced feature. However, although they do encompass many advanced aspects, they can also be by far the easiest way to create simple summaries. It’s even possible to use PivotTables to create an interactive Excel dashboard without the need to use a single Excel formula and with only minimal training on the creation of PivotTables, PivotCharts and Slicers.

Power Query

This might be the most controversial of my three key elements of basic Excel Training. Like PivotTables, Power Query is often seen as an ‘advanced’ Excel feature and one that has a very specialist use for working with large volumes of external data. For anyone whose use of Excel does involve working with structured data, a knowledge of Power Query is at least as important as being able to use the SUM() or IF() functions but, even if you only work with information within a single Excel workbook, Power Query can again be the simplest way of solving a wide range of Excel problems. Unlike cell-based, or legacy, Excel which often relies on users knowing which functions to use and how to use them, Power Query converts intuitive interface operations and choices into a step-by-step process that can transform an entire table of data without the user needing to type in a single formula. There’s a good argument for seeing Power Query as the simple option and the rest of Excel as the advanced feature.

Conclusion

Because people use Excel in so many different ways, even within a single profession, it’s difficult to be prescriptive about what an introductory course should cover. However, even if not all features can be covered in full detail, it’s important that users do have some idea what a range of different Excel features can be used to achieve, even if they do then have to engage in further research, or approach colleagues, for help in implementing them.