How Can I Recover Corrupted Excel File 2016?

Error Messages Indicating Corruption in Excel File

• When an Excel 2016 file turns corrupt, you’ll receive an error message that reads: “The file is corrupt and cannot be opened .”

• But sometimes, you encounter the “Excel cannot open this file” error message due to corruption in the file.

Why does Excel File turn Corrupt?

Following are some common reasons that can turn an Excel file corrupt:

• Large size of the Excel file
• The file is virus infected
• Hard drive on which Excel file is stored has developed bad sectors
• Abrupt system shutdown while working on a worksheet

Workarounds to Recover Data from Corrupt Excel

The workarounds to recover corrupted Excel file 2016 data will vary depending on whether you can open the file or not.

How to Recover Corrupted Excel File 2016 Data When You Can Open the File?

If the corrupt Excel file is open, try any of the following workarounds to retrieve the data:

Workaround 1 – Use the Recover Unsaved Workbooks Option

If your Excel file gets corrupt while you are working on it and you haven’t saved the changes, you can try retrieving the file’s data by following these steps:

• Open your Excel 2016 application and click on the Open Other Workbooks option.

• Click the Recover Unsaved Workbooks button at the bottom of the ‘Recent Workbooks’ section.

• A window with list of unsaved Excel files will open. Click the corrupt file you want to open.

This will reopen your last saved version of the Excel workbook. If this method doesn’t work, proceed with the next workaround.

Workaround 2 – Revert to Last Saved Version of your Excel File

If your Excel file gets corrupt in the middle of making any changes, you can recover the file’s data if the changes haven’t been saved. For this, you need to revert to the last saved version of your Excel file. Doing so will discard any changes that may have caused the file to turn corrupt. Here’s how to do it:

• In your Excel 2016 file, click File from the main menu.
• Click Open. From the list of workbooks under Recent workbooks, double-click the corrupt workbook that is already open in Excel.
• Click Yes when prompted to reopen the workbook.

Excel will revert the corrupt file to its last saved version. If it fails, skip to the next workaround.

Workaround 3 – Save the Corrupted Excel File in Symbolic Link (SYLK) Format

Saving an Excel file in SYLK format might help you filter out corrupted elements from the file. Here are the steps to do so:

• From your Excel File menu, choose Save As.
• In ‘Save As’ window that pops-up, from the Save as type dropdown list, choose the SYLK (Symbolic Link) option, and then click Save.

Note: Only the active sheet will be saved in workbook on choosing the SYLK format.

• Click OK when prompted that “The selected file type does not support workbooks that contain multiple sheets”. This will only save the active sheet.

• Click Yes when the warning message appears - “Some features in your workbook might be lost if you save it as SYLK (Symbolic Link)”.

• Click File > Open.
• Browse the corrupt workbook saved with SYLK format (.slk) and open it.
• After opening the file, select File > Save As.
• In ‘Save as type’ dialog box, select Excel workbook.
• Rename the workbook and hit the Save button.

After performing these steps, a copy of your original workbook will be saved at the specified location.

How to Recover Corrupted Excel File 2016 Data When You Cannot Open the File?

If you can’t access the Excel file, apply one of these workarounds to salvage the file’s data.

Workaround 1 – Open and Repair the Excel File

Excel automatically initiates ‘File Recovery’ mode on opening a corrupt file. After starting the auto-recovery mode, it attempts to reopen and repair the corrupt Excel file at the same time. If the auto-recovery mode does not start automatically, you can try to fix corrupted Excel file 2016 manually by using ‘Open and Repair’. Follow these steps:

• Open a blank file, click the File tab and select Open.
• Browse the location where the corrupt 2016 Excel file is stored.
• When an ‘Open’ dialog box appears, select the file you want to repair.
• Once the file is selected, click the arrow next to the Open button, and then click the Open and Repair button.
• Do any of these actions:
• Click Repair to fix corrupted file and recover data from it.
• Click Extract Data if you cannot repair the file or only need to extract values and formulas.

If performing these actions doesn’t help you retrieve the data, proceed with the next workaround.

Workaround 2 – Disable the Protected View Settings

Follow these steps to disable the protected view settings in an Excel file:

• Open a blank 2016 workbook.

• Click the File tab and then select Options.

• When an Excel Options window opens, click Trust Center > Trust Center Settings.

• In the window that pops-up, choose Protected View from the left side navigation. Under ‘Protected View’, uncheck all the checkboxes, and then hit OK.

Now, try opening your corrupt Excel 2016 file. If it won’t open, try the next workaround.

Workaround 3 – Link to the Corrupt Excel File using External References

If you only need to extract Excel file data without formulas or calculated values, use external references to link to your corrupt Excel 2016 file. Here’s how you can do it:

• From your Excel file, click File > Open.
• From the window that opens, click Computer and then click Browse and copy the name of your corrupt Excel 2016 file. Click the Cancel button.

• Go back to your Excel file, click File > New > Blank workbook.

• In the new Excel workbook, type “=CorruptExcelFile Name!A1” in cell A1 to reference cell A1 of the corrupted file. Replace the ‘CorruptExcelFile Name’ with the name of the corrupt file that you have copied above. Hit ENTER.
• If ‘Update Values’ dialog box appears, select the corrupt 2016 Excel file, and then click OK.
• If ‘Select Sheet’ dialog box pops-up, select a corrupt sheet, and press the OK button.
• Select and drag cell A1 till the columns required to store the data of your corrupted Excel file.
• Next, copy row A and drag it down to the rows needed to save the file’s data.
• Select and copy the file’s data.
• From the Edit menu, choose the Paste Special option and then select Values. Click OK to paste values and remove the reference links to the corrupt file.

Check the new Excel file for recoverable data. If this didn’t work, consider using an Excel file repair tool to retrieve data.

Alternative Solution to Recover Excel File Data

Applying the above workarounds may take considerable time to recover corrupted Excel file 2016. Also, they may fail to extract data from a severely corrupted file. Using Stellar Repair for Excel software can help you overcome these limitations. The software helps repair severely corrupted XLS/XLSX file and retrieve all the file data in a few simple steps.

Key benefits of using Stellar Repair for Excel are as follows:

• Recovers tables, pivot tables, images, charts, chartsheets, hidden sheets, etc.
• Maintains original spreadsheet properties and cell formatting
• Batch repair multiple Excel XLS/XLSX files in a single go
• Supports MS Excel 2019, 2016, 2013, and previous versions

Check out this video to know how the Excel file repair tool from Stellar® works:

Conclusion

Errors such as ‘the file is corrupt and cannot be opened’, ‘Excel cannot open this file’, etc. indicate corruption in an Excel file. Large-sized workbook, virus infection, bad sectors on hard disk drive, etc. are some reasons that may result in Excel file corruption. The workarounds discussed in this article can help you recover corrupted Excel file 2016 data. However, manual methods can be time-consuming and might fail to extract data from severely corrupted workbook. A better alternative is to use Stellar Repair for Excel software that is purpose-built to repair and recover data from damaged or corrupted Excel file.

[Fixed]: Freeze Panes not Working in Excel

Summary: This blog discusses the “freeze panes not working” issue in Excel. It mentions the possible reasons behind the issue and offers workarounds and methods to fix it. If the issue is associated with corruption in the Excel file, you can use the specialized Excel repair tool mentioned in the blog to repair the affected file.

The freeze panes feature in Excel is used to freeze the row/column headings to keep them visible while scrolling the worksheet. It is a useful feature when you’re working on a large worksheet containing data that exceeds the rows and columns on the screen. Sometimes, you notice that the ‘Excel freeze panes feature is not working’. There could be numerous factors that can trigger this issue. Let’s know the reasons for the freeze pane not working issue in Excel and how to resolve this issue.

Why can’t I freeze panes in excel?

Several factors may contribute to the Excel freeze panes not working issue in Excel. A few of them are:

• The cell editing mode is enabled in the workbook in which you are trying to use the Freeze Panes feature.
• The Excel file is corrupted.
• The worksheet is protected.
• Advanced Options are disabled in Excel Settings.
• The Excel application is not up-to-date.
• You might be trying to lock rows in the middle of the worksheet.
• Your Excel workbook is not in normal file preview mode.
• Wrong/incorrect positioning of the frozen panes.

How to fix ‘Freeze Panes not Working’ in Excel?

The freeze panes option is available in the View bar. Sometimes, you’re unable to see the View option. It usually occurs if you are using the Excel Started version. Check and try to open the file in the advanced Excel version, which supports all the features. If you are using the advanced Excel version, then try the below workarounds to fix the freeze panes not working issue in Excel.

Workaround 1: Exit the Cell Editing Mode

If your Excel file is switched from normal file view mode to cell editing mode, you can encounter the freeze panes not working issue. In cell editing mode, certain features in Excel, such as the freeze panes, are temporarily disabled to prevent any conflicts. You can disable cell editing mode by pressing the ESC or Enter key. Now locate the View tab and check whether the freeze pane feature is working. If not, then try the next workaround.

Workaround 2: Change the Page Layout View

The Excel freeze panes not working issue can also occur if your workbook is opened in Page Layout view. The Page Layout view doesn’t support freeze panes. If you select page layout, the freeze panes option gets disabled.

To enable the freeze pane option, go to View and click the Page Break Preview tab.

Workaround 3: Check and Remove Options under the Data Tab

Sometimes, you can experience the “freeze panes not working” issue if Sorting, Data Filter, Group, and Subtotal options are enabled in Excel workbook. Such options, when enabled, can lead to unexpected problems with the freeze panes’ functionality. You can check and remove these features from your workbook. To do so, follow these steps:

• Open the Excel file in which you are getting the issue.
• Navigate to the Data tab.
• Check and remove the below features (if enabled):
• Sort
• Filter
• Group
• Subtotal

Workaround 4: Check and Unprotect Worksheet

The freeze panes feature may stop working if your worksheet is protected. You can try to disable the worksheet protection option. Here are the steps:

• In the Excel file, go to the Review tab.
• Click Unprotect Sheet.

After unprotecting the sheet, check whether the “freeze panes not working” issue is resolved. If not, follow the next workaround.

Workaround 5: Use Correct Cell Positioning

The freeze pane is not working issue in Excel can also occur when you use incorrect cell positioning to apply the freeze panes feature. Several users have reported facing this issue when trying to lock multiple rows with the wrong cell selection. So, use correct cell positioning to freeze the rows. For example, if you are trying to lock two rows in an Excel worksheet, then you need to click on 3rd row’s column.

What if the above Workarounds Fail to Fix the Freeze Panes not Working Issue?

If none of the above workarounds works, then there are chances that the workbook is damaged or corrupt. In such a case, you can try the below methods to repair the corrupt Excel workbook.

Run Open and Repair Utility

In case of corruption in the Excel file, you can use the Open and Repair tool in Excel to repair the file. To use this utility, follow these steps:

• In the Excel application, navigate to File and then click Open.
• Click Browse to select the workbook in which you are facing the issue.
• The Open dialog box is displayed. Click on the affected file.
• Click the arrow next to the Open option and then click Open and Repair.

• Click on the Repair option to recover as much data as possible.
• You can see a completion message once the repair process is complete. Click Close.

Use a Professional Excel Repair Tool

If the Open and Repair tool doesn’t work to resolve complex file-related issues and your Excel file is severely corrupted, you can opt for a reliable third-party Excel repair tool, such as Stellar Repair for Excel. This tool can help you repair the Excel file and recover all the data with complete integrity. You can try the software’s demo version to scan the affected file and preview the recoverable data. The software is compatible with all MS Excel versions and Windows operating systems, including Windows 11.

Closure

The “freeze panes not working” issue in Excel can occur due to several reasons, like protected worksheet, incompatible Excel version, and incorrect cell position. Try the workarounds shared in the blog to fix the issue. If the Excel file is corrupt, you can use Stellar Repair for Excel to fix the corruption issues in the file. This tool can quickly repair the Excel file and recover all the data from the file with 100% integrity.

How to Fix Excel Run Time Error 1004

Summary: Run-time errors are windows-specific issues that occur while the program is running. This blog will teach you how to fix Excel run-time error 1004. In addition, you’ll learn about an Excel repair tool that can help fix the error 1004 if it occurs due to corruption in Excel files.

VBA (Microsoft Visual Basic for Application) is an internal programming language in Microsoft Excel. Sometimes, when users try to run VBA or generate a Macro in Excel, the Run-time error 1004 may occur. This error may occur due to the presence of more legend entries in the chart, file conflict, incorrect Macro name, and corrupt Excel files. In this blog, we have discussed the reasons and shared some solutions to resolve run-time error 1004.

Why This Error Occurs?

The run time error 1004 usually occurs when you run a VBA macro with the Legend Entries method to modify the legend entries in the MS Excel chart. It happens when the chart contains more legend entries than the available space, macro name conflicts, corrupt Excel files, or data-types mismatch in the VBA code.

Ways to Fix Excel Run-Time Error 1004?

Try the below workarounds to fix Excel run-time error 1004:

Create a Macro to Reduce Chart Legend Font Size

Sometimes, Excel throws the run-time error when you try to run VBA macro to change the legend entries in a Microsoft Excel chart. This error usually occurs when Microsoft Excel truncates the legend entries because of the more legend entries and less space availability. To fix this, try to create a macro that shrinks/minimize the font size of the Excel chart legend text before the VBA macro, and then restore the font size of the chart legend. Here is the macro code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

VBCopy
Sub ResizeLegendEntries()

With Worksheets("Sheet1").ChartObjects(1).Activate
      ' Store the current font size
      fntSZ = ActiveChart.Legend.Font.Size

'Temporarily change the font size.
      ActiveChart.Legend.Font.Size = 2

'Place your LegendEntries macro code here to make
         'the changes that you want to the chart legend.

' Restore the font size.
      ActiveChart.Legend.Font.Size = fntSZ
   End With

End Sub
Note: Make sure you have an Excel chart to run the code on the worksheet.

Uninstall Microsoft Work

You may encounter a run-time error 1004 in Excel version 2009 or older versions due to conflicts between Microsoft works and Microsoft Excel. This error usually occurs if your system has both Microsoft Office and Microsoft Works. Uninstalling one of them will fix the issue. Try the below steps to uninstall Microsoft Work:

• First, open the Task Manager using the shortcut CTRL + ALT + DEL altogether
• The Task Manager window is displayed.

• Click the Process tab, right-click on each program you want to close, and then click End Task.
• Stop all the running programs.
• Open the Run window and type appwiz.cpl to open the Programs and Feature window.

• Search for Microsoft Works and click Uninstall.

Try Deleting GWXL97.Xla File

The Add-ins files with .xla extension in MS-EXCEL is used to provide additional functionality to Excel spreadsheets. Sometimes, deleting the GWXL97.XLA file fixes the run-time error. Here are the steps to delete this file:

• Make sure you have an Admins rights, open the Windows Explorer
• Follow the Path C:\Programs Files\MSOffice\Office\XLSTART.
• Find and right-click on the GWXL97.XLA file
• Click Delete.

Change Trust Center Settings

Sometimes, run-time errors might arise because of incorrect security settings. The Trust Center settings help you find the Privacy and security settings for Microsoft Excel. Follow the below steps to change the Trust center settings:

• Open Microsoft Excel.
• Go to File > Options.
• The Excel options window is displayed.
• Choose Trust Center, and click Trust Center Settings.
• Tap on the Macro Settings tab, and select Trust access to the VBA project object model.

• Click OK.

Run Open and Repair Tool

The Runtime error also arises when MS Excel detects a corrupted worksheet. It automatically begins the File recovery mode and starts repairing it. However, if the Recovery mode fails to start, use the Open and Repair tool with the below steps:

• Click File > Open.
• Click the location and folder with a corrupted workbook.
• In the Open dialog box, choose the corrupted workbook.
• Click the arrow next to the Open tab, and go to the Open and Repair tab.
• Click Repair.

You can also opt for Stellar Repair for Excel if the Microsoft Excel’s built-in tool cannot fix the error.

Use Stellar Repair for Excel

Stellar Repair for Excel is a professional software for repairing damage. xls, .xlsx, .xltm, .xltx, and .xlsm files and recovering all its objects. Here are the steps to fix the error using this tool:

• First, download, install, and run Stellar Repair for Excel.
• Click the Browse tab on the interface window to choose the corrupted Excel file you need to repair.
• Click Scan. You will see the scan progress in the scanning window.
• Click OK.
• The tool can let you preview all the recoverable Excel file components including tables, pivot tables, charts, formulas, etc.
• Click Save to save the repaired file.
• A Save File dialog box will appear with the below two options:
• Default location
• New location
• Choose a suitable option.
• Click the Save option to repair the Excel file that you have chosen.
• Once the repair is complete, it will display a message “File repaired successfully.”
• Click OK.

Conclusion

Now you know the Excel run-time error 1004, its cause, and solutions. Follow the workarounds discussed in the blog to rectify the error quickly. However, Stellar Repair for Excel makes your task of removing run-time errors easy. It’s a powerful software to fix all the issues with Excel files. Also, it helps in extracting data from the damaged file and saves it to a new Excel workbook.

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.

Fix the Too many different cell formats Error in Excel?

Excel has set a limit on the number of unique cell formats within a workbook. Excel 2003 allows up to 4000 different cell format combinations, whereas Excel 2007 and later versions allow a maximum of 64000 combinations. When this limit exceeds, you may encounter errors, such as “Too many different cell formats”. It can prevent you from inserting or modifying workbook rows or columns. Sometimes, it prevents you to copy and paste the content within the same or different workbooks.  This error may also occur due to various other reasons.

You can encounter the “Too many different cell formats” error due to the below reasons:

• Formatting is missing in the workbook.
• Size of your Excel file has increased due to excessive use of complex formatting (conditional formatting).
• Workbook contains a large number of merged cells.
• There are multiple built-in or custom cell styles.
• Excel workbook is corrupted.
• The unused styles are unexpectedly copied to new workbooks (when moving or copying a worksheet from one to another).
• Workbooks contain multiple worksheets with different cell formatting.

Methods to Fix the “Too many different cell formats” Error in Excel

First, check that your Excel application is up-to-date. It helps in preventing duplicate styles in workbooks. If the error persists, then follow the below methods:

Method 1: Simplify the Workbook Formatting

You can face the error in Excel - Too many different cell formats, if the size of your Excel file has increased due to excessive or unnecessary formatting. You can try to simplify the formatting of the affected workbook. While reducing the number of formatting combinations, you can follow the simplifying guidelines, such as using a standard font and applying borders consistently. Follow the below steps to remove unnecessary formatting in your worksheet:

• First, open the affected worksheet.
• Now, use the shortcut key (Ctrl+A) to select all the cells.
• In the Excel ribbon, navigate to the Home tab and click Clear.

• Then, select the Clear Formats option.

The above steps will remove all unnecessary formatting from the selected cells, thus reducing the number of cell formats. Besides this, you can try removing the cell patterns (if any) or use cell styles  to remove unnecessary formatting in the workbook.

Method 2: Remove Conditional Formatting

Conditional formatting is also one of the reasons behind the “Too many different cell formats” error. It usually occurs if you have applied multiple rules to various cells or cell ranges within a workbook. Each rule has its own formatting settings. If you’ve applied a large number of conditional formatting to cells, it can increase the number of unique cell formats. You can check and remove the unnecessary conditional formatting. Here are the steps to do this:

• Open the Excel file in which you are getting the error.
• Go to the Home tab and locate Conditional Formatting.

• Select Manage Rules.

• The Conditional Formatting Rules Manager wizard is displayed. You can check the formatting rules and delete the unnecessary rule by clicking on the Delete Rule option.

Method 3: Repair your Excel Workbook

Corruption in the Excel workbook can also cause the “Too many different cell formats” error. You can try the Microsoft inbuilt utility to repair the file. Follow these steps to use this utility:

• Open your Excel application. Go to File > Open.
• Click Browse to choose the affected workbook.
• The Open dialog box will appear. Click on the corrupted file.
• Click the arrow next to the Open button and then select Open and Repair.
• You will see a dialog box with three buttons - Repair, Extract Data, and Cancel.

• Click on the Repair button to recover as much of the data as possible.
• After repair, a message is displayed. Click Close.

If the Open and Repair utility does not work or fails to repair the corrupted Excel file due to any reason, then you can use Stellar Repair for Excel to repair the Excel file. It is a simple-to-use third-party Excel repair tool with an intuitive UI that enables anyone to use it without much effort. The tool can help in fixing the “Too many different cell formats” error. It does so by repairing the Excel (XLS/XLSX) file and recovering all the components, including damaged cell style, without impacting the original formatting. You can download the software’s demo version and install it to check how it works.

Method 4: Save the Excel File to a Binary Workbook (.xlsb) Format

You can also get the “excel too many cell formats” error if the size of the spreadsheet is too large. You can try saving the Excel file in binary (.xlsb) format to reduce the Excel file size. Here’s how to do so:

• In Excel, navigate to File > Save As.
• Select Excel Binary Workbook (*.xlsb) in the Save as type dialog box.

• Click Save.

Some Additional Solutions

Here are some additional methods you can try to fix the issue:

1. Check and Fix the Un-used Style Copy Issue

Many users have reported encountering the “Too many different cell formats” error when moving or copying the content of a workbook from one Excel to another and the unused styles being copied from one workbook to another. Microsoft has released a hotfix package which contains a fix for this issue. You can install this hotfix package (2598143 ) to resolve the issue.

2. Use Clean Excel Cell Formatting Option

You can check and enable the Excel cell formatting option to fix the “Too many cell formats” issue. This option will help you remove the excess formatting  in your workbook. To locate this option, click on the Inquiabove steps willre tab. If you fail to see the Inquire tab, then check if the Inquire option is enabled in the Excel Com Add-ins settings.

3. Clean up Workbooks using Third-Party Tools

The “Too many different cell formats” issue can occur if your workbook contains a large number of unnecessary styles, as mentioned above. You can use third-party tools, such as XLStyles Tool   or Remove Styles Add-in  to clean up workbooks recommended in Microsoft Guide. However, Microsoft takes no guarantee of these tools.

Closure

If you’re getting the “Too many different cell formats” error in Excel, try the methods discussed in this post to resolve it. You can simplify the formatting by following standardized guidelines and clearing all the unnecessary conditional formatting. If the error has occurred due to corruption in Excel file, then you can use Stellar Repair for Excel to repair the Excel file. It is an advanced tool that can repair Excel worksheet and recover all its objects without losing the original formatting.

Repair Office 2016 Files (Word, Excel and PowerPoint)on Windows

If you frequently work with Microsoft Word (.docx), Excel (.xlsx), and PowerPoint (.pptx) files, then issues like file inaccessibility or corruption won’t be new to you.

Let’s discuss some common scenarios which may lead to corrupt MS Office 2016 files:

Scenarios behind Microsoft Office Files Corruption

Scenario 1 – Disruption during Data Migration

You decide to move Office files from your hard drive to other removable media. However, when you try to access the data within the files post-migration, you may find Word, Excel, and PowerPoint files showing gibberish characters. Due to a power surge, sudden system shutdown, and internal mechanical failure, the files may have turned corrupt.

Figure 1- Microsoft Word file showing garbage characters

Scenario 2 – Office Files and Registry Entries Become Infected

When you open or use the Microsoft Office application, it crashes as soon as it opens. You assume that an add-in was causing the problem and restart the Office application without add-ins loaded, but the application still crashes. This may happen because of a virus infecting the Office files and registry values, thus leading to corrupt or damaged Office files.

Scenario 3 – Inaccessible or Lost Data

Suppose all your Office files are stored on a USB device, and you unplugged the device while it was still open in Windows. Now, when you attempt to open a Word or an Excel file, all the data is gone. Unsafe removal of USB or any other external storage device may corrupt the data inside your Office files or turn the file inaccessible.

How Can You Deal with Microsoft Office Files Corruption?

Here are a few solutions that can help you fix or repair Office 2016 Files Corruption:

Solution 1 – Use Microsoft in-built Repair Utility

Microsoft recommends using its in-built repair utility, ‘Open and Repair’, to fix corrupt Office files. Follow these steps to understand how you can use the utility to repair the corrupt Word, Excel, and PowerPoint files:

• Launch the MS Office application whose file you want to repair:

1. To repair corrupt Word (.doc, .docx) files, launch MS Word
2. To repair corrupt Excel files (.xls, .xlsx) files, launch MS Excel
3. To repair corrupt PowerPoint (.ppt, .pptx) files, launch MS PowerPoint

• Click File, and then click the Open tab.
• Click Navigate to the location or folder where the Word, Excel, or PowerPoint file is stored.
• Select the corrupt file you want to repair by single-clicking on it, and then find the Open button and click on the drop-down menu next to it.

• From the drop-down menu, click the Open and Repair option and follow the subsequent instructions to repair Office 2016 files.

Solution 2 – Repair Office 2016 Installation

Try repairing the Office installation to fix the MS Office files. The steps to repair your Office installation may vary depending on the operating system you are using.

For Windows 7

• Open your PC’s control panel
• Click Programs

• Click Programs and Features, and then click Uninstall a program option

• Right-click on the Office application you want to repair, and then click Change

• Under Change your installation of Microsoft Office Professional Plus 2016, choose Repair and then click Continue.

For Windows 10

• Right-click the Start button, and type in Apps & Features (For Windows 10)

NOTE: This step will work for Windows 10/8/8.1/7 and Vista

• Click Programs from the window that opens, click on the MS Office product you want to repair, and then click on Modify

Note: Following the step will repair the entire Microsoft Office suite even if it contains only one application you want to repair such as an Excel or PowerPoint file. But, in case you have a standalone app installed, try to locate that application by name.

• Under Change your installation of Microsoft Office Professional Plus 2016, choose Repair, and then click Continue to initiate the repair process.

• Once the repair process completes, you’ll be prompted to restart your PC. Click Yes

Solution 3 – Use Stellar Toolkit for File Repair

Repair MS Office 2016 files by using Stellar Toolkit for File Repair . This software comprises four essential utilities that can help you repair corrupt MS Word, MS Excel, MS PowerPoint, and PDF files.

The toolkit helps repair corrupt Office 2016 and other version documents and files while maintaining the original file format, which is less likely achievable with inbuilt methods. Follow these steps to repair MS Office 2016 documents by using the Office file repair tool:

• Download and install Stellar Toolkit for File Repair.

• Launch the software.
• From the software’s main interface, select the MS Office file you want to repair.

• From the window that pops up, select the corrupted file to be repaired.

Note: If you don’t know the exact location of corrupt office files or if they are large in number, you can locate the files by using the Find/Search option included in the software.

• After selecting the file, click the Scan button to initiate the repairing process.
• Once the scanning process is complete, all the recoverable information is displayed in the software’s left-hand panel. Click on any item to preview it before recovery.
• To save the repaired data, click the Save button, and enter a destination of your choice.
• Click OK.

Conclusion

This post outlined possible scenarios and their causes that may lead to corruption in MS Office 2016 files. It also emphasized how the inbuilt methods such as Open and Repair, and Repair Office Installation help to resolve the corruption issues. But these are not competent enough to resolve all the errors. With Stellar Toolkit for File Repair, you can resolve all sorts of corruption issues and recover data of Office 2016 files – Excel, Word, PPT, and PDF – in their original state.

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.

Conclusion

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.