2

Is it possible to create a Pivot bar graph that Shows percentage and Count in the same bar? I only have Excel 2010

MWE:

Firm    Product
1   Car
2   Ship
3   Car
4   Ship
5   Car

I can easily create a Pivot table / charte using Product in the value and row category to get either the Count for car and ship 3 and 2 or the percentage 60% and 40% and the coresponding bar graph. I would like to Show the bar graph with Count at the y axis and Show the percentage on top or within the bar (similar to think cell). I can create the graph and adjust the bar description manually, but that seems to be very tedious and error prone

Desired output

  • I would make a new table and graph from that one. – Kevin Anthony Oppegaard Rose Jun 1 '18 at 7:45
  • So two tables and graphs? That would work, but not solve my question – Max M Jun 1 '18 at 8:02
  • No. Two tables, one graph. Simply make a second table summering the information in the first table, A row to be "Product" and B row to be "Percentage" then use formulas to work out your percentage and graph that. – Kevin Anthony Oppegaard Rose Jun 1 '18 at 8:14
  • I am aware of that solution, but I was hoping to learn if this is directly possible within one pivot Chart, getting the values is not the problem. If it is not possible, I ll have to deal with it in the way you mentioned – Max M Jun 1 '18 at 8:53
0

If you have Excel 2016, this is very easy:

  1. Create chart as above
  2. Add data labels
  3. Format data labels
    Label Options > Select Value From Cells and select the cells that hold your % value

If your version of Excel doesn't support that option natively, then use Rob Bovey's excellent Chart Labeler to accomplish the same thing.

| improve this answer | |
  • Sounds like a neat solution, but unfortunately I only have Office 2010 yet. – Max M Jun 4 '18 at 8:08
  • Did you try chart labeler? I used it all the time until I moved to 2016. If you need help with that, let me know. – dav Jun 8 '18 at 23:07
  • Global IT does not allow that :( – Max M Jun 27 '18 at 9:42
  • Since you can't use either of the two best options-does this have to be a Pivot Chart, or can it be a chart based upon data from a Pivot Table? If so, you could do a combo chart with the Labels essentially labeled XY points (on a secondary scale) overlaying the column chart. Also, there's probably a VBA solution if that would be acceptable. – dav Jun 27 '18 at 11:42
  • I think I ll just wait for Office 2016. The quick fix for my application was to just fill in the percentages manually. – Max M Jun 28 '18 at 13:19
0

I actually ended up duplicating the data series using the Sum Values pane, made it percentage, and stacked it up to the first series, copied it to Word, and made the background fill white... and moved them a little to make it look nice. Not the smartest solution but worked for me.

| improve this answer | |
0

Create your chart with the bar series the same as you have in your picture. Then create a second series by bringing your metric into the Values pane a second time. Set that metric to show values as percentage. It will look strange at first.

Now go to Change Chart Type and choose Custom. Choose bar graph for your bar series, and choose line for your percentage series. It will still look strange.

Right click within the graph on the percentage line series and choose Add data labels. Right click again and choose Format Data Series. Click on the paint can. Under line, choose No Line. Under Marker, choose No Marker.

Now you have an invisible series with the labels that you want.

| improve this answer | |

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.