Results 1 to 4 of 4

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

  1. #1
    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.

  2. #2
    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

  3. #3
    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.

  4. #4
    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.

Similar Threads

  1. How to calculate filed different in Excel Pivot Table
    By YajasK in forum MS Office Support
    Replies: 2
    Last Post: 10-02-2012, 04:20 PM
  2. How to unpivot the pivot table in excel
    By Raju Chacha in forum MS Office Support
    Replies: 2
    Last Post: 08-01-2012, 02:13 PM
  3. Automatically refresh pivot table on MS excel
    By Genna in forum Windows Software
    Replies: 6
    Last Post: 10-07-2011, 11:13 AM
  4. How to copy a pivot table into a 2 dimensional table ?
    By Udyami in forum Windows Software
    Replies: 4
    Last Post: 27-11-2010, 10:35 PM
  5. Pivot Table in Excel 2007
    By NIcaBoy in forum Windows Software
    Replies: 2
    Last Post: 02-03-2009, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •