Clustered column chart - highlight specific column if condition met

[JackDanIce]

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

 

[Toadstool]

Hi JackDanIce,

You could insert two new columns B and C with the formulae:

=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
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]15555[/TD]
[TD="align: right"]22000[/TD]
[TD="align: right"]15555[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]11555[/TD]

[TD="align: right"]11111[/TD]
[TD="align: right"]11555[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]33777[/TD]

[TD="align: right"]33333[/TD]
[TD="align: right"]33777[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]23900[/TD]

[TD="align: right"]23456[/TD]
[TD="align: right"]23900[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18888[/TD]
[TD="align: right"]21212[/TD]
[TD="align: right"]18888[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18888[/TD]
[TD="align: right"]39393[/TD]
[TD="align: right"]18888[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16110[/TD]

[TD="align: right"]15666[/TD]
[TD="align: right"]16110[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]19332[/TD]

[TD="align: right"]18888[/TD]
[TD="align: right"]19332[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]12222[/TD]
[TD="align: right"]18888[/TD]
[TD="align: right"]12222[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]19332[/TD]

[TD="align: right"]18888[/TD]
[TD="align: right"]19332[/TD]

</tbody>

JackDanIce​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(ABS(E1-D1)<1000,E1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1
[/TH]
[TD="align: left"]=IF(ABS(E1-D1)>=1000,E1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

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

 

[Toadstool]

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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: center"]Jul[/TD]
[TD="align: center"]Aug[/TD]
[TD="align: center"]Sep[/TD]
[TD="align: center"]Oct[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]21222[/TD]
[TD="align: center"]14466[/TD]
[TD="align: center"][/TD]
[TD="align: center"]23900[/TD]
[TD="align: center"]21211[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16110[/TD]
[TD="align: center"]19332[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19332[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]33777[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]18888[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12222[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]22000[/TD]
[TD="align: center"]14444[/TD]
[TD="align: center"]22222[/TD]
[TD="align: center"]23456[/TD]
[TD="align: center"]21212[/TD]
[TD="align: center"]39393[/TD]
[TD="align: center"]16110[/TD]
[TD="align: center"]18888[/TD]
[TD="align: center"]18888[/TD]
[TD="align: center"]18888[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]21222[/TD]
[TD="align: center"]14466[/TD]
[TD="align: center"]33777[/TD]
[TD="align: center"]23900[/TD]
[TD="align: center"]21211[/TD]
[TD="align: center"]18888[/TD]
[TD="align: center"]16110[/TD]
[TD="align: center"]19332[/TD]
[TD="align: center"]12222[/TD]
[TD="align: center"]19332[/TD]

</tbody>

Horizontal​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2
to
J2[/TH]
[TD="align: left"]=IF(ABS(A4-A5)<1000,A5,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3
to
J3[/TH]
[TD="align: left"]=IF(ABS(A4-A5)>=1000,A5,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Just format the series the desired colour to highlight.

 

[Jon Peltier]

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

 

[JackDanIce]

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

 

[Jon Peltier]

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.