Losing Formatting

Written by Allen Wyatt (last updated April 7, 2020)
This tip applies to Excel 2007 and 2010


47

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Dynamic Path and Filename in a Footer

You can easily place a path and filename in the footer of your document. What do you do if it appears that these elements ...

Discover More

Resizing Very Large Tables

When pasting a table into your document, you might discover that it extends beyond the right margin of your page. Here ...

Discover More

Restarting Footnote Numbering after Page Breaks

Footnotes can be handled many different ways in a document. If you want to restart the numbering of your footnotes every ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Getting Rid of Negative Zero Amounts

Have you ever seen a worksheet in which some zero values have a negative sign in front of them? There's a reason for ...

Discover More

Pasting Numeric Values in Other Programs

When you paste information from Excel into other programs, you may get more than you actually want. It is not unusual for ...

Discover More

Understanding Monospace Fonts

Information in a worksheet needs to be displayed using fonts. If you understand the two different types of fonts ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2022-09-30 08:07:25

Somit Sinha

Thank you!


2022-07-14 11:35:49

JP

This was really helpful. Thank you!


2021-06-10 17:28:07

genevie p baca

In order to retain formatting when saving files, we have to save Excel file in 2007/2010 format; however all that is offered in 97-2003 format.


2019-11-06 00:06:53

Aziz

For some excel files, I am losing the pre-mentioned as well as the formulas used to calculate data. The only left is numbers


2019-09-07 23:37:54

Al M

Still same problem in Excel 2013. Can see font color or Bold/Italic changes while formatting, and then it reverts to default black when you Save or click on another cell. By few other similar questions in Comments it looks like the author has abandoned this article.


2018-10-22 22:03:26

rosie

i had highlight some cell and save it. the day after, all the highlight was missing. and it changed the date to serial number. how can i fix this? i had no powerful skills in using excel so i hope you can help me.


2018-01-22 05:52:11

Naomi

I had this issue with the formatting disappearing when I clicked Edit Workbook. i found that this was because the spreadsheet automatically opened in Page Break View rather than Normal. Selecting Normal brought the formatting back.


2017-01-12 13:28:44

Paul Guether

I have found a solution that works for god knows what reason. I have graphs where I have line formulas color matched to the line color. If I choose any other color than the one I want and change the text color and then pick the color I want and change the text color, it seems to stick.


2017-01-09 23:32:11

PHIL

In Excel 2916 I keep highlighting some of the 2900 numbered records (lines) of my mailing list. I highlighted the send addresses in yellow & the don't send in pink. I save very few edits to both the documents & thumb drive. WHenI reopen after hours of work, I still cannot see any of the highlights. They're gone.
SUCKS!


2017-01-09 18:09:45

JC

I have an xls file with tick boxes in but when I save it as an xlsx file and reopen the tick boxes have disappeared - and I dont get the opening message that the file contains macros.

Does anyone know why?


2016-12-21 06:43:30

jaedimurph

"After "FIND and REPLACE" formatting disappears. HELP!"
I have submitted this query several times with no response.
At this stage I would be happy if some kind soul would put me out of my misery by telling me there is no solution.


2016-12-20 09:40:28

V

Save your file as a xlsx instead of a xls. From someone who just spent the past hour fighting with excel, I promise you it'll work!


2016-10-28 05:42:41

jaedimurph

After "FIND and REPLACE" formatting disappears. HELP!
I would be happy if text in italics stayed in italics.


2016-10-28 03:25:48

jkh

lose format in ms excel 2007 when save in excel97 2003 please guide to recover the complete formate


2016-10-16 14:01:23

Jagdeep Singh Sidhu

I had the same problem with .txt and .csv formats but then I tried .xls and it works for me!


2016-10-15 16:54:58

GC

Neither of these are the problem for me. I save in Excel Workbook (2007), I've tried copying to a new spreadsheet, but still when I open some (not all) filters, the formatting disappears. Even tried re-formatting with filters deleted & then re-introducing filters. Made no difference.


2016-07-22 02:31:20

SN N GIRI

EXCEL CAN NOT OPEN THE FILE BECAUSE THE FORMAT OR FILE EXTENSION IS NOT VALID. VERY THAT THE FILE HAS NOT BEEN CORRUPTED AND THAT THE FILE EXTENSION MATCHES THE FORMAT OF FILE


2016-03-16 12:04:38

Caleb Hutto

I have an excel spreadsheet that I use for a sales cover sheet for my sales team. In the spreadsheet, their are clickable check boxes for the various services we provide. The formatting remains when I save the document, close and reopen. My problem is, when my salesmen email the document as an attachment, the check boxes disappear completely (not just go unchecked, but are gone altogether) when the attachment is downloaded and opened. How can I stop this?


2016-02-29 09:46:46

harv

I have a date-formatted cell that calculated the entry as a general number, then displays that calculated figure as the datevalue's date; ex. I put in 3/1 expecting to get 3/1/2016. I actually get =3/1 in the edit-display area and a cell display of Jan-00 (or the value that works out to be =datevalue(3). Any ideas why? I can actually go to a new worksheet and copy the date into this cell and it takes the correct format and value. This problem exits for the entire worksheet.


2016-02-13 07:21:34

JD Murphy

Jaedi Murph 19 Jun 2015, 09:58
After "FIND and REPLACE" formatting disappears. HELP!
Is there anybody out there? One knock for yes ...


2016-02-12 09:23:38

John Fenton

I, too, had this problem. I copied the same calculations and formats into 4 different files (different weekly plans created from the same template). In just one of these date formats in some cells reverted to numbers each time the file was closed. I concluded the file must be corrupt.


2016-02-08 11:01:02

zaktec

i just changed the format from xls to xlsx and it worked. thanks


2016-01-27 19:47:06

Albert C Ooi

Thanks very much for solving my long time heartache!!


2015-10-19 15:27:39

Terry

I exported my Windows Live Mail contact list to excel 2010 in csv format. I edited the file and saved my work. The excel file looked good. When I closed the file and reopened it the entries in all the columns was merged into column A. What happened to my column format? How can I get the right format back.


2015-10-01 12:55:50

Karen Russell

I went in to update a spreadsheet and saved it every time I put in a new listing. Next day, I went back to the spreadsheet and my updates weren't there. What's going on? This is the 3rd time this has happened. This spreadsheet was on our schools website. I don't know if this matters. I'm getting tired of inputting the same information. Thanks for your help.


2015-09-16 10:37:01

Lisa

I created a document (bulletin for church) that had a bunch of text boxes and pictures...I created it so that when it printed I could fold in 1/2...created in 2013 version....when I emailed the document to myself to modify at home, it was completely different - and I had to redo the entire document...it skewed everything width and height wise where it did not fit on the page at all....at home I believe I have the Home office/student version of Excel 2013


2015-06-19 09:58:51

Jaedi Murph

After "FIND and REPLACE" formatting disappears. HELP!


2015-06-18 14:29:45

Mark

After much trial and error trying to solve lost formatting (custom page sizes) for users, I did find a connection where the formatting was being lost when the PC opening the file did not have the same default printer set as user who created the original .xlsx file.

Also, formatting appears to get lost at times when a file is backed up to a flash drive even when it's restored to same PC that created the file and made the backup.

Many years of experience with similar quirky things makes me conclude these are due to the 'goddess of the computer'...lol


2015-06-18 06:58:55

Tom

Standard Microsoft/Windows then.


2015-05-28 16:41:16

sandra rodriguez

I do not have Excel 2007/2010, what other type can i save it as? right now i have it save as Excel Workbook and everytime i open it back, all the formatting is different


2015-05-10 18:28:47

Bridget

I created a chart in Excel 2007. Looks perfect until I email it to someone, then it looks totally different, all the colors and shapes are missing. What am I doing wrong? How do I correct this? Thanks for your help.


2015-02-09 10:15:01

Deepak

Issues in MS-Excel-
Upon doing a Spell Check, all formatting is lost. :(
Have not been able to find a solution to it. If anybody has it, please mail me.
Thanks.


2014-12-13 08:36:45

Dibu

In a fully linked worksheet, data from some cells in the source worksheet is not appearing in the linked worksheet. This happens only when the source file is closed. Further values from adjacent cells (around 4 rows x 6 columns) in source file is missing.

When the source file is open, data is visible.

Please help me


2014-11-21 13:27:23

Kay

Why when I email an attached invoice, which I made from an excel Template, does it split in two landscape ways when opening it from the download?


2014-11-21 09:32:33

R. Webb

all my dates in a large workbook have changed to the number only format and will not change back using the change format.

Also all the equations lost functionality and the formula shows in the cell and not the result.

Also the workbook was shared and I removed the sharing after the problem arose.


I use this workbook daily -- help.


2014-09-24 07:53:30

Oyvind Buhaug

I have a similar issue with excel 2010. In some cases when I re-open a sheet, parts of the formatting is changed (different color etc.) in other cases the formatting disappears. When this the case excels standard colors can be stored and retained, i.e. the problem I have appears to be linked with non-standard colors


2014-07-08 09:14:00

Glenn Case

Ron:

Interesting note. Is the macro you mention something you could share here?


2014-07-07 01:41:06

Ron

Hi Allen

I came across something related to this a while back when copying the contents of one excel file to another. All of the styles in the source file were being copied into the destination file, even if they were not in use. I was copying quite a few files into one when I got an error telling me I could not paste.
After much googling I found that the problem was the number of styles exceeded the capacity and it would not paste any data at all.
I found and modified a macro to remove all of the styles and the problem went away, but then so did some of the formatting. So I made another version of the macro to remove the unused styles. This takes a bit longer because there is no record in excel of which styles have been used except in each cell. So the macro had to cycle through every cell to build a list of used styles.
Our organisation has to provide excel files to users of older versions and when they save out to the older version some of the styles get dropped. The older version has a smaller capacity for styles than the new. (excel 2003 ~ 4000 styles. Excel 2007 and above ~64000 styles. This may not be the cause of your post but it is related.

Ron


2014-06-12 10:23:32

Laura

When I sort a collumn to only show certain data I save right after but then i start working on something else in the file and my sorts do not stay the way I need them to. How do i keep my sorts the way I need them?


2014-05-19 10:51:53

Pattie

On Friday, May 16th, I saved my spreadsheet then closed it. On Monday when I opened it, some of my formatting was gone. I highlight the rows for different things and use different colors. When I opened it this morning, half of my spreadsheet was missing the colors. On the entire worksheet, 1/2 the columns had no formatting and the other half did, A - H the formatting was gone while I - the end had the formatting.


2013-10-09 10:38:11

Heidi

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.


2013-07-25 07:38:45

Barry Fitzpatrick

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.


2013-07-24 06:28:56

GM

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...............


2013-06-19 06:41:24

Rob

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.


2013-04-14 18:15:16

lester

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!


2013-04-14 17:42:15

lester

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.


2013-04-11 10:29:22

PJ

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.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.