Why learn Data modelling and Data analyis using Excel?

Basic knowledge of Microsoft Excel is not enough to deal with diverse and dynamic data analysis needs in modern businesses. You need to use more advanced features of Microsoft Excel to connect to scattered data sources, mesh up large datasets and create sophisticated and complex data models. Vlookup(), Match() and Index() formulas will help to create basic reports. However, to create more advanced and bespoke reports, you need to learn and use Data Analysis Expressions

This course includes

  • Four hours on-demand videos

  • 8 downloadable resources

  • Twelve months unlimited access

  • Certificate of completion - 8 CPD credits

What you 'll learn

  • In depth knowledge of connecting to multiple data sources.

  • How to handle and deal with big data.

  • Create more advanced and complex reports using Data Analysis Expressions (DAX).

  • Focus on creating and using Measures not just caculated columns

  • How to create fully automated reports and dashboard in minutes.

  • Who should attend

    Anybody who want to learn Advanced Data Modelling and Reporting using Microsoft Excel.

  • Prerequisites

    Basic knowledge of Microsoft Excel.

  • Requirements

    Microsoft Excel 2016 or later. Microsoft Excel 365 is preferred.

Course curriculum

  • 1

    Introduction

    • Modern Challenges for Everyone

    • What is PowerPivot?

    • Loading data to PowerPivot Data Model
  • 2

    Calculated Columns vs DAX Measures

    • How to add Calculated Columns in PowerPivot?

    • What is DAX - Data Analysis Expression?

    • How to add a DAX Measure?
  • 3

    Time Intelligence in PowerPivot

    • Standard Calendar vs Custom Calendar

    • Table Relationships

    • Increase drilldown limit of PowerPivot

    • Practice DAX measures

    • CALCULATE() - Your new friend.

    • How to use Disconnected Tables.

    • Time Intelligence functions

    • More Time Intelligence Functions
  • 4

    Conditional Measures

    • SWITCH(), IF() and BLANK() functions
  • 5

    Iterator Functions

    • SUMX(), MAXX(), RANKX() and TOPN() functions
  • 6

    Multiple Data Tables

    • Multiple data tables - Data Granularity and Data Relationships