Results 1 to 7 of 7

Thread: Clustered column chart - highlight specific column if condition met

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,296
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Clustered column chart - highlight specific column if condition met

    Hi,

    I have:

    A time line from A1:A10 (months)
    Revenue data: B1:B10 (+ve values)
    Cost of Revenue: C1:C10 (+ve values)
    Difference: D1:D10 with formula =ABS(C1-B1)<1000

    I currently use A1:C10 to show a clustered column table comparing Revenue vs Cost of Revenue over time

    I've been asked to highlight specific month(s) on the chart where D1:D10 = TRUE

    How can I do this please?

    TIA,
    Jack


  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    Hi JackDanIce,

    You could insert two new columns B and C with the formulae:
    Code:
    =IF(ABS(E1-D1) < 1000,E1,"")
    =IF(ABS(E1-D1) >= 1000,E1,"")
    ...so either will populate but not both. Revenue becomes column D and Costs is no longer graphed.

    If you now make the chart based on the first 4 columns you will still only see two entries in the stacked column but the colours will differ between TRUE and FALSE results of your comparison (so just use a colour scheme to highlight).


    A B C D E
    1 Jan 15555 22000 15555
    2 Feb 11555 11111 11555
    3 Mar 33777 33333 33777
    4 Apr 23900 23456 23900
    5 May 18888 21212 18888
    6 Jun 18888 39393 18888
    7 Jul 16110 15666 16110
    8 Aug 19332 18888 19332
    9 Sep 12222 18888 12222
    10 Oct 19332 18888 19332
    JackDanIce

    Worksheet Formulas
    Cell Formula
    B1 =IF(ABS(E1-D1)<1000,E1,"")
    C1 =IF(ABS(E1-D1)>=1000,E1,"")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,296
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    Hi @Toadstool, thanks for replying

    I think I can adapt this, my dates are horizontal rather than vertical (unfortunately it's not a normalised table). Assume I can, unless I reply back with more issues!

    Best,
    Jack


  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    I saw you said A1:A10 were months so thought it was vertical. Works the same horizontal.

    A B C D E F G H I J
    1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct
    2 21222 14466 23900 21211 16110 19332 19332
    3 33777 18888 12222
    4 22000 14444 22222 23456 21212 39393 16110 18888 18888 18888
    5 21222 14466 33777 23900 21211 18888 16110 19332 12222 19332



    Horizontal

    Worksheet Formulas
    Cell Formula
    A2
    to
    J2
    =IF(ABS(A4-A5)<1000,A5,"")
    A3
    to
    J3
    =IF(ABS(A4-A5)>=1000,A5,"")

    Just format the series the desired colour to highlight.

    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    I tried a bunch of different ways to highlight one or the other of the columns in the chart, and nothing I did clearly highlighted anything. I don't really think the effort by Toadstool really highlights what's happening: it's too complicated when more colors are incorporated.

    But then I thought of something else I could try.

    Here's the setup. Simple data and simple clustered column chart in the first row. In the second row, I've made instead a simple line chart, then I added up-down bars (select either series, click the plus icon next to the chart, check Up-Down Bars). I formatted the bars and then hid the lines. This doesn't really show what we want, but I'm not done yet. I'm going to combine the two approaches.



    So I'll start with the simple clustered column chart, and the columns have a gap width of 100%, so the gap between clusters is 100% as wide as a column in the cluster (top left). I copy the data range, select the chart, and use Paste Special on the Home tab's Paste dropdown, and I choose the options to add the data as new columns, in columns, series names in first row, categories in first column (top right).

    I right click on one of the series, choose Change Series Chart Type, and I change the added series to lines (middle left). Then I select one of the lines, and add up-down bars as before (middle right).

    I change the fill of the bars to match the columns (I left the border in place temporarily so you can see the bars against the columns), I change the line color of the line series to no line, and I delete the unwanted legend entries (click once to select the legend, click again to select the legend entry, then press the Delete key), which results in the bottom left chart.

    Finally I remove the borders from the up down bars, then I change the gap width of the up down bars. You do this by selecting one of the line series (click at the top or bottom of an up-down bar, and you should see the hidden markers highlighted) and formatting it. A gap width of 50% works good for a column chart gap width of 100% (bottom right).

    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,296
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    Thank you both, lots to review! And much appreciated.
    @Toadstool correct, I got my ranges wrong, meant A1:L1 etc
    @Jon Peltier, I think "No Border & Gap Width" may be best work around or visually easiest to show then change when Revenue ~= (or >) Exp


  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clustered column chart - highlight specific column if condition met

    Yes, of all of the things I tried, that was the best outcome.

    Note that the chart titles do not show options so much as progressive steps in the protocol.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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