Course Objectives

  • Apply key Excel functions to prepare data for analysis using pivot tables
  • Create and customize pivot tables to reconcile and analyze accounts efficiently
  • Utilize Power Query to clean up and prepare data for reporting
  • Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
  • Run macros to speed up work and utilize other advanced techniques in data analysis and reporting
  • Report and analyse big data using PowerPivot

Course Outline

  • Key functions to prepare data for pivot table reporting
    • Table format
    • Lookup functions
    • Text functions
    • Naming cells
  • Advanced techniques in creating and customizing pivot tables
    • Number and cell format
    • Report layout
    • Calculation in value field
    • Grouping and un-grouping fields
    • Default and customized sorting and filtering
    • Sorting using custom list
    • Creating calculated field
    • Filtering using slicers and timelines
    • Connecting multiple pivot tables to one set of slicers
    • Customizing reports using the Get Pivot Data option
  • Power Query: A must-have skill
    • Introduction to this new feature
    • Where does Power Query fit in the Power family!
    • Get and transform: Link your Excel to external other data sources
      • Excel files
      • Text files
      • Web
      • SQL
    • Creating and editing the Query
    • Get data from: Tables, files and folders
    • Power Query to clean up data
    • Practical examples:
      • UnPivotting data
      • Working with nested column headers and merged cells
      • Naming, merging, splitting and removing columns
      • Filtering rows in different ways
      • Transforming and formatting data
      • Combining queries: Merge and Append
        • The different types of joining data
      • Analyzing disparate data sources with pivot tables
        • Utilizing pivot table wizard
        • Using internal data model
        • Building pivot tables using external data sources
      • The new world of PowerPivot
        • Benefits and drawbacks of PowerPivot
        • Merging data from multiple tables without using Vlookup
        • Creating better calculations using the DAX Formulas
        • Using DAX to create calculated fields
        • Calculate and Related functions
      • Introduction to Macros: Let Excel do the work for you
        • Where you cannot use Power Query, use macros
        • Planning your macro
        • Creating and recording macros
        • Editing macros
        • Introduction to Visual Basic for Application (VBA)