Author : Aprajita Sharma |
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.
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.
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.
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
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.
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.
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.
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)
There’s another error message which you can get while opening a file:
We Found a Problem with some content in
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.
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:
Let’s get into both of these options one by one...
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.
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.
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.
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…
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.
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:
Range("B1").Value = _
"=""Your age is """ & _
"&DATEDIF(A1,TODAY(),""y"")" & _
"&"" Year(s), """ & _
"&DATEDIF(A1,TODAY(),""ym"")" & _
"&"" Month(s), and """ & _
"&DATEDIF(A1,TODAY(),""md"")" & _
Range("B1") = Range("B1").Value
Note: To write these code you need to have basic understading of VBA (make sure check out this guide for this).
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:
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.
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.
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.