+ Reply to Thread
Results 1 to 5 of 5

Pivot table and chart corrupting after pasting new data

  1. #1
    Registered User
    Join Date
    04-18-2024
    Location
    Houston, Texas
    MS-Off Ver
    365
    Posts
    2

    Pivot table and chart corrupting after pasting new data

    Hello, in the attached worksheet I have a simple data set and pivot table that I update monthly with new invoices. I've used this worksheet for almost two years, but this month the table corrupts when I attempt to include the new March data. Typically, I copy the data from the March Data tab into the Invoice Data tab, then switch to the pivot table sheet and update the data source. For some reason, the table and chart are corrupting, for lack of better term, when I update the data source to include the new data.

    I've checked that the cell formatting matches and everything I can imagine, without success. I paired this down drastically for purposes of uploading, it's typically around 100 lines per month. The data in this example through line 16 was existing data before I updated with March. Lines 17 & 18 are new data copied from the March Data tab and if you update the pivot table data source to include those lines it completely alters the table and chart. The only variable from normal was the source of the updated data so, again, I checked the cell types and don't see a difference. However, for posterity, the March data I pulled from Quickbooks Online rather than previously pulling from QB Desktop. I know that shouldn't matter, but I'm pointing it out because if I go into this worksheet and copy line 16 down to 17 instead of pasting from the March Data tab, then update the data source, it works just fine. So, it seems to be something to do with the data I'm pasting.

    I'm sure it's something obvious, but I'm completely vexed and appreciate any help!!

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,594

    Re: Pivot table and chart corrupting after pasting new data

    Hello travis1205 and Welcome to Excel Forum.
    It appears that when the new data is added to the dates become ungrouped and cannot be regrouped.
    I can't explain why that would happen; however, I can suggest using the years in column A (Invoice Data sheet) instead of grouping dates from the Date field.
    If the years in column A are not actually part of the download then you could convert your invoice data into an Excel table and populate the year field using: =YEAR([@Date])
    The formula will automatically copy down when new data is added in columns B:E and the pivot table and chart can be updated by right clicking any cell in the pivot table and selecting Refresh.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    04-18-2024
    Location
    Houston, Texas
    MS-Off Ver
    365
    Posts
    2

    Re: Pivot table and chart corrupting after pasting new data

    Thank you, I really appreciate the response. I have been traveling the last few days, but will try this when I get the chance.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,256

    Re: Pivot table and chart corrupting after pasting new data

    The dates in column B are actually text (you can see because they are left aligned in the cell by default, whereas they would be right-aligned if they were real dates. That would break your groups.
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,594

    Re: Pivot table and chart corrupting after pasting new data

    Thanks rorya.
    Yep, that dates on the March Data sheet are text.
    Text to Columns can be used to convert column B to actual dates and then the data can be copied to the Invoice Data sheet.
    I still suggest converting the range of data on the Invoice Data sheet into an Excel table and changing to the data source of the pivot table to be the name of the table on the Invoice Data sheet. That way you only have to refresh the pivot table when new data is pasted into the table on the Invoice Data sheet. Everything should work without the need of further modification at that point.
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automating Pivot table and pivot chart creation if data table names unknown
    By Vegiepie2016 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2016, 11:52 AM
  2. Pasting data all of a sudden ruins pivot table and file
    By i03theda in forum Excel General
    Replies: 0
    Last Post: 10-23-2013, 05:06 AM
  3. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  4. Replies: 2
    Last Post: 04-16-2012, 05:06 PM
  5. Cutting/Pasting data from one pivot table to another
    By Lord451 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2012, 05:40 PM
  6. Pivot table - copying & pasting the data into another sheet
    By chedges in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2007, 10:30 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1