+ Reply to Thread
Results 1 to 6 of 6

Adding Numbers and Percentages to Legends

  1. #1
    Registered User
    Join Date
    11-30-2009
    Location
    New York City
    MS-Off Ver
    Excel 2003
    Posts
    12

    Adding Numbers and Percentages to Legends

    Hi, I am trying to add the $ values and percentages to the legend of a pie chart. I know I can add it to the main area of the chart, but because of the number of fields, it is very hard to read that way. Do you know if there is a way to add the $ values and percentages to the legend of a pie chart in Excel? An example of how I would like it to display is attached. I've been playing around with it all morning and amfrustrated.

    Any help would be greatly appreciated.

    THANKS!
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Adding Numbers and Percentages to Legends

    Hi,

    I don't want to burst your bubble, but this kind of information should not be presented in a pie chart at all. If you're interested to read why not, and how to present this data better, visit sites like www.perceptualedge.com, http://www.perceptualedge.com/articl...or_dessert.pdf
    www.peltiertech.com, http://peltiertech.com/WordPress/whi...rm-do-you-use/ (this article is really about pie charts, not blogging)
    www.chandoo.org
    to name just a few.

    Of course, if you absolutely insist on presenting your chart in a design that makes it hard for the reader to understand the data, you can use a trick to concatenate the series names with the numeric values you want to show in the legend.

    For example, you have in cells A1 to C1 the series name, calculated percent value, and absolute value. Then you can use D1 to concatenate

    =a1&" "&b1&"% ("&c1&"mm)"

    copy down and use that column for your series names

    But I'd strongly urge you to use a bar or column chart instead.

    hth

  3. #3
    Registered User
    Join Date
    11-30-2009
    Location
    New York City
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Adding Numbers and Percentages to Legends

    Using concatenate, is it possible to cut off part of a phrase, such as concenating just "Industry Index" out of the field "Sum of Industry Index"?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding Numbers and Percentages to Legends

    you can use the MID formula to select part of a cells text.
    or SUBSTITUE

    assuming A1 contains the text to be altered,

    =SUBSTITUTE(A1,"Sum of","")&" "&b1&"% ("&c1&"mm)"
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    11-30-2009
    Location
    New York City
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Adding Numbers and Percentages to Legends

    Thank you

    Is it possible to make the formatting of concenation appear with a comma, meaning making "7504" would appear as "7,504"?

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding Numbers and Percentages to Legends

    wrap the cell reference in a TEXT formula

    =SUBSTITUTE(A1,"Sum of","")&" "&B1&"% ("& TEXT(C1,"#,##0") &"mm)"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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