Free Microsoft Excel 2013 Quick Reference

pivot table and % on grand total column only

Please, Is there anybody that knows how to calculate in pivot table, % on grand total column only, not all of cells
type1 type2 type3 type4 Grand Total
agent1 4 4
agent2 1 5 129 135
agent3 6 5 9 904 924
agent4 6 1 3 87 97
Grand Total 12 7 17 1124 1160

Thaks a lot


Post your answer or comment

comments powered by Disqus
Hi,

I have created a calculated field in a Pivot table and I am getting a #DIV/0! error using the formula:

=IF(AND('Actuals'=0,'Budget'=0),0,('Actuals'/'Budget')-1)

Screenshot:

div error pivot example.jpg

I can understand getting the error on line N, and I can remove that by using the 'On Error' within the Pivot table options. However, I would like to get the Grand Total Variance and I am unable to get this because of the error. The Variance columns calculate fine with a #DIV/0! in the column, but the Grand Total Variance ends up with this error - so the Grand Total columns must be calculated differently.

So my question is, does anyone know how to get the Grand Total Variance to calculate correctly please using a Calculated Field? Thank you in advance of any response.

Hi,
I would like to have both the horizontal and vertical grand total columns in
my pivot table to appear at the top line and left hand line respectively. At
the moment they are at the bottom row of the table and the furthest column to
the right, meaning I have to scroll to see either of them.
I have only managed to move and sort the actual data columns around, but not
these total columns.

Thanks

Here is my problem. I am using Pivot Table to summarize financial data
to preform a margin analysis on it. Currently I receive a dump of data
from the system which turn is put into the pivot table. The way the
pivot table is currently displayed is as follows:

Sum of Amount Account
Customer 40000 43000 50000 Grand Total
ABC $4,000.00 $(50.00) $(3,000.00) $950.00
PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00

XYZ $5,000.00 $(500.00) $(3,500.00)
$1,000.00
Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00

The 40000 and 43000 are the revenue, 50000 being the cost and Grand
Total is the Margin. What I would like it to do is calculate a
percentage of the Grand Total / (40000+43000) and look something like
this:

Sum of Amount Account
Customer 40000 43000 50000 Grand Total
Margin
ABC $4,000.00 $(50.00) $(3,000.00) $950.00 24%
PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00
29%
XYZ $5,000.00 $(500.00) $(3,500.00)
$1,000.00 22%
Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00 26%

Is there an easy way if at all way to do this with the pivot table.
Right now I have to write the formula outside the pivot able to just
reference the individual cells. Problem with this is if the pivot
table changes after refreshing the data then I may lose that formula or
not cover all the new cells. Help please..

Guys, I have a report I've built that uses multiple pivot tables to organize data with Row Labels set up as Product Line and Year, Column Labels for each month, and Values as sales data, like so (hope formatting isn't off too much):

ProdA 2008
2009
ProdB 2008
2009
ProdC 2008
2009

(edit: well, crud - it isn't using the spaces; the 2009's there should be aligned under the 2008's, sorry. And those are both the Row Labels - to the right then would be the actual values in subsequent columns).

Now, if I let the pivot table do a grand total at the bottom, it sums through all those cells. What I *need* is two grand/sub total lines the bottom - one that sums all of the 2008 values only, and another that sums all of the 2009 values only (for comparison/growth calculations).

I'm wanting this because the pivot table (and sub-totals) needs to be dynamic; if we introduce a new product line, the sub-totals need to allow it - I've done this right now by just adding a couple lines with SUMIF statements to get the subtotals I want, but if a new product line is introduced, updating the pivot table will wipe out and overwrite the non-pivot table lines below it.

Any suggestions as to how to add these subtotals (at the bottom of the pivot table) and have them be *part* of the table so they expand/contract as the content of the pivot table does?

Thanks!

I have a workbook that has four worksheets each associated to a different team. Then there is a summary worksheet that summarizes what is on the other sheets. On the team worksheets there is a column that has certain task IDs and a column with a Time Entry ID. THe summary tab contains the same columns but there is also a column which identifies which team owns that task. In another workbook there is a pivot table that has totals for those task Id/Entry ID combinations.

What I want to do is write a macro that will look at the task ID and Entry ID in the pivot table and retrieve the total. Then I want it to search the summary table for the same combination and find the team that owns that combination. THan I want it to take the total that it retrieved and put it in the correct row for the taskid/time entry column on the specific team worksheet.

Using Excel 2003; I have a pivot table that has a Grand Totals Column. I am trying to
reference that complete column in another sheet. (I do some Countif &
Sumif Formulas on that column) Everything works fine until the source
data changes and the pivot gets updated, which either shrinks or
enlarges the pivot table. When this happens my links to the Grand
Totals Column aren’t valid anymore. How do I accommodate for this
occurrence.
Thanks for any help.

Hi. I've attached a pivot table and am hoping someone can show me either how to insert a column next to the Grand Total dollar amount for the quarter which shows percentage for the quarter ONLY and not the months or split the Grand Total column. I'm attempting to get a total for the quarter ONLY on the percentage and do not need the percentage monthly as it takes up more space than I have on my dashboard. I tried the calculated field option but I'm fairly new to pivot tables and couldn't figure out how to get it working. The other option that would work if it's an option is to split the GRAND TOTAL column to show the dollar total on one side of the split and the percentage on the other side of the split. Is it even possible? Thanks in advance!

Hello everyone:

I have posted this question in 2 other forums and nobody seems to understand what I am trying to convey.

I have a pivot table and I would like the table to give me the Grand Average just as it gives the Grand Total, i.e. Grand Total divided by the number of column items displayed.

My source data is structed as follows in the PT: ID # in the Row field, Qtr in the column field and Sum of Price in the Data field. The Grand Total displays the Total sum of price for each ID# over all the qtrs displayed. I want the Grand Average to display the total sum of price for each ID# divided by the number of qtrs displayed.
If I use the Average function for the price rather than the Sum function, it finds the average price of each ID# in the Qtr, that's not what I want it to do. Say I have 20 ID 1s with a sum of Price of $200 for qtr1, the average function gives $200/20=$10, instead, I want the PT to give me total sum being $200 and then divide this by the number of qtrs displayed, in this case only qtr1 is displayed. I can decide to display just 2 qtrs. If the total sum for both qtrs is $500, then the Grand average should be $500/2=$250.

I hope someone will be able to help me out. Thanks a lot in advance.

Kemi

Hi, I have a simple pivot table with two columns A and B against several rows
of responses. The pivot table also shows the Grand Total Column.

I can graph the pivot table quickly and simply with the chart wizard (a bar
chart in this instance) and it will display a bar for column A and a bar for
B grouped per row response. Great.

I want to add what is effectively the third column in the pivot - Grand
total column, so I would have 3 bars per row response; A, B and Grand Total.
I can then compare A and B to the total bar.

How do I add/show the grand total in the pivot chart?

Thanks!

Hi,

I'm using Excel 2000. My pivot table contains calculated fields, the problem
I'm experiencing is that the grand total for the field is almost 2 times
higher than it should be. However, if I look at the individuals rows that
calculation is correct, if I sum the calculated field column (with exception
to the grand total) the sum is correct but the highlighted column doesn't
match it's grand total....not sure why.

I'm not user if the problem is with my source data worksheet, the only thing
I'm doing which maybe an issue is- for the rate column, I have it equal a
cell that's resides on another work sheet.
--
Linda

Hello,

I am unable to get the the grand total column on the right as shown below. I checked the 'Table Options' and the 'Grand totals for columns' is checked. So I am notsure what else to check.

Thanks
SJ

Data
CUSTOMER WO# Sum of LAB COST Sum of LAB OVHD Sum of MAT COST
FIENG 29599 96,745.00 145,114.00 110,742.00
48804 3,864.00 5,796.00 537.00
49537 - - 3,714.00
49626 18,778.00 28,167.00 8,641.00
FIENG Total 119,387.00 179,077.00 123,634.00


I'm using XL 2002 and I have a pivot table that has "Grand totals for rows"
turned on. This works perfect. However, amongst the rows of data, I have
one row that is static...it simply shows the 'goal' for the respective
column. I don't want that row to show anything in the Grand Total column.
Is there a trick way to defeat the appearance of the Grand Total for just
that row. Goal doesn't need to have any type of calculation performed on
it...it's simply a reference number for the related dat in the its respective
column that it appears in.

Example:

Item Q1 Q2 Q3 Q4 Grand Total
West 10 20 30 40 100
South 15 25 35 45 120
Goal 5 5 5 5 <---- Have nothing appear here.

Any help would be greatly appreciated.

Hi Guys,

I've got the below code that I recorded on the macro recorder (ugh) it worked fine but now it's broken and it totally confuses me! See the bottom of the post for a link to mrexcel where there's a copy of the source data

It's working but it's giving me:Data Export Import Import/Export (blank) Grand Total Count of 00 - 30 Days 3473 3398 1 6872 Count of 31-45 Days 3473 3398 1 915 7787 Count of 46-60 Days 3473 3398 1 915 7787 Count of 61-90 Days 3473 3398 1 915 7787 Count of 91-180 Days 3473 3398 1 915 7787 Count of 180 Days and Over 3473 3398 1 6872

What I need it to do is give me the SUM of the data not the count. I also need it not to have a blank column as that's strange?! I just literally need import and export. This is what I get for using the recorder but I literally had no idea on pivot tables and VBA!

Code:pivot source data.xls


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have posted this on mr excel under: http://www.mrexcel.com/forum/showthr...27#post3001227

Please help if you can, much appreciated!

I am using pivot table and on the grand total column I want to have a row to have average instead of sum. For example, i have data of headcount, revenues, hours by weeks and on the grand total column, I want them to be sum of all weeks but not for headcount. For headcount, I want to have headcount by weeks but the grandtotal column, I want it to be average of all weeks in the month. What can I do? Thanks.

I have a worksheet that lists various which sales persons have completed a survey. I want to display a table (by sales office) that shows the percentage of completed surveys. I think what I need to have is the count of completed surveys and the count of surveys sent to each sales office summarized on the same row of the pivot table. Can this be done with a single pivot table?

I can do this easily with two pivot tables. The first will count how many survey returns by sales office. The second will count how many surveys offered to each sales office. I get the answer by dividing the grand total column of the first pivot table by the grand total of the second pivot table.

However, since I would like to chart this analysis, the optimum solution would be to use a single pivot table so I can then sort the order of sales office by the percent completed.

Hi

See below - If I sort Cust# on Sales with Top 10 on, it will sort on Grand Total column. How can I sort on, say 200801 Sales, but also display Grand Total numbers (which is effectively YTD).

Any help would be appreciated, thanks

******** ******************** ************************************************************************>Microsoft Excel - CHC Customer Sales.xls___Running: 11.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutG2=
BCDEF2Financial YYYY2008 3 4Sales Period 5Customer numberCustomer200801200802Grand Total6HEB100Heb March Joint Venture$50,338$106,933$157,2717CIT110City Care Ltd - Christchurch$55,540$97,729$153,2698SIC300Sicon Ltd Darfield$108,247$26,621$134,8689LAI100Laing Contractors Limited$47,416$58,822$106,23810WOR450Downer EDI Works Ltd Christchurch$39,379$66,296$105,67511TRE250Trenching Dynamix Ltd$16,694$56,341$73,03512TRE251Pegasus Trenching Dynamix Ltd$21,008$51,390$72,39813CRT150CRT Society - Card Division$35,314$34,263$69,57714MIC500Mico Christchurch$19,892$40,651$60,54315THE150The Isaac Construction Co Ltd$13,093$46,328$59,42116Grand Total $406,921$585,374$992,295Sheet1
[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I used a Formula in a Pivot Table and the the total for the formula Column
is INCORRECT as it reflects only one portion of the formula. My formula is
an "if statement" that if one field is "0" use the other preexisting field.
Is there a way to have the total reflelct all amounts and not just the total
of certain amounts??

Hello -

I have a pivot table and in the column area I have a field called type. I
have two types that appear RD and RQ. My first issue is I don't want them
added together, and want to get rid of the grand total field in each row of
data. My second question is I want to show the results of RD/RQ as a
percentage and want to show the values of RQ and RD as well as the %.

Thanks for any help.

Marc

hello there,

i am in need of help. i need to automate an excel file so that the sheets that are automatically generated get automatically renamed upon selection and a pivot table has to automatically be generated too and also a table has to be updated too.

please help.

attached to this msg is the coding that has already been done up but i have to put in the other necessary codings.

the instructions on what is supposed to be "automated are listed below:

1.) Double click on grandtotal of the file Nostro Stats(Assignmed Group) - Sheet 1(with details will be shown). Rename to Details.

2.) Create pivot table based on data from new renamed tab.

Fields to add: OLT_GBM, Assigned Group, Individual Amount, AssignedGroupManager, Ageing, Individual GBP Equiv

3.) Rename sheet with Pivot Table to "Pivot Table".

4.) Based on the Pivot Table details, create and the table in "Summary" sheet.

5.) At Pivot Table sheet, upon double clicking on "grand total", a new sheet will be created which has to be renamed to "Breakdown".

Please help me as i am a noob.

thank you very much

regards

sam

Sub RefreshAllPivots()

'Dim pt As PivotTable
'' Variable to store the Pivots in the workbook
'
'Dim ws As Worksheet
'' Variable to store the worksheets in the workbook
'
'    For Each ws In ActiveWorkbook.Worksheets
'    ' we are iterating through all the worksheets in the active workbook
'
'        For Each pt In ws.PivotTables
'        ' Among the worksheets iterated in the outerloop we are checking for the pivots only
'            pt.RefreshTable
'            ' if there are any pivot then this line of code will refresh the first pivot stored
'            ' in the variable pt
'        Next pt
'        ' Iterating to the next pivot
'
'    Next ws

Sheets("Nostro Stats Summary").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Nostro Stats - Prior Day").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh


Sheets("Summary").Select
MsgBox "All Pivots Refreshed"

End Sub


Sub Save_Report()

Dim savepath As String

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   
    savepath = Range("svpath")
    
    Sheets("Nostro Stats Summary").Select
    Range("J2:K2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayHeadings = False
    Range("A1").Select
    
    Sheets("Nostro Stats (Assigned Group)").Select
    Range("J2:K2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayHeadings = False
    Range("A1").Select
    
    Sheets("Nostro Stats - Prior Day").Select
    Range("J2:K2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayHeadings = False
    Range("A1").Select
    
    Sheets("Internal Control Accounts").Select
    Range("I2:K2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayHeadings = False
    Range("A1").Select
    
    
    Sheets("Nostro Stats Summary").Select
    'Application.GetSaveAsFilename "Daily_Nostro_Escalation.xls", "Excel files (*.xl*),*.xl*", 1,
"Save Today's Nostro File"
    Sheets("Summary").Delete
    
    'ActiveWorkbook.SaveCopyAs "T:Business Development and
MIPersonalSatheeshNostroAnalysisAutomationNostro_OutstandingReports" & _
    "Daily_Nostro_Escalation_" & IIf(Len(Day(Date)) = 1, "0" & Day(Date), Day(Date)) &
IIf(Len(Month(Date)) = 1, "0" & Month(Date), Month(Date)) & Right(Year(Date), 2) & ".xls"
    
    ActiveWorkbook.SaveCopyAs savepath & _
    "RBS PLC Daily_Nostro_Escalation_" & IIf(Len(Day(Date)) = 1, "0" & Day(Date), Day(Date))
& IIf(Len(Month(Date)) = 1, "0" & Month(Date), Month(Date)) & Right(Year(Date), 2) &
".xls"
    
    ActiveWorkbook.Close saveChanges:=False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Report Saved"
    
End Sub


I used a Formula in a Pivot Table and the the total for the formula Column
is INCORRECT as it reflects only one portion of the formula. My formula is
an "if statement" that if one field is "0" use the other preexisting field.
Is there a way to have the total reflelct all amounts and not just the total
of certain amounts??

I wonder if anyone can help me. I have a spreadsheet (in Excel 2007) which is extracted from another source. This data originally comes into excel in a list with three columns (Quantity, Date and Customer Part Numbers). Within the list the quantity is the number sold, the date is just the month and year ie nov 09, and the part number is its reference. In some months there can be five different entries for the same part no but different quantities. I have managed to create a succesful pivot table to show the part number in column A, then the totals of the order go across the page.

ie

col a col b col c col d col e col f
NOV 08 NOV 08 NOV 08 DEC 08 DEC 08
BA101 22 50 40 22 24
BD509 42 48 30 12 20

What I need is a way to total up the month as a summary and hide or remove the duplicate information. for example for BA101 I want it to say BA101 NOV08 total of 112 (22+50+40) and for DEC08 46 (22+24)

I cant make the columns total a summary and remove the duplicate columns. I just want the totals of the month as a summary with the part number to display on a graph later.

Any help would be greatfully appreciated. I have attached a copy of the file, with the sheet 1 containing the original data. Sheet 3 contains the current pivot table created. I want it like this, but to summarise the months as a whole ie row 7 for product titled 17 - has order of 446+38=484, I need some VBA coding to make it display summary totals for product ordered in same month. I had shaded examples where multiple orders show, but need it to count summary for each month for each product.

Thanks in advance.

In attached Excel 2007 file the following problems present:

1. One column with MATERIAL (Part #)
2. Another column could have THREE different dates corresponding to that same material (within three different fiscal years to complicate matters).
3. Column with reason codes used for filtering in pivot table.

*** see columns marked in RED in sheet named "Data" ***

What I need:

1. The existing pivot table is filtered by column "Reason Code" and needs to remain so.

2. I need to have three columns (2008, 2009 and 2010) for the column with the dates "VALID TO". Whereas the fiscal year goes from Sep to Oct. Meaning if a VALID TO date for a materials states "12/31/9999" this belongs in fiscal year 2010. "VALID TO" 09/30/09 belongs in 2009, "VALID TO" 09/30/2008 belongs in 2008 and so forth. There is sheet for this to be added. I played with various scenarios but these are just not working.

3. I need to be able to have the three different dates on the SAME row, not in staggered rows. Meaning: If there was a change made in 08, 09 and 10 then all three years should have the LAST DATE of "VALID TO" in the field - not ALL dates available. As I said, some materials may have multiple dates - I only want the last one UP TO 09/30 of THAT fiscal year. Sometimes there will only be a change in one or two fiscal years.

4. The Pivot Table needs to be dynamic so that if I make a new download of new data it should automatically adjust to the different size of the new database.

For an Excel guru with expert pivot table skills this should be cinch to do in an hour or less. I am (obviously!) no expert and boss man is driving me nuts to make this happen today.

I had to zip the file because it's over 2 MB's, sorry. Uploading doesn't seem to work right now but I'd be happy to send the file to you if you PM me.

If anyone wants to earn a quick buck today I'd even be willing to pay for the help!!

Hi, Hope someone can help.

I have a couple of pivot tables on individual worksheets.

1. Sales forecast with item number and description as rows, columns are date grouped by month and year. The values are units forecast to be sold. The user currently enters a date range in a message box and I then run a sql query using the data as criteria. The forecast covers a period of about 6 months in the past up to about 18months in the future.

2. Actual Sales. Same data but obviously we only have actual sales up to the current month.

I need to retrieve the unit sales figure and unit forecast figure for each month and copy them across to a summary sheet one under the other. I cant for the life of me figure out an easy way to do this.

The summary sheet needs 2 rows for each item. One with the forecast figure from the forecast pivot table for that month and one with the actual sales figure from the sales pivot table. there would then be columns for each month of data that I have.

Can someone point me in the right direction of a function I can use to achieve this.

Thanks in advance

For a two-column pivot (one Data field, one Row field) I can sort the Grand Total column by highlighting the Row and Data ares and doing a Sort by column B. But how do I sort by the Grand Total column when I have a more complex Pivot Table? For instance, in the attached example, within each Unit I'd like to be able to sort by the number of FTEs in that unit. How would I be able to do that? And how about even more complex pivot tables?

Your help is greatly appreciated.

Thanks,
John


No luck finding an answer? You could always try Google.