Laying out a dashboard sheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which setting hides the grid lines so a dashboard sheet looks clean?
2. Three charts on your dashboard sit at slightly different heights. What is the best fix?
3. You want equal gaps between four charts in a row. Which tool do you use?
4. A key figure cell on a dashboard shows total sales. How should it get its value?
5. Which is the best way to keep a chart on the Dashboard sheet instead of the data sheet?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1View > Gridlines (untick) | AHides the grey cell gridlines so the dashboard sheet looks clean and uncluttered | |
| 2Align > Distribute Horizontally | BMove Chart > Object in: Dashboard | |
| 3A key figure cell shows a live total that updates when the data changes | CThe key figure is a typed number, so it will not update when the data changes. It should be a formula such as =SUM(Sales!C2:C100). | |
| 4Charts are moved onto one Dashboard sheet so everything can be seen together | DSpaces the selected charts evenly across the sheet so the gaps between them are equal | |
| 5Dashboard key figure typed as: 48250 | EThe charts are different sizes and not aligned, and the gridlines clutter the view. Resize them to match, use Align and Distribute, and untick View > Gridlines. | |
| 6Charts left at random sizes, gridlines still on | F=SUM(Sales!C2:C100) |
C
Definitions
Dashboard layout
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=___(Sales!D2:D50)
2.
View > Show > _________ (untick)
3.
Move Chart > Object in: _________
4.
Align > Distribute ____________
5.
=___(Sales!D2:D50)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
Sheet: Dashboard
B
B2: =SUM(Sales!C2:C50)
C
B3: AVERAGE(Sales!C2:C50)
D
View > Gridlines: off
Question 2
A
Select the three charts
B
Align > Align Left
C
Distribute Horizontally
Question 3
A
Sheet: Dashboard
B
B2: =SUM(Sales!D2:D50)
C
B3: 4850
Question 4
A
Click the Sales chart
B
Cut it with Ctrl+X
C
Click the Dashboard sheet tab
D
Paste it with Ctrl+Z
Question 5
A
View > Gridlines: on
B
Select the three charts
C
Align > Align Top
Answer Key
Laying out a dashboard sheet
Section A: Quick Questions
Q1
A — View > Gridlines (untick)
Q2
A — Select them and use Align > Align Top
Q3
B — Align > Distribute Horizontally
Q4
B — =SUM(Data!C2:C200)
Q5
A — Right-click the chart > Move Chart > Object in: Dashboard
Section B: Match the Pairs
Q1
View > Gridlines (untick) → Hides the grey cell gridlines so the dashboard sheet looks clean and uncluttered
Q2
Align > Distribute Horizontally → Spaces the selected charts evenly across the sheet so the gaps between them are equal
Q3
A key figure cell shows a live total that updates when the data changes → =SUM(Sales!C2:C100)
Q4
Charts are moved onto one Dashboard sheet so everything can be seen together → Move Chart > Object in: Dashboard
Q5
Dashboard key figure typed as: 48250 → The key figure is a typed number, so it will not update when the data changes. It should be a formula such as =SUM(Sales!C2:C100).
Q6
Charts left at random sizes, gridlines still on → The charts are different sizes and not aligned, and the gridlines clutter the view. Resize them to match, use Align and Distribute, and untick View > Gridlines.
Section C: Definitions
Q1
Dashboard layout — Arranging charts, slicers and key figures on one sheet, neatly aligned and sized, with gridlines hidden, so the main message is easy to see at a glance.Section D: Fill in the Blanks
Q1
=SUM(Sales!D2:D50)Q2
View > Show > Gridlines (untick)Q3
Move Chart > Object in: DashboardQ4
Align > Distribute HorizontallyQ5
=MAX(Sales!D2:D50)Section E: Spot the Error
Q1
C — A key figure cell must start with = to be a formula. Without it the cell shows plain text. It should be B3: =AVERAGE(Sales!C2:C50).
Q2
B — Charts sitting side by side in a row need their top edges aligned. Align Left lines up the left edges, which stacks them on top of each other. It should be Align > Align Top.
Q3
C — A typed number is hard-coded and will not change when the data changes. A key figure should be linked with a formula such as B3: =MAX(Sales!D2:D50).
Q4
D — Ctrl+Z is Undo, so the chart would not move to the Dashboard sheet. Paste is Ctrl+V.
Q5
A — A neat dashboard hides the gridlines so they do not clutter the charts. Gridlines should be turned off in View.