Excel pivot table automatic update

Question

Until Excel 2016 it was possible to have a pivot table update after a query table had updated. This functionality no longer works and we are left with 15 years worth of developed workbooks that no longer work correctly. 

Does anyone know how to get a pivot table to automatically update after the query table it is based on has updated without using VBA? 

Specifically, the query table (list object) is returning data from a SQL server. The query table automatically updates when a parameter is changed. The pivot table that uses the query table as its data source no longer automatically updates, and requires the user to refresh it manually. 

I have logged this with Microsoft via their in application feedback option, but have received no help. 

Thank you

Richard

Answer 1

Thank you

Answer 2

Hi Richard,

We totally understand the inconvenience caused due to limitation in Excel application about updating the PivotTable automatically as soon as the data source is updated. Given situation, we suggest you provide the feedback in Excel UserVoice as related team can take into consideration about it.

So, to satisfy your exact requirement only wat is to use VBA to achieve it and apologies for everything.

Appreciate your patience.

Best Regards,

Chitrahaas

Answer 3

Hi Chitrahaas,

Thank you for your reply. The option you reference is not available - the Pivot table is not connected to the SQL data source directly, it pulls from a worksheet that has the data returned as a list object.

I have just tried to create a pivot table linked directly to the SQL server, but I do not seem to have the option to do so - when I return the data from MS Query, the option to create a Pivot Table is greyed out:

I wonder if this is due to using a parameter in the query? This always crashed previous version of Excel which is why we historically return the data to a worksheet, and then create a pivot table separately.

Even if we can get this solution to work, the users work through data quickly, and having to wait a minute for each refresh would not work - they may as well continue to manually refresh the workbook.

It is frustrating that this functionality has been changed, as not only does it fundamentally affect many of our worksheets, but the previous functionality made perfect sense.

Kind regards,

Richard

Answer 4

Hi Richard,

Sorry for the trouble caused by this at your end.

From the description, as your requirement is to update the PivotTable automatically whenever the data source is updated, there is feature in Excel application where PivotTable will be refreshed based on every time Interval we set. So, to satisfy your requirement you can set the time interval to one minute, follow the below steps:

Open the Excel file where PivotTable is inserted> Click on the PivotTable> Click on PivotTable Analyze tab> Click the down arrow under Refresh> Connection Properties> Check the option Refresh every and set the time> OK

Hope these steps will help at your end to satisfy your requirement and appreciate your understanding.

Best Regards,

Chitrahaas