Excel Refresh All Not Working (Reasons and Solutions)

Get FREE Advanced Excel Exercises with Solutions!

The “Refresh All” feature in Excel is essential for retrieving up-to-date data and sustaining correct analyses. However, at certain times this functionality might not work as expected, disrupting workflows and making it more difficult to analyze data. In this article, we have illustrated some reasons why Excel “Refresh All” not working and shown possible solutions for those problems as well.

Overview Image showing Excel Refresh all not working in a protected Sheet


Download Practice Workbook

Download this file to practice with us.


5 Reasons and Solutions of Excel Refresh All Not Working

We will be using the following dataset as an example to show you reasons why Excel refresh all is not working and then elaborate on their solutions. The dataset is representing some employee records.

Dataset to Explain solutions of Excel refresh all not working


Problem 1: If the Sheet is Protected

Let’s say, we have created a pivot table from our dataset for better analysis. To create a pivot table, just have a look at How to Create a Pivot Table in Excel. Then, for safety purposes, we made the pivot table sheet password protected (see How to Protect Excel Sheet with Password). Here, we have marked all the headers and the pivot table is as follows.

Creating a pivot table

  • Now, suppose, we have to change some values in our parent data source. Let’s say, the salary of an employee has decreased to $20000.

Changing data in the source file

  • Now, this data should be updated in your created pivot table.
  • So, switch to your pivot table sheet, and from the toolbar go to the Data tab.
  • Click on Refresh All from the Queries & Connections section.
  • But, an error message “Cannot edit PivotTable on Protected Sheet” appeared on your worksheet.

refresh all button from the data tab not working in Excel

So, in a protected sheet refresh, all is not working. Now, we will show two solutions to this problem in the section below.


Solution 1: Unprotect the Sheet Manually

So now, if you want your changed data to be updated in your pivot table, you have to make the “Refresh All” work and for that, you have to make the pivot table sheet unprotected.

  • To unprotect the pivot table sheet, go to Leaf Bar and right-click on the mouse.
  • Then click on Unprotect Sheet option from the Context Menu.

Unprotecting pivot table sheet

  • Insert the password and click on OK. Here we have set the password as “12345”.

Inserting password

  • The sheet is now unprotected. Now simply, use the Refresh All button again and your data is refreshed and updated.

Apply the refresh all button again


Solution 2: Unprotect and Refresh Sheet With VBA Macro

You can unprotect and refresh your pivot table sheet with VBA macro as well.

  • First, have to enter the VBA module by following this article How to Write VBA Code in Excel.
  • After that, simply copy and paste the following code into the module section. Then click on Run.

Inserting code in the module section

Sub UnprotectAndRefreshSheet()
Dim ws As Worksheet
Dim password As String
password = "12345"
Set ws = ThisWorkbook.Sheets("Protected Pivot Sheet")
ws.Unprotect password
ws.PivotTables(1).PivotCache.Refresh
MsgBox "Refresh All Worked"
End Sub

VBA Breakdown

Sub UnprotectAndRefreshSheet()

This line introduces the “UnprotectAndRefreshSheet” procedure. In VBA, subroutines are used to organize a sequence of commands that must be carried out.

Dim ws As Worksheet
Dim password As String

Declares a variable “ws” which is a “Worksheet” type. A specific worksheet from the workbook will be referred to by this variable. After that, it defines the variable “password” as a “String” type. This variable will hold the unlocking password for the worksheet.

password = "12345"

The password “12345” enters the variable “password“. The password should be exactly the same as you have set while protecting the sheet.

Set ws = ThisWorkbook.Sheets("Protected Pivot Sheet")

This line instructs the variable “ws” to refer to the workbook’s specific “Protected Pivot Sheet” worksheet.

Unprotect password

Using the provided password, this line unlocks the worksheet referred to by the variable “ws“. This permits changes to be made to the secured worksheet.

PivotTables(1).PivotCache.Refresh

The following command refreshes the data in the pivot table worksheet referred to by the variable “ws“.

MsgBox "Refresh All Worked"

Returns a message box with the text “Refresh All Worked” if the operation is successful.

  • Your pivot table sheet is now unprotected and refreshed at the same time.

Run the code to unprotect and refresh the sheet


Problem 2: If Source Data Tables’ Header is Changed

If any of your source datasets’ header is changed, then there requires a slight modification in the pivot table fields while refreshing your data. We are explaining this scenario for Pivot Table.

  • Assume, the previous Salary header is changed to Wages as shown below in the source dataset.

Changing the header in the source dataset

  • Now, if you click on the “Refresh All” button from the Data tab, you will see that all the information about the salary is gone.

Click on the refresh all button


Solution: Fixing PivotTable Fields

  • Simply click on the pivot table to enable the sidebar PivotTable Fields.
  • Mark all the fields.
  • Again use the “Refresh All” button and your data is now refreshed.

Modifying pivot table fields


Problem 3: Calculation Options is Set as Manual

Let’s have a look at the below dataset. We have calculated each product’s Total Sales using the base formula Unit Price*Unit Sold.

Insert a formula in the dataset

  • Now suppose, you have changed some data in the Unit Sold But you noticed that there are no changes in the Total Sales values.

changing source data

It happened because we had set the Calculation Options was Manual. We need to make this automatic.


Solution 1: Manually Set Calculation Options Automatic

  • Simply set the Calculation Options as Automatic to refresh data.
  • Go to the Formula tab in the toolbar.
  • Click on Calculation Options and mark Automatic option.

setting calculation options to automatic

This enables an auto refresh of the formulas from now on if you change any data in your dataset.


Solution 2: Set Calculation Options Automatic with VBA Macro

One problem, multiple solutions. You can activate the Automatic option using VBA macro as well.

  • Simply go to the Module section.
  • Then copy and paste the following code there. After that, click on Run.

inserting code to activate automatic calculation option

Sub Macro1()
    Application.Calculation = xlAutomatic
End Sub

VBA Breakdown

Here, Application.Calculation = xlAutomatic sets the calculation options to Automatic.

 

  • Following this will activate the Automatic option and all the formulas will auto-refresh from now on.

Run the code to make the calculation option automatic


Problem 4: Add-Ins Causing Refresh All Not Working in Excel

There are several add-ins whose installation to your Excel can cause the “Refresh All” not to work. Some of the add-ins are Data Analysis add-ins, COM add-ins, Power Query add-ins, etc. It’s not like all of these add-ins will cause problems with the refresh all feature but you gotta figure out yourself which one is causing the issue. If your workbook is not corrupted and any of the previous solutions don’t fix refresh all not working, then the most feasible way to fix the solution is to disable add-ins one by one and see which one was causing the problem.


Solution: Detect and Uninstall/Disable Add-Ins That Causing Refresh All Not Working

You can uninstall or disable add-ins easily.

  • Follow the directory to enter the add-ins menu.

File >> Options >> Add-ins

  • Here, you will see the activated add-ins. At the bottom, click on Go.

Opening the add-ins menu

  • A dialog box titled “Add-ins” will appear on your screen.
  • Here, the activated add-ins are the marked ones. You will find some of these add-ins are built-in in Excel and some are externally installed. Like here the Cdata.Exceladdin was installed externally and then enabled in Excel.

Add-ins dialog box appeared

  • What you have to do is unmark and disable those add-ins one by one and identify for which add-in the problem was occurring. You may need to uninstall the externally installed add-in from the Apps & Features or from the Control Panel of your PC.
  • Like, here you need to uninstall the Cdata.Exceladdin add-in from the Apps & Features from your PC.
  • Simply go to the Installed Apps menu in your PC settings then uninstall that add-in.

Uninstalling External Add-ins

  • After uninstalling the externally installed add-in, it will disappear from the list as follows.

External add-ins gone from add-ins list after uninstalling

  • Now, check whether the Refresh all is working or not. If it isn’t working yet, then try disabling other add-ins one by one and check for which add-in the problem is actually occurring.
  • Let’s say, unmark the Analysis ToolPak add-in and click on OK.

Disabling Analysis Toolpak add-in to see whether the refresh all is working or not

  • Check the refresh all again. If it isn’t working still, try disabling another add-in and check for which add-in this problem is appearing.

Disabling Analysis Toolpak - VBA add-in to see whether the refresh all is working or not


Problem 5: Corrupted Workbook

A corrupted workbook can prevent the “Refresh All” feature from working correctly.


Solution: Creating a New Workbook

If any solutions shown previously do not work, try creating a new workbook then simply copy your data there. This will surely work if your Excel version is compatible.


Frequently Asked Questions

  • How do I force refresh all in Excel?

For force refresh all follow the instructions below.

  • At the toolbar, go to the “Data” tab.
  • In the “Queries & Connections” group, you will find the “Refresh All” button.
  • Simply, click on the “Refresh All” button.

This will attempt to refresh all data connections, queries, and linked data ranges in your workbook.

  • How do I fix refresh recommended in Excel?

Try these steps:

  • First, go to the File tab then click on Options.
  • After that, go to the Save menu then click on Delete cached files.

If this doesn’t work, then I think you should create a new workbook and simply copy your data there.

  • Why is refresh paused in Excel?

It typically occurs when multiple users are working on the same file at the same time and they have stored the files in a shared location.


Conclusion

As discussed in this article, there are several reasons why Excel refresh all not working. But as you have seen, all of those problems have effective solutions as well. Hope, you have got what you were looking for. Visit our site ExcelDemy to explore more relevant 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.
Raiyan Zaman Adrey
Raiyan Zaman Adrey

Raiyan Zaman Adrey, armed with a BSc in Civil Engineering from Bangladesh University of Engineering and Technology, efficiently combines engineering skills with a passion for Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only tackles complex issues but also demonstrates an enthusiastic mindset, efficiently managing critical situations with patience, showcasing his commitment to excellence. He is interested in C, C++, C#, JavaScript, Python, Microsoft Office, AutoCAD, Adobe Illustrator, Data Entry, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo