Grand Total - Pivot

L

loooser

Board Regular
Joined
Mar 26, 2007
Messages
50
Is there a way to make the grand total in a pivot table calculate a % in stead of it summing all of the cells in a column> I am trying to get the Margin % column to work for the grand total row as well.
 
Sort by date Sort by votes
Hi Loooser,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I managed to reproduce the problem of the yours PivotTable.<o:p></o:p>
<o:p></o:p>
I try this:<o:p></o:p>
<o:p></o:p>
I create a PivotTable Grand Total Manual, i.e., it is not part of the PivotTable, then the error # DIV/0! is displayed (for example in D16 =SUM(D5:D14) in my PivotTable – see my example below).<o:p></o:p>
<o:p></o:p>

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD>Valores</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Rótulos de Linha</TD><TD>Soma de Sales</TD><TD>Soma de Margin</TD><TD>Soma de Margin/Sales</TD><TD>Soma de Campo1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Automóvel</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Avião</TD><TD style="TEXT-ALIGN: right">R$ 3.741.958</TD><TD style="TEXT-ALIGN: right">R$ 1.643.375</TD><TD style="TEXT-ALIGN: right">43,92%</TD><TD style="TEXT-ALIGN: right">43,92%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Brontossauro</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">R$ 1.191.610</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Cavalo</TD><TD style="TEXT-ALIGN: right">R$ 5.038.664</TD><TD style="TEXT-ALIGN: right">R$ 2.274.753</TD><TD style="TEXT-ALIGN: right">45,15%</TD><TD style="TEXT-ALIGN: right">45,15%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Elefante</TD><TD style="TEXT-ALIGN: right">R$ 2.777.588</TD><TD style="TEXT-ALIGN: right">R$ 1.397.548</TD><TD style="TEXT-ALIGN: right">50,32%</TD><TD style="TEXT-ALIGN: right">50,32%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Girafa</TD><TD style="TEXT-ALIGN: right">R$ 3.764.935</TD><TD style="TEXT-ALIGN: right">R$ 2.053.523</TD><TD style="TEXT-ALIGN: right">54,54%</TD><TD style="TEXT-ALIGN: right">54,54%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Peterodáctilo</TD><TD style="TEXT-ALIGN: right">R$ 3.492.042</TD><TD style="TEXT-ALIGN: right">R$ 1.433.309</TD><TD style="TEXT-ALIGN: right">41,05%</TD><TD style="TEXT-ALIGN: right">41,05%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Tiranossauro</TD><TD style="TEXT-ALIGN: right">R$ 1.278.386</TD><TD style="TEXT-ALIGN: right">R$ 618.130</TD><TD style="TEXT-ALIGN: right">48,35%</TD><TD style="TEXT-ALIGN: right">48,35%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Trem</TD><TD style="TEXT-ALIGN: right">R$ 2.297.734</TD><TD style="TEXT-ALIGN: right">R$ 1.127.778</TD><TD style="TEXT-ALIGN: right">49,08%</TD><TD style="TEXT-ALIGN: right">49,08%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Zebra</TD><TD style="TEXT-ALIGN: right">R$ 4.788.170</TD><TD style="TEXT-ALIGN: right">R$ 2.485.608</TD><TD style="TEXT-ALIGN: right">51,91%</TD><TD style="TEXT-ALIGN: right">51,91%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Total geral</TD><TD style="TEXT-ALIGN: right">R$ 27.179.476</TD><TD style="TEXT-ALIGN: right">R$ 14.225.637</TD><TD style="TEXT-ALIGN: right">52,34%</TD><TD style="TEXT-ALIGN: right">52,34%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Total Manual</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">R$ 27.179.476</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">R$ 14.225.637</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">#DIV/0!</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">#DIV/0!</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="COLOR: #0070c0; FONT-WEIGHT: bold">=SUM(D5:D14)</TD><TD style="COLOR: #0070c0; FONT-WEIGHT: bold">=SUM(E5:E14)</TD></TR></TBODY></TABLE>Plan4


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B16</TH><TD style="TEXT-ALIGN: left">=SUM(B5:B14)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C16</TH><TD style="TEXT-ALIGN: left">=SUM(C5:C14)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D16</TH><TD style="TEXT-ALIGN: left">=SUM(D5:D14)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E16</TH><TD style="TEXT-ALIGN: left">=SUM(E5:E14)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


But as you've recreated the PivotTable and the error is still displayed, soon, unfortunately, this should not be the case.<o:p></o:p>
<o:p></o:p>
Soon, as I said earlier, I'm not viewing other suggestions to solve your problem.<o:p></o:p>
<o:p></o:p>
Markmzz<o:p></o:p>
 
Last edited:
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You must log in or register to reply here.

Similar threads

F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
285
Francois2France
F
A
  • Question Question
Grand total line as a secondary axis to a clustered column
Replies
2
Views
538
auroraissccc
A
H
  • Solved
Macro to clear Data in ColE one row below "Grand Total"
Replies
1
Views
267
howard
H
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
268
mumps
mumps
j3andc
  • Question Question
% of Grand total within a pivot table
Replies
2
Views
700
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,225,155
Messages
6,183,215
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top