This is 4th part of Creating Management Dashboards in Microsoft Excel 6 post series by Robert.
This 6 Part Tutorial on Management Dashboards Teaches YOU:
Creating a Scrollable List View in Dashboard
Add Ability to Sort on Any KPI to the Dashboard
Highlight KPIs Based on Percentile
Add Microcharts to KPI Dashboards
Compare 2 KPIs in the Dashboards Using Form Controls
Show the Distribution of a KPI using Box Plots
The Challenge – Adding Visualization to the KPI Dashboard
In this final post on KPI dashboards with Microsoft Excel, we will show you how to add meaningful graphical visualization directly into our dashboard table. With scrolling, sorting and highlighting the dash-board already offers some interesting analytical features (see previous posts). But it is still displaying the data as pure numbers. That makes it difficult for the user to recognize the relative sizes of the values at a glance. Furthermore it is often necessary to communicate the relative position of the data compared to one or several other calculated or given values like the total average or a target.
The solution
[click here to view larger size]
Inserting conditionally formatted bar-line-combination-charts directly into the dashboard table visualizes the shown data and enables the user to get an overview at a glance. The bars show the relative sizes of the corresponding values, the conditional formatting let us immediately identify which values are below target (red color) or larger than target (grey color) and the line makes it easy to see whether a value is above or below the total average.
Download the Excel file – KPI Dashboards with visualization
The Implementation
To implement the charts, we need some knowledge about creating and formatting special charts with Microsoft Excel. In my humble opinion, the by far best resource on charts with Microsoft Excel is Jon Peltier’s excel charts pages. All you have to know for our dashboard charts is brilliantly described on Jon’s website (follow the links below).
- Prepare the workbook for the new features (5 extra columns on the dashboard for the bar charts, additional rows on the data worksheet to define the targets and new columns on the calculation sheet).
- Insert 5 conditional formatted bar charts. Read Jon’s method to create a conditional formatted chart.
Use the table on the dashboard as the data source for the chart and use the targets defined on the sheet “data” as the threshold whether a value is formatted red (below target) or grey (larger than or equal target).
- Calculate the total average on the calculation sheet for each KPI and add an average line to each of the bar charts by using an XY-scatter chart type. Read more on Bar line combo.The necessary calculations for the steps 2 and 3 can be found in columns Q to AQ of the sheet “calculation”.
- Format the charts to make only the bars and the average line visible (no axes, no grid lines, no data labels, no caption, no border or fill color of chart area and plot area). Like Albert Einstein said: “as simple as possible, but not any simpler.“
- Adjust the charts on the dashboard to make them fit exactly to the corresponding cell ranges. One tip for this: Holding the ALT key pressed when resizing a chart will make the chart size auto-fit to the size of the cell range beneath it. That makes it easier to position the charts correctly.The bar charts already look exactly the way we want them to. But there is one undesirable effect: when scrolling up or down the table, the maximum scale of the horizontal axis changes and the bars seem to “jump” up or down.
To avoid this, add two additional XY-scatter-series to the chart, representing the minimum and the maximum of the total data and assign them to the secondary axis. Furthermore add 2 additional bar series to the chart, again representing the minimum and the maximum of the total data and assign them to the primary axis. We thereby “force” both horizontal axes to be identical and stay the same when scrolling up or down. Since we do not want to display these dummy-series, format them with no line and invisible markers (XY-scatters) respectively with no fill color and no border.
- Finally update the caption beneath the table to explain the meaning of the line and the bar colors.
What is next?
- Download the excel KPI dashboard final workbook
- Bookmark Dashboards using Excel pages for future reference.
- Drop a lovely note of thanks to Robert if you have benefited from this series.
Read the next part: Part 5: Compare 2 Decision Parameters in the Dashboards Using Form Controls
Also, Checkout our Excel Dashboards Page for more examples and resources.
14 Responses to “Group Smaller Slices in Pie Charts to Improve Readability”
I think the virtue of pie charts is precisely that they are difficult to decode. In many contexts, you have to release information but you don't want the relationship between values to jump at your reader. That's when pie charts are most useful.
[...] link Leave a Reply [...]
Chandoo,
millions of ants cannot be mistaken.....There should be a reason why everybody continues using Pie charts, despite what gurus like you or Jon and others say.
one reason could be because we are just used to, so that's what we need to change, the "comfort zone"...
i absolutely agree, since I've been "converted", I just find out that bar charts are clearer, and nicer to the view...
Regards,
Martin
[...] says we can Group Smaller Slices in Pie Charts to Improve Readability. Such a pie has too many labels to fit into a tight space, so you need ro move the labels around [...]
Chandoo -
You ask "Can I use an alternative to pie chart?"
I answer in You Say “Pie”, I Say “Bar”.
This visualization was created because it was easy to print before computers. In this day and age, it should not exist.
I think the 100% Bar Chart is just as useless/unreadable as Pies - we should rename them something like Mama's Strudel Charts - how big a slice would you like, Dear?
My money's with Jon on this topic.
The primary function of any pie chart with more than 2 or 3 data points is to obfuscate. But maybe that is the main purpose, as @Jerome suggests...
@Jerome.. Good point. Also sometimes, there is just no relationship at all.
@Martin... Organized religion is finding it tough to get converts even after 2000+ years of struggle. Jon, Stephen, countless others (and me) are a small army, it would take atleast 5000 more years before pie charts vanish... patience and good to have you here 🙂
@Jon .. very well done sir, very well done.
good points every one...
I've got to throw my vote into Jon's camp (which is also Stephen Few's camp) -- bars just tend to work better. One observation about when we say "what people are used to." There are two distinct groups here (depending on the situation, a person can fall in either one): the person who *creates* the chart and the person who *consumes* the chart. Granted, the consumers are "used to" pie charts. But, it's not like a bar chart is something they would struggle to understand or that would require explanation (like sparklines and bullet graphs). Chart consumers are "used to" consuming whatever is put in front of them. Chart creators, on the other hand, may be "used to" creating pie charts, but that isn't an excuse for them to continue to do so -- many people are used to driving without a seatbelt, leaving lights on in their house needlessly, and forwarding not-all-that-funny anecdotes via email. That doesn't mean the practice shouldn't be discouraged!
[...] example that Chandoo used recently is counting uses of words. Clearly, there are other meanings of “bar” (take bar mitzvah or bar none, for [...]
[…] Grouping smaller slices in pie chart […]
Good article. Is it possible to do that with line charts?
Hi,
Is this available in excel 2013?