Data Analytics with Excel

Data Analytics with Excel

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

• 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

Outline

1. Fundamentals of Analytics
   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
2. Excel
   1. Data in Excel
   2. Limitations
   3. Tools
3. Power Pivot
   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
4. Time Based Analytics
   1. Time manipulation
   2. Master Date and external date files
   3. Time Series
   4. Time based aggregations
5. Power Query
   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
6. Extending the Power of Excel with Power Pivot and Power Query
   1. Power Pivot and Power Query in conjunction
   2. Real time Reporting and Analytics
   3. Collaborative Analytics
   4. Data pipeline automation
7. Conclusion