Faysal AzizEnquire
FA-0152Data & Analytics

Data Analytics with Excel

Introduction

Why this course

This course has been designed for those who know Microsoft Excel but want to gain the ability to perform Analytics and create interactive reports. The tools used throughout this course are limited to only Power Pivot and Power Query. This is an intermediate level course with hands-on as well as theoretical parts.

Learning Outcome

By the end of this course, the learner may expected to have clear understanding and skills on the following topics:

  • Fundamentals of Data Analytics
  • Key aspects and flow of Data Analytics
  • Data essentials pertaining to using Excel for analytics
  • Implementation of analytical techniques using Excel
  • Create data access pipeline with Excel
  • Enable collaborative data access pipelines with Excel
  • Usage of DAX and M language for Analytics
  • Various forms of data conjunctions and their usage in analytics
  • Create interactive dashboards and reports
  • Placing interactive reports in the data pipeline

Duration

Duration: 3 days

Difficulty: Medium intensity

Level: Intermediate

Prerequisites

Prerequisites

  • Understanding of MS Windows’ filing system
    • Extension and type views
    • Zipping and unzipping
  • Experience using Excel
    • Basic data acquisition
    • Formula and functions
    • Basic reporting and visualizations
    • Basic pivoting
    • Conditions
Training outline

7 modules

·
01Fundamentals of Analytics11 topics
  1. Overview of Analytics
  2. Impact in the corporate world
  3. Corporate Data Governance
  4. Data Storage methodologies
  5. Data Lake, Pool and Mart
  6. Schemas
  7. Data Fluency
  8. Data Structure
  9. Data Analytics Steps
  10. Data Analytics types
  11. Data analytics methodologies
02Excel3 topics
  1. Data in Excel
  2. Limitations
  3. Tools
03Power Pivot13 topics
  1. Activation
  2. Data Sourcing
    1. Flat files
    2. Native files
    3. Remote RDBMS
    4. Linking
    5. Data Model
  3. ETL techniques
    1. Custom Columns
    2. Calculated columns
    3. DAX
    4. GUI usage
  4. Reporting
  5. Pivots and Reports
  6. Charts and REports
  7. Filters and Slicers
  8. Dynamic connection across sheets and sources
  9. Measures
  10. Sets
  11. Hierarchy
  12. KPI
  13. KPI vs cell level display
04Time Based Analytics4 topics
  1. Time manipulation
  2. Master Date and external date files
  3. Time Series
  4. Time based aggregations
05Power Query10 topics
  1. Data sourcing
    1. Flat FIles
    2. Native Files
    3. Web sources
  2. Query overview
  3. Query in depth
  4. ETL
    1. M language overview
    2. Date Time error management
    3. Table level ETL
    4. Column level ETL
  5. Data Cleaning
  6. Append
  7. Merge
  8. Solving analytical problems with Merge operations
  9. Functions
  10. Parameters Queries
06Extending the Power of Excel with Power Pivot and Power Query4 topics
  1. Power Pivot and Power Query in conjunction
  2. Real time Reporting and Analytics
  3. Collaborative Analytics
  4. Data pipeline automation
07Conclusion0 topics

A programme built around your team.

Share your training goals and requirements.

Training enquiry

Data Analytics with Excel
FA-0152

Share your requirements for this programme.

Training enquiry

Data Analytics with Excel