Three tools, five steps

Before you click anything, look at the map of the journey. The whole course uses only three tools that are already built into Excel, and their names sound scarier than they are. In this lesson you get to know the five working steps and the tool behind each one, then try out the finished dashboard.

123456 Zoom

Click a number on the picture or in this list to highlight that area.

1. 11 · Get Excel reads the contents of the Data Expense 2025 folder in one go with From Folder, not file by file. Module 2.
2. 22 · Combine Twelve monthly files are stacked into one table with 1,056 rows. Also Module 2.
3. 33 · Clean Power Query removes columns, tidies vendor names and fixes data types. Modules 3 and 4.
4. 44 · Relate The Data Model links all five tables with relationships: Expense, Budget, Departemen, Kategori and Kalender. Module 5.
5. 55 · Present PivotCharts, KPI cards, slicers and a timeline make up the dashboard. Modules 6 to 8.
6. 6VLOOKUP vs Data Model The old way: a VLOOKUP helper column across 1,056 rows, prone to #N/A. This course’s way: just one relationship line.

Step by step

1. 1

   Step 1: Follow the five boxes in the picture from left to right: Get (number 1), Combine (number 2), Clean (number 3), Relate (number 4) and Present (number 5). The first three boxes are handled by Power Query, the cleaner: it takes raw data and tidies it the way you tell it to.

   Tip: Remember this one sentence: Power Query records steps, not results. Copy-paste only gives you a one-off table; Power Query keeps the recipe. More data next month? Just click Data → Refresh All and every step runs again automatically.

2. 2

   Step 2: Look at the Relate box (number 4) and the comparison strip at the bottom of the picture (number 6). The transaction table only stores codes such as D04 and K08, not the names Marketing or Iklan & Promosi, which keeps the file light and free of typos. The Data Model links each code to its name: one relationship line means "whenever you see D04, it means Marketing", and that link automatically covers new data too.

   Watch out: The old VLOOKUP way means adding a helper column across 1,056 rows: the file bloats, #N/A keeps popping up, and the formula has to be dragged down again whenever new data arrives. In this course you won’t write a single VLOOKUP.

3. 3

   Step 3: The Present box (number 5) is the job of the PivotChart, a chart you can "pivot" any way you like. See the end result for yourself: open Dashboard Expense - CONTOH JADI.xlsx (if a yellow bar appears, click its button, e.g. Enable Editing), then click Marketing in the Departemen slicer. All four charts and the KPI cards change at once.

   Tip: You don’t need to build a PivotTable first. Just go Insert → PivotChart, and Excel builds the pivot table behind the scenes for you (Module 6).

   Watch out: When you’re done, click the Clear Filter icon (the funnel with an ✕) at the top right of the slicer, then close the finished example file without saving (Don't Save). You keep doing the exercises in the KOSONGAN file.