• 00Days
  • 00Hours
  • 00Minutes
  • 00Seconds

Other Course Start Dates

live sessions delivered via GoTo Training

Starting 10 Nov 2020 | Starting 23 Feb 2021 | Starting 27 Apr 2021
dbrownconsulting, Virtual Training Methodology

Course curriculum

  • 1

    Welcome to the course

  • 2

    1. The Reporting Infrastructure

    • The Reporting Infrastructure

    • Lab 01 - How Excel Works

    • Reporting Framework & the 7 Golden Rules of Data

    • The role of the Control & Report Sheets

    • How Excel Works

    • Managing Dates, Validations & Format Styles

    • Chapter Quiz

  • 3

    2. Get Data with Power Query

    • Get Data with Power Query

    • Lab 02 - Get Data with Power Query

    • Getting External Data into Excel

    • Connect to Excel Files in a Folder

    • Consolidate Excel Workbooks

    • Transform Data Types

    • Loading your Query to Excel

    • Chapter Quiz

  • 4

    3.Report Building Blocks

    • Report Building Blocks

    • Lab 02 - Report Building Blocks

    • Simple Report Template

    • Identify your Report Criteria

    • Automate with SUMIFS

    • SUMIFS & the Date Criteria Hack

    • Chapter Quiz

  • 5

    4. How to Build a Control Infrastructure

    • Building a Control Infrastructure

    • Lab 04 - How to Build a Control Infrastructure

    • Why We Need Date Controls

      FREE PREVIEW
    • Framework for Date Controls 1of2

    • Framework for Date Controls 2of2

    • How to Activate a Combo Box Form Control

    • Inserting Combo Box Controls

    • Linking Combo Boxes To Excel

    • Extract Combo Box Data with the INDEX Function

    • Chapter Quiz

  • 6

    5. Build a Dynamic Date and Report Selector

    • Build a Dynamic Date & Report Selector

    • Lab 05 - Build a Dynamic Date & Report Selector

    • Extract End Date From Combo Boxes

    • Create a Flexible Financial Year Start Month

    • Automate Start Month with CHOOSE & ROUNDUP

      FREE PREVIEW
    • Complete the Date Automation Process

    • Plug the Combo Box into Your Report Sheet

    • Chapter Quiz

  • 7

    6. The Report Automation Process

    • 6. The Report Automation Process

    • Lab 06 - The Report Automation Process

    • Automate your Report with the Controls FrameWork

    • Create Report Headings & Labels with CONCATENATE

    • Variance Analysis & Number Formating

    • Insert a Variance Analysis Chart

    • Report Update With Power Query

      FREE PREVIEW
    • Chapter Quiz

  • 8

    7. Visualising your Performance Reports

    • Visualising your Performance Reports

    • Overview of MoM Change Report

    • Overview of MoM Change Report 2

    • Building a Rolling 12 Month Report Table

    • Building a Rolling 12 Month Report Chart

    • Build a Monthly Volatility Chart

    • Insert Reference lines into a Chart

    • MoM Change in Revenue vs Units Sold

    • Chart MoM Change in Revenue vs Units Sold

    • Insert Benchmark Bands Into a Chart

      FREE PREVIEW
    • Chapter Quiz

  • 9

    8. Visualising Top N Reports & Cards

    • Top N Reports & Cards

    • Lab 08 - Visualizing Top N Reports & Cards

    • Top & Bottom 5 Report Part 1

    • Top & Bottom 5 Report Part 2

    • Chapter Quiz

  • 10

    9. Building your Dashboard

    • Building your Dashboard

    • Lab 09 - Building your Dashboard

    • Overview

    • Dashboard build Secrets

    • Using the Camera Tool

    • Edit & Shape Images for your Dashboard

    • Finalise Dashboard Design

    • Activate Auto Refresh For New Data

    • Final Review, Protect WorkBook & WorkSheet

    • Chapter Quiz

  • 11

    10. Mastering the VLOOKUP Function

    • Overview of lookup Functions

    • The VLOOKUP Function

    • The MATCH Function

    • MATCH Function to the rescue of the VLOOKUP function

    • The HLOOKUP Function

    • MATCH Function to the rescue of the HLOOKUP Function

    • The VLOOKUP Function, a cool Feature 1 of 2

    • The VLOOKUP Function, a cool Feature 2 of 2

    • Chapter Quiz

  • 12

    11. PivotTable & Pivot Chart in Excel

    • Download the Exercise Files

    • Overview of PivotTable

    • 5 ways of Reporting in Modern Excel.mp4

    • 5 Reasons to use PivotTables

    • Understanding 7 Golden rules of Data

  • 13

    12. Working with Pivot Table

    • Working with the Excel Table Tool

    • Planning your PivotTable

    • Creating a simple PivotTable Report

    • Using PivotTable recommendations

    • Designing a PivotTable Report

    • Managing dates in PivotTables

    • Creating weekly Report with PivotTables

  • 14

    13. Working with Calculations in Pivot Tables

    • Showing Percentage Calculations in PivotTables

    • Creating Running Total Calculations in PivotTables

    • Filtering a PivotTable with slicers

    • Sorting a PivotTable Report

  • 15

    14. Build a Smart Dashboard with Pivot Charts

    • Planning your Pivot Chart Dashboard Build

    • Building a Simple Chart Based on a Plan

    • Adding Data Labels to your Pivot Chart

    • Inserting Modifying Slicers in Pivot Charts

    • Comparing Monthly Performance with Pivot Charts

    • Smart Formatting Tricks for Pivot Charts

    • Finalising your Pivot Chart Dashboard

    • Using AI to extract insights from your Data

  • 16
  • 17

    Session 1

    • Understanding dirty data

    • Getting External Data in Excel

    • Navigator in Power Query

    • Connecting to multiple data

    • Load and Transform

    • Power Query Interface

    • Understanding Applied Steps

    • Transposing in Power Query

    • Fill in Power Query

    • Using First Row as headers

    • Renaming Columns in Power Query

    • Unpivoting Columns in Power Query

    • Assignment Upload - Session 1

  • 18

    Session 2

    • Content Overview

    • Assignment Upload - Session 2

  • 19

    Session 3

    • Content Overview

    • Assignment Upload - Session 3

  • 20

    Session 4

    • Content Overview

    • Assignment Upload - Session 4

  • 21

    Session 5

    • Content Overview

    • Assignment Upload - Session 5

  • 22

    Session 6

    • Content Overview

    • Assignment Upload - Session 6

  • 23

    Bonus Materials

    • Excel Formatting Syntax

    • Overview of Excel Styles

    • Advanced Formatting in Excel

    • Excel Functions for finding Duplicate values in a List

    • Reconciliation with Wild Card Characters in Excel

    • How to Reconcile in Excel without a clear reference

  • 24

    Evaluation & Certificate

    • Instructions

    • Before you go...

    • How to Attach your Certificates to your LinkedIn Profile