Pivot Table Not Refreshing (5 Issues & Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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.

Pivot Table Not Refreshing_Pivot Table of Existing Dataset

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.

Issue of Overlapping

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.

When New Data Not Included While Refreshing

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?

When New Data Not Included While Refreshing

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.

When New Data Not Included While Refreshing

⏩ 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.

When New Data Not Included While Refreshing

⏩ Eventually, the output will look as follows where a new column of states namely Arizona, and the new data are visible.

When New Data Not Included While Refreshing

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”.

Pivot Table Not Refreshing_Showing Error Message

If you look closely at the following screenshot, you’ll see that the field States is not there.

Pivot Table Not Refreshing_Showing Error Message

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.

Pivot Table Not Refreshing_Showing Error Message

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.

Issue of Overlapping

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.

Issue of Overlapping

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.

Issue of Overlapping

Therefore, when we move the PivotTable2 to the cells below and refresh the PivotTable1, the following output will be seen.

Pivot Table Not Refreshing Issue of Overlapping

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.

Pivot Table option

Then check the box before Refresh the data when opening the file option.

Pivot Table Options

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.

Issue of Format Changing

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.

Issue of Format Changing

To fix the problem, we need to open the PivotTable Options by right-clicking after selecting a cell within the Pivot Table.

Issue of Format Changing

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.

Issue of Format Changing

If you do that, the output will be just as follows, where the column width and cell formatting still do not change.

Issue of Format Changing


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.


Related Articles


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo