Home

Excel Update Pivot Table Source Not Working

%3CLINGO-SUB%20id%3D%22lingo-sub-856255%22%20slang%3D%22en-US%22%3EExcel%20Update%20Pivot%20Table%20Source%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856255%22%20slang%3D%22en-US%22%3E%3CP%3EFolks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20adding%20Pivot%20table%20source%20update%20comment%20in%20case%20others%20have%20the%20same%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EProblem%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20created%20a%20pivot%20table%20whose%20source%20data%20changed%20because%20I%20added%20rows%20to%20the%20source.%26nbsp%3B%20When%20I%20clicked%20on%20the%26nbsp%3B%20%3CU%3EChange%20Source%20Button%3C%2FU%3E%20to%20extend%20the%20pivot%20table%20source%20to%20the%20additional%20rows%2C%20the%20%3CU%3EChange%20PivotTable%20Source%3C%2FU%3E%20window%20popped%20up%2C%20but%20Excel%20did%20not%20return%20to%20the%20source%20data%20tab%20with%20the%20current%20row%20and%20columns%20marked.%26nbsp%3B%20Instead%2C%20it%20just%20remained%20on%20the%20Pivot%20table%20tab.%26nbsp%3B%20When%20I%20manually%20entered%20the%20new%20table%20source%20rows%20and%20columns%20on%20the%20%3CU%3EChange%20PivotTable%20Source%3C%2FU%3E%20form%20and%20clicked%20ok%2C%20Excel%20erased%20my%20Pivot%20table.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3ENormal%20Operation%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExcel%20is%20supposed%20to%20take%20you%20to%20the%20source%20table%20tab%20with%20the%20current%20selection%20marked%2C%20where%20you%20can%20either%20mark%20the%20new%20source%20rows%20and%20columns%20with%20your%20mouse%20or%20manually%20enter%20the%20rows%20and%20columns.%26nbsp%3B%20When%20you%20press%20Ok%20on%20the%20Change%20PivotTable%20Source%20pop-up%2C%20Excel%20is%20supposed%20to%20update%20your%20Pivot%20table%20with%20information%20from%20the%20new%20source%20rows%20and%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3ESolution%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENavigate%20to%20the%20Excel%20Options%E2%80%99%20Data%20tab%20and%20uncheck%20%E2%80%9CPrefer%20the%20Excel%20Data%20Model%20when%20creating%20PivotTables%2C%20QueryTables%20and%20Data%20Conversions.%E2%80%9D%26nbsp%3B%20If%20you%20need%20to%20leave%20this%20option%20checked%2C%20be%20careful%20to%20uncheck%20%3CU%3EAdd%20this%20data%20to%20the%20Data%20Model%3C%2FU%3E%20on%20the%20Create%20PivotTable%20pop-up%20when%20creating%20a%20new%20Pivot%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-856255%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivotTable%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857877%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Update%20Pivot%20Table%20Source%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408727%22%20target%3D%22_blank%22%3E%40Bill-895%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWorking%20with%20data%20model%20you%20may%20select%20to%20change%20connection%20properties%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20256px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132002i69DECD1A67BC9FC4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20here%20change%20the%20range%20on%20another%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20296px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132003iF3C0DEB390B2873D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EEven%20better%20to%20work%20with%20tables%20or%20named%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bill-895
New Contributor

Folks, 

I'm adding Pivot table source update comment in case others have the same problem.

 

Problem

I created a pivot table whose source data changed because I added rows to the source.  When I clicked on the  Change Source Button to extend the pivot table source to the additional rows, the Change PivotTable Source window popped up, but Excel did not return to the source data tab with the current row and columns marked.  Instead, it just remained on the Pivot table tab.  When I manually entered the new table source rows and columns on the Change PivotTable Source form and clicked ok, Excel erased my Pivot table.  

 

Normal Operation

Excel is supposed to take you to the source table tab with the current selection marked, where you can either mark the new source rows and columns with your mouse or manually enter the rows and columns.  When you press Ok on the Change PivotTable Source pop-up, Excel is supposed to update your Pivot table with information from the new source rows and columns.

 

Solution

Navigate to the Excel Options’ Data tab and uncheck “Prefer the Excel Data Model when creating PivotTables, QueryTables and Data Conversions.”  If you need to leave this option checked, be careful to uncheck Add this data to the Data Model on the Create PivotTable pop-up when creating a new Pivot table.

1 Reply

@Bill-895 

Working with data model you may select to change connection properties

image.png

and here change the range on another one

image.png

Even better to work with tables or named ranges.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies