MS Office File Repair

How to fix formatting issues in Excel?

Written By Priyanka Chauhan
Approved By Alexis Llontop
Updated on 1 Jun, 2023
Min Reading 3 Min
Share

Table of Content

Summary: Formatting issues in Excel are common that result in data loss and impede businesses. These Excel issues require proper and correct troubleshooting so that the data in the Excel files is meaningful and usable. Read on to know specific methods to fix the various Excel formatting issues.

Free Download100% secure

A formatting issue in Excel may affect fonts, charts, shading, colors, borders, number formats, file formats, cell formats, hyperlink formats, and several other elements. For each of these different Excel format issues — Font Type and Size changed, Chart formatting, Conditional Formatting Rules, Text formatted to Number, Number formatted to Text or Date, Dates formatted to Numbers, etc. there are specific solutions.

A few common Excel formatting issues and how to fix them

Date Format issue in Excel:

The typed-in date changes to a number, text, another format of date (for example, MM/DD/YYYY may change to DD/MM/YYYY), or any other format that Excel does not recognize.

An example of Excel Date Format issue is as follows:

A user enters or types a Date — September 6 in a cell of Excel file. However, on pressing the ‘Enter’ key the value of the cell converts to a ‘Number.’

This implies that the cell’s number format is not in sync with what the user is trying to achieve. The cell is set to the Number format, which converts the input to a numerical value.

Solution: Right-click the cell containing the Date, select ‘Format Cells’, click ‘Date’ present under Number à Category and finally choose a Date format of choice (Example: DD/MM/YYYY format).

Note – This is valid for older versions of Excel.

In Excel 2007 and above versions; click a cell, next click the drop-down arrow in the Number section of the Home tab. In doing so, a list of the Number format appears, along with the data represented in each of the formats.

Number Format Issue and how to fix it

The Number format issue in Excel is an issue wherein a Number is formatted or changed to Text, Date, or any other format that is not recognized by Excel.

Solution: In such cases, users can use Error Checking or Paste Special as fixes.

Formatting issues in font type, size, color, and conditional formatting:

These generally occur in Excel if users open Excel files on a system other than where those were created, or if the new system runs an older Office/Excel version.

Solution: Users should make sure they save the Excel files in the native and widely accepted Excel 2007/2010 format, and not in the older Excel 97-2003 format. These are the options that users get while saving the Excel file using ‘Save As’ feature.

Users can also try to fix the Excel formatting issue by transferring all data from the current spreadsheet or workbook to a new and usable Excel workbook.

Excel XLS or XLSX file corruption:

In such situations, the Excel files need to be repaired which can be done by using either the inbuilt ‘Open or Repair’ utility or an Excel file repair software such as Stellar Repair for Excel.

The software repairs the damaged Excel (XLS or XLSX) file in 3 quick and easy steps: Select, Scan, and Save. You can save the repaired file either at the default or desired location.

Conclusion

Despite being an amazing application that allows users to create and process complex data in workbooks. Microsoft Excel is not free from concerns including the ones that arise due to formatting issues. This is true for all Excel versions, be it the latest Excel 2016 or other lower versions. Therefore, the need to employ the appropriate fixes whenever required.

In addition, the use of an advanced Excel repair tool is recommended to fix issues that appear due to Excel file corruption. An advanced software such as Stellar Repair for Excel recovers the lost formatting from corrupt Excel files. It offers an intuitive interface to repair Font formatting, Chart Formatting, Conditional formatting rules and more while preserving the data format at the time of restoring the Excel files.

Was this article helpful?

YES0

About The Author

Priyanka Chauhan

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

20 comments

Rick says:

November 13, 2019 at 6:21 pm

I made a copy of a 5-year-old spreadsheet and found that the conditional formatting stopped working on all cells and any new rules also failed. All was ok if I saved and re-opened it, but if I added a row or column, even outside the conditional area, it all stopped working again. Undo wouldn’t restore it either. The cure: very simple – I duplicated the tab using ctrl+drag, deleted the original tab and all was well. I guess something got corrupted somewhere in the original tab.

Reply
1. Eric Simson says:
  
  November 13, 2019 at 7:13 am
  
  Thanks, Rick for sharing your experience. You can also try a Free demo of Stellar Repair for Excel for a faster solution.
  
  Reply
Nancy Ward says:

August 12, 2019 at 11:43 am

A file stops responding to F9 Recalculation. Kindly share shortcut key of Recalculate Worksheet

Reply
Anik Sharma says:

June 12, 2019 at 11:48 am

Somehow all my text mentioned in excel file cells has been changed to #value!.
the reason really unknown to me… it looks formula error.

By mistake, the file has been saved and previous version or recovery from temp is not possible as well.

Reply
Brett Valera says:

January 6, 2019 at 5:32 am

Corrupt file message occurs when trying to open .xls file created in 2007 and opening in 2016.

How to repair this file and get rid of this problem?

Reply
1. Eric Simson says:
  
  January 7, 2019 at 6:34 am
  
  Don’t Panic!
  
  Just try free demo of Stellar Repair for Excel for better understanding with Excel data repair process.
  
  Reply
Felicia T. Reaves says:

January 3, 2019 at 5:31 am

When I try to open XLSX file, suddenly an error occurs:

“Excel cannot open filename.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file”

Is there any way to repair the file and access all stored files?

Reply
1. Eric Simson says:
  
  January 4, 2019 at 6:32 am
  
  For instant relief, you can try Stellar Repair for Excel.
  
  Reply
Michael E. Murphy says:

December 24, 2018 at 8:32 am

I’m using Microsoft Excel to open .DAT file. My all data is converter except dates. After file conversion Short Date, Long Date & Time all are shown as #######. I also used the formulas but none of them worked. The number in the cell shown as “1231991” (12/3/1991). Help me.

Reply
1. Eric Simson says:
  
  December 24, 2018 at 4:36 am
  
  In Text Import Wizard try selecting the column containing dates in the Date Preview. Then set the ‘Column Data Format’ to ‘Date’ and choose MDY.
  
  Reply
Barry S. Merry says:

December 21, 2018 at 8:31 am

I’m working in a Private Bank, I have list of customers & their birthdates in an Excel 2016 sheet. Suddenly date get changed to number format like 25929. Now, I’m unable to get my dates back into readable format DD-MM-YYYY.

Please provide assistance.

Reply
1. Eric Simson says:
  
  December 21, 2018 at 4:29 am
  
  It’s just a cell formatting issue. Try to apply Date Format option from the Home tab of the ribbon.
  
  Reply
Lucy A. Nelson says:

December 17, 2018 at 9:13 am

Corrupt file message occurs when trying to open .xls file created in 2007 and opening in 2016. How to repair this file and get rid of this problem?

Reply
1. Eric Simson says:
  
  December 17, 2018 at 2:56 am
  
  Use inbuilt Open & Repair utility and fix corrupt file message problem.
  
  Reply
Richard D. McGinnis says:

December 14, 2018 at 9:12 am

When I try to open XLSX file, suddenly an error occur:
“Excel cannot open filename.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file”
Is there any way to repair the file and access all stored files.

Reply
1. Eric Simson says:
  
  December 14, 2018 at 3:00 am
  
  Try to use Excel file repair software like Stellar Repair for Excel and repair the file.
  
  Reply
Larry B Worthington says:

November 28, 2018 at 9:21 am

Wonderful post.
Mostly time, I have faced number format issue while entering the product price. Now, I’ll easily change the number format issue.

Reply
1. Eric Simson says:
  
  November 28, 2018 at 6:05 am
  
  Thank you for using the method. Bookmark this post for future usage.
  
  Reply

How to fix formatting issues in Excel?

A few common Excel formatting issues and how to fix them

Conclusion

Was this article helpful?

Why Choose Stellar?

About

RESOURCES

NEWS & EVENTS

PARTNERS

Useful Links