Easy Steps to Make Excel Hyperlinks Working

Summary: This blog discusses why hyperlinks won’t work in Excel and solutions to fix it. If nothing works, try using Stellar Repair for Excel software to recover your workbook with hyperlinks and all the data intact.

Hyperlinks in your Excel file could be references to a file’s location on the computer or a location within the same worksheet. Or, hyperlinks might be pointing to a URL. Sometimes, the hyperlinks won’t work and any of the following errors may pop up on your screen on clicking a hyperlink:

‘Cannot open the specified file.’

‘This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator.’

Causes of ‘hyperlinks not working in Excel’ problem, and solutions thereof

Here are some of the possible causes behind the ‘hyperlinks not working’ issue and solutions to fix it:

Cause 1 – Change in the name of the hyperlinked file

If the file name that appears in the hyperlink text is different than the actual file name, it will prevent the hyperlink from working.

Solution – Link to the renamed file

Ensure that the links in the Excel file are updated and points to the renamed file. For this, right-click the hyperlink and select ‘Edit the hyperlink’. Next, in the hyperlink address, replace the current filename with the renamed one in the hyperlink address.

Cause 2 – File name has a pound (#) sign

When you create a hyperlink for a file in Excel, you cannot use a pound character (#) in the file name that appears in the hyperlink. That is because the pound sign is not accepted in hyperlinks and may lead to the ‘Cannot open the specified file’ error.

Note: While you can use a pound character in a file name, it cannot be used in hyperlinks in an MS Office document.

Solution – Rename the file name and remove the pound sign

Open the file that contains the ‘#’ sign and rename it by following these steps.

Right-click the cell containing the hyperlink that is not working, and click Edit Hyperlink.
From the Address box, copy the address of the file you are linking to.
Go to the location where the file is stored, right-click on the file, and click Rename.
Remove the ‘#’ character from the name of the file.
Go back to the Excel file, right-click on the problematic hyperlink, and choose Edit Hyperlink. Next, browse and select the renamed file.
The renamed file without the pound sign will be added in the Address box.
Click OK.

Now try opening the hyperlink.

Cause 3 – Sudden system shutdown causes abrupt closing of Excel

There may be a discrepancy in the data in hyperlinks when a system shut down suddenly, without properly closing the Excel file. And so, when trying to open a link, it won’t open.

Solution – Enable the option to save hyperlinks

There is an inbuilt option in Excel to update hyperlinks every time the workbook is saved. Follow these steps to enable that option:

Note: The steps may vary based on the Excel version you are using.

For Excel 2013, 2016, or 2019:

Open Excel Workbook -> Go to File->Options->Advanced
Scroll down to find the General tab and click on Web Options
Web Options Window pops-up
In the Web Options Window, go to Files Tab and select the ‘Update Links on save‘ checkbox
Click on OK button and your option is saved

The steps are also explained in the image below:

For Excel 2007:

Click the Office button
Select Excel Options, then follow Step 1) to Step 5), as mentioned above and get the Excel Hyperlinks to work again.

Still unable to open hyperlinks in Excel?

If you fail to make Excel hyperlinks work using the above-discussed solutions, use an Excel repair tool to fix the hyperlinks issue. Download the Stellar Repair for Excel to repair an XLS/XLSX file and restore the hyperlinks.

See the working of the tool here:

The tool recovers all components of the Excel file including tables, charts, chart sheets, cell comments, images, formulas, and more. You can repair multiple worksheets and fix all dysfunctional Excel hyperlinks across multiple worksheets in a single workbook. Click on the workbook, select all worksheets and start repairing

Conclusion

Carefully read the possible causes behind the ‘Excel Hyperlinks not working’ issue to understand what resulted in the issue in the first place. If nothing helps, use Stellar Repair for Excel to restore the hyperlinks and save the result in a new Excel file, without interfering with worksheet properties and cell formatting.

Solutions to Repair Corrupt Excel File

Summary: MS Excel can throw various errors due to corrupted Excel files. This blog discusses the error messages that indicate Excel file corruption and the methods to prevent data loss due to a corrupt file. It also discusses the reasons behind the corruption in Excel file and their solutions. It also mentions a “Stellar repair for Excel” tool that can help to repair the corrupt or damaged Excel file.

Is your Excel file corrupted? And you don’t have backup of your data? There is no need to worry. There are some simple solutions to repair Excel file 2019. But before heading towards the solutions, let’s discuss the possible reasons for Excel file corruption and how you can prevent losing your data.

Error Messages that Indicate Excel File Corruption

When an Excel file gets corrupted, different error messages appear. For example:

“Excel found unreadable content in . Do you want to recover the content of this workbook, click Yes.”
“Can’t find project and library.”
“The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted.”
“Microsoft Excel has stopped working.”

Reasons Behind Excel File Corruption

The reasons for corruption in Excel file could be any of the following:

Improper system shutdown
Computer virus/malware attack/Hacker attack
Outdated anti-virus definition
Hardware failure
Unintentional deletion of files
Large Excel files
Bad sectors on storage media

How to Avoid Data Loss Due to Excel File Corruption?

Excel users should follow the below precautionary measures to prevent data loss due to Excel file corruption:

1. Create an Automatic Backup Copy

When you create an Excel spreadsheet, it is advised to Save As your document, as follows:

In Save As window, click Tools next to Save option.
Select General Options from the drop-down menu.
Then check the dialogue box Always create back up and click OK.

This will always create a backup of your Excel. If it’s deleted or corrupted at any time, it can be recovered.

2. Create Recovery File at Different Time Periods

Steps are as follows:

Go to File and then click Excel Options.
Click Save and then select the Save Auto Recover information every checkbox
Add the required minutes and location. Ensure that Disable AutoRecover for this workbook only box is unchecked.

Methods to Repair Corrupted Excel 2019 File

Try using these 5 methods to restore your Excel file and recover data:

Method 1: ‘Open and Repair’ Excel Files

Excel automatically opens the corrupted file in Recovery Mode. If not, you can repair Excel file manually through the following steps:

Click on the File and select Open.

Go to the location where the corrupt workbook is stored. In the Open window, select the corrupt file.
Click Open and then select Open and Repair.
In the window that opens, click Repair.

If the Repair option doesn’t work, you can select Extract Data and try to extract the values and formulae safely from the corrupt file.

Method 2: Recover Data from Open Workbook

If you face issues while working in an Excel file, you can choose to return to the last saved version of the Excel file. For this:

Click File. Then select Open.
Double click on the name of the workbook (the one that is open in your Excel).
Click Yes to reopen it.

The workbook will now appear.

Please note that it will show the last saved version and changes made after that won’t be recovered.

Method 3: Set Calculation Option as Manual

You can also recover data from Excel workbooks that you’re unable to open. For this, you need to configure the calculation option as manual in Excel. You can do this through the following steps:

Click on File. Select New and open a Blank workbook.
From File, select Excel Options.

From the Formulas category, under the section Calculation options, select Manual. Now click OK.

Then click File, and select Open to open the corrupted or damaged Excel file.

Method 4: Recover Content by Using External Links

You can also recover specifically the content (leaving formulas/calculated values) from the workbook by using external references (to link Excel workbook). For this:

Click on File, Select Open.
Navigate to the folder that contains the corrupted workbook.
Now, right-click on the file name of the corrupted workbook and click Copy.
Click File button. Then, select New and create another blank workbook.
In the first cell (A1), type =!A1 and press Enter.
- Select the corrupted workbook in the Update Values dialogue (if it appears). Then click OK.
- Select the relevant sheet in the Select Sheet dialogue (if it appears). Then click OK.

Again, select the cell A1, go to Home and select Copy.
Now select (start from the cell A1) an area equal to that of the data in the original workbook.
Go to Home now and select Paste.
Again, go to Home, and Copy the data (the same selection of cells).
Go to Home, and then click on the arrow below Paste. Then click on Values.

By pasting values, you removed the links to the corrupted workbook and only the data is left behind.

Method 5: Excel Repair Software

If the above-mentioned methods do not help in repairing the corrupt Excel file, try an Excel repair software.

One of the most commonly used Excel repair tools is Stellar Repair for Excel. Its trial version is available for free download, which lets you scan and preview the repaired Excel files. Once you’ve ascertained the effectiveness of the software, you can save the file after activating the software.

Here’s the complete repairing process of the corrupt Excel file

Conclusion

This post shared the reasons behind Excel file corruption and precautionary measures to prevent data loss. It also outlined different methods to repair corrupt Excel file 2019. There are several in-built utilities in Microsoft Excel to repair corrupt workbooks and recover data from it. In case these methods didn’t work, you can use Stellar Repair for Excel – an easy-to-use DIY tool that can fix all Excel corruption errors and restore data with all original properties.

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

Recover Corrupted Excel File 2007, 2010 | Easy Methods

There are several reasons that can cause Microsoft Excel workbooks to turn corrupt, such as virus attack, bad sectors on a drive on which Excel file is saved, system shutdown without properly closing the Excel application, etc.

Corruption in an Excel workbook can result in data loss or render the workbook inaccessible. Fortunately, Excel automatically starts recovery upon opening a corrupted Excel file. But, if it fails, you can manually repair the file or extract data from the corrupt file.

Quick Solution: Performing 2007, 2010 Excel repair or recovery process manually can be time-consuming. Also, manual workarounds to recover corrupt Excel workbook does not guarantee recovering the complete workbook data. Use Stellar Repair for Excel software to repair single or multiple Excel (XLS/XLSX) files in 3 simple steps. The software also helps recover the Excel file, keeping the data intact.

How to Fix Microsoft Excel 2010 & 2007 Files Corruption?

Microsoft Excel comes with an inbuilt repair utility, called ‘Open and Repair’, that helps fix and recover corrupted Excel files.

Steps to Repair MS Excel 2010 Files Manually

The detailed steps to open and repair Excel 2010 are as follows:

Open Microsoft Excel 2010 and click File from the main menu.

Next, click Open.

Browse the corrupt Excel 2010 file on your computer and select it in the Open dialog.

Click the arrow next to the Open button and choose Open and Repair.

Click Repair when prompted to recover data to the maximum.
If Excel fails to repair, click Extract Data to extract values and formulas in the corrupt file.
Excel prompts to ‘Convert to Values’ or ‘Recover Formulas’.
Click Yes if it prompts the following error:

“The document file name caused a serious error the last time it was opened. Would you like to continue opening it?

When Excel opens the last saved file, save it.

Once you’re able to access the last saved 2010 Excel file, try extracting the file contents.

Save Excel 2010 File in HTML Format

If you can open the Excel file, choose the HTML format to save it in filtered form. After that, close the Excel file as you have your data in the HTML file. The steps to save an Excel file in HTML format are as follows:

Open Microsoft Excel 2010, click Save As, and then choose Web Page in the ‘Save as’ type drop-down list.
Select the “Enable Entire Workbook” option, and then click the Save button.
Close the Excel file and reopen your Microsoft Excel application. Browse the HTML file that you have saved.
Click File from the main menu, and select Save As in the list.
Type-in a different name, choose Microsoft Excel Workbook in the ‘Save as’ type drop-down menu, and then click the Save button.

With this, you would be able to access the data in the corrupt Excel file.

If the inbuilt tool fails to repair Excel 2010 file, a few methods can help you recover data from corrupted or lost workbook manually.

Steps to Repair Excel 2007 Files Manually

Follow these steps to repair a corrupted 2007 Excel file by using the inbuilt Microsoft Excel repair tool:

Open Microsoft Excel 2007, click the Office button, and then select Open.

In the Open dialog box that pops-up, browse and select the corrupt Excel 2007 file. Click the arrow next to the Open button and choose Open and Repair.

Click Repair when prompted to recover as much data as you can from Excel 2007 file.

If a repair fails, follow steps 1 till 3, and then click Extract Data to extract values and formulas from the corrupt file.
In the window that appears, click Convert to Values or Recover Formulas to extract workbook data.

Note: The built-in Microsoft Excel 2007 repair tool may fail to resolve Excel corruption issue. Also, choosing to extract data from the workbook involves data loss risk. Using a professional Excel repair tool, however, can resolve all types of Excel file corruption errors and restore all its data.

Methods to Recover Data from Corrupt Excel 2010 & 2007 Files

If the ‘Open and Repair’ feature fails in getting your Excel 2010, 2007 file repaired, you can try retrieving the file contents by following some manual methods. However, the methods may vary depending on whether you can open a workbook or not.

Method 1 – Move Corrupt Excel File to another System

Move the corrupt Excel file to any other computer and try opening it in MS Excel 2010/2007. Doing so, may help you resolves disk or network-related errors leading to Excel file corruption.

Method 2 – Revert Unsaved Excel File to its Last Saved Version

If an Excel file turns corrupt while working on it but before saving any changes, try reverting it to its last saved version. To do so, perform the following:

Open your Excel application, click the Office button, and then click Open from the menu.
Browse the corrupt Excel file, click Yes when prompted to revert to its last saved version.

What if Nothing Works?

If you fail to recover a corrupt Excel 2007/2010 file, perform Excel file recovery with Stellar Excel repair software. The software is specially designed to help users fix their corrupted XLS/XLSX files quickly and easily without any technical assistance. It also helps restore all the file data to its original form.

Points to Remember

Close all the MS Excel instances before using the software
If the sheet you are repairing contains engineering formulas, please include ‘Analysis TooPak’ manually from Tools > Add-Ins

If you know the corrupt Excel 2007 or 2010 file location, click Browse to choose the file. Otherwise, click Search. Follow the below steps to recover data from corrupt Excel 2007/2010 file by using Stellar Excel repair tool:

Click the Repair button to scan the file.

Once the scanning process is complete, the software shows a preview of recoverable Excel file items.

To save the repaired file, click the Save File option on File menu.

In ‘Save File’ dialog box, choose to recover Excel 2007 & 2010 data to either the Default or New location. Click OK.

The repaired Excel file gets saved at the specified location.

Preventive Measures to Avoid Losing Excel File Data

The above-discussed methods might help salvage your data. But, it is recommended that you must take some preventive measures to avoid losing the data. One such important measure is backing up a copy of your workbook automatically. Doing so, will help you get back data in case the workbook is accidentally deleted or corrupted.

Steps to Create Backup Copy Automatically

You can automatically create an Excel backup copy by following these steps:

Click Save As from the main menu of your Excel application.
Browse to the location where the corrupt Excel 2010/2007 file is saved.

In ‘Save As’ dialog box, click the arrow next to Tools button (given at the bottom left corner) and choose General Options.

In ‘General Options’ box, check Always create backup checkbox, and then click OK.

With this, you instructed MS Excel to create a backup of every Excel file you create or open for work.

Conclusion

This article outlined the typical reasons resulting in a corrupt Excel 2010 or 2007 file, such as virus infection, bad sectors on drive, etc. It explained how to fix a corrupted Excel file by using the inbuilt MS ‘Open and Repair’ tool. The article also discussed methods to recover Excel files in MS Office 2010 & 2007 when the Microsoft Excel repair tool fails. Further, it explained how using a professional repair tool such as Stellar Repair for Excel can come in handy when the manual methods to repair and recover Excel 2007 and 2010 file fails. But, keep in mind, a workbook may get corrupt again. And so, make sure to automatically backup your workbook to avoid losing its data.

[Fixed] Excel VBA Runtime Error 9: Subscript Out of Range

Summary: The runtime error 9 in Excel usually occurs when you use different objects in a code or the object you are trying to use is not defined. This post will discuss the reasons behind the Excel VBA error “Subscript out of Range” and the solutions to resolve the issue. It will also mention an Excel repair tool that can help fix the error if it occurs due to corruption in worksheet.

Many users have reported encountering the error “Subscript out of range” (runtime error 9) when using VBA code in Excel. The error often occurs when the object you are referring to in a code is not available, deleted, or not defined earlier. Sometimes, it occurs if you have declared an array in code but forgot to specify the DIM or ReDIM statement to define the length of array.

Causes of VBA Runtime Error 9: Subscript Out Of Range

The error ‘Subscript out of range’ in Excel can occur due to several reasons, such as:

Object you are trying to use in the VBA code is not defined earlier or is deleted.
Entered a wrong declaration syntax of the array.
Wrong spelling of the variable name.
Referenced a wrong array element.
Entered incorrect name of the worksheet you are trying to refer.
Worksheet you trying to call in the code is not available.
Specified an invalid element.
Not specified the number of elements in an array.
Workbook in which you trying to use VBA is corrupted.

Methods to Fix Excel VBA Error ‘Subscript out of Range’

Following are some workarounds you can try to fix the runtime error 9 in Excel.

Method 1: Check the Name of Worksheet in the Code

Sometimes, Excel throws the runtime error 9: Subscript out of range if the name of the worksheet is not defined correctly in the code. For example – When trying to copy content from one Excel sheet (emp) to another sheet (emp2) via VBA code, you have mistakenly mentioned wrong name of the worksheet (see the below code).

Private Sub CommandButton1_Click()
Worksheets("emp").Range("A1:E5").Select
Selection.Copy
Worksheets("emp3").Activate
Worksheets("emp3").Range("A1:E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

When you run the above code, the Excel will throw the Subscript out of range error.

So, check the name of the worksheet and correct it. Here are the steps:

Go to the Design tab in the Developer section.
Double-click on the Command button.
Check and modify the worksheet name (e.g. from “emp” to “emp2”).

Now run the code.
The content in ‘emp’ worksheet will be copied to ‘emp2’ (see below).

Method 2: Check the Range of the Array

The VBA error “Subscript out of range” also occurs if you have declared an array in a code but didn’t specify the number of elements. For example – If you have declared an array and forgot to declare the array variable with elements, you will get the error (see below):

To fix this, specify the array variable:

Sub FillArray()
Dim curExpense(364) As Currency
Dim intI As Integer
For intI = 0 to 364
curExpense(intI) = 20
Next
End Sub

Method 3: Change Macro Security Settings

The Runtime error 9: Subscript out of range can also occur if there is an issue with the macros or macros are disabled in the Macro Security Settings. In such a case, you can check and change the macro settings. Follow these steps:

Open your Microsoft Excel.
Navigate to File > Options > Trust Center.
Under Trust Center, select Trust Center Settings.
Click Macro Settings, select Enable all macros, and then click OK.

Method 4: Repair your Excel File

The name or format of the Excel file or name of the objects may get changed due to corruption in the file. When the objects are not identified in a VBA code, you may encounter the Subscript out of range error. You can use the Open and Repair utility in Excel to repair the corrupted file. To use this utility, follow these steps:

In your MS Excel, click File > Open.
Browse to the location where the affected file is stored.
In the Open dialog box, select the corrupted workbook.
In the Open dropdown, click on Open and Repair.
You will see a prompt asking you to repair the file or extract data from it.
Click on the Repair option to extract the data as much as possible. If Repair button fails, then click Extract button to recover data without formulas and values.

If the “Open and Repair” utility fails to repair the corrupted/damaged macro-enabled Excel file, then try an advanced Excel repair tool, such as Stellar Repair for Excel. It can easily repair severely corrupted Excel workbook and recover all the items, including macros, cell comments, table, charts, etc. with 100% integrity. The tool is compatible with all versions of Microsoft Excel.

Conclusion

You may experience the “Subscript out of range” error while using VBA in Excel. You can follow the workarounds discussed in this blog to fix the error. If the Excel file is corrupt, then you can use Stellar Repair for Excel to repair the file. It’s a powerful software that can help fix all the issues that occur due to corruption in the Excel file. It helps to recover all the data from the corrupt Excel files (.xls, .xlsx, .xltm, .xltx, and .xlsm) without changing the original formatting. The tool supports Excel 2021, 2019, 2016, and older versions.

How to Fix the #Value! Error in Excel?

Summary: #Value! is a common error that occurs when using formulas in Excel. It can be due to an issue with the cells you are referencing or use of formulas in the wrong type or format. This blog will discuss some cases when this error may occur and the solutions to fix the issue. You’ll also find about an Excel repair software that can help fix the error if it has occurred due to corruption in Excel file.

You may experience the #Value! error in Excel when trying to enter invalid data type into the formulas. Sometimes, it appears when a value is not the expected type or when dates are given a text value. This Excel error may occur due to several reasons. However, the exact cause of this error is difficult to find. Below, we will be discussing some cases where you may get this error and the solutions to resolve the issues.

Case 1: Wrong Argument Data Type in Formulas

Sometimes, Excel throws the “#Value!” error if it recognizes incompatible arguments in the formulas.

For example: The Date function in the sheet expects only numerical values as arguments. In the below image you can see that when the formula’s string value is used in the month (January), it resulted in the #VALUE! error.

Solution

To fix the issue,

Double-click the formula to verify the type of arguments.

Correct the argument in the cell (B2).

The formula will work as expected.

Case 2: Using the Basic Subtraction Formula

Users often experience the #Value! error, when using the basic subtraction formula in Excel.

Solution

Check the formula and the type of values in the cell. If these are correct and the error persists, then follow these steps:

Go to the Start button on Windows, type Control Panel, and double-click on it.
Click Clock and Region > Region.

On the Format tab, click Additional Settings.

In the Customized Format window, search for List Separator.

Check if the List Separator is set to minus (-). Change it to comma (,).

Click OK.
Now, open the Excel file and again try to use the formula.

Case 3: Wrong Text Value

The #Value! error can also occur due to the formula’s wrong value.

For example: If you are using the formula to add values in cells and Excel recognizes the unexpected text value, you may get a #Value error.

Solution

To fix the issue, you can correct the value or use the SUM function. It is recommended to use functions instead of operations to reduce the errors. In Excel, the formulas with math operators may not able to calculate the text in the cells. The SUM function automatically ignores the text value(er), calculates everything as numbers, and displays the result without the #Value! error.

Case 4: Blank Space in Cells

You may get the #Value! error if your formula refers to other cells with space or hidden space. Sometimes, spaces that make a cell display blank but actually they are not blank.

Solution

You can either delete the space or replace the blank space. Here’s how:

1. Delete the Blank Space

First, check if a cell is blank or not. To do this,

Select the cell that looks blank.
Press F2.

The blank cell won’t show space.

Then, press the Backspace key to delete the space. It will fix the error.

2. Replace Blank Space

You can also use the “Find and Select” option to replace the blank space in Excel. Here are the steps:

Open the Excel file that shows #Value! error.
On the Home tab, click Find & Select > Replace.

In the Find what field, type a single space and delete everything in the “Replace with” field.

Click Replace All > OK.

Case 4: Problem with Network Connection

Many users have reported experiencing errors when using Excel online due to problems with the network connection.

Solution

Check your Internet connection and see if it is working properly.

Case 5: Wrong Formula Format

If you enter the wrong formula with a missing parenthesis or comma, then Excel can throw the #Value! error. The error can also occur if the application finds a special character within a cell.

Solution

Correct the formula and use the ISTEXT function to find the cells with issues.

Case 6: Corruption in the Excel File

If none of the above works, then it indicates the Excel file is corrupt. The formulas in the Excel file do not work due to corruption.

Solution

You can use the Open and Repair utility in Excel if you are getting the error due to corruption in Excel file. In case the utility fails or the Excel file is severely corrupt, you can use a third-party Excel repair software, such as Stellar Repair for Excel. It is a powerful tool to repair corrupted or damaged Excel files and recover all its data, with 100% integrity. The tool supports Excel 2019, 2016, and older versions.

Closure

There are several reasons that can trigger Excel to throw the #Value! error. It can occur if there is an incorrect argument data type in formulas or blank space, text, or special characters within a cell. This blog discussed the possible scenarios when this error occurs. You can apply the solutions mentioned above to fix the error. If the #Value! error occurs due to corruption in the Excel file, then you can use Stellar Repair for Excel . It is a reliable tool that helps in fixing corruption-related errors in Excel.

File Format and Extension of [filename] don’t Match in Excel File

Summary: The “File format and extension of [filename] don’t match. The file could be corrupted or unsafe” error message indicates that the Excel file you’re trying to open is unsupported, unsafe, or corrupted. Read this article to learn more about this error and how to fix this error. It also mentions an advanced Excel recovery tool to repair the corrupted Excel file and retrieve all its data in a few clicks.

You can encounter the “File format and extension of [filename] don’t match. The file could be corrupted or unsafe” error when the Excel application detects any issue with the file. This happens when you try to open an old version file format in a newer version or if the file is received from an unsafe destination. This can prevent you from opening the Excel file.

As indicated from the error message, this error occurs due to the following reasons:

The file has incorrect file extension.
The file is corrupted.
The file you are trying to open is protected.

Now, let’s see how to resolve this Excel error.

Methods to Fix the “File format and extension of [filename] don’t match” Error

Try the following methods to troubleshoot the “File format and extension don’t match” error in Excel.

Method 1: Rename the Excel File

You can face the “File format and extension don’t match” issue if the file has incorrect extension. It can occur if the file extension has been altered or you’ve mistakenly saved the file with incorrect extension. To fix this, you can try renaming the Excel file with the correct file extension.

Method 2: Check the Default Excel File Format

Different versions of Microsoft Excel use different default file formats. For example, .xls is the default file format of older versions (2003 and lower) of Excel, whereas .xlsx format is used by the newer versions (2007 and later). Opening the Excel file with an incompatible extension can cause the “File format and extension don’t match” issue. You can check the Excel version you are using and ensure it’s compatible with the Excel file you are trying to open.

Method 3: Change the Protected View Settings

You may receive the “File format and extension of excel don’t match” error if the Excel file is protected. You can check and try disabling the Protected View settings .

Caution: Changing the Protected View settings can put your system at risk. If the Excel file is being downloaded from the internet, it may contain viruses that can infect your system. So be careful before disabling the Protected View settings.

Steps to Change Protected View Settings in Excel:

In the Excel’s File menu, click on Options.
Select Trust Center > Trust Center Settings.

Under Trust Center, select Protected View and disable the below three options:
Enable Protected View for files originating from the internet.
Enable Protected View for files located in potentially unsafe locations.
Enable Protected View for Outlook attachments.

Click OK. Then, try to open the Excel file.

Method 4: Check and Provide the Excel File Permissions

Sometimes, you can get the error if you don’t have sufficient permissions to open the Excel file. This usually happens when you try to open the Excel file received from other sources. You can check and provide the desired permissions to fix the error. Here are the steps:

Locate the affected Excel file, right-click on it, and select Properties.

In the Properties window, click the Securities option and select Edit.

In the Security window, under ‘Group or users name’, select the user names. Check the file permissions and make sure Full Control is enabled. If not, then click on the Add option.

Click on the Advanced option in the Users, Computers, Service Accounts, or Groups window**.**

Click the Find Now option. A list of all users and groups appears in the search field.

Select “Everyone” from the list and then click OK.

In the object names field, you will see ‘Everyone’. Click on OK.

In the Permissions window, select “Everyone” and enable all options (Full Control, Modify, Read & Execute, Read, and Write) under Permissions for Everyone.

Click Apply and then OK.

Method 4: Repair your Excel File

As the error message indicates, corruption is one of the causes of the “File format and extension of [filename] don’t match” error. If your file is corrupted, you can repair it using Microsoft’s built-in Open and Repair tool. Here are the steps to run the Open and Repair tool to repair corrupted Excel file:

In Excel, click on File.
Click Open and then click on Browse to select the corrupted Excel file.
In the Open dialog box, click the Excel workbook (in which you are facing the error).
Click the arrow next to the Open button and select Open and Repair.
Then, click Repair to recover as much data as possible.
The Excel prompts a message after the repair process is complete. Click Close.

The Open and Repair utility may fail to give the intended results. In such a case, you can repair the corrupted/damaged Excel file using a specialized Excel repair tool . Stellar Repair for Excel is one such tool that can repair severely corrupted Excel files. With the help of this tool, you can quickly recover all the objects from the Excel file. The tool has a simple user interface that even a non-technical can use to repair the Excel files. The tool can also repair multiple Excel files at once. You can check the tool’s functionality by downloading its demo version.

Closure

You can encounter the “File format and extension of [filename] don’t match” error due to different reasons. To resolve the issue, you can check the file extension, permissions, protected settings, etc. If you suspect the error has occurred due to corruption in the Excel file, you can try repairing the Excel file using the Open and Repair tool. If nothing works for you, then try Stellar Repair for Excel . It can repair highly damaged Excel files and recover all the data while preserving the file properties and cell formatting. The tool can help you fix all the common corruption-related errors quickly.

Phone Solutions

Easy Steps to Make Excel Hyperlinks Working | Stellar