Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



To convert a pivot table to a flattened table in MS Excel

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 27-11-2010
Member
 
Join Date: Nov 2010
Posts: 9
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.

Reply With Quote
  #2  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,022
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
Reply With Quote
  #3  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,514
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,
  1. Copy as well as move to where you wish for the copy and decide to Paste.
  2. 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:
  1. Repeat step 1 above.
  2. 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.
Reply With Quote
  #4  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 996
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.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "To convert a pivot table to a flattened table in MS Excel"
Thread Thread Starter Forum Replies Last Post
How to calculate filed different in Excel Pivot Table YajasK MS Office Support 2 10-02-2012 04:20 PM
How to unpivot the pivot table in excel Raju Chacha MS Office Support 2 08-01-2012 02:13 PM
Automatically refresh pivot table on MS excel Genna Windows Software 6 10-07-2011 11:13 AM
How to copy a pivot table into a 2 dimensional table ? Udyami Windows Software 4 27-11-2010 10:35 PM
Pivot Table in Excel 2007 NIcaBoy Windows Software 2 02-03-2009 02:16 PM


All times are GMT +5.5. The time now is 09:15 AM.