work with pivot table output

Posted on 2011-10-19
Medium Priority
Last Modified: 2012-05-12
working with a list of net wages for the year and payments made, I get a list of over/underpayments on a line by line basis.
I have then used a pivot table to produce a total for the year for each employee.
I now want to strip out the December figure to see the result for the other 11 months separately.
I list the December figure in column D, all year figure in column C, I have tried to put a formula in Column E, but cannot copy the formula.
My question is how can I copy that 'formula' in column E down ? ( it works on a line by line basis, but that is too long winded.

NB whilst writing this qn, I realised I can do another Pivot Table on the 11 months data directly which will solve the problem (although trying to be careful about spreadsheet integrity, I like to add the individual numbers and thus check I get the same total by a different calculation, which I will not be able to do once I have a Pivot table as I have the same problem -  I cannot 'manipulate' the output)
Question by:thfc9wigan1
  • 2
  • 2
LVL 34

Accepted Solution

Rob Henson earned 897 total points
ID: 36992292
Does your data have or can you add a reference to month which you could use as a column header?

You could then have all 12 columns in the table and use the Data dropdown to hide the December column, the Grand Total for the row would then only be the 11 months that are visible.

Alternatively, add a column to the data which checks if the date of the transaction is pre december and then use this column in the table instead. Such as:

=IF(Date < 01/12/2011 Then Value Else Zero)

Rob H

Author Comment

ID: 36993114
thanks for those ideas.  There are clearly several ways to do this, but my aim was partly to learn how to manipulate the pivot output (as I have had this problem before) and also to avoid changing or complicating the appearance of the main result table.
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 897 total points
ID: 36999094
I have often found the best way to get results into the pivot table is to have them included in the base data.

You can use calculated fields within the table but these rely on distinctive data columns.

As a very basic example, if you had a table of data with a column of data with Sales and a column with Expenses you could have a calculated field of "Sales - Expenses" to give Profit.

However, if you only have one column of values which are identifiable by month from another column you wouldn't be able to use "Month - Month" because it would result in zero and it would be comparing Month column values rather than the Data column values. Likewise "Month - Value" would give the wrong result.

Hope that makes sense!

Rob H

Author Closing Comment

ID: 37015040
didn't really resolve my issue, but thanks for trying anyhow

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question