The error check is completed for the entire sheet.
What is the fastest way to find what is causing this problem?
Excel in Office 365 ProPlus, Windows 7.
5 Answers 5
The problem was caused by a chart with empty data ranges. This potential cause was mentioned in this forum.
This is caused by empty values in named ranges/cells.
To debug, go to Formulas > Name Manager. Look for Named Ranges with an empty value. Either change the where the reference points (the «Refers to:» column) such that it pulls in a value, or delete the reference if it is not needed.
Be careful deleting named ranges, as you can cause some downstream #REF errors.
The issue may not be with the workbook that is generating the error.
In my case, I had another workbook open that a colleague sent me, which caused the error message to appear every time I added a row to any data table in any other workbook I had open. When I closed that other workbook, the error message stopped appearing in the other workbooks.
So, if you have multiple workbooks open, pinpoint the problematic workbook(s). If you know which workbook is the problem, you can follow the advice in some of the other answers.
Excel Found A Problem with One Or More Formula References in This Worksheet [Solved]
You receive the error Excel found a problem with one or more formula references in this worksheet while saving the Excel workbook. Read this post to know how to fix Excel found a problem with one or more formula references error. If you have excel file loss problems, download EaseUS Excel recovery and repair software to get back Excel files.
Excel Found a Problem with One Or More Formula References!
«One of my least favorite messages that Excel provides is: Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct. But I can never find the bad references. It’s usually buried somewhere very deep, in a chart series formula, or a name definition, or who knows where else».
Here are some methods that you can follow to fix the error «Excel found a problem with one or more formula references in this worksheet».
Workable Solutions
Step-by-step Troubleshooting
Isolate the Spreadsheet Tab
Copy your tabs from the old workbook to a new one. Keep checking which tab. Full steps
Check Formulas in Excel
Go to Formulas and click on the «Error Checking» button. This runs a scan. Full steps
Check the External Links
Go to the «Data» Tab. In the Connections group, click on «Edit Links». It opens. Full steps
Check Your Charts
If you are still experiencing the fault, you can try to review the charts. Full steps
Other Workable Solutions
Check Pivot Tables. Full steps
Run Excel Repair Software. Full steps
Solution 1. Isolate the Specific Spreadsheet Tab
The first thing you need to do is isolate the specific Excel sheet. If you have a file with lots of tabs, you may want to start by isolating the specific tab to solve the case. It’s easy to isolate the tab.
Step 1. Copy your tabs from the old workbook to a new one.
Step 2. Keep checking which tab in the new workbook throws the error message.
Step 3. Click «Save» after copying each of the tabs. The error message usually appears at the time of saving the event. Then, you can find the problematic tab.
This will help you find the faulty sheet from the workbook that you can review. In case you can’t fix the error, back up the specific sheet and remove it after the backup.
Solution 2. Check Formulas in Excel
Small and silly formula errors in excel can also lead to big consequences. To avoid such a case, it’s advised to check your formula and make it error-free.
Step 1. Go to Formulas and click on the «Error Checking» button
Step 2. This runs a scan on the sheet and displays the issues.В
If no issue is found, it displays the following message:
«The error check is completed for the entire sheet.»
Then, you can try to save the Excel file again. If the error message persists, proceed to the next method.
Solution 3. Check the External Links
The next option to solve this issue is to find the external links that have errors. Follow the steps below to check out the external links containing errors:
Step 1. Go to the «Data» Tab.
Step 2. In the Connections group, click on «Edit Links». It opens the Edit Links dialog box will list all the workbooks that are being referenced.
Step 3. Check the links. If you find any faulty link, remove it and then save the sheet.
Solution 4. Check Your Charts
If you are still experiencing the fault, you can try to review the charts to solve the problem. As you know, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.
Here is a list of area locations where you need to check for errors.
Solution 5. Check Pivot Tables
There could be an error in your Pivot Table’s Data Source formula. To check Pivot Tables, follow these steps:
Step 1. Go to «PivotTable Tools > Analyze > Change Data Source > Change Data Source…»
Step 2. Check out whether any of your formulas are having the error or not.
Solution 6. Run Excel Repair Software
Step 1. Select a location to start finding corrupted Excel files
Launch EaseUS Data Recovery Wizard, and then scan the corrupted or damaged Excel files.
Step 2.В Scan for the corrupted Excel document
Find the damaged files through the filter, file path, search, etc.
Step 3.В Repair and save the Excel file to a secure location
Double-click the corrupt excel file, and this program will automatically repair corrupted and damaged Excel files. Click «Recover» to save the repaired Excel files to a safe location.
Conclusion
This article has provided you with almost all the fixes to troubleshoot this error «Excel found a problem with one or more formula references in this worksheet» error. But the solutions may lead to a partial loss of information. Therefore, you’d better make a backup to external devices in advance.
Was This Page Helpful?
EaseUS Data Recovery Wizard is a powerful system recovery software, designed to enable you to recover files you’ve deleted accidentally, potentially lost to malware or an entire hard drive partition.
EaseUS Data Recovery Wizard is the best we’ve seen. It’s far from perfect, partly because today’s advanced disk technology makes data-recovery more difficult than it was with the simpler technology of the past.
EaseUS Data Recovery Wizard Pro has a reputation as one of the best data recovery software programs on the market. It comes with a selection of advanced features, including partition recovery, formatted drive restoration, and corrupted file repair.
A blog focused primarily on Microsoft Excel, PowerPoint, & Word with articles aimed to take your data analysis skills to the next level. Learn anything from creating dashboards to automating tasks with VBA code!
Fix Excel Found A Problem With Formula References In This Worksheet
How To Troubleshoot This Persistant Error Message
If you are reading this article you have probably been frustrated with a persistent error message that Excel keeps showing you stating:
Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.
Well, thank you Excel for giving us a blanket, catch-all statement that is essentially telling us, «I’ve found an issue with your spreadsheet, I’m not going to tell you where it is or even what type of object is causing this. but enjoy scouring the entire file! Oh, and I almost forgot, I’m going to keep reminding you about this error until it’s fixed.»
So after clicking OK about ten times, you’ve probably more than a little time trying to figure out what is causing this message to keep popping up. Well, I have been in your shoes feeling the exact frustration and hopefully I can guide you through the different parts of your spreadsheet that may be housing this invalid formula reference. Best of luck!
Isolate The Specific Spreadsheet Tab
Sometimes if you have a enormous file with lots of tabs, you may want to start by isolating the specific tab(s) that are causing this error message. You can do this by copying over your tabs one-by-one and determining which tab in your new workbook triggers the error message. Make sure you save after each tab is copied over in case the Saving event is what triggers the error message.
Check External Links
Navigate to your Data Tab >> Queries & Connections >> Edit Links and see if any of your links contain errors in them. If you find any, simply remove (or break) those particular links. This may or may not solve the issue, but at the very least you are getting rid of some garbage that could causing corruption to your file down the road.
Check The Name Manager
It is a good idea to scroll through the Name Manager (dialog box that allows you to manage all your named ranges) and see if any of the formulas associated with your Named Ranges include errors (ie #N/A, #REF, #VALUE!, #NAME?, #DIV/0). Go ahead and delete all of the Named Ranges with errors and see if that action fixes your issue. There is even an option to filter on Named Ranges with errors as shown below that can make this process even faster.
Excel Found A Problem with One Or More Formula References in This Worksheet
Updated on November 26, 2020
Summary: The error ‘Excel found a problem with one or more formula references in this worksheet’ may appear while saving the Excel workbook. It occurs when Excel found a problem with the formula used in the sheet. However, it may also occur when the Excel workbook gets damaged or corrupt. In this guide, we’ve explained the reasons that may lead to this Excel error and methods to resolve the error, by using various Excel options and a third-party Excel file repair software.
If you are experiencing the ‘Excel found a problem with one or more formula references in this worksheet’ error message in the Excel workbook, it indicates that the Excel file is corrupt or partially damaged. However, it may also occur due to incorrect reference to a wrong cell or object linking, which is not working. The complete error message says,
‘Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.’
In any case, resolving the error is critical as it doesn’t let you save the file and may result in loss of information from the Excel workbook.
Reasons for Excel Formula References Error
A few reasons that may lead to such error are as follows,
Methods to Resolve ‘Excel Found a Problem with One or More Formula References in this Worksheet’ Error
Following are a few methods that you can follow to fix Excel file that can’t be saved due to problems with one or more formula references in the worksheet.
Method 1: Check Formulas
If the problem has occurred in a large Excel workbook with multiple sheets, it’s quite hard to pinpoint the problem cell. In such cases, you can use the Error Checking option that runs a scan and checks for a problem with formulas used in the worksheet.
To run Error Checking in the Excel sheet, follow these steps,
The error check is completed for the entire sheet.
In such a case, you can try saving the Excel file again. If the error message persists, proceed to the next method.
Method 2: Check Individual Sheet
The problem may also occur due to an issue with one of the sheets in the workbook. To find the faulty sheet and fix the problem, you can copy each sheet content in a new Excel file and then try to save the Excel file.
This will help you find the faulty sheet from the workbook that you can review. This method makes the entire process of troubleshooting Excel formula reference error quite easy and convenient.
In case the error is not fixed, you can back up the faulty sheet content and remove it from the workbook to save the Excel file.
Method 3: Check Links
When the Excel file contains external links with error, MS Excel may display such error messages. To check and confirm if external links are causing the error, follow these steps,
Method 4: Review Charts
You can review the charts to check if they are causing the formula reference error in Excel. It may take a while based on the size of the Excel file. Sometimes, it’s not practically possible to track down which Excel chart object is causing the error. Thus, you need to check specific locations, such as:
Method 5: Check Pivot Tables
To check Pivot Tables, follow these steps,
Method 6: Use Excel Repair Software
When none of the methods resolve the error, then you can rely on an advanced Excel repair software, such as Stellar Repair for Excel. It’s a powerful tool that is recommended by several MVPs and IT administrators for resolving common Excel errors, such as ‘Excel found a problem with one or more formula references in this worksheet.’
It repairs corrupt or damaged Excel (.xls/.xlsx) files, recovers Pivot tables, charts, etc., and save them in a new Excel worksheet. It helps Excel users, facing formula reference error, restore their Excel file without any risk of data loss, while preserving the sheet properties and formatting with 100% precision.
Conclusion
Although the error ‘Excel found a problem with one or more formula references in this worksheet’ can be resolved by using various options in MS Excel, it may lead to a partial loss of information. Thus, you must perform these operations after taking a backup of the Excel worksheet. Also, if the MS Excel options fail to resolve the problem, you can use an Excel file repair software, such as Stellar Repair for Excel. The software helps fix Excel file corruption and restores the information and data from corrupt or damaged Excel files (.xls/.xlsx) to a new worksheet.
Excel found a problem with one or more formula references что делать
This forum is closed. Thank you for your contributions.
Asked by:
Question
I am opening an Excel 2000 workbook on Excel 2007. This workbook contains several worksheets with charts, shapes, formulas and defined names. When I save the workbook in Excel 2007, it gives me the message «A formula in this worksheet contains one or more invalid references». It really does nor matter what worksheet I’m on while I try to save the workbook. Excel does not pinpoint the error which is not at all useful. How can I find the source of this annoying message?
All replies
Home Ribbon > Editing tab > Find And Select dropdown > Selection Pane.
Click each chart currently displayed on the sheet, and rename it over in the pane in order to differentiate the currently displayed charts from the extras in the list.
Once you rename each current chart, you know the rest are all old unused objects, and delete them by selecting them in the pane and pressing delete.
Do this for each sheet with chart objects, and save the workbook. The errors should no longer appear.
Finally found the problem, it was in one of my data series within one of the file’s charts.
I isolated the problem by:
1. Created a backup copy of the workbook and worked on it.
3. Delete series by series in the chart till I found the problematic one and fixed the invalid reference within it.
4. Fixed the problem in the original file.
Good tips. I had exactly the same message but in Excel 2010.
Main difference here is that the error message only seems to pop up when you are on the tab with the error in. So there’s no need to start deleting tabs etc. I may be wrong here but that’s certainly what I found.
Next up is that the error did indeed relate to the charts that I had on that tab. Again, that removes the need to start looking at all the numbers and formulas that you have on the page.
Finally, and this one was bizarre, it turned out that for some reason Excel had overlaid 2 additional charts onto the ones that I had placed there. It was these 2 charts that had the errors in. All I had to do was delete the 2 charts and as if by magic, there was one chart left underneath that had no errors in it.
Seemed like a bizarre solution in the end, but that’s how it worked for me. Find the tab (easy), look at your charts and have faith in deleting them. You may find as I did that you have to delete them twice until you reveal your original error free version hidden underneath them.
