+ Reply to Thread
Results 1 to 4 of 4

Vary Colors in Pivot Charts based on values (Overlay Charts)

  1. #1
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Question Vary Colors in Pivot Charts based on values (Overlay Charts)

    I am attempting to vary colors on a pivot chart based on values to use as an alternative to KPIs in PowerPivot. In the attachment is a simple example. If the value is -1 in the pivot, then the chart will be a 1 and red in color. If the value is 0, then the chart will be a 2 and blue in color. If the value is 1, then the chart will be a 3 and green in color. I'm fairly sure you can't vary colors based on values in the format options pane for a single chart. I did manage to create three different charts that only show a color for each value. I.e., one chart works when the value is only -1 and will show a red circle but will remain blank for any other value; the same idea is applied for the other two charts. Is there a way to overlay these charts (e.g., transparency, grouping, etc.) to effectively create one chart assuming the limitations of formatting a single chart? If I can achieve my desired result by just one chart then that is of course fine as well.

    Note: the Shipper slicer will give different values in the pivot and thereby the charts. Atlanta = 1 (Fail), Detroit = 2 (OK), Nashville = 3 (Pass).
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Vary Colors in Pivot Charts based on values (Overlay Charts)

    I did a bit of playing around. It's nice that the KPI's are consecutive (-1,0,1)

    Here is what I did. I created a three piece pie chart each wedge with an equal value.

    Then I created the small table in columns A, B. C with the intention of the A wedge being red, the B wedge being blue and the C wedge being green. I did not assign the colors just yet.

    Then I created a named dynamic range: Plot_Value = =OFFSET(Chart!$J$2:$L$2,Chart!$B$3+1,0).

    What this does is look at range J2:L2 (1,0,0) and goes down the number of rows indicated by Cell B3 + 1. So if B3 = -1, it goes down zero rows, If B3 = 0 it goes down 1 row. If B3 = 1, it goes down 2 rows.

    Then I went to the pie chart and right clicked and selected Select Data. I selected the series and edited it. I replaced the fixed cells after the apostrophe with Plot_Data. This assigned the dynamic range to the chart.

    Now I selected Nashvile, and the chart came up with a color. I clicked on the circle, and the Format Data Series came up. I selected green as the color. I selected Miami and did the same thing and assigned red, and finally Detroit for blue.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Vary Colors in Pivot Charts based on values (Overlay Charts)

    Dflak,
    Your way seemingly works, but you can also change the transparency of 2 of the 3 chart's background to 100% while leaving the other at 0%. You can them stack them on top of each and group them to create what is essentially one chart.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Vary Colors in Pivot Charts based on values (Overlay Charts)

    Well, now that you mention it . It turns out that is the easier approach and requires no coding. Right click to select the chart areas and the plot areas on each chart and format them to no fill.

+ 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. Link two pivot charts such that same axis categories get same colors.
    By cudh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-21-2014, 06:18 AM
  2. Replies: 0
    Last Post: 09-19-2014, 09:07 AM
  3. Consistent Colors for Pivot Charts
    By zasskar in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-15-2008, 11:22 AM
  4. Matching the colors Column Charts and Pie Charts
    By RohanSewgobind in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-21-2006, 04:40 PM
  5. [SOLVED] Overlay charts in excel
    By Amanda in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-15-2005, 12:05 AM
  6. OVERLAY TWO CHARTS(GANTT)
    By Mike in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-01-2005, 01:05 PM
  7. [SOLVED] Overlay charts
    By Ian in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 03-24-2005, 04:06 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