INSYNTIVEAcademy
IDEN
Sign inSign up

Start free · Free Learning

Excel: Zero to Dashboard

Combine 12 files with Power Query, relate tables in the Data Model, then build an interactive dashboard in Excel. Numbered pictures in every lesson.

8 modules · 37 lessons · ~3.5 hr · 9 free

Start learning

Modules 1–2 are free (9 of 37 lessons), no credit card and no sign-up. The other 28 lessons are for members.

Module 01 Free

Getting started

Set up Excel and the practice files, get to know the screen you’ll be using, then see the five-step path from 12 files to a dashboard.

  1. Set up Excel and the practice files4 steps · 6 min
  2. Tour the Excel interface4 steps · 7 min
  3. Three tools, five steps3 steps · 5 min
  4. Checkpoint: getting started4 questions · 3 min Checkpoint

Module 02 Free

Pull in and combine 12 files

Point Excel at one folder, combine 12 monthly files into a single 1,056-row table, then keep it as a connection in the Data Model. Not a single copy-paste.

  1. Get data from a folder5 steps · 6 min
  2. Read the file list, then Combine & Load To4 steps · 6 min
  3. The Combine Files window3 steps · 5 min
  4. Import Data: connection only + Data Model4 steps · 5 min
  5. Checkpoint: pull & combine data4 questions · 3 min Checkpoint

Module 03 Members

Clean it in Power Query

Tidy up the 1,056 combined rows in the Power Query Editor: give the query a short name, remove columns, make vendor names consistent and set the right data types.

  1. Tour the Power Query Editor4 steps · 7 min · members only
  2. Remove the columns you don't need3 steps · 4 min · members only
  3. Tidy up vendor names5 steps · 6 min · members only
  4. Set the right data types4 steps · 5 min · members only
  5. Checkpoint: Power Query4 questions · 3 min · members only Checkpoint

Module 04 Members

Master data & calendar

Add the department & category lookups, the monthly budget, and a 365-day Calendar table built with M code.

  1. Bring in the three Master Data tables5 steps · 6 min · members only
  2. Build a Calendar table with M code5 steps · 8 min · members only
  3. Check all five queries4 steps · 4 min · members only
  4. Checkpoint: master data & calendar4 questions · 3 min · members only Checkpoint

Module 05 Members

Data Model & measures

Connect the five tables in Power Pivot, sort the months correctly, then build the four measures behind every number on the dashboard.

  1. Create the five relationships5 steps · 8 min · members only
  2. Sort months January to December6 steps · 5 min · members only
  3. Total Expense & Total Budget measures5 steps · 7 min · members only
  4. Budget usage % and variance4 steps · 5 min · members only
  5. Checkpoint: Data Model & measures4 questions · 3 min · members only Checkpoint

Module 06 Members

PivotCharts from the Data Model

Four PivotCharts straight from the Data Model: monthly trend, departments, cost categories, and the fixed vs variable mix.

  1. Insert a PivotChart directly4 steps · 5 min · members only
  2. Chart 1: monthly trend, actual vs budget5 steps · 7 min · members only
  3. Charts 2 & 3: departments and categories5 steps · 7 min · members only
  4. Chart 4: fixed vs variable doughnut3 steps · 5 min · members only
  5. Checkpoint: PivotCharts4 questions · 3 min · members only Checkpoint

Module 07 Members

Polish, KPIs & slicers

Remove the field buttons, write titles that tell the story, add KPI cards, then make the whole dashboard filterable with slicers and a timeline.

  1. Tidy up all four charts4 steps · 6 min · members only
  2. KPI cards: the big numbers on top5 steps · 8 min · members only
  3. Slicers that drive every chart5 steps · 7 min · members only
  4. Period timeline & logo5 steps · 6 min · members only
  5. Checkpoint: polish & interactivity4 questions · 3 min · members only Checkpoint

Module 08 Members

Tell the story, maintain & fix

Arrange every piece into a dashboard that tells a story, update next month’s data with a single Refresh All, and learn the fixes for the most common problems.

  1. Arrange a dashboard that tells a story5 steps · 8 min · members only
  2. Next month: just Refresh All5 steps · 6 min · members only
  3. When something breaks5 steps · 7 min · members only
  4. Checkpoint: ready to send5 questions · 4 min · members only Checkpoint