+ Reply to Thread
Results 1 to 5 of 5

% of other chart

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    % of other chart

    Hello,

    I have a question but im not sure if it can be done.

    I have attached a sample sheet for reference.

    In Sheet 1 i have the data and in sheet 2 I have a pivot chart.

    In the pivot chart i want the "sum of cost" to have the % of "sum of sales" and also the "sum of savings" to have the % of "sum of cost"

    I know i could probably write them in but the data will be chainging and i want the percentages to be interactive with my changing data.

    Also for the "sum of cost" (not in the current data) there are 2 different categories of costs and I was wondering if i could somehow have the red bar 2 different colors showing the % of the bar are those 2 different categories of cost.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: % of other chart

    In the pivot chart i want the "sum of cost" to have the % of "sum of sales" and also the "sum of savings" to have the % of "sum of cost" I know i could probably write them in but the data will be chainging and i want the percentages to be interactive with my changing data.
    It's not clear to me exactly what you want, but it sounds like you want to label the bars in the chart with percentages, is that correct? If so, first you will need to calculate those percentages in the spreadsheet (if I understand correctly, something like =C5/B5 should do the job), then add them as data labels. Easiest way (2010 and earlier) to do this is to download and install Rob Bovey's XY chart labeler add-in (www.appspro.com -> Free utilities).

    Also for the "sum of cost" (not in the current data) there are 2 different categories of costs and I was wondering if i could somehow have the red bar 2 different colors showing the % of the bar are those 2 different categories of cost.
    If I am understanding correctly, this sound like you want a clustered and stacked column chart, instead of a simple clustered column chart. Start here: http://peltiertech.com/clustered-sta...mn-bar-charts/ Because of the spacing requirements for the chart data, this type of chart may not be readily created from your pivot table. You may need to create another table (based on the pivot table -- simple lookup functions should be enough). The clustered and stacked column chart would then be based on this "helper" table.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: % of other chart

    Awesome. the % thing worked perfectly.

    Im not sure what you mean by the 2nd statement but I think im going to scratch the idea lol

  4. #4
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: % of other chart

    @MrShorty

    One questions. Is there any other way to add those data lables to the chart without downloading that? im on a work computer and i dont think im able to download anything like that.

    Thanks

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: % of other chart

    Rob Bovey's add-in is coded in VBA, so, yes, it is completely possible to do the same thing without his add-in. It is just a lot easier -- especially if you are unfamiliar with VBA.

    One option -- I believe this same functionality was introduced as a native option in 2013. If you have access to Excel 2013, you could easily go use that computer long enough to add the data labels.

    If you go the "code your own routine" route, you will be using the .text property of the datalabel object (part of the datalabels() collection). https://msdn.microsoft.com/en-us/lib.../ff838393.aspx

    If you are interested in writing your own code, I might suggest this recent thread with a couple of examples by other users here: http://www.excelforum.com/excel-char...ter-graph.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: 0
    Last Post: 01-07-2015, 01:22 PM
  2. Chart order in trad. Pivot Chart vs. PowerPivot chart
    By mr_jules in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-06-2015, 11:17 AM
  3. Replies: 2
    Last Post: 02-13-2014, 06:18 AM
  4. Replies: 5
    Last Post: 12-05-2012, 12:07 PM
  5. Replies: 5
    Last Post: 08-08-2011, 02:09 PM
  6. can we combine line chart and colum chart in one chart
    By reedzhou in forum Excel General
    Replies: 1
    Last Post: 06-20-2011, 10:03 PM
  7. [SOLVED] Cannot Activate Chart Area in Chart. Chart Object Failed
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2006, 09:45 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