Overview

Many organizations rely upon Excel workbooks as support for their biggest decisions. They are often complex and interlinked. So much so, that they become “black boxes.” Wouldn’t it be useful to know how it is really working under the covers, to know that the calculations right and the data being relied upon is reliable?

Since what you see isn’t always what you get, this course begins with mapping a workbook structure including named ranges, hidden worksheets and macros. 

Then, we move to dissecting formulas to ensure that they are performing as expected. You’ll learn how to create visual diagrams showing the relationship of cells, worksheets and workbooks in formulas. 

We will dive a bit deeper into linkage between workbooks, data sources, pivot tables, where they often go wrong and how the errors aren’t necessarily obvious, including calculated items, calculated fields, Get Pivot Data statements and pivot table formulas.

We’ll introduce you to where to look to validate data sources in Power Query and Pivot Tables. Whether you’re looking at worksheet data or the results of query and how to determine the impact of refreshing and updating data.

Finally, we’ll provide you with tools to use to test functionality and show a “proof” of operations to allow you to report the accuracy of a workbook with confidence. From using watch windows, error checking and tracing precedents/dependents.

Why you should Attend

As a decision-maker, auditor, compliance professional or accountant, not understanding how the numbers you’re relying upon can sometimes seem just beyond your grasp. Why? Over a period of time and in multiple hands, a critical Excel workbook can change in unexpected ways. Do you know how the formulas are working? Are the links to external data obvious? How fragile is the workbook if a seemingly simple change is made? Will you know the knock on effect of changing a single table structure? If you would like to feel more confident about all that, this course will give you great tips on how to reveal exactly what’s going on in your most important workbooks.

You will learn ways to reverse engineer formulas, determine if inconsistent cell values are causing errors, you may not even know about. You’ll even be able to test the impact of a change in one workbook, on another.

Areas Covered in the Session

• Workbook Structure
• Formulas
• Linkage
• Testing Functionality

Who Will Benefit

• Auditors, Managers
• Compliance Analysts
• Data Analysts
• Accountants
• Legal Analysts