About this course
Every BI model in Excel has a moment where the numbers stop matching. Sometimes it’s a broken formula, sometimes it’s a stale pivot, sometimes it’s a data source that drifted. Most analysts find these errors by accident — usually after a stakeholder spots them. This course teaches a systematic alternative: building error-checking into the model from the start so problems surface during refresh, not during a review meeting.
Excel MVP Ken Puls walks through both the framework (a global error-checking layer that lives at the top of every workbook) and the specific local checks — counting total versus unique table entries, cross-checking summaries against table statistics, verifying that pivots are updated, and adding pivot-status checks into a working model. Eleven lessons, demo-driven, applicable to any Excel-plus-Power-Query reporting workflow.
What you’ll learn
- Why error handlers belong in every production BI solution
- Build a global error-checking framework at the top of the workbook
- Add local error checks to specific worksheets (SCF, IS, model summaries)
- Count total vs unique table entries to catch data-shape issues
- Cross-check pivot summaries against source table statistics
- Detect stale pivots before they cause downstream errors
- Combine multiple checks into a model-wide audit pattern
Who this course is for
Analysts who own production Excel + Power Query + PivotTable models that other people rely on. Anyone who has been burned by an undetected error in a delivered workbook.
About the instructor
Ken Puls, FCPA, FCMA, is an Excel MVP and co-author of M is for (DATA) MONKEY. He has built BI solutions in Excel and Power BI for finance and operations teams for over two decades.
What’s in this course
Get the Downloads
- Get All the Files
Introduction to Error Handling
- The Need for Error Handlers
Error Checking Steps & Demos
- Building the Global Framework
- Building a Local Error Checking Solution
- Adding Error Checks to the SCF Worksheet
- Adding Checks to the IS Worksheet
- Counting Total and Unique Table Entries
- Using Table Statistics to Cross Check Summaries
- Checking if a Pivot is Updated
- Adding Pivot Checks into a Model
- Final Testing