This course is for people who have a basic familiarity with Excel but want to improve their skills and make themselves more effective using Excel. Mastery of Excel comes through understanding how to write good formulas, and knowing the capabilities of the most important functions - and how and when to apply them.
1 day
This course teaches attendees how to write Excel formulas effectively.
Completion of the Excel foundation course, or equivalent.
This course provides an overview of many common and useful functions. It also covers the new dynamic array capabilities and spill functions.
VLOOKUP, or better still XLOOKUP, allow us to look up the values of one table based on the values in another table. This is often useful when reconciling data from different sources.
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 spill functions reshape arrays. The most useful are VSTACK and HSTACK. We take a quick look at these and others that come in useful.
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.
We often want to summarise data. These functions can help:
We look at a few advanced functions that make writing formulas easier and more efficient.