Home | Excel | Power BI | Copilot | Visualising Data
This is a one day course split into several scheduled topics. Attendees can join one, several or all topics depending on their interest.
Attendees are familiar and comfortable using Excel. This course assumes that people have attended the Excel Foundation Course and possibly the Excel Intermediate Course or have equivalent expertise.
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.
By the end of the course, attendees will be able to
Let’s say we have some tables of data with some text columns (names, product codes) and we need to clean that data: remove prefixes, split names, etc. We can use the newish functions that spill such as TEXTBEFORE, TEXTAFTER, TEXTJOIN and TEXTSPLIT to do this more easily than older methods
Let’s say we have several monthly data sets and we want combine these into a single table for year to date. We will look at two approaches: one uses the VSTACK and HSTACK spill functions; the other uses Power Query
We may have a table of data with some calculated columns (for example using XLOOKUP) and we need to bring our formulas to expand as our data expands. Normally tables are a good solution but they don’t work in some cases (for example with spill functions). In this case, the TRIMRANGE function can save us time and effort, especially we write our formulas using the concise “trim ref dot operator” syntax.
Sometimes, Excel has not got a function to do exactly what we want, for example, we may want to find the range of values when using a GROUPBY function. In that case, we can write our own LAMBDA function to do this. LAMBDA functions allow us to define and reuse custom functions. We can create both named and anonymous LAMBDA functions (where we define a function then immediately use it).
Let’s say we have a table of student scores from three tests and we want to calculate the overall score for each student - as the average of the best two of the three test scores. To solve this, we put in practice some of the modern features of Excel that the course covers, including the TRIMRANGE, LET and LAMBDA functions.