work with pivot table output

Posted on 2011-10-19
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
    LVL 31

    Accepted Solution

    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

    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 31

    Assisted Solution

    by:Rob Henson
    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now