Most Pivot Table Fields Disappear on Refresh/Refresh All

S_Abdullah

New Member
Joined
Oct 14, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have created a dashboard from pivot tables of demo data on COVID-19. Initially the pivot tables were from solely created from one data sheet but later other pivot tables (and charts) needed data from a second sheet. Excel created a Data Model to do this (have pivot tables from different source sheets). Now, when the data is refreshed, for one table or using refresh all, most of the fields for the numerous pivot tables disappear. (There doesn’t seem to be any pattern to the field/tables that remain.)

How do I stop this (the fields disappearing) from happening?

Note: “Autofit columns on update” has been disabled.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,675
Office Version
  1. 365
Platform
  1. Windows
Are the fields that are disappearing value fields or row/column fields ?
The most likely reason is that the column names changed.
The value fields are more likely to disappear the row/columns seem to be slightly more tolerant.

How are the underlying tables being refreshed is it possible that the headings have changed ?
 

S_Abdullah

New Member
Joined
Oct 14, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
The value fields disappear but column fields do as well.
I did need to add 3-5 new columns after I'd started creating pivot tables, so some columns moved, and needed to edit some column names due to typos. Everything seemed to have shifted without issue when I made the changes.
Does this mean I need to redo every pivot table and chart? (There are about 75 pivot tables!)
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,675
Office Version
  1. 365
Platform
  1. Windows
In the source table(s) moving columns didn't seem to worry it and renaming non-value columns didn't seem to worry it either, even when the name was used in a relationship.
Changing the name of a value column did lose the data from the pivot. (I didn't test using Explicit measures)

I think if you have lost data it would seem likely you will need to rebuild the pivots.
I would be interested to know what you are doing to end up with 75 pivot tables on the one set of data.
 

S_Abdullah

New Member
Joined
Oct 14, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

In the source table(s) moving columns didn't seem to worry it and renaming non-value columns didn't seem to worry it either, even when the name was used in a relationship.
Changing the name of a value column did lose the data from the pivot. (I didn't test using Explicit measures)

I think if you have lost data it would seem likely you will need to rebuild the pivots.
I would be interested to know what you are doing to end up with 75 pivot tables on the one set of data.
Yes, it does seem like I can't avoid recreating every pivot.

(I'm making a dashboard of COVID-19 data with 5741 individuals and almost 100 indicators.)
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,675
Office Version
  1. 365
Platform
  1. Windows
I don't think anything with that many pivots can be considered a dashboard ;)
Typically you have a summary Pivot & Chart and then you select what you want to drill further down on.
You might want to look at using Power BI for your application.

 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,235
Messages
5,768,951
Members
425,507
Latest member
AndreaWorkPlace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top