Help me pivot the pivot table back!

June 20, 2012 1:20 PM

How to reformat pivot table data so that it works like any other spreadsheet? Simple instructions needed.

Here is the deal: I know nothing of pivot tables. Someone else gave me a spreadsheet with the raw data and the pivot table; my boss wants me to cut some lines out of the pivot table so that we can recalculate the numbers. However, I can't do this. Also, the source data does not actually include all the lines that appear on the pivot table, so I don't understand how to modify it to get the desired results.

IE, on the pivot table, it shows that four cats each have a climbing tower and a cardboard box. But on the raw data, it shows that while all four cats have climbing towers, only two cats have cardboard boxes. So I don't even know where the "all four cats have cardboard boxes" data is coming from on the pivot table.

What I would like to do is copy the pivot table in some way that turns in into just a regular spreadsheet that I can manipulate - delete numbers, delete lines, etc. (We would like to do calculations based only on the number of climbing towers and subtract all the cardboard box lines.)

Can I do this? If so, how? Please give me very simple "and then you highlight the lines"-level instructions, as I have not used excel for anything more complicated than basic formulas before.

posted by Frowner to computers & internet (6 answers total)

I copy the whole pivot table- then paste into a new sheet using the paste special "values and number formats" to do this.
posted by Nimmie Amee at 1:22 PM on June 20, 2012 [1 favorite]

Copy and Paste Special, Values,

Now you can fool around with it as much as you like.
posted by Ruthless Bunny at 1:23 PM on June 20, 2012 [1 favorite]

Oh, if you want to see where data is coming from on a Pivot Table, double click the number furthest to the right in the table and it will display separately on a new spreadsheet page.

What version of Excel is it? 2007 , 2010?
posted by Ruthless Bunny at 1:24 PM on June 20, 2012

It is 2010! I have successfully pasted now - thanks for such quick answers!
posted by Frowner at 1:26 PM on June 20, 2012

Just for anyone else's reference, sometimes you'll have to do a bit of formatting in the pivot table before this'll work- click into the table, then click the down arrows on your row labels, and go to field settings; once there turn subtotals to None and then under Layout & Print click "Show items in tabular form" and "Repeat item labels" Repeat for all row labels, and then when you copy and paste special it won't have any weird gaps in the columns etc.
posted by MangyCarface at 2:40 PM on June 20, 2012

You can actually do some slick "real-time"(ish) tricks by referencing the cells of the PivotTable elsewhere in your spreadsheet. Just food for thought.
posted by Yowser at 4:10 PM on June 20, 2012