FUTURE-READY EXCEL: From Basics to Brilliance - Session 2: Moving from Data to Information

Building on the cleaned dataset from Session 1, participants will see how to convert data into Excel Tables, enabling the use of structured references that make formulas more robust and easier to maintain. The session will then explore key lookup and aggregation functions commonly used by accountants, including XLOOKUP, SUMIFS, COUNTIFS, AVERAGEIFS, as well as dynamic techniques using INDEX, MATCH, and FILTER.

Participants will also learn how to leverage PivotTables to quickly analyse financial data from multiple perspectives — such as by month, cost centre, or client — and how to enhance reports with slicers and timelines for interactive analysis. The session will introduce several of Excel’s newer dynamic array functions, including FILTER, SORTBY, PIVOTBY, GROUPBY, LET and LAMBDA, helping participants build more flexible and powerful calculations.

Finally, the webinar will demonstrate practical financial analysis techniques, including running totals, ageing calculations, ratio analysis, and sensitivity testing using What-If tools such as Goal Seek and Data Tables.

By the end of this session, participants will understand how to move from raw numbers to clear financial outputs—including reports and dashboards—that can form the backbone of a repeatable monthly reporting process.

In completing this learning engagement, you will have developed the following professional competencies indicated in the green section below: