To convert a pivot table to a flattened table in MS Excel
Hie friends, I m currently using MS-Excel 2007 and I'm tiresome to convert the data from a pivot table into a flatten table. I have attempt to copy as well as pasting the cells of the pivot table into a new worksheet, but then I am gone with blank cells in the column on the left-hand surface of the table. I'm thinking that if there is a way to convert a pivot table into a flatten table without losing the underlying data (i.e. no blank cell should be there after copy/pasting). So anyone has any idea about quote what is the solution must be? If so then please reply to my post.
Re: To convert a pivot table to a flattened table in MS Excel
A copied Pivoted table forever will have unfilled or empty cells in the area of
the criterion - this makes a pivoted table much easier to read, particularly when
you have very complex criteria’s.
To fill those empty cells you could try out the following code:
- Select the area or the columns with the require cells
- run the following code:
Code:
Sub Fill_empty_cell()
If Selection.WorksheetFunction.CountBlank(.Cells) > 1 Then
Selction.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Selction.Value = Selection.Value
End If
End Sub
Re: To convert a pivot table to a flattened table in MS Excel
If you copy a whole table, when you paste you get the whole table. So I am doubt what's happening when you say the left hand side of the table is blank.
Set your cursor in the pivot table as well as choose PivotTable Tools, then go to Options then Select the Entire Pivot Table,
- Copy as well as move to where you wish for the copy and decide to Paste.
- This is a duplicate copy of the pivoted table and it is a pivot table using the same underlying data.
If you desire for a non pivot table copy then:
- Repeat step 1 above.
- Copy as well as then move to anywhere you wish for the copy and decide Home, Paste, Paste Values.
This is what I believe that you mean by flat pivot table. But the original data is no more linked to this series, but it is connected to the original pivot table. You can function against this range very soon like any standard range in Excel.
Re: To convert a pivot table to a flattened table in MS Excel
Assuming that we cannot able to upgrade: So select the flat pivot table in my prior example, without using the Report Filter, and press F5 key, particular, blank, then select OK. Then type the next three keystrokes:
= , Up Arrow, then press Ctrl as well as Enter
Choose any single cell in the pivot table and Press Ctrl and A, then Copy, then go to Home page, Paste, Paste the required Values which you have copied. This is precisely what Thomas' macro does.
There is also a MS-office 2007 way to replicate the labels in the Row Labels region of a pivot table but its deception.