Forum Discussion
Excel Pivot Table shows all records but one row is displaying blanks for some columns
Hello,
My Excel pivot table returns all rows that should be returned according to filters applied directly to column filters in the pivot table itself, or via some Slicers that I've created off of this pivot table. For 2 particular Business Units, there is one row (again, that is properly returned as a row in the filtered pivot table - i.e., no 'missing' records) that shows values in the first 4 columns of the pivot table, but does not display the values in the subsequent 9 columns, despite the fact that the data is fully populated in the Source data. The one source field I've added to the 'Values' section of the pivot table (Report Count) does display values as expected.
Troubleshooting tried so far:
- I've ensured that my Named Range data source is properly capturing the entire source dataset - i.e., no dataset rows or columns left off
- I've refreshed my pivot table numerous times after changing filters directly in the pivot table itself, as well as changing the filters in Slicers built off of that pivot table.
- In the Pivot Table Options, on the 'Data' tab: the 'Number of items to retain per field' is set to 'None'. And the 'Save source data with file' check box is not checked off.
- I've deleted/re-added the source data for the problematic, 'invisible' pivot table columns (not showing the data as expected) and refreshed the pivot table, but that did not correct the issue.
- I inserted a blank row adjacent to 1 of the 2 problematic records in the source data and copied the data from the problematic record into it, and deleted the original record and refreshed my pivot table, which also did not remediate the problem.
- I've also built another pivot table in the same workbook on a different Excel worksheet (using the same Named Range data source) and added all the same fields for one of the problematic records and they are all displaying normally.
- The only error message of any kind that is generated is when I go to one of the problematic records in the pivot table itself, select one of the cells in that row that is displaying a blank and then try to click on the Formula bar - the error message is, 'We can't change this part of the pivot table'. But, I believe this is a normal message if Excel senses that you're trying to update data in a pivot table. There are no error other messages or difficulty refreshing to report.
Other relevant points:
- All told, I have 239 total rows in my source data, and all rows appear normally in the pivot table with the exception of these 2 problem records.
- There have been no other performance/display issues of concern outside of this one.
- This Excel workbook is mainly utilized on SharePoint by multiple Users, but for more complex updates/troubleshooting, I am easily able to bring it down to work in Excel Desktop as needed - and this problematic display with these 2 records is present regardless of whether I'm working with the file on SharePoint or with Desktop Excel.
I can't think of what else to try at the moment - I've never seen anything like this in my 25+ years of working with Excel. I hope I'm missing something simple - any troubleshooting suggestions are greatly appreciated!
1 Reply
- PatDoolsBrass Contributor
OK - solved it (using the term 'solved' very loosely)... It was a Display setting on the Pivot Table. I didn't realize that the 'Show Expand/Collapse buttons' option had been activated. Thus, my Pivot table was showing the correct records, but whatever column had been collapsed, all the columns to the right would then become 'invisible'. I was clearly not paying attention to the Expand/Collapse buttons. This is the Setting that (in my opinion) should remain unchecked to avoid Users accidentally 'hiding' data and not knowing how to un-hide it:
Learn from my pain. 😆