Advanced Excel for Accountants - From Data to Dynamic Insights

Introduction

This advanced-level modular webinar is designed to help accountants transform the way they use Excel. Presented by expert speaker Alan Murray, this series provides a practical approach to building scalable, automated solutions using modern Excel tools such as Power Query, Power Pivot and dynamic array formulas.

By the end of the webinar series, you will be able to structure data efficiently, create robust data models and develop dynamic reports that update automatically - saving time, reducing errors and enhancing the quality of your financial analysis.

What You Will Learn

This modular webinar series will cover the following:

Module 1: Getting & Preparing Data Properly

Build once, update forever - learn how to eliminate manual data handling and create repeatable, automated data processes.

• Why traditional copy/paste workflows fail
• Importing data using Power Query:
  • Excel files
  • Folders containing multiple files (typically exports from an accounting system or similar)
• Transforming the data:
  • Removing columns and filtering rows
  • Performing calculations before loading data
  • Correcting and managing data types
  • Merging tables (no more VLOOKUP)
  • Combining multiple Excel sheets or files into a single dataset
• Loading clean data into Excel tables
• Refreshing connections
• Dynamic thinking (no dragging formulas, no helper columns mindset)

Module 2: Modelling & Analysis (Power Pivot & PivotTables)

Stop working in one table and start modelling your data - move beyond flat spreadsheets and unlock relational data modelling.

• PivotTables versus data model PivotTables
• Building PivotTables from the data model
• Using fields from multiple tables
• Writing DAX (Data Analysis Expressions) formulas
• Creating a calendar table
• Enhancing reports with slicers and interactivity
• Creating from PivotTables

Module 3: Modern Excel Formulas

No more dragging - build dynamic, single formula solutions that update automatically.

• Transition from traditional to dynamic formulas
• Replace manual copying with scalable maintainable solutions
• Leverage modern functions e.g. UNIQUE and GROUPBY
• Shape and extract data from arrays
• Write iterative formulas for advanced calculations
• Build interactive reports driven by dropdowns, checkboxes and more