Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Can a chart bar color be based on conditional format color?

0

Can a bar chart pick up the conditional format color? I was watching your video on making a chart look like a thermometer and I got thinking about my application.

In the attached Excel you will see I'm already using conditional formating in our balances column. I like the way I can quickly see our spending but this doesn't print well for my family to see. That's why I also use the chart. I was hoping that a chart could do the same.

See " I would this:" columns

Is there a way to do this that isn't too complicated. I was going to try more conditional formatting to do this but I'm not sure it would work.

Answer
Discuss

Discussion

Hi JoyhillDad and welcome to the Forum.  

A chart can't "pick up the conditional format color" but you can create something similar. It isn't as straightforward as conditionally formatting cells but you do could use helper columns and the addition of overlapped series to your chart to get something similar. It depends on what you'e looking to do so an Excel file would help us see it it's possible. 

Please edit your question to explain more fiully what you're trying to do and use the Add Files... button to upload a representative Excel file (without any personal data) to show your chart and data. Then we should be able to give specific help.
John_Ru (rep: 3972) Jun 11, '22 at 10:44 am
Thanks John. I updated it.
JoyhillDad (rep: 2) Jun 14, '22 at 8:35 am
Thanks for that but not sure I understand... do you want a chart with a coloured background (as in your "I would like this:") where the item appears only as a bar (with the data point value). Is it one chart per item?

I'd imagined that you wanted a bar chart where the colour of the whole bar (per item) is determined by its value c.f a budget/ thresholds. Might that be okay?

Either way I don't have any time today to look at this but (if you reply) will try to do so tomorrow.
John_Ru (rep: 3972) Jun 14, '22 at 9:44 am
YES!! That would work. If the whole bar changed colors as the amounts lowered that would be great. 
Apologies if I didn't explain it well. Normally I'd ttry to slug it out but I've been focusing my learning on other details of excel. Thanks for the help!
JoyhillDad (rep: 2) Jun 14, '22 at 10:11 am
Add to Discussion

Answers

0
Selected Answer

JoyhillDad

Here's a way to have the chart change colour as thresholds are passed (you'll need to do something similar on your own file).

In the attached file, there's some imaginary data in A2:E6 - actuals, budgets and % thresholds (which you can change). If you change B3 (Gas Actual) to 450 say,  the column will grow and turn red since the 90% threshold (E2) has been exceeded.

What's behind this is "helper" columns D and E, There's a formula which gives a value when a test is met or nothing if it's not (and the chart has a data seres with a colur which appears if the test is met or doesn't if not). In E3 for example, the formula is:

=IF($B3/$C3>=E$2,$B3,"")
so if the actual in row 3 divided by the budget (in C3) exceeds the percentage threshold in E2, the actual value is the result. Otherwise it's nothing, "". Dollar signs fix columns and/or  rows when the formula is copied (as you know hopefully).

Do to create this effect in that file, first delete the chart. Then select A3:B6 (press Ctrl) and  D3:E6 then go file menu/ Insert then ribbon Recommended charts/ Clustered Column, Ok.

You'll get a chart with Electricity showing three columns. Click on the right (Series 3), right click then pick Fill and choose red (from Standard Colours). For the middle (series 2), do the same but pick an orange and for Series 3 pick a green but also right click and choose Add Data Labels... this will display the Actual value (whichever colour the column appears).

You now have some different coloured bars of the same height. Right click on one again and choose Format Data Series.... Under Series Options, change Series Overlap from -27% (probably) to 100%. This makes them overlap so you only see the colour for the rightmost figure.

Now if you vary the Actual figues in column A to above or below the thresholds, the columns will change size and colour to suit.

Hope this makes sense and you can use it to create a working file.

Discuss

Discussion

Thanks!! I love it. I'll let you know when done.
JoyhillDad (rep: 2) Jun 14, '22 at 8:10 pm
Glad you like it. Thanks for selecting my answer. 
John_Ru (rep: 3972) Jun 15, '22 at 1:23 am
Add to Discussion
0

This is exactly what I wanted John!

Thanks for the help.

Discuss

Discussion

That looks good- well done!
John_Ru (rep: 3972) Jul 3, '22 at 2:54 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login