Excel for Data Analysis: PivotTables, Charts and Dashboards
Turn raw lists into answers: clean data, write analysis formulas, build PivotTables and charts, and finish with a one-page dashboard you can share.
- 18 lessons
- 5 h 15 min
- Certificate
Data and databases - Advanced
Advanced Excel: Power Query, Power Pivot and Automation
Go beyond formulas: import and reshape data with Power Query, model it with Power Pivot and DAX, use dynamic arrays, and automate repeat work safely.
About this course
When reports grow, copying and pasting from many files becomes slow and risky. Professional analysts use Excel differently: they connect to the data, clean it with repeatable steps, model it as related tables, and write measures that answer questions in any slice of the data. This course teaches those tools.
You will use Power Query to import and reshape files, Power Pivot and the Data Model to relate tables, DAX to write measures, dynamic array formulas to build live lists and reports, and macros and good workbook habits to save time and reduce errors. The course ends with a capstone project that brings them together.
This is an advanced course. You should already be comfortable with Excel Tables, PivotTables, SUMIFS and lookups. Some tools depend on your version of Excel, and each lesson says what to use if yours does not have them.
What you will learn
- Import data from files and folders with Power Query and refresh it with one click
- Transform data with repeatable steps, including merging, appending and unpivoting queries
- Build a Data Model with related tables following star schema principles
- Explain the difference between calculated columns and measures and choose correctly
- Write DAX measures using SUM, CALCULATE, FILTER, DIVIDE and basic time intelligence
- Use dynamic array functions such as FILTER, SORT, UNIQUE, SEQUENCE and LET, or alternatives in older versions
- Record a simple macro, explain what VBA and Office Scripts are, and recognise their risks
- Protect, document and tune a workbook, and deliver a complete reporting model
Course content
5 modules · 17 lessons · 6 h 5 min · 6 quizzes
Module 1: Power Query
Import data from files and folders, transform it with repeatable steps, combine queries and refresh.
- Why Power Query? Importing from files 20 min
- Transforming data in Power Query 20 min
- Combining data: append, merge and folders 25 min
- Unpivot, grouping and refreshing 20 min
- Quiz: 7 questions
Module 2: Data modelling with Power Pivot
Relate several tables in the Data Model, design a star schema, and tell calculated columns from measures.
- The Data Model and relationships 20 min
- Star schema basics 20 min
- Calculated columns versus measures 20 min
- Quiz: 7 questions
Module 3: DAX essentials
Write measures with SUM, CALCULATE, FILTER and DIVIDE, understand filter context, and add time intelligence.
- DAX basics and filter context 20 min
- CALCULATE, FILTER and DIVIDE 25 min
- Time intelligence basics 20 min
- Measures for dashboards 20 min
- Quiz: 8 questions
Module 4: Advanced formulas and dynamic arrays
Use FILTER, SORT, UNIQUE, SEQUENCE and LET, build lookup patterns, and handle errors, with alternatives for older versions.
- Dynamic arrays: FILTER, SORT and UNIQUE 20 min
- SEQUENCE, LET and array thinking 20 min
- Lookup patterns and error handling 20 min
- Quiz: 8 questions
Module 5: Automation and quality
Record macros, understand VBA and Office Scripts, protect and document workbooks, tune performance and deliver a capstone project.
- Macros, VBA and Office Scripts 25 min
- Protecting, documenting and tuning workbooks 20 min
- Capstone project: a complete reporting model 30 min
- Quiz: 8 questions
Final exam
26 questions. Pass with 70% or more to earn your certificate.
What you need
- Completed Excel for Data Analysis (PivotTables, charts and dashboards), or equivalent intermediate skills: Tables, PivotTables, SUMIFS and XLOOKUP or INDEX-MATCH
- Excel for Windows with Microsoft 365, Excel 2021 or Excel 2019 Professional Plus (Power Pivot is not in every edition; see Lesson 2.1)
- Sample files to practise with, which you will create during the lessons
Further reading
More courses to explore
Databases and SQL for Beginners
Understand how databases store information and use SQL to ask questions, summarise results, join tables and change data safely, finishing with a small library project.
- 19 lessons
- 5 h 2 min
- Certificate
Data Analysis with Python: pandas and Charts
Use Python, pandas and matplotlib to load, clean, summarise and chart real-world data, then complete a full survey analysis project with ethical, privacy-aware habits.
- 20 lessons
- 5 h 25 min
- Certificate