Excel Dashboards For Beginners
Excel Dashboards For Beginners
Want to get started with Excel Dashboards? You are in the right place. Excel Dashboards can look cool, but putting one together can be tricky. In the Excel Dashboards for Beginners series, Chris explains his simple approach for putting together powerful, dynamic and visually-pleasing Excel dashboards that you and your customers are going to love. Let’s get into it!
Click here to download the Excel Dashboards For Beginners download file.
Part 1 – 4 Cool Features of Excel Dashboards
The best way to tackle this complex topic is first to develop an appreciation of what a good Excel Dashboard looks and feels like. Chris has built a demo file that you can download and click through – check out the download link above. This dashboard contains four features that all good Excel dashboards share: (1) a helpful layout (2) consistent spacing (3) layering (4) good use of colour.
Let’s go through these features one-by-one, noticing how the are implemented. Make sure you try to implement these in your Excel Dashboards too! Let us know in the YouTube comments how you get on.
Let’s go through these features one-by-one, noticing how the are implemented. Make sure you try to implement these in your Excel Dashboards too! Let us know in the YouTube comments how you get on.
(1) A Helpful Layout
How on earth do you get started with an Excel dashboard? An empty spreadsheet can be daunting, so try applying Chris’ ‘three-by-two’ default layout to get things started. In the video, Chris shows how to set up this layout, and how the layout can create a cool Excel Dashboard.
How on earth do you get started with an Excel dashboard? An empty spreadsheet can be daunting, so try applying Chris’ ‘three-by-two’ default layout to get things started. In the video, Chris shows how to set up this layout, and how the layout can create a cool Excel Dashboard.
With the layout in place, how do you know what should go where? This depends on your situation; make sure you prioritise by discussing requirements with your customer and establishing the what the most important information is. Since the Western reader’s eye traces from the top-left to the bottom-right corner of the spreadsheet, it is important to place the most important data (Eg. ‘profit’?) in the top-left – don’t hide it away somewhere else!
(2) Spacing
Neat-and-tidy presentation is another feature of a good Excel dashboard. ‘Tidiness’ can be subjective (ask your mother about when you were a teenager), so what rules can we apply here? Consistent row heights and columns are a must. In the video, Chris shows how all columns in the example Excel dashboard are one of two column widths, creating a super clean look and helping the user assimilate the data. Try this approach in your Excel dashboard, you and your client will love it.
Neat-and-tidy presentation is another feature of a good Excel dashboard. ‘Tidiness’ can be subjective (ask your mother about when you were a teenager), so what rules can we apply here? Consistent row heights and columns are a must. In the video, Chris shows how all columns in the example Excel dashboard are one of two column widths, creating a super clean look and helping the user assimilate the data. Try this approach in your Excel dashboard, you and your client will love it.
(3) Layering
This might come as a surprise, but all cool Excel dashboards showcase this third key feature. When a lot of data is visible (check out your car dashboard), layers help the user navigate and make sense of the information. How can we do this in Excel? Shapes with gradient fill allow us to lift the main dashboard pane from the background. Then, shading around charts and other objects can make them stand out. Check out Chris’s demonstration in the video and have three layers in mind for your next Excel dashboard.
This might come as a surprise, but all cool Excel dashboards showcase this third key feature. When a lot of data is visible (check out your car dashboard), layers help the user navigate and make sense of the information. How can we do this in Excel? Shapes with gradient fill allow us to lift the main dashboard pane from the background. Then, shading around charts and other objects can make them stand out. Check out Chris’s demonstration in the video and have three layers in mind for your next Excel dashboard.
(4) Colour
What constitutes helpful colouring? This is to some extent a matter of opinion, but some general principles can really help. The most important is that colour should be used sparingly, or (at least) judiciously. Too many colours confuse and mean that individual colours lose impact and resonance. Stick to 2-3 key colours and use 1-2 highlight colours (yellow is helpful here) to make the important information stand out. Remember you can use an online colour picker to match your colours to a company brand identity. Chris demonstrates his approach in the video.
What constitutes helpful colouring? This is to some extent a matter of opinion, but some general principles can really help. The most important is that colour should be used sparingly, or (at least) judiciously. Too many colours confuse and mean that individual colours lose impact and resonance. Stick to 2-3 key colours and use 1-2 highlight colours (yellow is helpful here) to make the important information stand out. Remember you can use an online colour picker to match your colours to a company brand identity. Chris demonstrates his approach in the video.
———————————
Part 2 – 17 Tips For Formatting Excel Dashboards
1. Consistent Row (Heights) and Column (Widths)
a. Display columns (8/10) and spacer columns (2)
a. Display columns (8/10) and spacer columns (2)
2. Use Subtle Background Formatting (Not A Distraction)
a. Dotted pattern fill works well
a. Dotted pattern fill works well
3. Shapes Help Create Layers (Use a gradient fill)
4. Switch Off Gridlines (But Horizontal Borders Are Important!)
a. Creates ‘Clean’ Look (but gridlines do support readability)
b. Do you need ‘all borders’? Horizontal only improves readability
a. Creates ‘Clean’ Look (but gridlines do support readability)
b. Do you need ‘all borders’? Horizontal only improves readability
5. Get Rid Of The Units (Write It Once)
6. Get Rid Of The Zeroes (With Special Formatting)
a. Some interesting ones to try (working with thousands):
i. 0.00,
ii. 0,”K”
a. Some interesting ones to try (working with thousands):
i. 0.00,
ii. 0,”K”
7. Use Colour Sparingly (Within A Scheme!)
a. Same formatting in tables and charts (to highlight important items)
b. Consider conditional formatting
c. White font on dark colour provides a nice contrast
d. Match to client brand identity?
a. Same formatting in tables and charts (to highlight important items)
b. Consider conditional formatting
c. White font on dark colour provides a nice contrast
d. Match to client brand identity?
8. Position Output Cell In the Top-Left (Not The Bottom Left)
9. Highlight User Input Cells (Top-Left)
a. Position in the top-left for easy access
b. Use a highlight colour (yellow works well)
a. Position in the top-left for easy access
b. Use a highlight colour (yellow works well)
10. Differentiate Font Size (Make Output Stand Out)
a. Like on your car dashboard
a. Like on your car dashboard
11. Use Dynamic Headers (Highlight Dynamic Quality!)
a. Use formulae to create this effect
a. Use formulae to create this effect
12. Declutter The Axes
13. Cell Comments – An Additional Layer?
a. Option to switch on / off (can annoy the user)
a. Option to switch on / off (can annoy the user)
14. Use A ‘Catch-All’ Category In Charts / Tabulation (To Simplify)
15. Consider Role Of Company Logo And Titles (Are They Needed?)
a. Can use up prime ‘real estate’
a. Can use up prime ‘real estate’
16. Make (Most) Fonts Consistent
a. Can be considered ‘lazy’ to use the ‘default font’
b. Arial is recommended (obscure fonts may not be supported)
c. Fit to company brand identity?
a. Can be considered ‘lazy’ to use the ‘default font’
b. Arial is recommended (obscure fonts may not be supported)
c. Fit to company brand identity?
17.
Golden Rule 1: Formatting Should Support Assimilation
Golden Rule 1: Formatting Should Support Assimilation
Golden Rule 2: Remember To Some Extent It’s A Matter Of Opinion – Whose Matters?
Part 3 – Create Dynamic Quality In Excel Dashboards