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.
3. “Filename.xls” cannot be accessed
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:
- Repair File
- 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.
- As Values
- 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.
About the Author
Aprajita is the Co-Founder of ExcelChamps. She has an MBA in Sales and Marketing and has been using Microsoft Excel for the last 8 years. Her journey started from learning a basic pivot table from Google which made her fall in love with Excel.
7 min read
