work with pivot table output

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)
thfc9wigan1Asked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
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)

Thanks
Rob H
0
 
thfc9wigan1Author Commented:
Rob
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.
0
 
Rob HensonFinance AnalystCommented:
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!

Thanks
Rob H
0
 
thfc9wigan1Author Commented:
didn't really resolve my issue, but thanks for trying anyhow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.