Data Visualisation Principles for SME Dashboards: Charts That Actually Inform Decisions

Introduction

Australian SMEs generate more data than ever before-sales figures, inventory levels, staff hours, customer acquisition costs, cash position. Most are collecting it in spreadsheets. Many are building dashboards. Few are getting clear answers from them.

The problem isn't a lack of data. It's how that data is presented. A dashboard cluttered with six chart types, eleven KPIs, and rainbow-coloured conditional formatting doesn't inform decisions-it overwhelms the person trying to make them.

This article covers the principles that separate useful dashboards from decorative ones. These aren't about fancy Excel tricks. They're about design thinking: what to show, how to show it, and what to leave out.

Principle 1: One Question Per Dashboard

The single most common mistake in SME dashboards is trying to answer everything at once. A single screen showing sales, expenses, headcount, customer satisfaction, inventory turnover, and cash flow serves none of those metrics well.

Instead: Build a separate dashboard for each key business question.

• Cash dashboard: "Do I have enough cash to operate this month?"
• Sales dashboard: "Which products and channels are driving revenue?"
• Operations dashboard: "Are we delivering on time and within budget?"

Each dashboard should be answerable with a yes/no or a clear trend direction. If you can't summarise the dashboard's purpose in one sentence, it's trying to do too much.

Principle 2: Put the Answer at the Top

Business dashboards aren't exploratory data analysis tools. They're decision-support instruments. The person opening the dashboard should know the most important thing within three seconds.

Structure every dashboard with three zones:

1. Summary row (top) - the key metric, its trend direction, and whether it's on track. A single KPI or a small set of 3-5 numbers with clear up/down indicators.
2. Context charts (middle) - the trend over time, breakdowns by category, comparisons to budget or prior period.
3. Detail data (bottom) - the underlying table if someone needs to drill in.

This inverted pyramid structure means the executive gets their answer at a glance, while the analyst can still dig deeper.

Principle 3: Choose the Right Chart for the Job

Excel offers 16+ chart types. Most SME dashboards use maybe three-and often the wrong one.

The quick reference:

A note on pie charts: They're rarely the best choice. Humans are bad at comparing angles. A sorted bar chart communicates the same information more accurately. Reserve pie charts for showing a single dominant category vs everything else (e.g., "one customer represents 60% of revenue").

Principle 4: Remove Chartjunk

Edward Tufte coined the term "chartjunk"-visual elements that add no information. In Excel dashboards, chartjunk is everywhere:

• 3D effects on bars and pies (they distort perception)
• Gridlines that are darker or more prominent than the data
• Excessive axis labels (every single month on a 5-year chart)
• Decorative backgrounds, shadows, and gradients
• Legends when a direct label would be clearer
• Too many colours (use 2-3 per chart, max)

The test: Remove an element. Does the chart still communicate the same information? If yes, that element is junk. Remove it.

Principle 5: Use Colour With Purpose

Colour in dashboards should carry meaning, not decoration. A common and effective scheme:

• Green - on track, positive, above target
• Amber - watch, borderline, needs attention
• Red - critical, below target, action required
• Blue/grey - neutral data, no status implied

Limit your palette. A dashboard with red, green, blue, orange, purple, and yellow bars conveys no additional information over one with three colours-it just looks busier.

For accessibility, avoid relying solely on colour to convey meaning. Use shapes, patterns, or position as a backup. Approximately 8% of Australian men have some form of colour vision deficiency.

Principle 6: Consistent Scales and Axes

When comparing multiple charts, keep scales consistent. Two charts showing monthly revenue and monthly expenses should use the same Y-axis scale if they're displayed together. If the scales differ, a 10% drop in revenue and a 10% rise in expenses will look dramatically different even though the relative change is identical.

Exception: If metrics have very different magnitudes (e.g., revenue in millions and headcount in dozens), use separate scales but clearly label them. Better still, normalise both to an index (100 = baseline period) so trends can be compared directly.

Principle 7: Make Comparisons Easy

A single number is rarely useful. The same number compared to something is informative.

For every KPI on your dashboard, show at least one comparison:

• Actual vs budget - are we on track?
• This period vs last period - are we improving?
• Current vs target - are we close to our goal?
• This year vs last year - is the trend seasonal or structural?

Excel's conditional formatting icons (green up arrow, red down arrow) are a quick way to add this. But a simple column showing the variance percentage is even more useful.

Principle 8: Design for the Lowest-Tech Viewer

Your dashboard will be viewed by people who aren't Excel experts. Design accordingly:

• Freeze panes so headers stay visible
• Set print area and page layout in case someone prints it
• Avoid volatile functions (OFFSET, INDIRECT, TODAY) that cause recalculations on open
• Use Excel Tables (Ctrl+T) so ranges auto-expand as data grows
• Add a brief instructions sheet or comment explaining how to update the data
• Test it on a screen smaller than your own-laptop, tablet, phone

Putting It All Together: A Dashboard Checklist

Before you call a dashboard finished, run through this checklist:

1. Does it answer one clear question? (Yes/No)
2. Can you understand the key takeaway in 3 seconds?
3. Is the chart type appropriate for the data?
4. Are 3D effects, excessive colours, and unnecessary gridlines removed?
5. Does colour carry meaning, or is it decorative?
6. Are axes and scales consistent across comparable charts?
7. Is every KPI shown with a comparison?
8. Will a non-Excel user understand it?
9. Can someone update it without breaking it?
10. Did you remove anything that doesn't add value?

Conclusion

An effective dashboard isn't defined by how many charts it packs in or how colourful it is. It's defined by how quickly it answers a business question. Start with the question, design the dashboard around the answer, and remove everything that doesn't help someone make a better decision.

For more practical guides on building financial systems and business tools, visit ExcelWiz.com.au.