SubscribeExcel KPI Dashboards – Adding Micro Charts [Part 4 of 6]
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:
Part 1: Creating a Scrollable List View in The Dashboard
Part 2: Add Ability to Sort on Any KPI to the Management Dashboard
Part 3: Highlight Values Based on Percentile
Part 4: Add Microcharts to Management Dashboards
Part 5: Compare 2 Decision Parameters in the Dashboards Using Form Controls
Part 6: Show the Distribution of a Parameter using Box Plots
The Challenge – Adding Visualization to the Management Dashboard
In this final post on management 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 Key Performance Indicator 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
Trackbacks & Pingbacks
- Pingback by topwebbusinesses » Blog Archive » Excel KPI Dashboards - Adding Micro Charts [Part 4 of 4] on September 10, 2008 @ 11:27 am
- Pingback by How to create KPI (Key Performance Indicator) dashboards in Microsoft Excel? [Part 1 of 4] | Pointy Haired Dilbert - Chandoo.org on October 6, 2008 @ 10:40 pm
- Pingback by Excel Dashboard Tutorials | Dashboards By Example on October 16, 2008 @ 1:49 am
- Pingback by Adding Box Plots to Show Data Distribution [Excel Dashboard Tutorials Part 2] | Pointy Haired Dilbert - Chandoo.org on October 29, 2008 @ 10:14 pm
- Pingback by Management Dashboards in Microsoft Excel - Highlighting Values based on Percentiles [Part 3 of 6] | Excel Tips | Pointy Haired Dilbert - Chandoo.org on February 28, 2009 @ 6:07 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
I’d be happy to see this post EVERY week!
Part 4 didn’t disappoint.
What a wonderful series. I hope it continues. I have had fun adding the targets to the bar chart as we are often aiming for the target and want to see how we compare to it.
Thanks. I look forward to learning so much more!
David
Simply fantastic series….makes a really professional dashboard tool…
@James, David, prem mathew:
Thank you very much for your kind and encouraging comments! I greatly appreciate your feedback!
One further comment:
I sent a sneak preview of this post and the excel file to Fabrice Rimlinger (http://sparklines-excel.blogspot.com/) before Chandoo was publishing the post and the file.
Fabrice was kind enough to have a very close look on the file. He did some testing and pointed us to a little bug Chandoo and I haven’t seen.
Thanks to Fabrice’s feedback we were able to fix that issue before publishing and to deliver a clean and working solution to the readers of PHD.
Fabrice: thank you so much for your precious time and your extremely valuable inputs!
Merci beaucoup!
Wicked !
Simple and elegant… I love it.
@ Chandoo & Robert : Terrific association, keep the good post coming.
Do not hesitate to ask me for early debugging or pre-release “constructive” critics… Allways a pleasure.
Salut !
I`ll keep you posted on Sparklines for XL upgrades.
Yes, this series was great! I would like to see more.
Great stuff Roger!! Would love to see more of your work too.
(I for one am not afraid to leverage the excellence of others)
I might have missed something – Do you have a way returning to the original sort order?
Adding a sixth radio button, making the sort KPI calc reference column J in the data sheet does the trick.
Leaving column J blank works fine – I added a reference to the ‘No.’ field to keep it clean.
Justin,
thank you very much!
No, you did not miss a thing. The version posted for download has no way to return to the original sort order (i.e. the order of the items on the data sheet). What you described in your comment is a simple and clever way of doing this.
Even better and more sophisticated would be an option button to sort the table by product names (texts in column D). The sorting algorithm described in post #2, however, is not working with texts.
Sorting texts is more complicated. As far as I know, there is no way to do this without array formulae. John Walkenbach offers an array-formula solution on Daily Dose of Excel:
http://www.dailydoseofexcel.com/archives/2006/10/11/dynamic-text-sorting/
See also Vivian’s comments on the second post of the series.
Hi Chandoo,
I have a list of colors that I would like to assign to each of the 56 color index in Excel…can that be done through vba?
Thanks
@SpeedBall
you do not need VBA to change the color palette. You can modify the color palette using the menu Tools/Options/Color Tab, select a color and use the button Modify to assign the color you want. But you would have to do this one color after the other…
If you don’t want to do this manually you could use VBA code as well, e.g.
ActiveWorkbook.Colors(1) = RGB(255, 0, 0)
etc.
Hey,
……..ur work left me spellbound…….gr8 job…this kind of work should be a great thing for excel crazy like me. Keep on the great job !!
WOW!
Hi all! Most of this is new to me, so I apologize if this is elementary: My XY scatter line for average (or Min/MaxXY) doesn’t stretch all the way across my graph as in the sample file. What have I done wrong? (or not yet done)
Brian,
thanks for your comment and question.
You have to manually set the axis scale of the secondary vertical axis (minumum = 1, maximum = 10), otherwise the autoscale will set the minimum to 0 and the maximum to 11 and the lines will not stretch all the way.
Brian,
I forgot: there is no need to apologize. I guess you are one of the few who are rebuilding the dashboard from scratch. I think this is the best way to learn these techniques by heart and get the most out of it, even if it is time-consuming and maybe even exhausting sometimes.
I would like to encourage you to keep on doing this. Whenever you have questions on the KPI dashboard series or the dashboard revisited posts, please do not hesitate to come back. I am happy to help wherever I can.
thank you so much!! this tutorial helps a lot!