Hello
The title says it all really, I would like to highlight the minimum and maximum values in a chart.
See attached
Thanks
Graphs.xlsx
Hello
The title says it all really, I would like to highlight the minimum and maximum values in a chart.
See attached
Thanks
Graphs.xlsx
The same colour, or a different one for each?
Remember what the dormouse said
Feed your head
Different for each if possible, I've started a new thread on the VBA forum
http://www.excelforum.com/excel-prog...ml#post3868089
Here's a non-code method.
Thank you so much Rory
At the moment, everything is fixed to 'numbers' meaning the percentages are being shown as what it should be divided by 100. Is there a way to change the format based on what KPI is selected? Or would it be easier to group the numbers and percentages into different charts?
You could use a flag for each row adjust the numbers by 100 for percentages, then have an axis title that shows "Percentage" when appropriate? See attached demo.
Works perfectly! This is huge for me as I really don't have a clue about charts, but these little 'cheats' are amazing.
Thank you
Glad to help.
If you use a named range for the source data of the 1st series then theformatting of the max/min series will match, allowing for switching between numbers and percents
Yep, that's much better.
I can't get this DATA bit to work? I can't use it as a legend?
Can't get it to work in what way?
Do you mean chart title rather than legend? If so add chart title and link it to B11
You've got this 'thing' on your attachment called DATA, how do I implement that into my spreadsheet?
The only legends I have on my chart now are min and max are working the way you've done it.
Data is an Named range.
Use Name Manager on the Formulas tab.
DATA: =OFFSET(GraphData!$B$1:$F$1,MATCH(Graphs!$B$11,GraphData!$A$2:$A$22,0),0)
Done that (except mine is Graphs B10), how do I add that to the chart?
select the chart title.
in the formula bar type =
then click the cell you want to link to and press enter
http://www.andypope.info/tips/tip001.htm
I'm using Excel 2010 and can't see a chart title anywhere like on your link.
The important part of that page is the final picture of the formula bar.
If your real chart does not have a title you can add that via the Chart Tools > Layout > Chart Title > Above Chart, button.
Right, got the title working, but on the chart I only have the min and max bar. There's nothing telling it look up the values of the drop down (I've entered DATA on the Name Manager).
What am I missing hahah? I'm useless at charts!
If you look at my example you will see the chart contains 3 dataa series.
The first uses the named range to locate in selected data on GraphData sheet. When you select that series it has the series formula,
=SERIES(Graphs!$B$11,GraphData!$B$1:$F$1,'1043575.xlsx'!DATA,1)
The other 2 series, for Min and Max are linked directly to the ranges on graphdata sheet.
Max Series =SERIES(GraphData!$A$29,,GraphData!$B$29:$F$29,2)
Min Series =SERIES(GraphData!$A$30,,GraphData!$B$30:$F$30,3)
The formula in those two ranges output values when matched with Max/Min value and #N/A for all other data points.
I can't get the first series to work. My min and max are fine, I don't know how to explain it!
When I select your first series it just says ='1043575.xlsx'!DATA in the formula
Try updating you original example file and post that so we can see what you are actually doing.
Is that the formula bar of the worksheet, in which case I don't know how you managed that.When I select your first series it just says ='1043575.xlsx'!DATA in the formula
Or is that the formula bar of the Select Data dialog?
It thinks DATA is a sheet
Do you have a worksheet tab named data in you workbook?
If so change then Named formula to something else, and then update the series formula.
I've attached my file
I really don't understand this hahah
Graphs.xlsx.xlsm
I have the same open, tell me what I'm doing wrong / what I should be doing and I would unbelievably grateful
No I don'tDo you have a worksheet tab named data in you workbook?
Last edited by PDBartlett; 10-16-2014 at 11:16 AM.
The attached contains screen shots of the Select data dialog for modifying the series formula.
Your chart type is stacked column when it should be column cluster.
You then need to format the series so the overlap is 100%
I've changed it column cluster, but I'm still stuck with the first series, it's just not accepting anything I put into "Series values:"
What I do:
Click on the chart, under chart tools click design, click select data, click add, series name = Graphs B10, series values doesn't accept anything??
If I copy "=Graphs.xlsx.xlsm!DATA" from your workbook into my series values.... it works (because it's linking to your workbook). So what do I enter here?
Okay so modify that to reference your workbook. If it does not work that would suggest the named range DATA either does not exist in your workbook or is not defined correctly.
I change it and it comes up with an error, I still don't understand what you're physically doing to enter anything into the series values box?
What are you typing?
=OFFSET(GraphData!$B$1:$F$1,MATCH(Graphs!$B$10,GraphData!$A$2:$A$22,0),0)
That's my DATA, what do I do with that?
I've completely given up.
I have used the Name Manager, called it DATA which equals this:
=OFFSET(GraphData!$B$1:$F$1,MATCH(Graphs!$B$10,GraphData!$A$2:$A$22,0),0)
That is exactly the same as your Name Manager. You haven't explained how I use that in my chart. Like I said above, I click on the chart, under chart tools I click design, then I click select data, click add, series name: =Graphs!$B$10, and in series values I enter =DATA.
I type in =DATA because it looks like you've done just that, but it doesn't work in mine. So what have I done wrong, I'm clearly missing a step.........
Try using =Graphdata!DATA
It will probably be replaced with the name of your workbook followed by !DATA
Rory.
I don't think you understand how much stress you have relieved me from, that's all I was missing.... "=GraphData!DATA" instead of "=DATA".
Absolute life saver, I can sleep well tonight.
Me too - I've decided to win the Euromillions and retire.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks