How Do I Repair and Restore Excel File?

When an Excel file turns corrupt, the file might become inaccessible or you might receive errors. You may encounter errors, such as ‘the file is corrupt and cannot be opened,’ ‘Excel found unreadable content in “filename>”,’ ‘Excel cannot open “filename” because the file format or extension is not valid,’ etc.

Common Reasons for Excel File Corruption

There are several reasons that can turn the file corrupt. The most common reason is a damaged hard drive. Other factors that can cause corruption in an Excel file are as follows:

• System crash or abrupt shutdown of the system while the file is still open
• Viruses infecting the file with malicious code
• Bug in the operating system
• Bad sectors on the drive where the file is stored
• Large spreadsheets with formulas and other components

Whatever be the reason, if your business is dependent on an Excel file, corruption in the file could hamper your business continuity. Also, you may lose crucial data. In such a situation, you could try to repair the file.

Before We Begin

It is important to identify the root cause behind Excel file corruption. If the problem has occurred due to a faulty hard disk drive, contact your hardware vendor to get it fixed. Also, move the file to another local drive and check if it opens. If nothing works, proceed with the methods discussed below to repair and restore the file.

Methods to Repair and Restore Excel File

Try the following methods to fix corruption in an Excel file and restore it.

Method 1 – Use the Built-in ‘Open and Repair’ Tool

You can use the Excel built-in Open and Repair utility to repair the corrupt file. Follow these steps:

• Open your Excel application and click on Blank workbook.

• On the blank workbook screen, click on the File tab.

• Click Open > Computer > Browse.

• Select the file you want to repair and then click on Open and Repair from the Open dropdown box.

• Click Repair to fix corruption in the Excel file and recover maximum data.

• If you get the following error message, click Yes to open the file.

• If clicking Yes opens the file with garbage entries (see the image below), perform Step 1 – 5 and click Extract Data. This will only help you recover data without formulas and values.

Note: You may also try to recover the data from a corrupted workbook by using the methods suggested by Microsoft .

A better way to repair and restore an Excel file with complete data is to use a specialized Excel file repair tool .

Method 2 – Use Excel File Repair Tool

Stellar Repair for Excel is a powerful tool designed to help users fix corrupted .xls or .xlsx files without any technical assistance. Also, the tool recovers all the components from a corrupted workbook, including tables, pivot tables, cell values, formulas, charts, images, etc. You can preview the repaired file and its contents by downloading the free demo version from the link below. It is a useful feature that allows the user to validate the data before saving it.

[

](https://tools.techidaily.com/stellardata-recovery/repaire-for-excel/ “Free Download For Windows”)

Here’s the step-by-step instructions to repair a corrupt Excel file using the software:

• Run the software. The software main interface opens with an instruction to add some add-ins if you’ve engineering formulas in the file you want to repair.

• Click OK to proceed.

• Select the file you wish to repair by using the Browse option.

Note: If you’re not aware of the file location, choose the ‘Search’ option to locate the file.

• A screen showing progress of the Excel file repair process is displayed.

• Preview of the repaired Excel file and its recoverable data is displayed.

• After verifying the data, click on the Save File button on the File menu to save the repaired file.

• Select the location where you wish to save the repaired file on the Save File window and then click OK.

A confirmation message will pop-up after completion of the repair process. You can now try to open the file in your Excel program.

End Note

Even if you’re taking preventive measures, you might still experience corruption in an Excel file. So, it’s crucial to take regular backups of your workbooks. For this, ensure that the ‘Always create backup’ option is enabled in Excel. You can find it in General Options by clicking on the Tools button in the Save As dialog box. Enabling it will ensure that the Excel backup file is updated with the changes made in a spreadsheet.

Additionally, ensure that the Excel ‘AutoRecover’ feature is set to save a version of your Excel file after every 10 minutes. You can increase or shorten the interval as per your requirement.

Excel File Corruption Warnings and Solutions

Summary: Many users reported error messages they receive when they try to save or open an Excel file. In this blog, you will learn about the warning messages that indicate your Excel file is corrupt and possible solutions to repair it. It also outlines the Stellar Repair for Excel to repair corrupt Excel files.

Excel users often report about receiving warning messages suggesting corruption in the workbook. This usually happens while opening an Excel file, ‘.xls’ or ‘.xlsx’ file created by earlier versions, or attempting to create a copy of the workbook.

Excel file corruption may occur due to several reasons including (but not limited to) virus infection, sudden system shutdown during write operation, and leaving excel file open on the shared network.

Occurrences of Excel File Corruption Warnings

Occurrence 1 – “Excel found unreadable content in . Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes”.

On clicking ‘Yes’, you will receive the following error:

 “The file is corrupt and cannot be opened”.

Occurrence 2 – “Excel cannot open the file , because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file”.

Besides the warning messages outlined above, there are a few other tell-tale signs of Excel file corruption such as:

• Excel crashes or freezes, preventing you from accessing the workbook and information stored in it.
• Unexpected errors occur during the save operation listed as below:
  • “An unexpected error has occurred. AutoRecover has been disabled for this session of Excel”.
  • “Errors were detected while saving ”.

Solutions to Fix Excel File Corruption Issue

Follow the below-listed solutions to deal with corruption issues in Excel:

NOTE: If you encountered problem opening Excel files after upgrading to latest Windows Operating System (OS) and Office program, try updating your Office as well as Windows OS to latest patches provided on the Microsoft site. Microsoft frequently releases Office and Windows OS patches to help users’ correct known errors. Check if you can open the corrupt workbook after installing the update.

Solution 1 – Use Open and Repair Utility

Excel comes with a built-in recovery mechanism. It automatically starts ‘File Recovery Mode’ when a user opens a corrupt workbook, and attempts to open and repair the workbook. Sometimes, the recovery mode might not start automatically. In that case, you will need to repair the Excel file manually by using ‘Open and Repair ’ utility.

Steps to use Microsoft’s built-in repair utility are as follows:

Step 1: Select File > Open.

Step 2: Click the folder containing the corrupt workbook, and then click Browse.

Step 3: In the Open window, select the corrupt workbook.

Step 4: Next, click the arrow in the Open button, and then click Open and Repair.

Step 5: In the window that appears, click Repair.

If  ‘Open and Repair’ doesn’t work in excel , select Extract Data to extract formulas and values from the corrupt workbook.

NOTE: If you need a quick solution to salvage your data, use an Excel file repair tool.

Or else, attempt the following solutions to deal with corruption in Excel file .

Solution 2 – Uninstall and Re-install Office Installation

NOTE: Make sure to create a backup of your Excel file before uninstalling and re-installing your Office application.

Download the Office uninstall support tool to remove the application.

You can read: Simple Ways to Open Corrupt Excel file Without any Backup

To reinstall Microsoft Office, follow these steps:

NOTE: Before proceeding with Office re-installation process, make sure that you have license keys ready.

Step 1: Open the Microsoft Office site.

Step 2: Select Sign in.

NOTE: You may skip this step if you’re already signed in.

Step 3: After signing in, from the Office sign-in page, click Install/Install Office

Your Office application will get re-installed. Now open the backed-up Excel file and see if the problem is fixed.

Solution 3 – Move Excel File to a Different Location

Often moving a corrupt Excel file to a different location can help solve the corruption problem. Here’s how:

Step 1: Open the corrupt Excel file by navigating to the following path:

C:\Users\User_Name\AppData\Roaming\Microsoft\Excel

NOTE: Make sure to replace User_Name with your user name. If you are unable to find the Excel file, you will have to search for the file manually in Program Files (x86).

Step 2:  Open the Excel folder, and move the corrupt file to some other location.

Step 3: Delete the files from the Excel folder.

Now try opening the Excel file you have moved and see if the issue is resolved.

Solution 4 – Use Excel File Repair Software

If none of the above solutions works for you, use Stellar Repair for Excel. It is a specialized Excel file repair software that helps repair corrupt Excel file and recover workbook data in its original state.

Essentially, the software helps rebuild the corrupt file to restore every single object in the file. It can recover objects including user-defined charts, conditional formatting rules, formatting of the charts, properties of worksheet, engineering formulas, etc.

Steps to use Stellar Repair for Excel are as follows:

Step 1: Download, install and launch Stellar Repair for Excel software.

Step 2: In Select File window, click Browse to select the file you want to repair.

NOTE: If you are unaware of the Excel file location, click ‘Search’ in the Select File window to find the file.

Step 3: Once the files are selected, click Repair to initiate the repair process.

Step 4: Preview the repaired file and select all or specific files you want to save.

Step 5: Click Save File on Home menu.

Step 6: In Save File window, choose ‘Default Location’ or ‘Select New Folder’ to select the location where you wish to save the file. Click OK.

The selected files will be saved at the specified location.

Conclusion

You may experience Excel file corruption warning messages while opening or saving an Excel file. The file may become corrupt due to malware infection, sudden system shutdown, and forgetting to close workbook on a shared network. This post outlined occurrences of Excel file corruption warnings, and also described solutions to fix the issue.

You may try using Microsoft’s built-in ‘Open and Repair’ tool to repair corrupt workbook and recover data from it. If this solution doesn’t work, proceed with uninstalling and re-installing the Office application. Another solution is to move corrupt files to another location. But if the problem still persists, use Stellar Repair for Excel software to repair single or multiple Excel (.xls or .xlsx) files and restore data.

[Fixed] Excel Cannot Open the File Because the File Format or File Extension Is Not Valid

When opening an older version of an Excel file in a newer version, you may encounter the “Excel cannot open the file because the extension is not valid” error. This happens if you have accidentally renamed the file with a different file format or an incorrect file extension. Also, you may get the ‘Excel cannot open the file’ error if the file has become unreadable or corrupted.

This is how the complete Excel error message looks like:

“Excel cannot open the file filename.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”

Check out this video for a quick demonstration of how to fix “Excel Cannot Open the File Because the File Format or File Extension Is Not Valid”

How to Fix the ‘Excel Cannot Open the File Because the File Format or Extension Is Not Valid’ Error?

Try any of these workarounds to fix the error and regain access to your Excel file data:

Workaround 1 – Change the Default File Format

By default, an Excel file is saved in .xlsx or .xls file format. Trying to open the file with a different or incorrect extension may cause the ‘Excel cannot open the file because the extension is not valid’ error. To fix the error, try changing the Excel default file format by following these steps:

Note: Take a backup of the Excel file that you cannot open and try the steps on the backup copy.

• In Excel 2010 and newer versions, click on the File menu.

Note: For Excel 2007 and earlier versions, click on the Office button.

• On the left panel, click on Options. 
• In ‘Excel Options’ window, under Save workbooks section, click on the Save files in this file format: drop-down. Select a file format (i.e., the one supported by your Excel version).

• Click OK.

Check if you can open your Excel file. If not, try the next workaround.

Workaround 2 – Edit the Excel File Permissions

You may receive the ‘Excel file format or file extension is not valid’ error if you lack sufficient permissions to open the file. Usually this happens when you try to open an Excel file received from some other user. Follow these steps to change the file permissions and see if it fixes the problem:

• Right-click on the file that won’t open and click Properties.
• In the file properties dialog box, click the Security tab and then hit the Edit button.

• When the file’s permissions dialog box appears, click on the Add button.

• Click on the Advanced button from the ‘Select Users or Groups’ window.

• Select Find Now. A list of all users and groups will get displayed in a search results box. Choose Everyone group from the list and hit OK.

• You can see the ‘Everyone’ group in the ‘Enter the object names to select’ textbox. Click OK once again to return to the file’s permission dialog box.

• Click Everyone from the Group or user names: box and check all the checkboxes under Allow.

• Click on Apply and then OK.

Now try to open the Excel file. If you’re still getting the Excel file cannot open error, use the following workaround.

Workaround 3 – Recover Unsaved Workbook

If the ‘Excel file extension not valid error’ occurs when attempting to open an unsaved workbook, do the following to recover the unsaved workbook:

• In Excel, click File. 
• From the Info screen, under Manage Versions, click Recover Unsaved Workbooks.

Excel will list any unsaved files. Try opening the file and save it. If this doesn’t work, skip to the next workaround.

Workaround 4 – Repair the Workbook

If none of the above workarounds has worked for you, chances are that the Excel file has become corrupted. Try to repair the file using the Excel inbuilt ‘Open and Repair’ utility by following these steps:

• In your Excel application, click on File > Open.
• In the ‘Open’ dialog box that pops-up, select the Excel file you want to repair and click the arrow next to the Open button.

• From the dropdown list, select Open and Repair.

• Excel will ask you to attempt to repair the file or extract data from it. Click Repair to retrieve maximum data. If the Repair option fails, click on Extract Data to recover the data without formulas and values.

If the ‘Open and Repair’ utility doesn’t help fix the corrupted file and/or recover the data, use an Excel repair tool  such as Stellar Repair for Excel to regain access to your file and its data.  

Stellar Repair for Excel software can help repair severely corrupted XLS and XLSX files. Also, it helps recover all the file components, including tables, pivot tables, cell comments, charts, chart sheets, images, formulas, etc., without impacting the original structure of the Excel file. 

To learn how the software works, read this: How to repair corrupt Excel file using Stellar Repair for Excel?

Conclusion

You may encounter the error ‘Excel cannot open the file because the extension is not valid’ when opening a workbook with a different file format or an incorrect file extension. Also, the error may occur if the Excel file has turned corrupt. This article has covered some of the most effective workarounds to resolve the error. But if nothing works, you can try to repair the Excel file and retrieve its data using the Stellar Repair for Excel software.

[Fixed] Excel Found a Problem with One or more Formula

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,

• 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,

• Go to Formulas and click on the ‘Error Checking’ button

• This runs a scan on the sheet and displays the issues, if any. If no issue is found, it displays the following message,

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 errors, MS Excel may display such error messages. To check and confirm if external links are causing the error, follow these steps,

• Navigate to Data Tab > Queries & Connections > Edit Links
• Check the links. If you find any faulty link, remove it and then save the sheet

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:

1. Check horizontal axis formula inside Select Data Source dialog box
2. Check Secondary Axis
3. Check linked Data Labels, Axis Labels, or Chart Title

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

How Do I Repair and Restore Excel File?

When an Excel file turns corrupt, the file might become inaccessible or you might receive errors. You may encounter errors, such as ‘the file is corrupt and cannot be opened,’ ‘Excel found unreadable content in “filename>”,’ ‘Excel cannot open “filename” because the file format or extension is not valid,’ etc.

Common Reasons for Excel File Corruption

There are several reasons that can turn the file corrupt. The most common reason is a damaged hard drive. Other factors that can cause corruption in an Excel file are as follows:

• System crash or abrupt shutdown of the system while the file is still open
• Viruses infecting the file with malicious code
• Bug in the operating system
• Bad sectors on the drive where the file is stored
• Large spreadsheets with formulas and other components

Whatever be the reason, if your business is dependent on an Excel file, corruption in the file could hamper your business continuity. Also, you may lose crucial data. In such a situation, you could try to repair the file.

Before We Begin

It is important to identify the root cause behind Excel file corruption. If the problem has occurred due to a faulty hard disk drive, contact your hardware vendor to get it fixed. Also, move the file to another local drive and check if it opens. If nothing works, proceed with the methods discussed below to repair and restore the file.

Methods to Repair and Restore Excel File

Try the following methods to fix corruption in an Excel file and restore it.

Method 1 – Use the Built-in ‘Open and Repair’ Tool

You can use the Excel built-in Open and Repair utility to repair the corrupt file. Follow these steps:

• Open your Excel application and click on Blank workbook.

• On the blank workbook screen, click on the File tab.

• Click Open > Computer > Browse.

• Select the file you want to repair and then click on Open and Repair from the Open dropdown box.

• Click Repair to fix corruption in the Excel file and recover maximum data.

• If you get the following error message, click Yes to open the file.

• If clicking Yes opens the file with garbage entries (see the image below), perform Step 1 – 5 and click Extract Data. This will only help you recover data without formulas and values.

Note: You may also try to recover the data from a corrupted workbook by using the methods suggested by Microsoft .

A better way to repair and restore an Excel file with complete data is to use a specialized Excel file repair tool .

Method 2 – Use Excel File Repair Tool

Stellar Repair for Excel is a powerful tool designed to help users fix corrupted .xls or .xlsx files without any technical assistance. Also, the tool recovers all the components from a corrupted workbook, including tables, pivot tables, cell values, formulas, charts, images, etc. You can preview the repaired file and its contents by downloading the free demo version from the link below. It is a useful feature that allows the user to validate the data before saving it.

[

](https://tools.techidaily.com/stellardata-recovery/repaire-for-excel/ “Free Download For Windows”)

Here’s the step-by-step instructions to repair a corrupt Excel file using the software:

• Run the software. The software main interface opens with an instruction to add some add-ins if you’ve engineering formulas in the file you want to repair.

• Click OK to proceed.

• Select the file you wish to repair by using the Browse option.

Note: If you’re not aware of the file location, choose the ‘Search’ option to locate the file.

• A screen showing progress of the Excel file repair process is displayed.

• Preview of the repaired Excel file and its recoverable data is displayed.

• After verifying the data, click on the Save File button on the File menu to save the repaired file.

• Select the location where you wish to save the repaired file on the Save File window and then click OK.

A confirmation message will pop-up after completion of the repair process. You can now try to open the file in your Excel program.

End Note

Even if you’re taking preventive measures, you might still experience corruption in an Excel file. So, it’s crucial to take regular backups of your workbooks. For this, ensure that the ‘Always create backup’ option is enabled in Excel. You can find it in General Options by clicking on the Tools button in the Save As dialog box. Enabling it will ensure that the Excel backup file is updated with the changes made in a spreadsheet.

Additionally, ensure that the Excel ‘AutoRecover’ feature is set to save a version of your Excel file after every 10 minutes. You can increase or shorten the interval as per your requirement.

How to Fix Excel Formulas Not Working Properly | Step-by-Step Guide

Summary: Excel formulas sometimes fail to function correctly and even return an error. This article explains what you might be doing wrong that prevents Excel formulas from working properly and solutions to resolve the issue. If your formulas have disappeared from the Excel spreadsheet and you are having trouble recovering them, you can use an Excel repair tool to recover the formulas.

When working with Excel formulas, situations may arise when the formula doesn’t calculate or update automatically. Or, you may receive errors by clicking on a formula.

Problems Causing the ‘Excel Formulas not Working Properly’ Issue and Solutions

Let’s check out the possible reasons that cause Excel formulas to work properly and solutions to resolve the issue.

Problem 1 – Switching Automatic to Manual Calculation Mode

Automatic and manual are the two modes of calculation in Microsoft Excel.

By default, Excel is set to automatic calculation mode. Everything is recalculated automatically when any changes are made in a worksheet in this mode. You may switch from automatic to manual mode to disable the recalculation of formulas, particularly when working with a large Excel file with too many formulas.

Excel will not calculate automatically when set to manual calculation mode. And this may make you think that the Excel formula is not working properly.

Solution – Change Calculation Mode from Manual to Automatic

To do so, perform these steps:

• Click on the column with problematic formulas.
• Go to the Formulas tab, click the Calculation Options drop-down, and select Automatic.

Problem 2 – Missing or Mismatched Parentheses

It’s easy to miss or incorrectly place parentheses or include extra parentheses in a complex formula. If a parenthesis is missing or mismatched and you click Enter after entering a formula, Excel displays a message window suggesting to fix the issue (refer to the screenshot below).

Clicking ‘Yes’ might help fix the issue. But Excel might not fix the parentheses properly, as it tends to add the missing parentheses at the end of a formula which won’t always be the case.

Solution – Check for Visual Cues When Typing or Editing a Formula with Parentheses

When typing a formula or editing one, Excel provides visual cues to determine if there’s an issue with the parentheses inserted in a formula. Checking for these visual cues can help you fix missing/mismatched parentheses.

• Excel helps identify parenthesis pairs by highlighting them in different colors. For instance, the pair of parenthesis outside is black.
• Excel does not make the opening parentheses bold. So, if you’ve inserted the last closing parentheses in a formula, you can determine if your parentheses are mismatched.
• Excel helps identify parentheses pairs by highlighting and formatting them with the same color once you cross over them.

Problem 3 – Formatting Cells in an Excel Formula

When adding a number in an Excel formula, don’t add any decimal separator or special characters like $ or €. You may use a comma to separate a function’s argument in an Excel formula or use a currency sign like $ or € as part of cell references. Formatting the numbers may prevent the formula from functioning correctly.

Solution – Use Format Cells Option for Formatting

Use Format Cells instead of using a comma or currency signs for formatting a number in the formula. For instance, rather than entering a value of $10,000 in your formula, insert 10000, and click the ‘Ctrl+1’ keys together to open the Format Cells dialog box.

Problem 4 – Formatting Numbers as Text

Numbers are displayed as left-aligned in a sheet in a worksheet, and text formatted numbers are right-aligned in cells. Excel considers numbers formatted as text to be text strings. Thus, it leaves those numbers out of calculations. As a result, a formula won’t work as intended. For example, in the following screenshot, you can see that the SUM formula works correctly for normal numbers. But, when the SUM formula is applied to numbers formatted as text, the formula doesn’t return the correct value.

Sometimes, you may also see an apostrophe in the cells or green triangles in the top-left corner of all the cells when numbers in those cells are formatted as Text.

Solution – Do Not Format Numbers as Text

To fix the issue, do the following:

• Select the cells with numbers stored as text, right-click on them, and click Format Cells.
• From the Format Cells window, click on Number and then press OK.

Problem 5 – Double Quotes to Enclose Numbers

Avoid enclosing numbers in a formula in double-quotes, as the numbers are interpreted as a string value.

Meaning if you enter a formula like =IF(A1>B1, “1”), Excel will consider the output one as a string and not a number. So, you won’t be able to use 1’s in calculations.

Solution – Don’t Enclose Numbers in Double Quotes

Remove any double quotes around a number in your formula unless you want that number to be treated as text. For example, you can write the formula mentioned above as “1” =IF(A1>B1, 1).

Problem 6 – Extra Space at Beginning of the Formula

When entering a formula, you may end up adding an extra space before the equal (=) sign. You may also add an apostrophe (‘) in the formula at times. As a result, the calculation won’t be performed and may return an error. This usually happens when you use a formula copied from the web.

Solution – Remove Extra Space from the Formula

The fix to this issue is pretty simple. You need to look for extra space before the equal sign and remove it. Also, ensure there is an additional apostrophe added in the formula.

Other Things to Consider to Fix the ‘Excel Formulas not Working Properly’ Issue

• If your Excel formula is not showing the result as intended, see this blog .
• When you refer to other worksheets with spaces or any non-alphabetical character in their names, enclose the names in ‘single quotation marks’. For example, an external 5reference to cell A2 in a sheet named Data enclose the name in single quotes: ‘Data’!A1.
• You may see the formula instead of the result if you have accidentally clicked the ‘Show Formulas’ option. So, click on the problematic cell, click on the Formula tab, and then click Show Formulas.
• If you’re getting an error “Excel found a problem with one or more formula references in this worksheet”, find solutions to fix the error here .

Conclusion

This blog discussed some problems you might make causing an Excel formula to stop working properly. Read about these common problems and solutions to fix them. If a problem doesn’t apply in your case, move to the next one. If you cannot retrieve formulas in your Excel sheet, using an Excel file repair tool like Stellar Repair for Excel can help you restore all the formulas. It does so by repairing the Excel file (XLS/XLSX) and recovering all the components, including formulas.

How to fix runtime error 424 object required error in Excel

The Runtime error 424: Object required occurs when Excel is not able to recognize an object that you are referring to in a VBA code. The object can be a workbook, worksheet, range, variable, class, macro, etc. Some users have also reported that this error occurred when they tried to copy the values of the cells from one workbook to another.

Let’s understand the error through a small scenario. Suppose, I want to check the last field row in a table in a spreadsheet named “First” using the VBA code. To do this, I have added a command button and double-clicked on it and entered the below code in the backend:

Private Sub CommandButton2_Click()

Dim LRow As Integer

LRow = Worksheets(“First”).Cells(Rows.Count, 2).End(xlUp).Row

MsgBox (“Last Row “ & LRow)

End Sub

In this code, Worksheets(“First”) is a data object. If I mistakenly delete this data object and insert any random name (for example - kanada), then it will not be recognized by Excel. When I run this code, I will get the “Run-time error 424”.

Causes of Runtime Error 424 in Excel

The Runtime error 424: Object required can occur due to the following reasons:

• Incorrect name of the object you are trying to refer to in a code.
• You have provided an invalid qualifier to an object.
• You have not used the Set statement while assigning an object reference.
• The object is corrupted.
• Missing objects in a workbook.
• Objects you are trying to call in a code are mistakenly deleted or unavailable.
• You have used an incorrect syntax for object declaration.
• You are trying to perform an invalid action on an object in a code.
• Workbook is corrupted.

Solutions to Fix Runtime Error 424: Object Required in Excel

The VBA error ‘object required’ may occur due to different reasons. Based on the reason, you can follow the solutions mentioned below to fix the error.

1. Check the Name of the Object

The Runtime error 424 can occur when you run the VBA code using an incorrect name of the object. For example, the object name is ‘MyObject’ but you’re using “Backcolor”.

 When you click the Debug button, the line with the error will highlight.

To fix the issue, you need to provide the correct name of the object.

2. Check if the Object is Missing

 The Runtime error 424 can occur if the object you are referring to as a method is not available or you are using the wrong object in a code. In the below example, you can see that the error occurs when an object named “Employee” is not available in the Project list.

 You can check and mention the object which is available. For instance, Sheet2 in the below code.

3. Check All References are Declared in the Code

You can get the Runtime error 424 if all the references are not declared. So, make sure you have declared all the references in the code. To verify this, you can use the debug mode by pressing F5 or clicking on the Debug option.

4. Check the Macro Security Settings

Sometimes, the error can occur if macros are disabled in the Macro Security settings. You can check and change the settings by following these steps:

• On the Developer tab, in the Code section, click Macro Security.
• In the Trust Center window, select Enable all macros.

• Click OK.

5. Repair your Workbook

Sometimes, the ‘Object required’ error can occur if your Excel file is damaged or corrupted. In such a case, you can try repairing the file using Microsoft’s in-built utility - Open and Repair. To use this utility, follow these steps:

• In Excel, go to File > Open > Browse.
• In the Open dialog box, click on the corrupted Excel file.
• Click the arrow next to the Open button and select Open and Repair from the dropdown.
• Select Repair to recover as much data from the file as possible.

If the Open and Repair utility fails or stops working, then you can try a professional Excel repair tool, such as Stellar Repair for Excel . It is an advanced tool that can repair severely corrupted Excel files (.xls, .xlsx, .xltm, .xltx, and .xlsm). It helps recover all the file components, including images, charts, tables, pivot tables, cell comments, chart sheets, formulas, etc., without impacting the original structure.

Conclusion

The Runtime error 424 usually occurs when there is an issue with the objects in your VBA code. In this article, we have covered some effective methods to resolve the “object required” error in Excel. If the error occurs due to corruption in Excel file, then you can repair the corrupt file using Stellar Repair for Excel. It is a reliable tool that can repair severely corrupted Excel file without changing its actual formatting. You can download the free trial version of the software to evaluate its functionality.