+ Reply to Thread
Results 1 to 3 of 3

How to get pivot table to include new data added after the pivot table was created

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Norwich
    MS-Off Ver
    Microsoft 365
    Posts
    12

    How to get pivot table to include new data added after the pivot table was created

    I am new to pivots but can see how quick and useful they can be.

    I have a list of of events on my spreadsheet that so far go down to row 146. These events happen continuously so one of my column headers is "Year" and I list what year it happened. So far I have done 2009 / 2010. Eventually I will bring it fully up to date to 2014. Once up to date, I will list new entries as they occur.

    I have set up several pivot tables as a means of learning how to do pivot tables. In the drop down for Column Labels it currently has the options of 2009 / 2010 / blank. I now want to add the data for 2011 and beyond. I did the first few and then looked at the pivot tables. It did not add 2011 to the option or any of the data. Is there a way that I can get a pivot table to include data added after it is created? When I created the pivot table I didn't just select the range of cells that had data in them at that point (A2:K124) . I selected the column headers (A:K) thinking it would utomatically expand as new data was added to the selected range.

    Is there a way of getting the pivot table to include new data?
    Thanks for your help

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,966

    Re: How to get pivot table to include new data added after the pivot table was created

    See Debra's excellent tutorial here.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: How to get pivot table to include new data added after the pivot table was created

    Quote Originally Posted by CDobby View Post
    Is there a way of getting the pivot table to include new data?
    click the "Refresh" button in the "Data" pane in the "PivotTable Tools: Options" ribbon

    or keyboard shortcut ALT+F5

    That will tell the Pivot Table to go back and re-fetch the data range you fed into it.

    Also,

    Note that telling the pivot table to use A:K is an error; probably what actually happened is it fetched from row 1 to 2^16 or whatever the limit of the spreadsheet you created it in was.

    You might want to consider putting your data in a predefined autotable and then feed the table as a named range into the pivot table instead of just giving it a monster range. The autotable will automatically expand if you concatenate data on the bottom. (If you add/remove columns from the autotable that will automatically change when you refresh the pivot table too).
    Last edited by ben_hensel; 09-03-2014 at 10:56 AM.

+ 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. Replies: 6
    Last Post: 02-23-2024, 02:11 PM
  2. [SOLVED] In a pivot table, can the avg of a column include rows w/o data?
    By Aaron in forum Excel General
    Replies: 3
    Last Post: 04-04-2016, 03:14 PM
  3. Replies: 3
    Last Post: 03-28-2014, 12:04 PM
  4. [SOLVED] Pivot Table - Added data
    By lyn402 in forum Excel General
    Replies: 1
    Last Post: 05-13-2012, 11:45 AM
  5. macro with a pivot table where data is constantly added
    By mariusescu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2008, 02:38 PM

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