• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Conditional Formatting of an Area Chart-Possible Or No?|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Conditional Formatting of an Area Chart-Possible Or No?|Dashboards & Charts|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsConditional Formatting of an Area C…
sp_PrintTopic sp_TopicIcon
Conditional Formatting of an Area Chart-Possible Or No?
Avatar
Matthew Snow
Member
Members

Dashboards

Power Pivot

Power BI
Level 0
Forum Posts: 11
Member Since:
August 8, 2018
sp_UserOfflineSmall Offline
1
October 31, 2018 - 10:18 pm
sp_Permalink sp_Print

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?

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
November 1, 2018 - 2:29 am
sp_Permalink sp_Print sp_EditHistory

Hi Matthew

Can you post some sample data and your expected result (with some explanation) for us to understand your needs better.

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
November 1, 2018 - 12:24 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
November 1, 2018 - 2:04 pm
sp_Permalink sp_Print

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

Avatar
Matthew Snow
Member
Members

Dashboards

Power Pivot

Power BI
Level 0
Forum Posts: 11
Member Since:
August 8, 2018
sp_UserOfflineSmall Offline
5
November 15, 2018 - 9:25 pm
sp_Permalink sp_Print

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?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
November 16, 2018 - 2:27 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
November 16, 2018 - 11:53 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
November 16, 2018 - 12:32 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
D Harvey
Member
Members
Level 0
Forum Posts: 6
Member Since:
January 31, 2019
sp_UserOfflineSmall Offline
9
February 8, 2019 - 9:09 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
February 11, 2019 - 10:25 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
D Harvey
Member
Members
Level 0
Forum Posts: 6
Member Since:
January 31, 2019
sp_UserOfflineSmall Offline
11
February 11, 2019 - 9:12 pm
sp_Permalink sp_Print

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.       
         
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
February 12, 2019 - 12:04 pm
sp_Permalink sp_Print

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

Avatar
D Harvey
Member
Members
Level 0
Forum Posts: 6
Member Since:
January 31, 2019
sp_UserOfflineSmall Offline
13
February 12, 2019 - 6:34 pm
sp_Permalink sp_Print

thank you so much ,

this will really help

DH

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Blair Gallagher
Brandi Taylor
Hafiz Ihsan Qadir
Gontran Bage
adolfo casanova
Annestine Johnpulle
Priscila Campbell
Jeff Mikles
Aaron Butler
Maurice Petterlin
Forum Stats:
Groups: 3
Forums: 24
Topics: 6369
Posts: 27852

 

Member Stats:
Guest Posters: 49
Members: 32359
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.