Excel found a problem with one or more formula references что делать

Обновлено: 08.07.2024

Are you guys looking for some easy tricks to fix “Excel found a problem with one or more formula references” error? Well in that case this blog will surely help you to get rid of this persistent Excel error message.

So, read the complete tutorial and try all the given solutions for fixing up Excel formula references error. Before diving into the solution let’s run a quick eye over the scenario which many users like you complaining about.

User Overview:

Upon saving my Excel sheet, I receive the following error message:

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

“Error Checking” (under “Formulas” → “Formula editing”) returns

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.

source:

To fix corrupt Excel file, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
Preview the repaired files and click Save File to save the files at desired location.

What Does Excel Found A Problem With One Or More Formula References In Worksheet Error Means?

In Excel, formulas play a great role and thus many experts consider that Excel is all about formulas. Though Excel provides so many formulas and functions to work flawlessly in this application. But sometimes silly mistakes in these formulas start showing annoying errors like “Excel found a problem with one or more formula references in worksheet”.

Error declaration:

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

Screenshot of the error:

The reason behind getting such an error can be the following:

when your Excel spreadsheet formula having a wrong cell reference
maybe you have used object linking or link embedding OLE to the application which is not working.

Like this, there are many more reasons behind such Excel Formula References Error.

After catching a complete idea about the root causes of this excel error. Now it’s high time to run down into the fixes section. So that you can easily troubleshoot “Excel found a problem with one or more formula references”.

How To Fix “Excel Found A Problem With One Or More Formula References” Error?

If in case you have an enormous number of files with so many tabs then start isolating some specific tab that is generating this error message. Well to perform this you just need to copy your tabs from the old workbook to a new one. Keep checking which tab in the new workbook throws the error message.

Press the save option after copying down each of the tabs. As it is seen that error message usually appears at the time of saving the event.

The second option to fix “Excel found a problem with one or more formula references” is by picking out the external links having errors.

To check out the external links containing errors, you just need to follow this step:

In your Excel application, go to your Data Tab >> Queries & Connections >> Edit Links.

If you encounter any fault in the Excel external links then remove it.

Another very effective way to troubleshoot Excel found a problem with one or more formula references error is by checking out the name manager.

Then delete all the named ranges one by one which is showing such error. and after every deletion also check whether your action fixes the issue or not.

The second option is to filter out the Named Ranges having errors. Well, it is quite a faster way to check out the error showing Name Manager.

It’s a quite difficult task to track down which excel chart object is actually causing this error. Mainly when you have so many charts on the given tab.

Generally, it is seen that if a chart is having some kind of error then it will pop-up after you select the chart or when you insert the Select Data Source dialog box.

Below here is the list of area locations where you need to check for errors.

If in case all the above trick fails to identify the error causing chart. Then in that case try Copy/Pasting the chart and watch out whether the new copy is also showing the same error message.

Now, here you need to check out whether any of your formula is having the error or not.

Check Formulas in Excel

Sometimes small and silly formula errors in excel can also lead to big consequences. Whether you have misplaced the comma or entered a wrong reference; all these will severely hamper your results. So, to avoid such a situation it’s better to check your formula and make it error-free. Learn how it is to be done:

For checking out the formula of some specific cell:

Firstly, you need to choose the cell after then press the F2 key from your keyboard.

This will highlight the cells referenced in the formula. After then you can make easy check and editing of your excel formulas.

To show all Formulas

Choose any cell of your excel spreadsheet and from your keyboard press the Ctrl and tilde (

This will present you with all the formulas having on your worksheet. To go back to the normal view just press the Ctrl and Tilde (

) button once more.

You can get this tool also from the Formula Auditing group present within the Formula tab.

Whereas, in Excel terms, Dependents is used for the cells which depend on its content from another cell. If the above-mentioned formula is written within the cell C9. Then in that case C9 will dependent on C6.

For showing precedents in your workbook cell:

choose the cell having formula and tap to the Formula
Now from the Formula Auditing group select the Trace Precedents.
Arrow will indicate the precedent cells. You can toggle this feature off just by tapping on the Remove Arrows option of the same Formula Auditing group.

For using the Dependents in your workbook cell:

Choose the cells which are already referenced in some other cell’s formula.
Now go to the formula tab and then choose the Formula Auditing Group.
Hit the Trace Dependents option. This will figure out the cells which depend on that particular cell.

Automatic Solution: MS Excel Repair Tool

Besides trying the manual solution to fix Excel found a problem with one or more formula references error. If unfortunately, none of the above solutions works to fix it. In that case, chances are also that may be any of your Excel objects is somehow got corrupted. And thus, it is showing such formula references error.

To deal with such a situation we always recommend our users to go with reliable and professional MS Excel Repair Tool. This is the best repair utility to fix all sorts of issues, corruption, and even errors in the Excel file. With the help of this, the users can repair the Excel file easily and also restore the entire corrupted data including cell comments, charts, other data, and worksheet properties. it is the best professionally designed program to easily repair corrupt .xls and .xlsx files.

Steps to Utilize MS Excel Repair Tool:

Tips To Minimize The Occurrence Of Excel Formula Errors

Here are some few helpful tricks to minimize the occurrence of Excel formula errors. so, read it and do follow it.

Start every formula in Excel by putting an equal sign in the beginning.
Make use of the * symbol to apply multiplication, not an X.
Check out the opening and closing parentheses that they are in pairs or not.
Put quotation marks in formulas around the text.

Conclusion:

I have tried my level best to cover all the fixes to troubleshoot this “Excel Found A Problem with One Or More Formula References” Error. but if you come across some other issue within your Excel spreadsheet then please let us know just by asking it in our comment section.

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!

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:

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

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.

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,

Wrong formula or reference cell
Incorrect object linking or link embedding OLE
Empty or no values in named or range cells
Multiple Excel files (not common)

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,

Method 5: Check Pivot Tables

To check Pivot Tables, follow these steps,

Navigate to PivotTable Tools > Analyze > Change Data Source > Change Data Source…

Check if any of the formula used is problematic. Sometimes small typo, such as misplaced comma, can lead to such problems in Excel. Thus, check each formula thoroughly and correct the formulas wherever needed.

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

About The Author

Ravi Singh

Ravi Singh is a Senior Writer at Stellar®. He is an expert Tech Explainer, IoT enthusiast, and a passionate nerd with over 5 years’ experience in technical writing. He writes about Data Recovery, File Repair, Email Migration, Linux, Windows, Mac, and DIY Tech. Ravi spends most of his weekends working with IoT devices and playing games on the Xbox. He is also a solo traveler who loves hiking and exploring new trails. Read More

Summary: The blog outlines some quick tips to fix ‘We found a problem with some content’ error in Microsoft Excel 2013/2016. It explains manual procedure to resolve the error and also suggests an automated tool to perform the repair process to retrieve all possible data from a corrupt workbook.

Quick Fix: Try Stellar Repair for Excel software to fix all common Excel file corruption errors and restore the file data to its original, intact state. The software supports all MS Excel versions: 2019, 2016, 2013, 2010, 2007, 2003 & 2000.

What Causes ‘Excel Found Problem with Some Content’ Error?

You try opening the Excel file saved on a network shared drive.
A string is added in a cell in Excel, instead of a numeric value.
Text values in formulas exceed 255 characters.

How to Resolve ‘Excel Found Problem with Some Content’ Error?

Follow these tips to fix the Excel error:

File Recovery mode is a native Excel recovery utility that automatically opens whenever any inconsistencies are found in the worksheet. If Microsoft doesn’t detect any issue or fails to open the File Recovery mode, you can start it manually to recover the corrupt Excel file. To do so, follow the steps below:

Click on the File menu, and then select Open.
In the Open dialog box, navigate to the folder location where the corrupt Excel file is saved.
Select the corrupt file, and then click on arrow sign available next to Open button to select Open and Repair option.

Next, click Repair to recover maximum possible data.
If the repair is not able to recover the data from the workbook, select Extract Data to extract all possible formulas and values from the workbook.

If repairing the corrupt Excel file doesn’t work, you can try an Excel file repair tool to fix corruption errors. You can also try to recover data from the corrupt file manually by following the next tips.

To make the file accessible, try setting the calculation option in Excel from automatic to manual. As a result, the workbook will not be recalculated and may open in Excel. For this, perform the following:

Now, again click on the File menu and then click Open.
Navigate to the corrupt workbook, and double-click it.

When the workbook opens, check if it contains all the data. If not, proceed to the next tip.

Several users have reported that they were able to fix ‘We found a problem with some content in <filename>’ error message by copying contents from the corrupt workbook to a separate workbook. Detailed steps are as follows:

Open the Excel workbook in ‘read-only’ mode, and copy all its contents.
Create a blank new workbook and paste the copied contents from the corrupt file to the new file.

Use external references to link to the corrupted workbook. By implementing this fix, data contents can be retrieved. However, it is not feasible to recover formulas or calculated values using this solution.

Follow the steps below:

In Excel 2013/2016, click File > Open.
Navigate to the folder where the corrupt file is saved.
Right click the file, select Copy, and then click on Cancel.
Again, click on File and then New.
Under New option, click on Blank workbook.
In the cell A1 of new workbook, type =File Name!A1 (where File Name indicates the name of the damaged workbook being copied in Step 3).
If Update Values dialog box appears, click the corrupt workbook, and choose OK.
If Select Sheet dialog box appears, click the appropriate sheet, and then click OK.
Select cell A1.
Next, click Home, and then click Copy (or, press Ctrl +C).
Starting in cell A1, select area approximately the same size as that of the cell range that contains data in the damaged workbook.
Next, click Home and select Paste (or click Ctrl + V).
Keep the range of cells selected, click Home and then Copy.
Finally, click on Home, click on the arrow associated with Paste and under Paste Values click on Values.

This will remove the link to the corrupt workbook and will retrieve data. But, keep in mind, the recovered data will no longer contain formulas or calculated values.

Alternative Solution – Stellar Repair for Excel

If the above manual methods fail to fix the ‘we found problem with some content in excel error’, try using the Stellar Repair for Excel software to resolve this error. The software helps repair and recover corrupt Excel files in just a few clicks. It can be used on a Windows 10/ 8/ 7/ Vista/ XP/NT machine to repair corrupted workbook, and recover every single bit of data from all the versions of Excel workbook.

Conclusion

In this blog, we discussed some possible reasons behind Microsoft Excel 2013/2016 ‘We found a problem with some content’ error. The error may occur when an Excel file becomes corrupt.

You may try repairing the corrupted Excel file manually by using the built-in ‘Open and Repair’ feature. Or, try the manual workarounds to extract data from the corrupt file discussed in this post.

If the manual solutions don’t work for you, using the Excel repair tool can come in handy in repairing the corrupt Excel (.xls/.xlsx) file and recover the complete file data. You can download the free trial version of the software to preview the recoverable Excel data before deciding to activate the licensed version of the software.

About The Author

Jyoti Prakash

Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.

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.

Table of Contents

About the Author

Reviews and Awards

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

If you are experiencing the error - Excel found a problem with one or more formula references in this worksheet in the Excel workbook, it indicates that the Excel file is corrupt or partially damaged. Resolving the error is critical as it doesn't let you save the file and may cause Excel data loss.

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

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.

In the source data of the chart.
In each of the chart series formulas present within the Select Data Source dialog box.
Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
Also, check-in the chart's Secondary Axis.
Also check in the linked Axis Labels, Data Labels, or the Chart Title.

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

If none of the solutions can solve the error - Excel found a problem with one or more formula references in this worksheet, you can turn to advanced Excel repair software. EaseUS Data Recovery Wizard is a powerful tool that can recover and repair corrupt Word documents, Excel files, videos, and photos. Follow the steps below to recover and repair Excel files.

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?

Brithny is a technology enthusiast, aiming to make readers' tech life easy and enjoyable. She loves exploring new technologies and writing technical how-to tips. On her spare time, she loves sharing things about her favorite singer - Taylor Swift on her Facebook or Twitter.

Product Reviews

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.

Читайте также: