It is a common issue that can be frustrating when Pivot Tables seem to not get updated properly. There are several common reasons Pivot Tables are not showing all the data you expect to see.
Table of contents
YouTube Reasons a Pivot Table is not showing all the data
Pivot Table not updated
The simplest step when encountering missing data is to right-click anywhere on the Pivot Table and choose refresh (as shown below). The data might not have been updated since the data source was changed, causing certain elements to be ‘missing’.
Connection and Data Source
Sometimes, duplicating a Pivot Table may result in it referencing the location of the older dataset (especially in other workbooks). It is important to ensure that when you copy a Pivot table, it is connected to the expected data source. Check the source by going to ‘Pivot table analyze’ > ‘Change data source’ .
Another potential issue might be that all the rows in the data source are not included in the Pivot Table source. If you notice missing data or incomplete information, check whether the Pivot table is limited to a certain number of rows via ‘Pivot table analyze’ > ‘Change data source’. By adjusting this range or reselecting the appropriate data source, you can include all the rows.
In more recent versions of Excel, slicers play a significant role in filtering data. It is possible for missing data to be a result of a slicer selection which is not visible on the screen.
As shown below, there doesn’t seem to be any filters on, but when you look in the field list you will see that Salesperson is filtered. In this case there is probably a slicer that is affecting your Pivot Table. You need to find it and select all items again.
Compact Form Filters
When a Pivot table is in a compact form, checking filters is not as simple as when the Pivot Table is in tabular form. In this situation, the field list can be used. Checking the field list will highlight where the filters are applied, and you can easily identify and rectify any exclusions.