fbpx
Search
Close this search box.

Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

Share

Facebook
Twitter
LinkedIn

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

dashboard-key performance indicator -excel-with-graphs

[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).

  1. 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).
  2. Insert 5 conditional formatted bar charts. Read Jon’s method to create a conditional formatted chart.excel-dashboard-graphs-howto

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

  3. 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”.
  4. 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.
  5. 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.

  6. Finally update the caption beneath the table to explain the meaning of the line and the bar colors.

What is next?

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.

Learn How to make Excel Dashboards - Join Excel School

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

83 Responses to “Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]”

  1. James says:

    I'd be happy to see this post EVERY week!
    Part 4 didn't disappoint.

    • Chandan Mishra says:

      Conditional Formatting of Excel Charts....explains about each column having different color. I have improved my excel competences just EeEVERYDAY I am investing 1-2 hours on your website....this is excellent so far I have seen in internet.

      in Excel 2010...right click on chart and go to Format data series > Fill > Vary color by each point...gives same result...

  2. David says:

    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

  3. prem mathew says:

    Simply fantastic series....makes a really professional dashboard tool...

  4. Robert says:

    @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!

    • Robert says:

      Hello! Congrats for this excellent post! beautiful and useful. Thanks!
      Just a little question related to the file for downloading here: there's a 5 on cells G6, J6, M6, P6 & S6, what is it for?/where does it come from?

  5. Fabrice says:

    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.

  6. DPizz says:

    Yes, this series was great! I would like to see more.

  7. Justin says:

    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.

  8. Robert says:

    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.

  9. SpeedBall says:

    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

  10. Robert says:

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

  11. Deep says:

    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 !!

  12. [...] Post 4 - Excel Dashboards for Data Visualization [...]

  13. [...] by a selected KPI with one click, to identify the best and poorest performers with quartiles and to visualize the data, the averages and the targets with bar-line-combination charts. Also we have learned how to improve this dashboard by adding an interactive series comparison [...]

  14. Brian says:

    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)

  15. Robert says:

    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.

  16. Robert says:

    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.

  17. smoe says:

    thank you so much!! this tutorial helps a lot!

  18. [...] 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 [...]

  19. Linda Campbell says:

    Chandoo, Robert,

    Love this site. A quick question for "Dummies". My X Axis Maximum scale is way to big (600). I can manually change this, but what if the data changes and this is no longer appropriate, is there a way to link the max scale to the maximum value of the data (or do you have any other suggestions?)

    Thanks,
    Linda

  20. Robert says:

    Linda,

    in the workbook posted for download, all charts have additional dummy series (with maximum values, formatted to be invisible). This ensures to scale the axis correctly and keep the scale during scrolling up and down. Maybe you want to have a closer look on the data source of the charts in the workbook posted for download.

    I hope this will be helpful.

    • manish agarwal says:

      Hi,

      To address the issue of scale that keeps changing when we scroll:

      I double clicked on the axis to show up the property dialog box
      unchecked all the options from the scale window for Min, max, etc and set them to min = 0, max = 600, major unit = 100, minor unit = 20

      Then I scrolled, and chart worked fine..

      Please let me know if this is a permanent solution or it might change to auto mode by itself

  21. Karthiganesh says:

    Dear Chandoo,

    I never thought that DashBoards in EXCEL will be this much powerful and simple. I am addicted to your Chandoo.org

    When I read the 'awesome' frequently, i thought you are joking. At end of the each post, I can't stop exclaiming "Awesome!"

    Thanks a lot Chandoo.

    I will keep reading and sharing.

    Cheers!

  22. Mike says:

    @Robert - You still around?

    I am working on building this from scratch....wow, crazy - head holding at times - but thank you thank you!. I am plugging away at the fourth post! I thought I had this but apparently not....

    First I have a question. One of my KPIs MAX is in the missions while the MINs are lower than 100....any suggestions on things I may encounter and should do in these cases?

    My question has to do with the second paragraph of your 5th step....add a scatter chart? And an additional bar series? I copied the bar chart from your workbook to mine and modified and it looks like it worked although its the KPI that has MAXs in the millions.

    Any thoughts?

  23. Mike says:

    @Robert - Some more questions:

    Your MIN and AVERAGE formulas I had to modify to not count blanks and zeros...do you think that would be a problem and mess up anything else (other formulas)? My first guess was that was why the microcharts are not acting right but the change didn't seem to affect much at all.

    Also - How would offset match up to index being used in this scenario in regards to performance?

  24. Robert says:

    Mike,

    here are my 2 cents on your questions:

    1. "One of my KPIs MAX is in the missions while the MINs are lower than 100"

    I am sorry, but I think do not understand this. Do you mean you have one KPI with 100 data rows and another one with less than 100? If so, you still have 100 products (or whatever your category is), don't you? If the category is there and has a KPI 1, it has per definition a KPI 2 too, even if the value is 0. I suspect I simply do not understand your question. Could you please clarify your problem?

    2. "I copied the bar chart from your workbook to mine and modified and it looks like it worked …"

    If you copied the charts directly from the workbook provided for download, you do not have to add anything, since the additional dummy series to align the scaling of the axes are already included. Have a look at the data source of one of the charts and on all the data series (real data series and dummy data series) that are used.

    3. "Your MIN and AVERAGE formulas I had to modify to not count blanks and zeros…do you think that would be a problem and mess up anything else (other formulas)?"

    There shouldn't be a problem if you changed the formulas. The charts are displaying exactly what is in the ranges on [Calculations] used as the source of the data series. If your formulas return the correct values, the charts should be ok.

    4. "How would offset match up to index being used in this scenario in regards to performance?"

    You are right: INDEX is faster than OFFSET, so especially in larger workbooks I would recommend using INDEX instead of OFFSET. However this wouldn't make a big difference in terms of performance in the small workbook posted for download. Chandoo and I decided to go with OFFSET, because we wanted to not only show the dashboards, but also the use of OFFSET with these articles.

    I hope this will be helpful.

  25. Mike says:

    oops typo on the number one..I meant to say Millions.

  26. Mike says:

    Ok read all of your responses...thank you very much for getting back to me, on Sunday evening at that. In regards to OFFSET vs INDEX...I am dealing with a good deal of data (2000 rows with 10 KPIs). 2000 rows is just for test data and will go much higher (~100,000 rows).

    Any limitations in 2007 I should be aware of in regards to building tables with similar functionality? I am aware that it may be slow but for now it will have to do...I will be moving to sharepoint soon using what I am doing now in excel to help the developers understand exactly what we need.

    Thanks again!

    Mike

  27. Mike says:

    As far as copying the charts from your sheet to mine...

    I figured the same (not having to do much) after the first KPI. Then the second..I can only see a fraction of the first two and the rest (bars) are so small you cannot even see them. Frustrating because I came to a halt and thought I may just get through the fifth post tonight...

    Last but not least...if I move to INDEX is there anything major other than obvious that I need to change with the formula in your example workbook?

  28. Robert says:

    Mike,

    if your data range is from less than 100 to than several millions, well, what can I say? It is the way your data is. There are 2 general options:

    1. Live with it. You will not even see the bars for the very small values, but this is just the way your data is and the sort option will help your users to easily switch sort order.

    2. Take out the dummy series for the minimum and maximum bars from the bar charts. This way, the scaling will only refer to the 10 bars displayed on the dashboard. However, scrolling up and down will make the "bars jump up and down" and the scaling of the bars is not intuitive anymore. I would not recommend this. I think it is confusing for the user.

    Secondly, if you are have 100,000 rows or more, it will pay off to switch to INDEX. I am not sure that your workbook will be of reasonable performance anymore (especially with the sorting options), but INDEX will at least be faster than OFFSET. Limitations in 2007? I do not think so, but you will have to expect a pretty bad performance i.e. high recalculation times.

    I do not think there is much to consider when switching from OFFSET to INDEX. Simply replace the function and refer to the correct parameters. That's all.

  29. Mike says:

    Thanks again Richard...Ok so one last question (that may lead to another):

    I understand what you are saying about my KPI with hundreds to millions...the problem is though that chart works fine with the expected small bars when you scroll down. Ok well thats not the problem...the problem is my KPI2 which has a min of .18 and max of 5.00. I would expect the same except I see nothing...only a fraction of the first two.

    I duplicated the chart that works and made the necessary changes for KPI2 and tripled checked...no bars no where. Is the difference between 100 and 6,000,000 not larger than .18 and 5.00!?

    This is where I am lost and refuse to proceed further until figuring out whats going on.

  30. Mike says:

    Its weird...in KPI2 if I right click and hit Select Data it pops up and in the list of conditions everything appears to be set up correctly (quadruple checked)...minbar, maxbar, etc. all in there and set up properly. However unlike my chart for KPI2 I can not click on the "invisible" bars...they along with the other bars appear not to be there...all expect the top two for some reason.

    Ok when..super zooming it is clear that MaxBar is not in the chart but like I said above...when right clicking and selecting data everything is there? What am I missing?

  31. Mike says:

    Ok well they are there...just showing so small they cannot be seen with the naked eye on extra zoom.

  32. Robert says:

    Mike,

    go to the format dialog of the horizontal axes and make sure that there are no fixed settings: maximum, minimum, major units and minor units have all to be set to "Auto". Let me know if it is still not working.

  33. Mike says:

    Robert - Thank you! That was the problem...cant believe how long I went through things, glad I did though.

    How would I go about adding a drop down to this list in order to filter it by product category? Planning for it I already have it on my Data and Calculations sheets...just not on my dashboard.

    I understand sorting text is more complex...is it possible to integrate a text sort in product name and product category (if I get it on the dashboard)?

    Moving on to the next...

    Thanks again!

  34. Robert says:

    Mike,

    for sorting texts, you are already on the right blog. Have a look at Chandoo's great article here:

    http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

  35. Robert says:

    Mike,

    with regards to your product / product category selection challenge. Have a look here:

    http://www.box.net/shared/ffhf06d32f

    Not exactly what you are asking for, but it should point you into the right direction.

  36. Michael says:

    Sweet...I see how this is accomplished. Only problem...I have well over 100 product categories and I am concerned about the arrangement of the categories Data!D9:D13 Where else could I put that list safely?

  37. Robert says:

    Michael,

    you have different options for creating the list of categories as the input range of the drop down:

    1. If you don't want to have the list of categories on the data sheet, you can put the list to the sheet calculation or to any other sheet.

    2. You can create a named formula returning the list of categories (unique entries) and use this name as the input range of the drop down.

    3. You can write a small piece of VBA to create a list (array) of the unique categories, assign the resulting array values to a name (array) and use this name as the input of the drop down.

    Does this answer your question?

  38. Michael says:

    Thanks again Robert...

    So in your example, what is the 5 referring to in E9=5?

    =IF($E$9=5,TRUE,E14=$F$9)*IF($E$10=5,TRUE,$F$10=F14)*IF($E$11=5,TRUE,$F$11=G14)

  39. Robert says:

    Michael,

    the hardcoded 5 in this formula is checking whether the user selected "All" in the dropdown list (e.g. 4 Sales Regions, the 5th entry of the drop down is "All").

    I admit it definitely isn't best practice to use hardcoded numbers in formulas. You should replace this with a link to a cell and calculate the number of the categories in this cell.

    However, in my defence: this example wasn't part of an article here (I am sure with this implementation I would have never passed Chandoo's quality assurance....). I jotted down this workbook to answer one question in an earlier comment and reused the workbook to answer yours. There is a lot of space for improvement in the workbook...

  40. Michael says:

    I get it...ok so where is "All" being pulled from?

  41. Robert says:

    Micheal,

    you have the three combo boxes (drop downs) on the dashboard. E.g. the drop down for the sales region on the dashboard uses the Data!D9:D13 as the input range (West, North, South, East, All) and Calculation!E9 as the target cell. If the user clicks on "West", Calculation!E9 will be set to 1. If the user selects "All", Calculation!D9 will be set to 5. The formulas in column H of Calculation ckeck which data rows are relevant i.e. which data rows fit the filtering the user selected (as a combination of all 3 dimensions). If the user selects "All" in all drop downs, all rows will be included. If he selects "West" as the sales region and "All" for the other 2 dimensions, only the data rows with sales region "West" will be relevant (i.e. have a 1 in column H of Calculation). Data from North, East and South will have a 0.

    Does this help or am I clear as mud?

  42. Michael says:

    Ok ok..grinding through this. I will post back after more time spent. Until then do you have any idea why I am getting TRUE/FALSE instead of 0 or 1's in the relevant columns?

    Also, I am going to be doing everything that I can to make this in a template so the whole hardcoded thing scares the crap out of me. Any thoughts here would be helpful no doubt.

    I will be continuing to go through this regardless if its hard coded but any help on how I can modify it in order to get me where I am trying to get faster would be most helpful.

  43. Michael says:

    =IF($K9,INDEX($D8,$I9,0),"")

    ^ My forumula modified for only one category.....correct?

  44. Robert says:

    Michael,

    1. You are getting TRUE/FALSE because you are having only one IF-clause. The workbook posted for download has 3 dimensions / filters and thus there are 3 IF-clauses and a multiplication of the 3 results in the formula: TRUE*TRUE*TRUE is 1; if one or more of the IF-clauses return FALSE, the multiplication returns a 0.

    2. If you want to make this a generic template, you need to do 2 things:

    a. create a list with unique entries from the category column of your raw data. You will find at least one article on this challenge here on Chandoo's blog. Otherwise Google will help you to find different ways of creating lists with unique entries. If you want to have an "All" option, you need to add the entry "All" to this list.

    b. Calculate the number of entries of the list created above in an additional cell and refer to this cell in your formulas calculating the relevant values.

    3. =IF($K9,INDEX($D8,$I9,0),”")

    Which formula / cell are you referring to?

    No matter what you are trying to calculate, it doesn't look right at first sight, because the first parameter of INDEX would usually be a matrix, not one single cell reference. Your formula would only work if I9 is 1 and and it would not do much more than a simple direct link to D8 (=D8). Maybe you can elaborate a bit what column in the original workbook you are referring to?

  45. Michael says:

    Ok well thats my problem...you basically have 3 categories in your example where I am only dealing with 1.

    I will be looking into everything else. As far as your last question...I think you answered that in your first comment (that formula is from the relevant column).

  46. Michael says:

    adding *1 seems to have fixed the FALSE/TRUE problem when having a single category. Formula looks like this: =IF($D$6=53,TRUE,D9=$E$6)*1

    Thoughts?

  47. Robert says:

    Michael,

    looks good from my point of view (yes, TRUE*1 is 1 FALSE*1 is 0, because TRUE is treated a 1 in calculations and FALSE is treated as 0), but you do not have to do the multiplication by 1.

    The column "relevant" is only used in the IF-condition of column "Unique". Use IF(H14,....) instead of IF(H14=1,...) and you are cleaner and faster (you won't notice the performance inporvement, though).

  48. Michael says:

    Well Robert...I got it! Thank you!!! Only question before moving on, for empty cells is there anyway to build in something to remove the Green and Red plus an minus (and arrows)?

  49. Michael says:

    Robert - So say I want to now take Product out of the picture and show the averages and sums (in some cases) for KPI 1 to 5 for all product categories?

    I am sure you can imagine I will be trying until I hear back from you...

    Thanks a million!

  50. Robert says:

    Michael,

    here is one way to suppress the display of the plus/minus and the arrows in empty rows:

    1. Calculate the number of data rows that fulfill your filter criteria, e.g. count the number of values in column e.g. S of the sheet calculation. For instance, type in COUNT(S14:S113) in cell S11 of sheet calculation. The result is the number of data sets that are included in your filter.

    2. On the dashboard, change the formula in the columns with the arrows to check the maximum number of entries calculated in step 1. E.g. in cell I8 on dashboard, change the existing formula to: =IF(OR($C8>Calculation!$S$11,mySortCriteria=H$6),"", [… rest of the formula stays unchanged…])

    3. Copy down this formula and copy the formulas to the according columns of each KPI

    This should do the job.

    If you want to display sums and averages of product categories, I would simply use the same technique used for products (i.e. all the formulas doing the sorting, filtering, chart preparation, etc.) but consolidate the raw data first by using SUMIFs or array formulas.

  51. Michael says:

    Ok cool...I will be working on this tonight. Consolidating the data using SUMIFs or arrays...yeah going to be required for this project. Where exactly would the SUMIFs be used...on the dashboard only?

  52. Robert says:

    Michael,

    I recommend seperating the calculations from the display. I am always trying to have the display on the dashboards via direct cell references to the calculation sheet. But this is my personal way of structuring workbooks....

  53. Michael says:

    Ok well based on this:

    =IF(OR($C8>Calculation!$S$11,mySortCriteria=H$6),”",

    It appears that ID is required on the dashboard?

  54. Robert says:

    Michael,

    no it is not required, but it happens to be there already, thus I used it in my suggestions. You may put this column to [Calculation] too.

  55. Eric says:

    Hi Guys,

    I need some help with this step, please

    I've been cracking my head open over this to no avail. I am still unable to create the bar charts

    Where can I find more explanations/instructions or can somebody point me to a tutorial for these bar charts.

    Thanks a lot for any help provided.

  56. Robert says:

    Eric,

    I don' know which roadblock you are hitting with the bar charts, but here are some details:

    The bars have 2 data series for the bars (black and red), one for the average line (which in fact is no line but an XY scatter) and 4 invisible dummy series (Min and Max for the bars and the line) to ensure the correct scaling of the axis during scrolling.

    The data source of the bar chart is calculated on the sheet calculation in columns Q through AQ. The formulas there aren't too complicated. MIN, MAX, AVERAGE, IF-clauses and NV(). I think you can easily figure out how this is working.

    The rest is

    - creating the bar charts
    - adding the bar dummies
    - adding the average data series
    - changing the average data series to an XY scatter and format it
    - add the dummy series MinXY and MaxXY and make them XY scatters too
    - format the dummy series with no line, no fill, etc. to make them invisible.

    Does this answer your question?

  57. Eric says:

    Hi Robert,

    Thanks for your prompt reply and explanation.
    I finally got it working!

    One last question, how do you add the bar dummies?
    the only difference between my charts and those from the tutorial are these two bar sets.

    Is there another way?

    Thanks!

  58. Robert says:

    Eric,

    I am not sure that I understand your question: there is no difference between adding the "normal" data series and the dummy series. It is just the standard way of adding a data series to a chart. The only difference is the fact that you have to format the dummy bars with no fill color and no border line to make them invisible.

    I hope this helps.

  59. Ajay K Y says:

    Dear Moonly creatures ("Chand" translates to "Moon" in english!)
    First of all, thanks for maintaining such a wonderful blog.
    Ever since (2 days ago) I found it, I have been hooked to it.
    I am facing problem in conditional bar-line chart as described on the downloaded excel.
    I have created very simple version to just this feature. But, there seems to be problem, as you will observe, in how the graphs are displaying (not matching to the values).
    I am sharing the file for your understanding. https://docs.google.com/open?id=0BwNI92zFX-_-MTI0YWNjMDAtNjQ0OC00MTJiLWExNGQtMmVmN2Q2YjZjYzIy
    Looking for your reply.
    Thank you!

    • Robert says:

      Ajay K Y,

      what I can see from the image you posted on Google docs, I would assume you have to format the axis of the bar chart to display the categories in reversed order and you should be alright.

      Let me know if it is still not working.

  60. Lawrence says:

    Hei guys,

    Liked the KPI dashboards so far. It is pretty "awesome" and spot on applicable to business.

    A small question though.

    I got the charts right, however, the minimum values are extending to the left of the minimum value trendline. Any ideas?

    Disclaimer: I am an intermediate excel user and absolute noob in excel graphing.

    • Robert says:

      Lawrence,

      sorry, I don't understand your question.

      What do you mean by "trendline"? The average line? And what does "extend the minimum values" mean?

      Can you elaborate a bit on your problem, or even better post your example workbook somewhere for download?

  61. Ashley says:

    Hi,
     I would like to change the color scheme of the charts from light gray, dark gray, and red, to yellow, green and red. I am able to do this manually by selecting each bar individually; however I would like to have this be a formula/function, where the color of the bar changes automatically.
     
    Is this possible?
     
    Thanks!
    Ashley

    • Robert says:

       
      Ashley,
       
      I just discovered your comment, so I apologize for the late reply.
       
      You can't change the colors of already existing charts with worksheet formulas / functions. You need VBA code / macros to automate the process. Even if you don't know any VBA, you can still record a macro: select one chart, start the macro recorder, format the chart the way you want to and stop the macro recorder. You can then select the next chart and run the recorded macro, and so forth.
       

  62. Just Legz says:

    Tks for sharing.. I'll just try to explore it...

  63. Lolly says:

    Thank you so much for sharing your knowledge.

  64. Rob says:

    Hello there! can someone tell me what is the # 5 on cells G6, J6, M6, P6 & S6 ("dashboard" sheet from the downloading file) for? or where does it come from?
    Thanks & congrats for this excellent post!

  65. […] Executive Dashboards – Excel Training […]

  66. Brendan says:

    I do believe all the concepts you've offered to your post.
    They're very convincing and will certainly work.
    Nonetheless, the posts are very brief for novices.

    Could you please extend them a little from next time?
    Thank you for the post.

  67. Investawise says:

    Terrific post however , I was wanting to know if you could
    write a litte more on this subject? I'd be very thankful if
    you could elaborate a little bit more. Kudos!

  68. hien says:

    Dear team,

    How to create the chart that still the grid line can be seen when I adjust it into the table? Thanks.

    • hien says:

      Never mind my question, I already found out it. Thank you for such an useful article. All the best!

  69. Manny says:

    Excellent site and info!

    I am recreating the spredsheet from scratch. when adding a new series (the average) into xy plot, the label appears opposite side it should. Like at the side instead of the the top. I am using Excel Office 365. Any insight?

  70. Manny says:

    Again, excellent info. I found a workaround. Instead of converting the average into an xy plot, I colored it with a white border. Now the graph shows a whit line instead of a black one. But if someone has an answer to my original question, I would appreciate it. Thanks

Leave a Reply