Pivot Chart data labels

[joebanana]

I have data in a Pivot Table and a Pivot Chart based on the table.

I need to display the labels for Bad and Ratio. Right now, they're overlapping so it's unreadable.
How can I display them side-by-side?

Thanks in advance!

chart.gif

[Andy Pope]

Because you have overlapped the bars the best you can do automatically is to format one set of data labels to be inside end and the other to be outside end.
Even this will have overlap when the values are similar

[joebanana]

Yeah that's what I thought...

I guess for this specific chart I can use a simple table. I'll have to bear with the fact that some years will just have no data.
Then I can add another helper column for my labels.

Thanks anyways

[joebanana]

By the way, there is no way to add a helper column to a Pivot Table?

Calculated Fields will only allow numeric values...

[joebanana]

Based on the attached picture, is there a way to hide Years that have 0 values in the chart?

The good thing about Pivot Tables is that it's dynamic but causes me these labels issues.

If I could hide empty years, I would be set...

Thanks in advance!

[joebanana]

I found a way!

I just changed the formatting of my number label to " "0 to add leading spaces.
It's not perfect but at least now I can see both numbers.

[Andy Pope]

It hard to tell from just pictures but can you not filter the table?

[joebanana]

I found a way!

I just changed the formatting of my number label to " "0 to add leading spaces.
It's not perfect but at least now I can see both numbers.

Well it seems it was too good to be true.
While it works fine in Excel 2010, it makes Excel 2007 crash.
As soon as I modify the formatting Excel stops responding...

[Andy Pope]

Try these custom number formats instead.

<ALT+010>General

General<ALT+010>

To actual enter <ALT+010> use the numeric keypad to enter 010 whilst holding the ALT key.

[joebanana]

Andy,

I opened your file and didn't see anything special? Was there something to show?
Also, I tried your tip and ended up with the same result, Excel froze up on me...

[Andy Pope]

Check the data labels for period 2009. Both bars have the value of 3, although you can only see the red bar, yet you can see both data labels.

[joebanana]

I don't think it solves my problem in this case...

See I have two values for which I want to display the label.
One is a count (from 0 to a couple of thousands), the other is a percentage.

The percentage is always "low" compared to the count, because it never exceeds 1.
Which is ok, I guess, because I can put the label close to the axis.

When the count is high, it's ok too, because it will be far from 1.
However, when the count is low they overlap.

Inserting leading spaces in Excel 2010 was perfect. I'm really wondering why it makes Excel 2007 crash.

Andy are you able to reproduce the problem?
For information, if I update the file in Excel 2010, then opens it in Excel 2007, it's slow, and all my pivot tables get corrupted, so that's not an option either.

[Andy Pope]

No, but then I don't have your file.

Why are you overlapping the bar? Would not using secondary axis or even column/line chart be a simpler solution.

[joebanana]

I want to display the total bar (but not its label) and overlap the count so I can visually see the ratio.
I also display the count label, and the ratio label. For the ratio bar I just have no fill/line so I display only the label.

My count is plotted on the secondary axis while my total is plotted on the primary axis.
I can attach the file on Monday when I'm back to the office if you want.

I really appreciate your help!

[joebanana]

Well Andy, your last comment made me think and I was able to solve my issue.

I changed the chart type to Stacked, move my count to the secondary axis, put my ratio at the end, put the count label at center and the ratio label outside, and voilà!

It was so simple... thanks Andy =)

[joebanana]

Andy, while I managed to get the display I wanted, attempting to change the label format keeps crashing Excel.

I have attached my file for you to review, if you wish to.
For example, I tried changed my label to 0;;; to hide 0 values. Just that made it crash.

I was able to do it in Excel 2010, save the file, and reopen in Excel 2007 and it works.

Please let me know if you are able to reproduce with your version of Excel 2007.

[Andy Pope]

If you apply the custom number format to the pivot tables, rather than the pivot chart, it does not crash for me.
But it still does not make the cluster of data labels any easier to read.