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.
83 Responses to “Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]”
[...] Original Chandoo [...]
I'd be happy to see this post EVERY week!
Part 4 didn't disappoint.
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...
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!
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?
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.
[...] Add Microcharts to Dashboards [...]
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 !!
[...] Post 4 - Excel Dashboards for Data Visualization [...]
[...] 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 [...]
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!
[...] Part 4: Add Microcharts to Management Dashboards [...]
[...] Part 4: Add Microcharts to Management Dashboards [...]
[...] 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 [...]
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
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.
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
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!
@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?
@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?
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.
oops typo on the number one..I meant to say Millions.
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
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?
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.
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.
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?
Ok well they are there...just showing so small they cannot be seen with the naked eye on extra zoom.
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.
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!
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/
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.
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?
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?
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)
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...
I get it...ok so where is "All" being pulled from?
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?
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.
=IF($K9,INDEX($D8,$I9,0),"")
^ My forumula modified for only one category.....correct?
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?
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).
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?
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).
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)?
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!
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.
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?
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....
Ok well based on this:
=IF(OR($C8>Calculation!$S$11,mySortCriteria=H$6),”",
It appears that ID is required on the dashboard?
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.
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.
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?
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!
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.
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!
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.
Thanks Robert!
It was so obvious!! Bt, somtime an outsider's view only makes is so.
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.
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?
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
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.
Tks for sharing.. I'll just try to explore it...
Thank you so much for sharing your knowledge.
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!
[…] Executive Dashboards – Excel Training […]
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.
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!
Dear team,
How to create the chart that still the grid line can be seen when I adjust it into the table? Thanks.
Never mind my question, I already found out it. Thank you for such an useful article. All the best!
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?
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