Tour the Excel interface

You’re probably used to Excel for SUM or making a table. In this course, most of the work happens in places people rarely open: the Data tab, the Queries & Connections pane and the Power Pivot tab. This lesson introduces the 9 areas you’ll hear about often, and makes sure the Power Pivot tab is switched on. Click a number on the picture to highlight its area.

123456789 Zoom

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

1. 1Title bar The AutoSave and Save buttons, the name of the open file and the Search box for finding commands.
2. 2Ribbon tabs From File to Help, plus Power Pivot at the far right. The ones this course uses most: Data, Insert and Power Pivot.
3. 3Get & Transform Data The first group on the Data tab. The Get Data button is the way into Power Query.
4. 4Queries & Connections group Refresh All updates all data; the Queries & Connections button opens or closes the pane on the right.
5. 5Name Box & formula bar Left (Name Box): the address of the selected cell, B2 in the picture. Right (formula bar): that cell’s content or formula.
6. 6Worksheet grid Lettered columns (A, B, C…) and numbered rows (1, 2, 3…); where they meet is a cell, e.g. B2. The Dashboard sheet is set up without gridlines on purpose, so you only see cell edges through the column letters and row numbers.
7. 7Queries & Connections pane The list of queries in this file. Empty for now; later it holds Expense, Budget, Departemen, Kategori, Kalender and a few helper queries.
8. 8Sheet tabs Mulai Di Sini holds the instructions, Dashboard is where you build the dashboard. The + button adds a new sheet.
9. 9Status bar & zoom On the left, Ready means Excel is waiting for your next command. On the right, drag the zoom slider to zoom in or out; 100% shows the current size.

Step by step

1. 1

   Step 1: In Latihan Dashboard Expense - KOSONGAN.xlsx, click the Dashboard sheet tab at the bottom left (number 8). Then look at the very top: the title bar (number 1) holds the AutoSave and Save buttons, the file name and the Search box.

   Tip: Forgot where a button is? Type its name in the Search box (or press Alt + Q), e.g. "Queries", and Excel shows you the command.

   Watch out: AutoSave can only switch on for files stored on OneDrive or SharePoint. For a file on drive D: it stays Off, so make a habit of pressing Ctrl + S after every lesson.

2. 2

   Step 2: Click the Data tab on the ribbon (number 2). The leftmost group, Get & Transform Data (number 3), holds the Get Data button: this is where Power Query pulls in data in Module 2. Then look at the right end of the tab row: there should be a Power Pivot tab.

   Tip: Right next to that group is the Queries & Connections group (number 4). Its Refresh All button (or Ctrl + Alt + F5) reruns every Power Query step. That’s the button that lets you update next month’s dashboard with one click (Module 8).

   Watch out: No Power Pivot tab yet? Turn it on now: File → Options → Add-ins → in the Manage box pick COM Add-ins → Go… → tick Microsoft Power Pivot for Excel → OK. You need this tab from Module 5 on.

3. 3

   Step 3: Click the Queries & Connections button (number 4). A pane with the same name opens on the right (number 7). It’s empty because this file has no queries yet.

   Tip: Click the same button again, or the ✕ at the pane’s top-right corner, to close it. By the end of Module 4 this pane holds five main queries, e.g. Expense with 1,056 rows and Kalender with 365 rows.

4. 4

   Step 4: Click cell B2 in the grid (number 6): click at the start of the grey text, right below the column letter B. The Name Box to the left of the formula bar (number 5) shows B2, and the formula bar shows that cell’s content: a note (in Indonesian) that your dashboard will be built on this sheet ("Bab 8" in the note is a chapter of the ebook; in this course the charts are built from Module 6). Finally, look at the very bottom: the sheet tabs (number 8) and the status bar with the zoom slider (number 9).

   Tip: Select a few cells with numbers and the status bar instantly shows their Average, Count and Sum. A quick way to check a total without writing a formula.

   Watch out: Formula bar empty and the Name Box says C2 or D2? You clicked the middle of the text. The text only spills over into columns C to G because the cells next to it are empty; its content lives in B2.