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.
- 17 lessons
- 6 h 5 min
- Certificate
Data and databases - Intermediate
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.
About this course
Most workplaces collect data: sales receipts, school marks, survey answers, attendance, donations. The skill that people value is not typing the data in but turning it into answers: which product sells best, which class improved, which month was weakest. Excel can do this very well when the data is prepared properly and the right tools are used.
This intermediate course takes you from a messy list to a finished dashboard. You will clean data, write formulas such as SUMIFS and XLOOKUP, summarise thousands of rows with PivotTables, choose honest charts, and combine everything into a one-page report. Every module uses small, realistic examples from shops, schools and community projects.
You should already be comfortable with the basics of Excel: entering data, simple formulas, SUM and IF, sorting and filtering, and basic charts.
What you will learn
- Prepare raw data so it is tidy, consistent and ready to analyse
- Clean text, dates and numbers stored as text using Excel tools and functions
- Write analysis formulas with SUMIFS, COUNTIFS, AVERAGEIFS, IFS, IFERROR and XLOOKUP or INDEX-MATCH
- Use Excel Tables, named ranges and structured references to make formulas easier to read
- Build PivotTables with grouping, calculated fields, slicers and timelines, and turn them into PivotCharts
- Choose the right chart for a question and avoid charts that mislead
- Use sparklines and conditional formatting to show patterns at a glance
- Build, protect, print and share a one-page KPI dashboard from raw data
Course content
5 modules · 18 lessons · 5 h 15 min · 6 quizzes
Module 1: Preparing data
Shape data into a tidy layout and clean up the common problems before any analysis begins.
- Tidy data and Excel Tables 15 min
- Cleaning text: TRIM, CLEAN and friends 15 min
- Dates and numbers stored as text 15 min
- Duplicates, find and replace, and data validation 15 min
- Quiz: 8 questions
Module 2: Formulas for analysis
Conditional totals, lookups, nested logic, text and date functions, and readable formulas using names and Tables.
- SUMIFS, COUNTIFS and AVERAGEIFS 20 min
- Logic: nested IF, IFS and IFERROR 15 min
- Lookups: XLOOKUP and INDEX-MATCH 20 min
- Names, structured references, text and date functions 20 min
- Quiz: 8 questions
Module 3: PivotTables
Summarise large lists in seconds: build, group, calculate, filter with slicers, refresh and chart.
- Building your first PivotTable 20 min
- Grouping dates and numbers, and calculated fields 20 min
- Slicers, timelines and refreshing 15 min
- PivotCharts 15 min
- Quiz: 7 questions
Module 4: Charts and visual storytelling
Pick the right chart, combine chart types, add sparklines and conditional formatting, and avoid misleading visuals.
- Choosing the right chart 15 min
- Combo charts and sparklines 15 min
- Conditional formatting and misleading charts 20 min
- Quiz: 7 questions
Module 5: Dashboards and reporting
Design a clear one-page dashboard, link it to your analysis, and complete a project from raw data to a shared report.
- Dashboard design and KPIs 15 min
- Building the dashboard: linking, protecting and sharing 20 min
- Worked project: from raw data to report 25 min
- Quiz: 7 questions
Final exam
28 questions. Pass with 70% or more to earn your certificate.
What you need
- Completed Microsoft Excel: Spreadsheets from Zero, or equivalent skills (formulas, SUM, IF, sorting, filtering, simple charts)
- A computer with Excel 2019 or Microsoft 365 (a few features need Microsoft 365 or 2021 and are marked in the lessons)
- Comfort with saving files and using folders
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