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.

How to Fix a Corrupted .xls File? The Everything Guide

Undoubtedly, Excel is so powerful that it can help you to process, analysis, and store data, in masses.

That’s the reason it has been there for years and helping this world in data.

But…

With all those powers comes some nasty problems which no Excel users like to face. Can you guess what I’m talking about?

Think about a Corrupted Excel File. Nightmare? Isn’t it?

And do you remember that last time when you have opened a workbook and you got a message that this workbook is might corrupt?

The TRUTH is, this is something which you cannot avoid, but, you can prepare yourself in the best way and deal with it like a PRO.

So today, in this post, I’d like to share with you to everything you need to know about a corrupt Excel file (.xls), why it happens, how to fix it like a PRO, and much more.

…let’s get started.

Note: In this post, we’ll be covering the .xls version (which is the extension for the file which is created in Excel 2007 or the earlier versions) and if you want to know about the new version, here’s the quick fix for that.

Why My Excel File Got Corrupted?

There can be one or multiple reasons for an Excel file to get corrupted. Below I have detailed about some of the major of them.

1. Large Excel File

You can store data in a workbook the way you want but sometimes using excessive thing can make an Excel file bigger in size.

And that kind of data files can crash at any point in time. Here are a few things which make the Excel files heavy, like

• Conditional Formatting.
• Colors formatting.
• Using merged cells in place of text alignment.
• Volatile functions: Formulae that iterate every time you open or change a cell value; OFFSET, NOW.
• Using a complete column or row as a reference than the data set range.
• Using complex formulas; VLOOKUP in place of Index/Match, Nested If in place of MAXIFS, MINIFS.
• Calculations or reference across workbooks.

Related: How to Fix Formatting Issues in Excel

2. Abrupt System Shutdown

Shutting down the system without following the procedure can corrupt your data file.

This shut down can be due to a power failure or any other unexpected technical challenges.

So it is always important to follow the procedures and shut down your system properly to avoid data losses.

3. Infected Excel File (Virus Attack)

This is the most common and obvious reason for Excel file corruption.

Although we always keep our system safe using various Antiviruses, still there is always a probability of virus attacks and loss of important files.

It is always advised to use a safe and strong antivirus compatible with your system requirements.

What are the Signs to Know When an Excel File is Corrupted?

In this section, we will discuss what are the signs which you can get when an Excel file is corrupted, let’s dig into it.

1. The File is Corrupt and Cannot Be Opened

This is one of the most common messages you can see when your workbook is corrupted.

But there is also a chance that it is just because of the version compatibility where you have a .xls file but you are using the latest version of Excel check out this detailed post by Priyanka

2. We Found a Problem with some Content in this File…

There’s another error message which you can get while opening a file:

We Found a Problem with some content in Do you want us to recover as much as we can? If you trust the source of this workbook, click yes.

There are a lot of applications out there (I think almost every) which exports the data as a .xls format. Those files have a greater chance of having this kind of error.

There can also be a situation where you get the error:

“Filename.xls” cannot be accessed. The file may be corrupted, located on a server that is not responding.

Well, this message is a bit misleading.

You won’t be able to decide that your file is actually corrupted or just not on the location.

My Excel File Got Corrupted, now What Should I Do?

There are many ways to recover the data from the corrupt excel files. But before you start, it is always advised to create a copy of the corrupted file.

You can save a lot of time with Stellar Repair for Excel, which make data recovery just with few clicks.

But before you go for a data recovery software, let’s try out some manual steps which can help.

When a workbook get corrupted the first thing comes to the mind is to recover data from it…

…and you what there’s a simple option there in the Excel which you can use to do this. Below are the steps you need to follow:

• First of all, open the Excel and click on the office icon.

• After that, go to the “Open” and select the file which is corrupted.

• Now, click on the open drop-down and select “Open and Repair”.

• At this point, you have two options:

1. Repair File
2. Extract Data

Let’s get into both of these options one by one…

1. Repair File

This option helps you to repair the file and the moment you click on it it takes a few seconds afterward and shows you the result with a message box and also provide you a log file.

And once it is done with repairing, you’ll get your file opened and you can save that file as a new copy.

Yes, that’s it.

2. Extract Data

If somehow you aren’t able to get your file repaired, you can also extract data from that file using “Extract Data” option.

Even in this option, you can get data in two ways.

1. As Values
2. With Formulas

In the first option, Excel simply extracts data as value ignoring all the formulas driving those value (which is the best way if you just need to have that data back).

But in the second option, Excel tries to recover the formulas as much as possible.

Check out this smart technique by Jyoti which you can use it you aren’t able to recover data from the file.

Preventions to Not to have any Excel File Go Corrupt in Future

Future is fragile, what I’m trying to say is the more you work in Excel and process data there could be a chance that your workbook goes corrupt.

If there’s no security then what an EXCEL POWER user should do?

Well, there are few things which you can do or take care of while working with Excel so that you won’t have to worry about corruption of Excel workbooks.

Let’s see what you can do…

1. Change Recalculation Option

Now here’s the thing when you work with a hell lot of data, there a common thing that you gotta using formulas. Right?

But, the thing these formulas are something which makes your Excel file slows down sometimes make them go corrupt.

There’s one small tweak you can do in your workbook is change the calculation method.

Now with the manual calculation, you just need to whenever you open your file it won’t recalculate all the formulas.

And when you update your data you can simply click on the “Calculate Now” and it will calculate all the formulas again.

Quick Tip: Beware of Volatile Functions and use them with caution as recalculates them every time you change something in the worksheet.

2. Use VBA Codes Instead of Formulas

Now, this is what I do when I need to use complex formulas in a workbook.

Here’s how you can do this: Let’s say you have a formula in the cell A1, like below, which calculates the age.

=“You age is “& DATEDIF(Date-of-Birth,TODAY(),”y”) &” Year(s), “& DATEDIF(Date-of-Birth,TODAY(),”ym”)& “ Month(s) & “& DATEDIF(Date-of-Birth,TODAY(),”md”)& “ Day(s).”

Now, instead of simply entering it into the cell A1 which I would write a macro code which inserts this formula into the cell A1 and then convert it into the a value.

Here’s the code:

Sub CalculateAge()
Range(“B1”).Value = _
“=””Your age is “”” & _
“&DATEDIF(A1,TODAY(),””y””)” & _
“&”” Year(s), “”” & _
“&DATEDIF(A1,TODAY(),””ym””)” & _
“&”” Month(s), and “”” & _
“&DATEDIF(A1,TODAY(),””md””)” & _
“&”” Days(s).”””
Range(“B1”) = Range(“B1”).Value
End Sub

Note: To write these code you need to have basic understading of VBA (make sure check out this guide for this).

3. Use a File Recovery Application

Recently we asked a quick question to our readers on ExcelChamps that if they have ever faced a situation where they got a corruption message in Excel.

You’ll be astonied to hear that 50% percent of the people said “YES” they faced this thing in the past.

Now, this is alarming, if you are heading a team or you have a bunch of people in your company who use Excel…

…there’s a high probability that half of them gonna face this issue. So the best way to deal with this to have an App FIX your Excel file for you.

With STELLAR REPAIR FOR EXCEL, you just need a few clicks, yes that’s right. Let me show you with the below steps:

• First of all, download the app and install it (it’s simple).

• After that, open the app and click on the “Browse” and simply select the file which is corrupted.

• In the end, click on the REPAIR to let the Excel repair software fix your file (it takes a few seconds).

Once you complete repairing your file, you’ll get a message in your on the status bar and after that, you can open your file.

Final Thoughts

If you are a POWER Excel user then there’s a must for you to have known how to deal with a situation where you got a corrupt Excel file.

But I must recommend you to TRY OUT Stellar Repair for Excel so that’s you don’t have to worry about your Excel files anymore.

I’m sure you found this post helpful, and please don’t forget to share this tip with your colleagues, I’m sure they’ll appreciate it.

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

1
2
3
4
5
6
7
8

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:

1
2
3
4
5
6
7

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.

‘Open and Repair’ Doesn’t Work in MS Excel

Summary: In this Blog, we will go through Microsoft office most important product i.e Microsoft excel, let’s get into all possible Manual and an alternate method to deal with MS Excel open and Repair doesn’t work issue, read on to know more.

Whether you are a student or an entrepreneur, the features of Microsoft Excel do not delude anyone. Setting goals, creating budgets, analyzing data, calculating salaries, is there anything that Excel can’t do? All of us have used it and trusted it to calculate and provide a solution to our most difficult problems. However, like every other software application, this otherwise reliable application can sometimes fall prey to unexpected errors which can even threaten to make our critical data inaccessible.

A good idea to avoid loss of data when a Microsoft Excel file becomes corrupt is to take some proactive measures, such as saving a backup copy of your files and creating an automatic recovery file at periodic intervals. If you are faced with a corrupted Excel file, you know you can still use the ‘Open and Repair’ function provided by Microsoft to fix and open corrupt Excel file. However, what should a user do when ‘Open and Repair’ is not working? This is a query shared by millions of Excel users worldwide. Sometimes, the ‘Open and Repair’ functionality of Excel stops working due to unknown reasons. In such cases, if users face Excel file corruption, they get stuck with no idea how to fix the Excel file.

Manual method “Reference”

In this guide, we’re providing you with the solutions to this very problem. If Excel ‘Open and Repair’ is not working, read on to find out the procedures that you can perform to open corrupted files.

‘Open and Repair’ doesn’t work: Try an alternative solution i.e. Stellar Repair for Excel to recover everything from corrupt Excel files.

How to Fix Excel file that Won’t Open

If your workbook is opening in Excel, there are two options to recover its data. It would be best if you try to perform one, and if you are unsuccessful, move on to the next.

Revert the workbook to the version that was saved before the corruption

• Launch Excel and click File -> Open

• Select the file that is corrupted and open it
• Click ‘Yes’ to save the copy of the workbook that was saved before corruption

Important Note: If you use this method, you will lose all changes made to the file after it was corrupted.

Save the workbook in the SYLK file format

• Launch Excel and click File -> Save As.
• In the Save as Type field, select SYLK (Symbolic Link) from the drop-down menu, and click Save.

• To save only the active sheet in the workbook, click OK. The system will display a message that the sheet has features that are not compatible with the SYLK file format.
• Click Yes.
• In Excel click File -> Open.
• Select the file that you saved in SYLK file format and open it.
• In Excel click File -> Save As.
• In the Save as Type field, select Excel Workbook from the drop-down menu.
• In the File Name field, type a new name for your workbook and click Save.

The SYLK file format will filter out the corrupted elements from your workbook, thereby restoring your data.

Important Note: Using this method you only be able to salvage the active sheet in the workbook.

How to Open/Fix an Excel file that cannot be opened

In this case too, there are two options to recover the data. Try to perform one, and if you are unsuccessful, move on to the next.

Set the calculation option to Manual

• Launch Excel and click File -> New.

• From the Available Templates window, select Blank workbook.

• Click File -> Options.
• Under Formulas, in the Calculation options section, click Manual.

• Click OK.
• In Excel click File -> Open.
• Select the corrupted file and open it.

The system opens the corrupted file. Since the workbook won’t be calculated, it might open.

Link the workbook to external references

• Launch Excel and click File -> Open.
• Copy the name of the corrupted file and click Cancel.
• In Excel click File -> New.
• From the Available Templates window, select Blank workbook.
• In the new workbook, on cell A1, type the following:

=File Name!A1

In the above command, the filename is the name of the corrupted file.

• On the Update Values dialog box, select the corrupted file and click OK.
• On the Select Sheet dialog box, select the sheet and click OK.
• Select cell A1. Select the same range of rows and columns as occupied by the data in the corrupted sheet, including cell A1.
• Under the Home tab, in the Clipboard section, click Paste.
• While the range of rows and columns are still selected, click Copy.
• Click the Paste
• Under Paste Values, click Values.

Note: This method lets you recover only the data but not the values and formulas from the workbook.

Alternative Solution

In addition to the above-mentioned techniques, you can also use macros to extract data from a corrupted workbook. However, macros are generally risky, and executing them needs prior technical knowledge.

Thus, if the above methods do not yield the desired results, a quick and easy way for reconstructing Excel files is to use Excel Recovery Software . Stellar Repair for MS SQL software is the best choice for rebuilding damaged Excel files and restoring everything to a new Excel file. The product lets you recover table, chart, chart-sheet, cell comment, image, formula, sort and filter data from damaged workbooks and also allows you to fix multiple files at one go.

Wrapping it up

Though one of the above-mentioned techniques should recover Excel file if ‘_Open and Repair’ utility doesn’t work_, in case you’ve reached nowhere even after using them, contact Microsoft support for more help.

How to Fix Excel has Encountered a Problem

While working on MS Excel, you may encounter various errors that can hamper your work and productivity. One of the errors that you may receive is ‘Microsoft Excel has encountered a problem and needs to close’.Due to this error, your Excel program may stop and asks you to recover the data from Excel file.

What are the Reasons for ‘MS Excel has Encountered a Problem’ Error?

Following are some primary causes that may result in the ‘Microsoft Excel has encountered a problem and needs to close’ error:

• Corrupt Excel File: If you try to open a corrupt or damaged Excel file, the file may not open and displays this error message.
• File not Saved Properly: If Excel files aren’t saved correctly, this error may occur when you open the file.
• Incompatible File Version: If the MS Excel application version does not support the Excel file version, the file may not open and throws the error.
• Issues with MS Office/MS Excel Installation: This error can sometimes be caused due to damaged MS Office/MS Excel installation.

How to Fix ‘MS Excel has Encountered a Problem’ Error?

You can resolve the error by using the following methods:

1. Try to Open Excel in Safe Mode

Open the Excel application in safe mode and then try to open the Excel file. This will help you find out if the problem is caused by some incompatible add-ins. The steps are as follows:

• Hold Windows + R keys together to launch the Run dialog box.
• Type Excel /safe in the search box and hit Enter.
• If your Excel application opens in safe mode, it means that the issue is caused due to incompatible or faulty add-ins. In such a case, you need to disable the add-ins:
• Go to the File menu and click the Options menu. Further, choose the Add-ins option.

• Now, choose the Go button at the bottom of the Excel Options window.
• A list of available add-ins appears.
• Now, uncheck the boxes against the add-ins.

2. Disable Macros Using the Trust Center Settings

Sometimes, the Macros prevent Excel from managing the files. You can disable the Macros to resolve the issue. Follow these steps:

• Launch your MS Excel application.
• Now, go to File > Options > Trust Center.
• Further, click the Trust Center Settings.

• Now, navigate to the Macro Settings option.

• Herein, select the ‘Disable all macros with notification’ radio button. Then, click OK.

3. Repair MS Office Application

Sometimes, problems with your MS Office application may cause the Excel has encountered a problem error. In such a case, you need to repair your MS Office application. Here are the steps to do so:

• Launch Control Panel > Uninstall a Program.
• Find your MS Office application and click the Change option.
• A new window will appear. Herein, select the Repair option.

• Now, follow the MS Office installation wizard to finish the repair process.

What to do if the above methods don’t work?

If you have tried the solutions mentioned above and are still not able to resolve the ‘Excel has encountered a problem and need to close’ error, it indicates that the Excel file is corrupt. You can use a professional Excel repair software, such as Stellar Repair for Excel , to repair the corrupt file. The software repairs the file and retrieves all the data, including the tables, charts, formulas, etc. from the damaged workbook. It is compatible with all the MS Excel versions.

To know how Stellar Repair for Excel works, see the following video:

To Wrap Up

The ‘Excel has encountered a problem and needs to close’ error may occur due to different reasons. You can fix this error by following the methods mentioned in this post. If the error has occurred due to corruption in the Excel file, you can use a third-party Excel repair tool, like Stellar Repair for Excel. The software can repair damaged or corrupt Excel file of any size and retrieve all the data.

Data Disappears in Excel - How to get it back

Summary: You may face the issue of ‘Excel spreadsheet data disappeared’ after changing Excel file properties and formatting rows and columns. This blog discusses the possible reasons for data disappearance and the solutions to fix the issue. Also, it mentions an Excel file repair tool to retrieve the data from the file. Sometimes, while editing or formatting a cell in an Excel spreadsheet, the data may go missing or disappear. Let’s discuss in detail the reasons that may cause the ‘Excel data disappeared’ issue along with the solutions.

Probable Reasons for Data Disappearing in MS Excel and Solutions Thereof

Reason 1 – Unsaved Data

While entering data in an Excel spreadsheet, it is important to save the data at frequent intervals. Doing so prevents any unsaved data from disappearing if you lose power or accidentally click ‘No’ when prompted to save the file. Unfortunately, such a situation is quite common as users often close the file without saving the recently made changes to a spreadsheet.

Solution – Use the ‘AutoSave’ Feature

With the AutoSave feature enabled in Excel, data won’t be lost in the event of power failure or abruptly closing the Excel program. By default, Excel automatically saves the information in a spreadsheet after every 10 minutes. You can reduce the limit to a few seconds to reduce the chances of Excel file data lost after being saved.

Reason 2 – Changing Excel Format

You can save an Excel file in various formats, like spreadsheet, text, webpage, and more. However, at times, saving the spreadsheet in a different format may lead to missing data. For example, when you save a workbook to a text file format, all formulas and calculations applied to the data will be lost.

Solution – Adjust a Spreadsheet for the Changed Format

If you’re changing the format of a spreadsheet, make space for the rows and columns. Also, remove all calculations before saving the file.

Note: If the sheet is shared on multiple computers, then save the file in compatibility mode.

Reason 3 – Merging Cells

You can combine two or more cells data to make one large cell. This technique is primarily used to fit the text of a title in a sheet. If there is data in two or more cells, then only the data in the top-left cell is displayed and the data in all other cells is deleted. If the other merged cells have been populated with data after merging, the data is not featured and it does not appear even after remerging the cells.

Solution – Merge Cells inside One Column

To merge cells without data loss, combine all the cells you want to merge within a column and do the following:

• Select the cells to be combined.
• Ensure that column width is wide enough to fit the contents of a cell.
• In the spreadsheet, under the Editing group, click ‘Fill,’ and then click ‘Justify.’

• Under Alignment, click on the ‘Merge & Center’ option to center align the text. Or, click on ‘Merge Cells’.

Note: This solution works for text only. You cannot use it to merge formulas or any numerical values. If you need to combine two or more cells with formula into a single cell, try using the Excel CONCAT function .

Reason 4 – Cell Formatting

Cells and text in the cells can be displayed in different colors to make the spreadsheet simple to create and infer. You may experience data loss when you try to modify the data or change the color or size of the data. Though the information may exist, the data may show an error due to the following reasons:  

• White-colored text will not show in a white-colored cell
• Large font-sized data may not appear in small-sized cell
• Calculations may show (#VALUE) error after cell-formatting

Solution – Check and Clear Formatting

Make sure to use dark-colored text on a white-colored cell. Also, resize the cell to fit the text size. Check if numbers in a cell are entered as text. If so, you need to apply a number format to the text-formatted numbers. Read more about it, from here .

What Else You Can Do to Resolve the ‘Excel Data Disappeared’ Issue?

If you can’t recover the missing Excel file data, try to repair or extract the data from the file using the built-in Excel repair tool. Follow the below steps to use the tool:

• Open MS Excel, click File > Open > Computer > Browse.
• On the ‘Open’ window, select the file you want to repair and then click on the Open dropdown.
• Select Open and Repair.

Use the ‘Repair’ option to repair the file and recover as much data as you can from the repaired file. If this doesn’t work, use the ‘Extract’ option to recover the data.

If you fail to retrieve the disappeared data from that file using the above-listed steps, opt for an Excel repair tool , like Stellar Repair for Excel. This software has a proven track record of repairing corrupt or damaged Excel files and recover all the data.

The software helps:

• Fix all corruption errors. It helps in getting back the data which has disappeared.
• Repair a single as well as multiple Excel files.
• Recover all components of XLS/XLSX files – tables, chart sheet, cell comment, image and more.
• Preserve the worksheet properties and cell formatting.
• Support the latest Excel 2019 and earlier versions.

The Excel repair software repairs the Excel file in these simple steps:

• Launch and open the software.
• Select the corrupt Excel file by using the ‘Browse’ option. If the file location is not available, then find the Excel file using the ‘Search’ option.

• Click ‘Repair’ to scan the corrupt file.

• Once the repair process is complete, verify the components of Excel file and check if the available preview shows complete data that disappeared from Excel.

• Save file at default location or preferred location.

The Excel file with all the restored data will be saved at the selected location.

Click here

It is better to repair the affected Excel file than suffer the loss when data or text disappears in Excel. A professional software ensures that users get back all the data in the form of a new Excel file. Stellar Repair for Excel software repairs the corrupt file without modifying the original content and file format. The software’s easy-to-use user interface lets you perform the functions without formal software training and technical expertise.

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.