Excel Pivot with percentage and count on bar graph

Question

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

Solution

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.

Comments

[Kevin Anthony Oppegaard Rose]

I would make a new table and graph from that one.

[Max M]

So two tables and graphs? That would work, but not solve my question

[Kevin Anthony Oppegaard Rose]

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.

[Max M]

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]

Sounds like a neat solution, but unfortunately I only have Office 2010 yet.

[dav]

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.

[Max M]

Global IT does not allow that :(

[dav]

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.

[Max M]

I think I ll just wait for Office 2016. The quick fix for my application was to just fill in the percentages manually.