Generally, Pivot Tables are among the most reliable ways to organize and present Excel data, but they aren’t perfect. Every now and then, you might run into an issue where a pivot table isn’t working correctly. This can be either the table not presenting the correct data or throwing an error. Here are some of the most common ways to troubleshoot the problem.
Contents
Fix 1 – Remove the Old Items
If you’ve heavily modified a pivot table to the point where some categories have been completely replaced, you may encounter “phantoms” of the old data in drop-down lists and filters, which may propagate into the pivot table. There’s a simple setting you can flip to remove that.
Step 1. Right-click on any cell in the pivot table, including the headers.
Step 2. Select “PivotTable Options.” You’ll get an Options pop-up box.
Step 3. Go to the “Data” tab.
Step 4. For the “Number of items to retain in the field” value, choose “None.”
Step 5. Click on “OK” and exit the window.
Fix 2 – Pivot List Item Not Showing the Right Result
By default, a pivot table should list sums of categories. However, in some cases, you might get a count of items instead. This is a relatively simple fix.
Step 1. Right-click on the field that is displaying the wrong result.
Step 2. From the context menu, select the function you want from the list.
You can use the “More options” setting to go to advanced options and customize the function that you want to calculate with.
Fix 3 – Pivot List Not Updating With New Rows
Sometimes, a pivot list won’t automatically update when new rows are added below the current dataset. Usually, this comes from the table’s source being fixed and not accounting for new rows.
Step 1. Select the pivot table (click on any cell inside it) and go to the PivotTable Analyze tab on the main toolbar.
Step 2. Click on “Select Data Source” and go to “Choose Data Source” again under it.
Step 3. You’ll get a pop-up menu that contains a “Table/Range” field. You can click on the small arrow in the field and drag over the area you want to use as the new source, then hit Enter. Alternatively, overwrite the field’s value to include the added rows.
Step 4. Hit “Ok,” and the table should update.
Fix 4 – Pivot Table Not Working With an External Data Model
If your pivot table or dataset is fetching data from another file, the link between the two files might get broken, which causes the pivot table to stop updating.
In this case, however, there doesn’t seem to be an easy fix to the issue. In some cases, disabling OneDrive automatic saving and recreating the pivot table could resolve the issue. In others, just refreshing the pivot table manually updates the data.
To make sure the pivot table is updated, you can set it to refresh when you open the file.
Step 1. Right-click on the pivot table.
Step 2. Choose “PivotTable options” on the bottom of the context menu.
Step 3. Go to the “Data” tab.
Step 4. Check the “Refresh data when opening the file” box.
Step 5. Click on “OK” and exit the pop-up menu.
Fix 5 – Pivot Table Fields Empty or Unintuitive
There are a few main reasons why a field in a pivot table might lack correct data. The first two are usually a fairly simple fix:
- Blank cells: If you have a blank field in a column, Excel will automatically try to convert all of the column information into text values, making them pretty much useless for analysis.
- Text values: When Excel encounters text values, the auto-summing function that the table will usually perform will get replaced with a count function.
To fix these cells, insert numerical values inside the column. You may need to rebuild the pivot table for Excel to reapply the auto-sum function. Alternatively, in the PivotTable fields section, drag the “Count of X” items out of the “Values” area and drop in the “Sum of X” instead.
For grouped values, you’ll need to ungroup the values and then rebuild the pivot table in the same way as before.
