excel charts

How to visualise your data in Excel

This Friday we are talking about why pie charts are not the best way to display the data, quick undo shortcut and EOMONTH function.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

5 reasons to stop using pie charts in Excel

Pie charts are an almost guaranteed element of most dashboards, but that doesn't make them great ways to display data. Here are 5 reasons not to use pie charts in your dashboards:

1. They're difficult to visually gauge proportions 

Got a category on your pie chart at 7%? Could you spot that it was 7% without a legend or commentary? Didn't think so. 

If a chart requires call out commentary to be able to read an actual value, then the chart isn't doing its job particularly well! A simple table or column/bullet chart would show this much better. 

2. They're cluttered with any more than a couple of categories

If you are using a pie chart to display any more than a couple of categories you're asking for the chart to be ignored be your audience. Even worse, is your pie chart so bad that the legend is as big as the chart itself, and there's so many colours you can't tell them apart? 

If this is the case you should question if a pie chart is really the best way to display your data. A simple bar chart may be a much better solution. 

3. They are unable to show trending

If you're using a pie chart to display performance of a specific metric at a point in time, why aren't you showing trending over time? Pie charts cannot do this. 

I've seen dashboards with date drop downs and fancy sliders, but ultimately it's never a good way to display a metric over time.

If you only have a couple of categories consider a 100% stacked bar chart, if you have more consider a regular bar chart with a drop-down menu for the category.  

4. They are one dimensional

This is an obvious one, but you can only display a single point in time value on a pie chart. Yes, you could add filters for other categories and allow the chart to update when used, but what it's often better to track multiple metrics on a single chart as a comparison, or use other charts types to create multidimensional bar charts. 

5. They're a waste of valuable space

For a chart that shows no trending, one dimension, one metric, pie charts take up far too much valuable space on any dashboard. Maybe the old fashioned managers still love them, but we'd recommend avoiding pie charts at all costs when designing dashboards in any business scenario. 

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

7 things every dashboard should include

Despite the popularity of dashboards in business, there's a huge variability in the quality of dashboard design and setup. We recommend 7 key characteristics that every great dashboard should have. Do your dashboards have these?

Clean layout 

Still using primary colours and 3D pie charts? I hate to break it to you, but they're a terrible way to show data. Edward Tufte's famous principle of 'data ink' from his book 'The visual display of quantitative data' directs the designer to minimise the amount of ink on a printed dashboard not telling the story of data. If you create a relevant report in a clean format, it should speak for itself without dated looking bells and whistles. 

Traceability 

Any dashboard is only as good as the data that goes into it. How many mistakes or omissions have you found in reports, especially those manually produced? We recommend structuring reports in a way that any figure can be proved by tracing back to the underlying process or system

Actionable metrics

Do your metrics pass the 'so what' test? This is a test I learned early in my career developing dashboards for a particularly blunt manager - I'd talk through a new report or metric in his office. 'So what' was invariably the question that was asked whilst explaining the importance of a specific figure. If you can't answer this question, why is the metric on the report? You should ask this regularly to make sure your dashboards stay relevant. 

View by the most relevant categories

There will undoubtedly be a number of lenses through which you look at your business, such as geographical, by business unit, by salesman etc. We recommend displaying tables and charts by default using the most relevant categories. If the report audience cannot see the split of a metric across the business it will lose relevance. 

Self-service filters 

Dashboards are often used by stakeholders across entire businesses, and it's difficult to design a dashboard that pleases all of the audience, all of the time. Some will create multiple reports, for example by region, but this is short-sighted from both a process and maintenance perspective. We find report filters, adding drop-down menus to narrow down the report by specific criteria allow reports to maintain relevance across wide audiences. 

Drill-down capability

Similar to the above comments on the traceability of metrics, the actionability of those metrics is also key. We recommend adding functionality to easily get to the underlying data in excel with minimal effort. Most leading BI software allows this by default, but make sure that functionality is explained to users and that the right data is exposed. For example, if showing a metric of items past a due date, the ability to drill down to see the IDs or owners of those items would be useful to end users. 

Glossary / Definitions

Business data is complex by nature. The job of the dashboard designer is to bridge the gap between technical and business definitions of metrics and data. Often a business definition isn't understood by an IT team and a technical definition doesn't always make sense to someone in the business. A page of metric definitions or a glossary is the most effective way to ensure the entire report audience is reading the report in the same way. Ensure it is kept up to date, accessible, and it should be referred to as the golden source of information on how the reports are configured. 

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit