9

So apparently Pivot Table, when converted into Pivot Chart, does not use the "Grand Total" line when asked to display the "Data Table" below the chart.

Online and on Stackexchange, it has been suggested that one should create a separate table, which has the values in the pivot table and then create a chart using that table.

I did that but now I have Grand total showing up both in the Chart and the Table. I don't want that. I also don't have the competence to use "Calculated Field" to add a "Grand Total" in the pivot table but would appreciate being taught how to do that.

Ok, so this is what I want:

enter image description here

But these two are what I can manage: enter image description here enter image description here

How can I fix this?

whiskeychief
  • 81
  • 10
Amatya
  • 1,203
  • 6
  • 32
  • 52

1 Answers1

4

On the second, chart where the total column is green, if you right-click on it and select format data series, you'll get this:

enter image description here

From there, go to fill on the left side, and select no-fill like this:

enter image description here

Once you've done, press close and your chart should look like this:

enter image description here

EDIT: You'll also need to delete the totals in the legend. You can do that by clicking the legend, then clicking total, and hitting the delete key.

PermaNoob
  • 849
  • 5
  • 17
  • It's totally working, thanks a lot. An slightly related question, how do you select the data column you want to no-fill if it is really small. Can you zoom in?Is there a way to select the column via some wizard or some drop-down menu rather than clicking on the picture? – Amatya Dec 23 '13 at 22:28
  • 2
    You can try changing it to a 3D chart. When it's 3D, I find it easier to see smaller values. – PermaNoob Dec 23 '13 at 22:33
  • 2
    Don't waste time with 3D. Select a series you can easily click on, then use the up/down arrow keys to select the one you want. – Jon Peltier Sep 03 '15 at 15:30
  • Instead of formatting the Totals as another clustered bar with no fill, change its chart type to a line chart, and format it to use no lines or markers. This way you don't get stuck with the blank slot on the axis where the hidden columns are (not) drawn. – Jon Peltier Sep 03 '15 at 15:32