Log In
Register
Home
Dashboards
Power Pivot
Power BI
August 8, 2018
Offline
Hi folks, I am using area charts to display performance information on over/under expectations for a group of employees, and would like to conditionally format the areas to green (over) or red (under). Is this possible in excel? I know you can do this with bar charts, but would prefer area charting. Thoughts?
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
Can you post some sample data and your expected result (with some explanation) for us to understand your needs better.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
I don't think that an area chart is suitable for what you wanted to display.
Anyhow you can give this a try.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Just to show you how odd it would look with an area chart, here is another try.
I overlay one chart on top of another to create the effect.
Sunny
Dashboards
Power Pivot
Power BI
August 8, 2018
Offline
Hi Sunny
Thanks for the help! I actually do not think I was very clear about my example-
Heres an example of what I am looking at;
The scale on the area chart is actually set to zero point. so an employee would either be over or under expectation, with meeting charted at zero.
For example
John Week 1 +5.0
John Week 2 +1.2
John Week 3 -2.5
John Week 4 -3.0
John Week 5 -4.5
John Week 6 +4.2
If that makes sense?
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
Sorry if I am not clear about your needs.
It would be better if you could attach an image of how you wanted the chart to look like (with the above data). Even a hand-drawn image would suffice.
In the mean time see if the attached meet your needs.
What I did was copy the colored image and paste it onto the area chart.
You will then need to adjust the charts Data Series - Fill - Picture Or Texture Fill - Offsets Top/Bottom to adjust the colors position.
This adjustment is not dynamic so you will need to do it for every chart.
If if is not good enough then you can refer here https://peltiertech.com/area-c.....-negative/
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Here is another try.
1) Create 2 similar area charts with different color (red and green)
2) Fix the axis max and min value (to prevent the chart from resizing)
3) Copy and paste red chart (you will need to select the range behind the chart to copy) as linked image and crop the top positive values image.
4) Move the cropped chart and superimpose onto the green chart.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
A 3rd method, copy the chart and paste link image.
Now set the chart fill color to transparent (PictureTools-Format-Color-Set Transparent Color)
Now the chart area is transparent and you can superimpose over cells filled with red and green colors.
Again, you must fix the axis max and min values to prevent resizing.
Sunny
Answers Post
January 31, 2019
Offline
can you link conditional formatting to an area chart,, so that it changes colour according to the values of several cells behind the chart area and is dynamic?
eg
if you had 4 KPIs,, if all are red, then overall KPI chart area shows red but if all are green, then overall KPI is gold and if a mixture of colours in the 4 basic KPis, then overall KPI shows orange...,, And this formatting of the overall KPI is dynamic so will change as the 4 basic KPIs change
i can easily do conditional formatting on the basic KPIs but cannot fathom how to do the overall one.
any ideas? Would an array function help me?
Thanks for you ideas in advance
DH
VIP
Trusted Members
June 25, 2016
Offline
Hi DH
Since you are able to conditional format the cells, I am sure there must be some values the cells are referring to.
There is no need to refer to the color of the cells to CF the chart. Just refer to the CF values instead.
As I don't know what these values are, I will just assume they are Red and Green.
Based on these values, I have created a sample (refer attachment). The test data generates some random numbers when you press F9.
No idea what your gold color looks like but you can play with the fill color using the RGB.
If this is not what you are looking for then can you post a sample of the KPI and the expected result?
It will help us to understand your needs better.
Sunny
January 31, 2019
Offline
Hi Sunny
I can't attach a file for some reason (!) i don't think i explained myself clearly enough:
but here is the essence of the query :
the individual KPIs are set to change colour in a sheet depending on their values as below
the Overall KPI box needs to turn Red if any of the Individual KPIs are red, Gold if the individuals are all green, Green if there are 4 green and 2 amber and amber if there is a mixture of amber and green,
we can do this by assigning values of 1 in a separate chart and then counting the outcome values , but i would love to know if it is possible to do it in one calculation in the KPI outcome cell.
i can get it to turn gold if all green conditions are met and then another colour if not or red if one KPI is red but I can't get a formula that will account for all 4 overall outcomes in one formula..
any ideas ?
| Red | Amber | Green | ||
| KPI1 | <2 | 2 to 5 | >5 | |
| KPI2 | <5 | 5 to 10 | >10 | |
| KPI3 | <6 | 6 to 10 | >10 | |
| KPI4 | <200 | 200 to 250 | >250 | |
| KPI5 | >50 | 50 to 2 | 1 | |
| KPI6 | >300 | 300 to 250 | <250 | |
| Overall KPI | GOLD or Green or Amber or Red depending on the combination of results above. | |||
VIP
Trusted Members
June 25, 2016
Offline
This is the best I could come up with. It uses a helper column A.
The CF counts the colors and determine if it meets a specific combination to format the KPI box and Chart.
Please note that the KPI box and Chart default color is Amber (Yellow) so you don't need to check for miscellaneous color combinations other than those that you had already defined.
Good luck.
Sunny
1 Guest(s)
