ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Losing Formatting

Jennifer is in an office with ten people, all using Excel 2007. Several of them have intermittent problems with losing formatting. The formatting includes fonts, colors, shading, borders, number formats, and so on. They save and close the workbook; the next time they open it, the formatting is gone. They can't find a pattern, and were wondering if this is a known problem.

There is no acknowledgement from Microsoft that it is a known problem (at least, as far as we can find), but it apparently is a problem shared by many people. As far as we can tell, the problem could be due to two different potential causes.

First, you should make sure that your workbook is being saved in native Excel 2007/2010 format. If you are saving it in the older Excel 97-2003 format, then it is possible that the losses you are seeing are due to the formatting not being supported in the older format. This is particularly true with colors and conditional formatting.

The other possible cause is that the workbook file is corrupted in some manner. The solution is to transfer your data from the current workbook to a new workbook and then see if the problem occurs in the new one.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9484) applies to Microsoft Excel 2007 and 2010.

Related Tips:

Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates. Check out ExcelTips: Times and Dates today!


Comments for this tip:

Heidi    09 Oct 2013, 10:38
If your spreadsheet is "Shared" and just one user seems to go in and lose formatting, you could try getting someone else to remove the troublesome user's custom view. This will often cure the problem whereby just one user is corrupting the format.
Barry Fitzpatrick    25 Jul 2013, 07:38
This might be unrelated but Excel has a limit to how many different cell formats it can handle. Because different Users will be handling different spreadsheets the chances are that each machine will have a different number of cell formats (I am not sure if this a cumulative effect or only applicable to currently open workbooks). Opening a file with additional cell formats could on certain machines take the total number over Excels limit which result in the formatting being stripped out.

The following Microsft KB article gives more details: http://support.microsoft.com/kb/213904
plus some tips/tools on how to remove some cell formats.
GM    24 Jul 2013, 06:28
I have the problem in Excel 2010 with "general" format being applied to "date" ,"currency", "hyperlinks", colors go missing in fact when I re-open one work book we are taking bets to see where the latest revert backs are to be found. Any one tried open office...............
Rob    19 Jun 2013, 06:41
I had a problem in Excel2010 with "general" format changing to "date" or "currency" format.

I tried copy-paste into a new worksheet, but after re-applying my desired cell-formatting the problem continued. I think the origin of the problem lies in the fact that by applying your own customized cell-formatting Excel (somtimes?) creates "new" styles based on the original "General" Style-format. These new styles appear in the top of the Style-selection Box and the name(s) look like the default Style.

By removing these "new" style formats (right mouse click) in the corrupted file, all connected cells fall back to the default Style. After deleting the "bastard" styles, I re-applied all my desired cell-styles and formatting and the style selection Box remained clean of unwanted styles.

So far, so good. I saved and re-opend the file on two different computers (Vista an WinXp) in Excel2010 and the formatting DID NOT CHANGE !!

I hope this information can help some of you in beating this annoying bug in Excel.
lester    14 Apr 2013, 18:15
Found the answer.

Re-set your Accessibilty Options

1. Click Start, click Control Panel, and then click Accessibility Options.
2. Click the Display tab, and then click to clear the Use High Contrast check box or adjust them. (You may need to play around with this setting to your liking).
3. Click OK to close the Accessibility Options dialog box.

I am not sure what I did in changng my desktop appearance settings that corrected the issue, but my colors are back!
lester    14 Apr 2013, 17:42
My problem is ocurring on Excel 2007. All of a sudden, the font/cell colors revert to the default black and white. The font style and size are intact.

1) If I copy a page from the black and white workbook to Word 2007 (using the default paste option) in the same computer, the copied colors display in the Word doc.

2) If I copy the above back to the excel workbook, the colors are gone.

3) When I copy the file to a different computer that uses Office 2007, the same excel workbook displays the colors.

4) I also noticed that the 'colorless' excel shows 'selected' rows, columns, cells and all format buttons (i.e. align-left/right/center, font attributes-Bold/Italic, etc.) medium blue. I think it used to be grey.

PJ    11 Apr 2013, 10:29
We are having same issue. It is only occurring for one user though. It just started about a month ago. We are using 2010. I enter all the formatting, she uses the file and formatting goes away. I then put back in or she does but the next time she opens, gone. I have saved the excel files in 2010 format.

Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 3+4? (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.