Undoubtedly, the Pivot Table is an essential tool in Excel to summarize a larger dataset effectively. However, users may get troubled as the Pivot Table is not refreshing automatically. In this article, I’ll point out 5 issues for which Pivot Table is not refreshing with their solutions.
Pivot Table Not Refreshing: 5 Issues & Solutions
This is our today’s dataset where the Product Category, Quantity, and Sales are given based on states.
You may visit the How to create a Pivot Table article to insert a Pivot Table for the above dataset. Already I have created a Pivot Table which is as follows.
Assuming that we need to add 3 more rows after the existing dataset, that is to say, we can check the issues for what Pivot Table is not refreshing and find out the solutions of those.
Let’s go into the main discussion.
1. When New Data Is Not Included While Refreshing
After adding that new data, I pressed the Refresh button by right-clicking over a cell within the Pivot Table (keyboard short is ALT+F5) like the following screenshot.
Unfortunately, the Pivot Table does not update with the new data, which means the refreshing option is not working well.
So how can we fix the problem?
Just follow the steps below.
Steps:
⏩ Select a cell within the created Pivot Table.
⏩ Click on the Change Data Source… by from Change Data Source in the PivotTable Analyze tab.
⏩ Then, you’ll get a dialog box, namely Move PivotTable. Also, fix the new range of source data as $B$4:$E$15, and press OK.
⏩ Eventually, the output will look as follows where a new column of states namely Arizona, and the new data are visible.
You may visit the How To Update Pivot Table article to explore other efficient methods e.g. creating an Excel table & dynamic range, using the OFFSET function & VBA code to refresh the Pivot Table automatically.
2. Showing PivotTable Error Message While Refreshing
Likewise, in the process of refreshing as discussed in the previous section, I want to refresh the Pivot Table but Excel shows the error message “The PivotTable field name is not valid”.
If you look closely at the following screenshot, you’ll see that the field States is not there.
Hence, it is clear that the Pivot Table does not work or refresh unless the field name is available.
However, if you want to settle the problem, you need to add the field name.
Provided that the field name States is added, and after pressing the Refresh button, the output will look as follows.
If you wish to explore more issues regarding the field name, you may visit the Causes & Corrections for Pivot Table Field Name is Not Valid article.
Read More: Automatically Update a Pivot Table When Source Data Changes in Excel
3. Issue of Overlapping and Pivot Table Not Refreshing
Now, I’ll show you a different issue while refreshing the Pivot Table. To do that, we have to add new data where the Product Category is new. That means I want to create more rows in the Pivot Table.
Right away, if we wish to update the Pivot Table using the Refresh button, Excel shows the error message for which reason the Pivot Table does not update.
In a single word, we can say the problem is “overlapping”.
Actually, there are two Pivot Tables in the current worksheet.
If we want to refresh the PivotTable1, it will extend over the below different Pivot Table.
But it is not possible as Excel does not allow this overlap.
Therefore, when we move the PivotTable2 to the cells below and refresh the PivotTable1, the following output will be seen.
The red-colored new data created an overlapping issue for the immediate below PivotTable2
After moving the table, the PivotTable1 is refreshing well.
4. Issue of Pivot Table Not Refreshing When Workbook is Opened
Sometimes we use some functions like TODAY & NOW in our dataset and reports which are changeable over time. In such a situation, we need to refresh the Pivot Table every time, which is really a boring task.
Rather than, we may use the following option to update the whole workbook when users open the workbook in Excel.
For doing this, you have to click PivotTable Analyze > Options.
Then check the box before Refresh the data when opening the file option.
Read More: How to Auto Refresh Pivot Table without VBA in Excel
5. Issue of Format Changing While Refreshing Pivot Table
Currently, I’m discussing another issue of formatting, which may be changed while refreshing the Pivot Table.
For example, the column width is 12 and the cell border is available in the following Pivot Table.
Now, if we wish to refresh the Pivot Table by clicking the Refresh button, you’ll see the following output where the column width and other cell formatting changes.
To fix the problem, we need to open the PivotTable Options by right-clicking after selecting a cell within the Pivot Table.
In the PivotTable Options dialog box, uncheck the box before the Autofit columns widths on update option and check the box before the Preserve cell formatting on update option.
If you do that, the output will be just as follows, where the column width and cell formatting still do not change.
Download Practice Workbook
Conclusion
These are the issues and their solutions for what the Pivot Table is not refreshing. Obviously, I believe this article might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.
