2

I'd like to build a bar graph in excel. The thing i'm having issues with is the formatting of the labels on the datapoints. I'd like the labels to pull from 2 datasets. The bar would be drawn using the first data point say "66%" but i'd like the label to show the percent and the count, something like "66% (14)"

Is this possible?

2 Answers 2

1

First you need to calculate the label as 1 column, e.g.

=TEXT([@Percent], "0%") & " (" & [@Value] & ")"

Then if you don't need to preserve the category, just choose this column as the Axis Label (you can also add category in the above formula).

But if you want to separate the axis category from the labels, you will need to do following (steps based on Excel 2010):

  1. insert a Stacked Bar chart, based only on Category and Percent columns
  2. add Data Labels > Inside Base for the whole chart
  3. add a 2nd series based on the Label column (via right-click Select Data... > Add, do not change Axis Labels at this stage) - all values will be 0 because it is a text column
  4. delete the original Percent-only labels
  5. right-click on the 0 labels and choose Format Data Series... > Plot Series on Secondary Axis
  6. delete the secondary axis itself that appeared on top of chart
  7. right-click on 0 labels again, Format Data Labels... > tick Category Name and untick Value
  8. in Select Data... dialog, choose the Label series and click Edit Axis Labels (right column) - choose the Label column for the labels

Note you can also choose the Percent column as the values of your Label series combined with the Inside End data labels option for the labels to appear at the right-hand side of the bars, but you will need to make sure that both primary and secondary axes are scaled the same.

dual_label_printscreen

0

Excel 2013 provides an option which allow to adding separate values from cells > see button Select Range...

Example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.