+ Reply to Thread
Results 1 to 2 of 2

Automate color of chart area based on value of a helper column (not value column)

  1. #1
    Registered User
    Join Date
    04-11-2016
    Location
    Melbourne
    MS-Off Ver
    2013
    Posts
    18

    Automate color of chart area based on value of a helper column (not value column)

    Hi all,

    I am working towards automating the color of excel chart areas based on value of a column in additional to the value column.

    For example, in the attachment where column B says 'work' the chart colour should be grey (for example), and when it says 'rest' the corresponding area should have a different color.

    Is there a way I can automate this?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automate color of chart area based on value of a helper column (not value column)

    I don't know of any way to do this with a chart, but I don't work with charts often, so it's absolutely possible that I'm wrong. You can, however, use conditional formatting to mimic the look of the chart you appear to be aiming for. In the attachment, I did the following:

    1. Create a helper column in C with a zero in C1 and the formula below in C2, filled down through C13:

    =$C1+$A2

    The helper column can be hidden for aesthetic purposes, if you prefer.

    2. Design a range to look like your chart. I used D18:AA18 for the hour markers and stretched row 17 to serve as your color-coded timeline.

    3. Select D17:AA17 and create the following conditional formatting rule, with the format set to pink:

    =INDEX($B$1:$B$13,MATCH(D18,$C1:$C13,1)+1)="Work"

    4. Repeat step 3, changing "work" in the above formula to "rest" and selecting a different color (gray).

    5. Repeat again as necessary for any other activities/colors you want to incorporate.

    I don't know what your overall plan is for the data, but this approach would at least deliver the visuals your chart is attempting to create. Take a look at the attachment to see if it will do:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 03-10-2017, 02:20 AM
  2. Sort by helper column even if values in helper are alike
    By NoSleepForMe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2016, 04:14 AM
  3. Transpose based on helper column VBA
    By carlosriver24 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2013, 03:12 AM
  4. Column chart - Change color/value based on original y value
    By leossj433 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-14-2012, 12:23 PM
  5. Column Chart - color based on x value
    By smilewithme in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-03-2012, 08:39 AM
  6. Tuning column chart to get area-column effect
    By goels in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 01:02 PM
  7. [SOLVED] Automate bar chart color based on value postive or negative
    By kathy in forum Excel General
    Replies: 1
    Last Post: 08-31-2005, 05:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1