+ Reply to Thread
Results 1 to 37 of 37

Highlight min & max column on bar chart

  1. #1
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Highlight min & max column on bar chart

    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

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    The same colour, or a different one for each?
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    Different for each if possible, I've started a new thread on the VBA forum

    http://www.excelforum.com/excel-prog...ml#post3868089

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    Here's a non-code method.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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?

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    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.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    Glad to help.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    Yep, that's much better.

  11. #11
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    I can't get this DATA bit to work? I can't use it as a legend?

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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

  13. #13
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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.

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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)

  15. #15
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    Done that (except mine is Graphs B10), how do I add that to the chart?

  16. #16
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar 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

  17. #17
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    I'm using Excel 2010 and can't see a chart title anywhere like on your link.

  18. #18
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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.

  19. #19
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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!

  20. #20
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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.

  21. #21
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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

  22. #22
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    Try updating you original example file and post that so we can see what you are actually doing.

  23. #23
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    When I select your first series it just says ='1043575.xlsx'!DATA in the formula
    Is that the formula bar of the worksheet, in which case I don't know how you managed that.
    Or is that the formula bar of the Select Data dialog?

  24. #24
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    It thinks DATA is a sheet

  25. #25
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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.

  26. #26
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    I've attached my file

    I really don't understand this hahah

    Graphs.xlsx.xlsm

  27. #27
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    I have the same open, tell me what I'm doing wrong / what I should be doing and I would unbelievably grateful


    Do you have a worksheet tab named data in you workbook?
    No I don't
    Last edited by PDBartlett; 10-16-2014 at 11:16 AM.

  28. #28
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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%
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

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

  30. #30
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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?

  31. #31
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Highlight min & max column on bar chart

    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.

  32. #32
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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?

  33. #33
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    =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?

  34. #34
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

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

  35. #35
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    Try using =Graphdata!DATA
    It will probably be replaced with the name of your workbook followed by !DATA

  36. #36
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Highlight min & max column on bar chart

    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.

  37. #37
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Highlight min & max column on bar chart

    Me too - I've decided to win the Euromillions and retire.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-19-2012, 12:43 PM
  2. Conditionally highlight bar chart with UCL/LCL
    By jeffreybrown in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-03-2010, 07:40 AM
  3. VBA to Highlight Bar on Chart
    By hassankhan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2010, 06:07 AM
  4. Macro or VBA code to highlight specific named columns in a column chart
    By glenp99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-03-2010, 05:12 AM
  5. highlight areas of chart?
    By Stan Altshuller in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-14-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1