Free Microsoft Excel 2013 Quick Reference

Calculating difference on Pivot Table Totals

I have a pivot Table as follows

Region State jan Feb ... Columm
North NY 1 5
NJ 5 1
North Total 6 6

South FL 2 6
GA 6 2
South Total 8 8

Grand Total 14 14

I would like to change the display of Grand total to show the difference
between the region as follows

Grand total difference (2) (2)

Any help on this is greatly appreciated.


I have one example pivot table report. In the source worksheet, there is
columns named as Account and Department.

When used this data(worksheet) as pivot table source data, on pivot table
feilds list there are two feilds created named as Account2 and Department2. I
want to know how I can create and use the feilds in pivot table report those
have no columns in source data.

I want to use a pivot table for a monthly timesheet. As I work on a task I
record the hours spent on that Task as "Time" in my source data. The pivot
table shows me the Time spent on each Task per Month. The pivot table has a
calculated field with the following formula:

=ROUNDUP(Time*Rate,-1)

The gives me a $ figure rounded up to $10 multiples.

However, the pivot table totals appear not to sum the rounded up values,
rather they appear to sum the source data and then round up. This results in
column totals that do not match the sum of the rows below them. Is there a
workaround for this?

Hi,

By default, the Grand total on Pivot table sums up the value on the column or rows.
If I want to find the difference & put it on the garnd total how will I dfo it ?

Hello,

Is it possible to create a pivot chart to get the grand total subtract from 100% (eg 660), and also place a target line (eg 89% of 660 = 587), and a 100% line on the graph.
The result at the moment is the sum of a number of minutes per day. (eg 25) (This is calculated by the pivot table)
So I would like that I see 635 in stead of 25, and if it is possible that this number is converted to 96% from 660 in the displays.
The problem, for me, is that 25 is a calculated number and not directly from the data retrieved. How can I use the calculated data, and use the result.
Attached the file with the PivotChart and data.
Thanks for helping,

aisietieResJaar 2011.xlsx

I am having issue with subtotaling few calculated fields in pivot table. We generate report from ERP system that provides following items in Excel:

Customer Name Invoice Number Invoice Date(when the invoice was created) Sales Order Number Aging Date ( date when the report is exported to Excel) Invoice Amount (fnamount) Days Late ( Aging date invoice date)
My report attempts to find invoices amounts that are due between 0 days to 30 days past, 31 days to 60 days, 61 days to 90 days and 90 days and above. I use if formula to properly categorize my due payments into correct column.

Here is the example of my If formula:
0 to 30 = IF('Days Late'30,'Days Late'

First off, I'd like to say I'm new to the board, and the information here is top notch. Thank you to all the people that take time out of their busy schedules for helping others out.

Is there a way to have my pivot table totals (just the values) automatically copy to a cell in another worksheet (within the same workbook)? I want the new totals to copy to a different cell on my summary worksheet everytime I change my pivot categories.

My Pivot Table has drop downs for different sales people and different transaction types. I have a summary sheet that contains all the sales people that summarizes the totals for each transaction type based on the pivot table. What I'm doing now is copying and pasting the values, and it takes forever.

I can not get the GETPIVOTDATA function to accomplish it for me.

How do I perform extended calculations in a Pivot Table in Excel 2003?

From the raw data in a worksheet, I created a Pivot Table shown below. How
do I:

1. add columns to the Pivot Table which would show me the additional
calculations?

2. Make these additional calculations dynamic so that whenever the original
worksheet is updated, the Pivot Tables and the calculations are updated or
refreshed automatically.

Fail Pass Grand Total
Jan-2008 4 6 10
Feb-2008 10 20 30
Mar-2008 3 17 20
Apr-2008 2 28 30
May-2008 1 39 40

Grand Total 20 110 130

The calculations I would like to perform a

1. A column adjacent to the Pass column showing the cumulative Pass
2. A second column to the right of the Grand Total showing the cumulative
Grand Total
3. A third column showing the calculated ratio of the Pass to Grand Total
column
4. A fourth column showing the calculated ratio of the Cumulative Pass
column to the Cumulative Grand Total column.

Currently, I am copying the Pivot Table and creating the percentages using
the Field Settings Option in this table. Additionally, I am manually creating
another table showing the cumulative totals and the corresponding percentages.

Please suggest a good book or reference on Pivot Tables that would cover
this type of topics and more!!! Thanks in advance.

As above.

There is a code:

For i = 1 To iDataCols / 2
With
ActiveSheet.PivotTables(sDatabase1Name).PivotFields(FieldNames(i))
.Orientation = xlDataField
.Caption = "Sum of " & FieldNames(i)
.Position = i
.Function = xlSum
End With
Next i

Every iteration needs more and more time, because pivot table is
recalculated in each iteration.

Is there any way to switch it off similar to Application.Calculation =
xlCalculationmanual.

Application.Calculation does not work on pivot tables.
Thanks

Rather than the total of Sold and Capacity at the bottom of this pivot table, I want to work out the absolute difference in Sold and Capacity numbers between the 2 report dates.

Any help would be much appreciated.

Regards

I use excel often but am not an advanced user. I have the following data in a list, have successfully developed a pivot table but the one thing I can't seem to generate is the %'s I'm looking for. I am trying to show %'s for employee hours based on a time card hour "code". I have 3 code types: Billable, Not Billable, and Unapplied - those are in the "row" area. Within each code type I have "code descriptions" such as 01, 02, 03, etc (these are also in the row area). Employee Names are in the column area. The hours in the data area. I want to take the total of the Billable Hours and compare them to the total of the Billable plus the Unapplied Hours for EACH employee plus showing a total for ALL. I also need to take ALL of the 3 code types and compare them to the "available" working hours. I've tried about every way possible (I have 5 books on pivot tables!) that the books say I should be able to make %'s, using the formula and options features, etc. and I can't. I've resorted to making the %'s outside the table. Hope someone can give me a SIMPLE, if possible, answer. Thanks, Patti

Hi,

What I required is either a Macro or Code for formulas in column 'F' in the attached spreadsheet that correspond to the SUM of each description and divided by 37.5 e.g. in F10 the formula should be =D10/37.5 the formula should be F12 D10/37.5 and so on all the way down the Pivot table

My problem is as the amount data increases on the Data Tab the formulas in column 'F' will become out of line with the corresponding Sum of each description so I guess I need some code or formula that check every time the Pivot table is refreshed.

I would be most grateful for any assistance in the this matter

NB Excel Version 2003.

Regards

Rob

Hi All ,

Any one know the above mentioned ? I am not able to locate the " calculate filed " in pivot table.

PAul Yeo

In Excel 2007, how do I set the calculation method in pivot tables so the
default is "count" (rather than "sum," "average,", etc)?

I am using Excel 2000, is it possible to set up a "YTD" calculation within a
pivot table? If so, could you explain how?

I need to show on the report fields of pivot table only de data that are on
DB, if a new information change on DB i need to refresh on pivot table, not
only values, besides the data.

I am trying to do cycletime calculations in a pivot table. For each order there would be a step with a date it was completed. I would like to show those and add an additional field showing elapsed time between those dates. I have mocked up and attached an excel spreadsheet in how I would like it to appear.

My problem is I cannot seem to get dates to show in the body of the pivot table.

Is this possible?

I am using Excel 2000, is it possible to set up a "YTD" calculation within a
pivot table? If so, could you explain how?

I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request. Unfortunately, every time I do it I get the result #NA. I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result

Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?

Thanks

I can’t believe I’m having trouble doing something which should be so easy – working around Excel’s only being able (I believe) to sum calculated fields in pivot tables. The attachment describes the problem – how do I construct pivot table column D calculated values to display each employee’s average ratio of monthly sales to annual target? I assume (hope) there’s an easier way than adding those calculated values to the data source.

Thanks very much.

Hey guys,

I have a problem with the formatting on pivot tables.

Here's the issue.
I have a pivot table that is connected to a view in a SQL database.
That table has user names in rows.
I applied formatting of my own to all of the pivot table (like inner and outer gridlines).

The problem is whenever I open the excel , connect to the view again and refresh the data i have issues with formatting.

Such as, let´s say I have a new user called Peter. He is added to the the table as the last row, like after Andy despite the column having sort from A-Z activated.

The other issue is this new user and all the data inherent to him are not formatted accordingly (no inner or outer borders around its cells).

I have "Preserve cell formatting on update" option of the pivot table turned on but this still happens.

Can anybody help me?

Is this possible to solve?

Thank you in advance

I have a spreadsheet showing Total Sales for Rep and Quarterly Quota.
I am also calculating number of weeks remaining in each quarter. When
remaining weeks = 1 I set the value to 1. In the Pivot Table I'm
showing what the Quarterly Quota is and the Total Sales per Quarter.
Then I have two calculated fields:

Difference (Quota - Total Sales) per quarter

Sales Per Week (Difference / Weeks-Left) This is sales needed per week
to meet quota

Everything works great except Total Sum of Sales Per Week not summing
correctly.
Should be $810 + 180.60 = $991.55.
I am assuming I can create a calculated field from another calculated
field but maybe that's my problem.

SREADSHEET:
Total
Date Rep Item Sales Quota Weeks-Left
-----------------------------------------------------------------------------------------------------------------------------------
1/1/2005 Jones $1,000.00 1
4/1/2005 Jones $1,200.00 2
7/1/2005 Jones $1,400.00 0
10/1/2005 Jones $1,600.00 0
1/6/2005 Jones Pencil $189.05
4/1/2005 Jones Binder $299.40
6/8/2005 Jones Binder $539.40
8/15/2005 Jones Pencil $174.65
9/18/2005 Jones Pen Set $255.84
10/2/2005 Jones Pen $575.36
2/18/2006 Jones Binder $19.96
7/4/2006 Jones Pen Set $309.38

PIVOT TABLE:

Rep
Years Date Data Jones Grand Total
---------------------------------------------------------------------------------------------------------------------------------
2006 Qtr1 Sum of Quota $1,000.00 $1,000.00
Sum of Total Sales $189.05 $189.05
Sum of Difference $810.95 $810.95
Sum of Sales Per Week $810.95 $810.95
----------------------------------------------------------------------------------------------------------------------------------
Qtr2 Sum of Quota $1,200.00 $1,200.00
Sum of Total Sales $838.80 $838.80
Sum of Difference $361.20 $361.20
Sum of Sales Per Week $180.60 $180.60
-----------------------------------------------------------------------------------------------------------------------------------
Total Sum of Quota $2,200.00 $2,200.00
Total Sum of Total Sales $1,027.85 $1,027.85
Total Sum of Difference $1,172.15 $1,172.15
Total Sum of Sales Per Week $390.72 $390.72

Any ideas?

I HAVE SEARCHED AND SEARCHED, but haven't been able to stumble into the right topic to find my answer, but before I start this might sound complicated because I like to be detailed, but it should be fairly easy because I've come very close to accomplishing my goal.. I also want to mention that the amount of raw data on sheet 1 changes every month, so I have a dynamic named range in order to expand down as many rows as it needs to... (I am very un-talented when it comes to excel, so I only have a mild understanding of what this function actually is capable of).

On sheet 1 there are 5 total columns.

Column K (Value) is a calculation of the difference between the Ship Date (Column J) and the Date Requested (Column I). There is Conditional formatting on column K that distinguishes if it is early (less than -7 days, yellow) on time (in between and equal to -7 and 7, green) or Late (greater than 7 days, red)

Column L (Category) is a simple calculation that displays the name Early, On Time, or Late. which I am currently using to populate a pivot table on sheet 2.

Now let me explain what my workbook does as of this moment. When I am on sheet 2 I have a Macro set up that allows you to refresh the data in the pivot table and chart formatting. The pivot table currently counts the amount of Late, On Time and Early deliveries separated by months (with regard to The SHIP DATE, NOT the Date Requested), and also displaying years to avoid confusion (even though the current raw data on sheet 1 is only in one year, 2010, this is subject to change in a few months, so i want to be prepared for a larger quantity of row data).

Now THE PROBLEM IS that I never accounted for column H (untitled, but it is the Delivery Quantity). Which means that the Pivot table on Sheet 2 is counting only a single amount for each Late, On Time or Early delivery. I need to find a way to multiply each row of raw data by its corresponding delivery quantity in column H in order to achieve accurate delivery quantities on the pivot table while still being separated by months..

Template MACRO Assignment.zip

I would love to stay close to this formatting if I can, but I am open to all suggestions, and I want to thank everyone who takes the time to help me out in advance! you guys are the best!

Is there a way to create a chart based on data in a pivot table but have it place the data in different series?

I have a pivot table calculating the sum of total data for a "Rule", and the sum of data for the number of times the "Rule" was rejected.

If I have 2 rule, the chart currently comes out as 2 lines (In a line chart). I need 4 lines:
1 Line for rule1 rejected
1 Line for Rule1 total
1 Line for Rule2 rejected
1 line for Rule2 total.

I have attached a sample workbook. It has raw data and a pivot table (grouped on dat by 7 day period). It has the chart generated when using the chart wizzard. And then a "Desired Chart"

I use excell quite a bit but am new to pivot tables. I understand how to set them up etc. Where I am stumped is generating %'s for my data. Even though I have FIVE, yes five books on Pivot tables I can't figure this out. What I have is: (this is a hour labor report based on time card codes) in the page area: # of possible individual hours, in the column area: Code type: Billable, Not Billable, Unapplied. and Code Description 01, 02, etc which designates the labor task within the code type, In the row area: employee names. In the data area: hours. What I am trying to do is get a % of the 3 codes types compared to the # of possible individual hours. I also need to compare the TOTAL of the code types to TOTAL of the hours. I DO NOT need to compare all the individual code descriptions, just the 3 major code types. I have tried different configurations of the layout, as well as setting up my own formulas and using the field settings calculations. NOTHING WORKS. I hope someone can help me. Simple answer (if possible) please.