Do you think your Excel workbook is corrupted? Are you having trouble opening or saving it? Recovering corrupted Excel files can be a challenging process.
If you are able to open the workbook, this method may save you time and headaches. I encourage you to take these steps sooner rather than later in order to have the best chance of recovering your data, formulas and functions.
Step 1 – Select Worksheet(s)
Right-click the sheet’s tab, then click Select All Sheets.
Step 2 – Show all Formulas
To display all formulas, click the Formula tab on the ribbon. In the Formula Auditing group, select Show Formulas. (Shortcut: CTRL ~)
Step 3 – Export the Worksheet(s)
The next step is to export each worksheet to a text file.
Excel 2010 Instructions
Select a worksheet and click the File tab, then Save As. Next to the Save as type select Text (tab delimited). Click the Save button.
When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if our want to keep the workbook in this format. Click the Yes button.
Excel 2013 Instructions
There are at least two ways to export a worksheet to a text file in Excel 2013.
- Select the File tab, then Export. Select Change File Type. Under Other File Types, Select Text (Tab delimited). Then click the Save As button.
- Select the File tab, then Save As. Click Computer, then Browse. Under Other File Types, Select Text (Tab delimited). Then click the Save As button.
When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if you want to keep the workbook in this format. Click the Yes button.
Shortcut (all Excel versions):
F12, then select Text (Tab delimited). (If you have a newer keyboard, you might have to hold down the FN key while hitting F12)
When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if our want to keep the workbook in this format. Click the Yes button.
Remember: you will have to do these steps for each worksheet.
Step 4 – Create a New Workbook based on the text file.
Close the workbook you suspect is corrupted.
Create a blank workbook (Shortcut: CTRL N).
Click the File Tab, then select Open (Shortcut: CTRL O).
In the dialog box, change the file type to Text Files.
Select your text file, and click the Open button.
This launches the Text Import Wizard.
In step 1, select Delimited, then click Next.
In step 2 of the import wizard, make sure a checkmark is next to Tab, and click Next.
In step 3 of the import wizard, you can choose the format of each column (this step is usually unnecessary). When you are done, click Finish.
Your text file will be imported into Excel with all of your data, formulas and functions restored.
You can save the text file as a workbook and continue opening the other text files you created from exported worksheets in Step 2.
Once you’ve imported all of the data, save your new Excel workbook.
Do you have an Excel question?
Comment below and it just might make it to a blog post!
About the Author
Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.
Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.
Click to Grab Your Excel Ebook – Free!