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.
This course assumes that people have attended the Excel Foundation Course
This course covers more advanced techniques with lots of practice writing Excel formulas especially for looking up, reconciling and summarising data.
VLOOKUP is the most used function in Excel after SUM. This is because many people need to reconcile values in two Excel tables or move values in one table to another. This lesson covers how to use the VLOOKUP function and introduces a more modern flexible alternative function XLOOKUP.
The foundation course introduced formulas. This lesson provides practice writing formulas based on case studies of typical Excel challenges.
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.
There are two ways of summarising data in Excel; pivot tables and functions. The foundation course covered pivot tables. This lesson looks at using functions such as GROUPBY, PIVOTBY, UNIQUE and SUMIFS. Functions have some advantages over pivot tables.
Note: we recommend taking the previous session on spill functions if joining this session.
Data tables are useful in several circumstances, for example, what-if calculations. This lesson shows how to create them and why they are very useful. We look at a particular case study where data tables can estimate the impact of inflation on the net present value (NPV) of a the cash flows of a project spanning several years.
We also make take a quick look at Excel goal seek capabilities, if time allows.