Modern Excel

Excel has improved a lot over the last few years. This session will demo practical features and functions of modern Excel. We'll start with XLOOKUP(), a better and more flexible alternative to VLOOKUP. Spill functions can spill their results not only to the cell containing the formula but to neighbouring cells also. This sounds boring but in fact makes them very useful. We will look at a few popular spill functions: SEQUENCE, UNIQUE, SORT, and TRANSPOSE; and demo examples of practical uses for the FILTER function. Finally, we will introduce dynamic arrays, explain how to use them and why they are so useful.

You can download the Excel spreadsheet that has the demos from https://github.com/MarkWilcock/lbag-o...

This is a recording of a meetup event on the 16th January 2023. It is part of a series on Excel from the data perspective. More details at https://www.meetup.com/london-busines...

The speaker, Mark Wilcock, is the organiser of the London Business Analytics group and is a consultant and trainer and a Microsoft MVP.

Timestamps

00:00 Challenges - things that have historically been difficult to do in Excel
00:20 Challenge 1 - Split Text
00:00 Challenge 2 - Stack data
02:30 Challenge 3 - Apply a formula to a grid of cells using mixed cell references
04:50 Introduction to the sample data used in the demos
06:00 XLOOKUP, a flexible, simpler alternative to VLOOKUP
00:00 Introduction to Spill Functions; the UNIQUE, SORT and TRANSPOSE functions
17:30 The SEQUENCE function
19:17 The FILTER function
22:12 Dynamic arrays are now fundamental in Excel
25:30 Dynamic arrays make it easier to apply a formula to a grid of cells
28:00 The TEXTBFEORE, TEXTAFTER and TEXTSPLIT spill functions
30:00 Challenge 2 explained and solved using Excel tables and the VSTACK function
35:50 Challenge 1 explained and solved using the new TEXT... spill functions
38:20 Challenge 3 solved using dynamic arrays as arguments

We run a full range of data analysis and generative AI courses in London and online. See our courses at zomalex.co.uk