Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Pivot Table Retains Old Source Data in Addition to New Source Data

  1. #1
    Join Date
    19th August 2006
    Posts
    60

    Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi All,

    Not sure if anyone has encountered this before. I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a "pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

    Does anyone know how to purge the old data from the internal Pivot Table memory?

    I hope this is enough information....let me know if you need more.

    Thanks in advance for any help,

    Jon

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,749

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    To manually clear the old items from the list,first drag the pivot field out of the pivot table. on the Pivot toolbar click the Refresh button, finaally drag the pivot field back to the pivot table
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi,

    Try,

    VB:
    Sub DeleteMissingItems2002All() 
         'prevents unused items in non-OLAP PivotTables
         'http://www.contextures.com/xlPivot04.html
         'in Excel 2002 and later versions
         'If unused items already exist,
         'run this macro then refresh the table
        Dim pt As PivotTable 
        Dim ws As Worksheet 
         
        For Each ws In ActiveWorkbook.Worksheets 
            For Each pt In ws.PivotTables 
                pt.PivotCache.MissingItemsLimit = xlMissingItemsNone 
                pt.PivotCache.Refresh 
            Next pt 
        Next ws 
         
    End Sub 
    
    

  4. #4
    Join Date
    19th August 2006
    Posts
    60

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi Again,

    Thank you for your help. Both methods appear to get the old items off of the list. Unfortunately, my file size is not coming down (the 2005 file is just over half the size of the updated 2006 file). I guess its just an internal issue with Excel and whatever memory/database it creates for the pivot table. I wonder if there is a way to bring this up to Microsoft...have either of you every done anything like that?

    Thanks again,

    Jon

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Under the Pivot Tables Options there are choices under Data Options.
    Last edited by Dave Hawley; September 8th, 2006 at 12:26.

  6. #6
    Join Date
    19th August 2006
    Posts
    60

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi Dave,

    Thanks for your reply. I've got the "Save Data with Table Layout" option unchecked on both the old 2005 report and the updated 2006 report. For some reason the 2006 report is just much larger when I save it (even having cleared out the old 2005 items). My worry is that I'll either have to recreate the report each year (its huge to begin with) or that it will just keep growing and growing each year.

    Thanks,

    Jon

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    This often happens if you include mnay extra rows in your PT Source Data range. The best way is to use a Dynamic Named Range and base your PT off that, e.g =MyRange

    Also, see this page Unusual Excel File Size Increase

  8. #8
    Join Date
    19th August 2006
    Posts
    60

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi Dave,

    I can't seem to get my pivot table to work with the dynamic range. The data for the pivot table is stored in an external workboook (report in one workbook and data-source in a second workbook shared by multiple reports)...could this be causing the problem?

    Also, a side question: Is there a defined Microsoft track for becoming something like a "Certified" Excel or Office developer? I have a CompSci background and started out after school working in a Financial Environment -- my position didn't involve any development work so I found myself using Excel VBA as an outlet for my skills. I'm now working as a business/revenue analyst in a company's Sales department. I've gotten pretty good at Excel and VBA but have had no formal training. I like Excel and figure that since this is the path that I am headed down I figure I should formalize my training to carve out a specific career path for myself (I've been out of school for about 3.5 years now and I'm not headed in any specific direction...I feel that I would be more successful if I had a longer-range career path laid out).

    Thanks,

    Jon

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi John

    Yes, that would be the problem. Dynamic ranges wont work when reference from another Workbook. However, go here Excel Hacks Workbook Download Examples and donwload Chapter 4 and it shows how you can easily pull in the needed data using Workbook Open Event

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    Oops, forgot. Please start a New Thread for your question regarding certification etc so the Thread Title matches.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Pivot Table field not in data source
    By Dog in forum Excel General
    Replies: 6
    Last Post: 4 Weeks Ago, 23:34
  2. Delete Pivot Table Source Data
    By DWildman in forum Excel General
    Replies: 2
    Last Post: June 1st, 2007, 10:27
  3. Reformat Data for Pivot Table Source
    By ryckiej in forum Excel General
    Replies: 4
    Last Post: May 31st, 2007, 11:24
  4. Pivot Table Refresh and Data Source
    By Alphy in forum Excel General
    Replies: 2
    Last Post: November 16th, 2005, 22:24
  5. Finding the data source of a pivot table
    By DavidgCZ in forum Excel General
    Replies: 2
    Last Post: October 22nd, 2004, 18:04

Bookmarks

Posting Permissions

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