Home | Power BI | Excel | Python | SQL | Visualising Data | Generative AI
People who are familiar with Excel users. This course introduces new features in the most recent versions of Excel that can make their spreadsheets simpler and better.
1 day
Over the last few years, Excel has added many new capabilities. These include new functions such as dynamic arrays and spill functions that help us build better and more robust spreadsheets and models. This course demonstrates and explains many of these new capabilities, always with a practical perspective on how we can take advantage of them.
Completion of the Excel foundation and intermediate courses.
Dynamic Arrays underpin many of the new capabilities. We start with a brief look at the essentials: for example, even ordinary old-school functions now work with arrays and produce multiple results rather than a single value under the right conditions. Dynamic arrays allow us often to write simpler formulas, for example, to avoid mixed references in grid formulas.
Spill functions return not just a single result in the cell where the formula is written but the results spill over into surrounding cells. We start with a few examples of spill functions, UNIQUE(), SORT() and TRANSPOSE() then focus on a very useful spill function FILTER(). This can filter a table or range and return a set of rows.
Several spill functions, TEXTBEFORE(), TEXTAFTER(), TEXTJOIN() and TEXTSPLIT() make handling text much easier than before. We no longer need to combine functions like SUBSTRING(), FIND() and LEN() together which was always hard.
Several new functions reshape arrays. The most useful are VSTACK() and HSTACK(). We take a quick look at these and others that come in useful The course covers several new advanced functions and explains when and why these are useful. The functions include: