Advanced Excel, Power Pivot & Dashboards - Masterclass - 2015 - Preparatory Material

Hi there,

Welcome to my Masterclass, Australia 2015. Thank you so much for enrolling in this program. I am eager to meet you in Australia this May / June & help you become awesome in your work.

In order to gain the most from this class, I want you to complete below preparatory course work before attending the classes this winter (May – June 2015).

How much time this prep-course takes?

It should take no more than 8 hours of your time. Please aspire to finish it all to gain most from the Masterclass.

If you have not enrolled in the masterclass yet, Please click here to enroll.

Material for Advanced Excel & Dashboards class

Please go thru this material if you are joining us for Advanced Excel & Dashboards Masterclass.

Absolute vs. Relative References in Excel [30 mins]

Understanding various reference styles is very important to develop simple, repeatable & useful formulas. And formulas are the engine of any successful dashboard.

Please go thru below links to learn about various referencing styles in Excel.

IF formula & its variations [45 mins]

IF formulas help us build various real-life conditions & scenarios in Excel. Learning how to use IF formula is critical to building your formula knowledge.

Please go thru below links to learn about IF formulas:

SUMIFS Formula [45 mins]

SUMIFS (along with its cousins – COUNTIFS, SUMIF, AVERAGEIF etc.) plays a vital role in quickly summarizing data and getting answers to our questions.

Please go thru below link to learn more:

VLOOKUP formula [60 mins]

VLOOKUP formula helps us answer questions like what is the price of product PR1234-01? by looking up data. Along with other powerful functions like MATCH & INDEX, VLOOKUP helps us do many useful things when building dashboards. Please note that we will be learning about MATCH & INDEX in the Masterclass. So, just brush up on your VLOOKUP for now.

Learn how to use VLOOKUP formula.

[optional, takes another 60-90 mins] Comprehensive guide to Excel LOOKUP formulas

Excel Tables [30 mins]

Excel Tables help us structure our data & write simpler formulas. They are vital for creating dashboards that have ever-changing data.

Introduction to Excel tables

[Optional, takes another 30 mins] How to use VLOOKUP with Tables

Conditional Formatting [30 mins]

Conditional formatting is a quick & easy way to add wow-factor to your reports, analysis & dashboards. Learn the basics now. We will learn advanced stuff in the Masterclass.

Material for Power Pivot & Data Analytics Class

Please go thru this material if you are joining us for Power Pivot & Data Analytics class.

Introduction to Pivot Tables [30 mins]

Pivot tables form the core of Power Pivot. So get a good understanding of what they are and how to use them.

Introduction to Power Pivot & its capabilities [30 mins]

Your first brush with Power Pivot can be crazy. So go thru below introduction video to understand what it can do, how it is setup and how to use it.

Introduction DAX – the Power Pivot formula language [60 mins]

DAX is the Power Pivot formula language. Fear not, it is just a fancy name given to the VLOOKUP, SUMIFS & AVERAGE of Power Pivot. Once you know how it works, you can make miracles in your workbook. So get started with it here. We will talk alot more about DAX in our class.

What slicers can do – a case study [30 mins]

Slicers help us add a bit of interactivity to our reports & dashboards. So learn how useful they can be thru this case study (video).

Venue of the Masterclass

Click on the city links to know about venue, parking & stay options.