Pivot Table Not Refreshing (5 Issues & Solutions)

Undoubtedly, Pivot Table is an essential tool in Excel to summarise 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 also with their solutions.


Download Practice Workbook


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 section


1. When New Data Not Included While Refreshing

After adding that new data, I have 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 for exploring other efficient methods e.g. creating an Excel table & dynamic range, using the OFFSET function & VBA code to refresh the Pivot Table automatically.

Read more: Pivot Table is Not Picking up Data in Excel


2. Showing PivotTable Error Message While Refreshing

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

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: [Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)


3. Issue of Overlapping and Pivot Table Not Refreshing

Now, I’ll show you a different issue while refreshing the Pivot Table. For doing 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.

Read more: How to Refresh All Pivot Tables with VBA (4 Ways)


Similar Readings


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 (3 Smart Methods)


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

Read more: How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)


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.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo