Excel and Power BI

By the end

What you'll build

• Design a workbook that separates source data, calculation and presentation so others can maintain it
• Clean and reshape an imported file into a reliable analysable table
• Summarise data with pivot summaries, conditional aggregation and ranking, and interpret the result critically
• Build a simple relational data model with repeatable import and transformation steps
• Design a one-screen dashboard around a specific question, using accessible charts and filters
• Validate a report against a known source and explain its assumptions, refresh cycle and limitations

Curriculum

What's inside

8 modules · 40 lessons

1. 01

   Spreadsheet foundations that prevent rework

   5 lessons
   • Designing a workbook someone else can pick up
   • Separating data, calculation and presentation
   • Cell references, named ranges and absolute addressing
   • Number formats and dates that behave predictably
   • References and layers at a glancematch pairs
2. 02

   Working with real, messy data

   5 lessons
   • Cleaning an imported file into an analysable table
   • Combining text, lookup and date functions
   • Duplicates, blanks and inconsistent categories
   • Validation rules that stop bad data at entry
   • Cleaning decisions, in your wordsfill blank
3. 03

   Analysis and summarising

   5 lessons
   • Building a pivot summary and reading it critically
   • Conditional aggregation and multi-criteria counting
   • Grouping, ranking and running totals
   • Simple scenario and sensitivity checks
   • Producing a summary you can defendsequence order
4. 04

   From spreadsheet to data model

   5 lessons
   • Tables, relationships and why one flat sheet stops working
   • Repeatable import and transformation steps
   • Fact and dimension tables in plain terms
   • Calculated measures compared with calculated columns
   • The customer who moved regionscenario
5. 05

   Dashboards and visual reporting

   5 lessons
   • Designing a one-screen dashboard around a single question
   • Choosing a chart that makes the comparison obvious
   • Filters, slicers and drill-through
   • Colour and accessibility in reports
   • One screen, one job eachmatch pairs
6. 06

   Trust, refresh and sharing

   5 lessons
   • Making a report you are willing to be questioned about
   • Checking totals against a known source
   • Refresh schedules and the risk of stale data
   • Sharing, permissions and confidential fields
   • Complete the trust rulesfill blank
7. 07

   Applied project

   5 lessons
   • Rebuilding one recurring manual report end to end
   • Documenting definitions and assumptions
   • Handing the report over to a colleague
   • Recognising when a spreadsheet is the wrong tool
   • Each choice prevents one failurematch pairs
8. 08

   Practice and check

   5 lessons
   • Symptom to root causematch pairs
   • From raw export to a summary you can defendsequence order
   • The vocabulary that keeps a workbook honestfill blank
   • Four o'clock, one export, a nine o'clock meetingscenario
   • Course quizquiz

The shape of it

How this course works