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.

(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.

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.

(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.

(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.

———————————

Part 2 – 17 Tips For Formatting Excel Dashboards

1. Consistent Row (Heights) and Column (Widths)
a. Display columns (8/10) and spacer columns (2)

2. Use Subtle Background Formatting (Not A Distraction)
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

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”

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?

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)

10. Differentiate Font Size (Make Output Stand Out)
a. Like on your car dashboard

11. Use Dynamic Headers (Highlight Dynamic Quality!)
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)

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’

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?

17.
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

Click here to download the simplified file for this video.

What do you we mean by ‘dynamic quality’ in Excel Dashboards? Let’s take a typical dataset such as the (fictional) personal spending data our example. Without a dynamic mechanism, the dashboard displays analysis for ALL the data. This is useful enough, but soon the user will wish to ‘slice and dice’ the data to include and exclude certain values. How much money did I spend in May, for example? How much do I spend on that particular expenditure item?

A dynamic dashboard allows the user to adjust settings and to display ONLY data that conforms to a particular criteria; in our case a month in the year. Not only does this give the customer greater power to get insight, it also gives the dashboard an interactive feel. The user can ‘play’ which makes the dashboard more insightful, and fun! This is why dynamic quality is so cool; but how on earth do you build it into an Excel dashboard?

There are a number of different ways, each with its pros and cons. One way is to use pivot tables; there is no doubt pivot tables are supremely powerful, but their layout can be difficult to predict (since it is auto-generated), and they often require an additional button click to make them update. A VBA-based solution is another option; this creates the slickest, most bespoke functionality; but, on the downside, the programmer must know sophisticated coding to make everything work.

The approach I demonstrate in this video offers a nice balance between powerful functionality and complexity of implementation. The approach uses INDIRECT to isolate ranges to look at in the dataset, determined by user selections (in this case, the month) on the dashboard. A notable benefit is that it updates instantly (because it is formula-based), so no additional button clicks are required to update the dashboard. Over the years it has worked very well for me on client projects.

Download the file, follow the demonstration and let me know what you think. Did you get the mechanism work for you, and have you created your own dynamic dashboard? How do you think it compares to other possible approaches? Will you be adding this approach to your Excel development toolkit? I would love to hear your thoughts in the YouTube comments and I promise I will get back to you!

Did this series help you get started with Excel dashboards? We would love to hear what you think, and to answer your questions in the comments – so go ahead and leave us a query right there. More to come soon from the Excel Dashboards for Beginners series – thank you for watching!