Raiyan Zaman Adrey
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.
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.
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.
- 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.
- 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.
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.
- Insert the password and click on OK. Here we have set the password as “12345”.
- The sheet is now unprotected. Now simply, use the Refresh All button again and your data is refreshed and updated.
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.
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 SubVBA 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 StringDeclares 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 passwordUsing 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.RefreshThe 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.
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.
- 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.
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.
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.
- Now suppose, you have changed some data in the Unit Sold But you noticed that there are no changes in the Total Sales values.
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.
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.
Sub Macro1()
Application.Calculation = xlAutomatic
End SubVBA 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.
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.
- 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.
- 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.
- After uninstalling the externally installed add-in, it will disappear from the list as follows.
- 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.
- 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.
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.
