Pivot table does not include new data from the table source when refreshed

Hi, my guess is that the pivot table source range doesn't include the whole range, go to change source range and check if all the range is included. to be able to get the pivot table to included all the range, proceed as explained by Debra using dynamic data source

http://www.contextures.com/xlPivot01.html

Hi,

yes,

if your Pivot Table was created from 100 rows
then every new row 101,102,.. in Source Table,

is not included in Pivot Table, after refreshing.

Hi,

Thanks very much for your prompt reply.  We used to use the Offset function to create our dynamic data ranges (and when I'm delivering training I still encourgage people to do so).  However, my colleague has been using a table named Table1.  This is because she understands Format as Table better than dynamic data ranges.  In the past, the same pivot table has, upon refresh, picked up the new data in Table1.  The pivot table is definitely using the table named Table1 as its source.  The Table1 itself is performing as I would expect a table to do when new data is added.

If you've any other ideas I'd be grateful.  Else, I'll convert her data to a dynamic range and make it look and operate like a table!

Many thanks

Karen

Convert your data range as a Table (Tab Insert/Table), It will format your table colored with Headings and alternate rows in different color.

Now insert pivot table based on this table, so now whenever new rows/data will be added that will become part of Table and hence also will be updated in Pivot Table while refreshing,

So with this your range will become dynamic.

Hi,

Thank you very much for your prompt reply to my question.  I agree that if the pivot table was based on a static data range then it would not pick up the new data.  However, the data range has been converted to a Table which is supposed to be dynamic.  We have created dynamic data ranges in the past; but my colleague prefers to use the Format as Table option for her data.

In the past the addition of new records via copy, paste and remove duplicates followed by a pivot table refresh has worked nicely.  I'm unsure why it would be different now.

Perhaps I'm lacking in my understanding of using the Table functionality of Excel.  Could it be that there are limits on Excel Tables?

I'm greatful for all of your ideas. 

Thanks very much.

Karen

Hi Vijay,

Thanks very much for your reply. 

The table we're using has already been converted to a Table. The Table is called Table1 and the pivot table is definitely using it as its data source.

As you've rightly said, the source should then be dynamic and it has been, up until now.  We're wondering what oculd have happened that makes Table1 behave as it should; but the pivot table not see the new rows upon refresh.

If you have any further ideas I'd love to hear them.

Many thanks

Karen

Try this, go to the table and then in the ribbon click on summarize with pivot table, create a new pivot and see the results, maybe the problem is how you add and delete duplicats from the table data in the table

Hi,

Thankyou very much.  We're going to try that way now. 

What we're finding odd is that my colleague who uses this workbook has been following the same procedure to:

• Select data from Snap
• Use transfer it to a spreadsheet
• Copy it
• Paste it into the Table (she has been using paste rather than paste values etc.)
• Use the Data tab to remove duplicates (where the whole row is duplicated)
• Refresh the pivot table

it has always worked until now.  I'll let you know how we get on.

Thanks again.

Karen

As 'Remove duplicates' being used from Data/DataTools when we have the option in Table.

It means it may be that the additional rows copied are not part of Table, Just see whether are you able to use remove duplicates from Table Ribbon after giving a duplicate row in the last.  Also go to last right cell of last row and click Tab and see whether it is creating a new row automatically with the same formatting.

Also you may check that in excel options/Proofing/Auto correct Options/Auto format as you type/ Ensure that all 3 options, particularly the 2nd one are checked.

Hope you find something to fix it.

Hi Vjay,

Apologies for the late reply.  I've been delivering training.

Thank you for getting back to me.  It is appreciated.

I've tried what you've asked and these are my findings:

• The table expands when I press the TAB key in the last cell
• Remove duplicates removes any new entries if they are duplicates
• When removing duplicates the format of the table isn't always as it was.  Because some rows have been removed there can be several shaded rows together (I'm sure that must be normal)
• The tick boxes you mentioned in Options/Proofing/Autocorrect Options/Autoformat as you type are ticked

Unfortunately it is still returning incorrect data.  You can imagine our frustration.

Thank you very much for your help.

Karen