# Format is not retained when I close an Excel doc!



## ssobel (Feb 22, 2008)

I've searched high and low on this one: I create an Excel 2003 document - its got the usual colors, number formats, date formats, etc. I save it, then send it via Outlook to someone - they open it and the formatting is gone - but not all of it! If I re-open the saved doc, correct the missing lines, formats, colors, etc., save it, then re-open, most of it is saved, but again not all. VERY FRUSTRATING. There is one pattern I can identify so far - if a cell is blank, when I re-open the spreadsheet, all formatiing on that cell is gone - no borders or colors. This happens to me and a few of my co-workers, but not all.

Any suggestions? I'm an advanced Excel user, but this has be stumped.

Thanks!!!!


----------



## OBP (Mar 8, 2005)

I have asked Firefytr to have a look, he is a Microsoft Excel guru.


----------



## ssobel (Feb 22, 2008)

Thank You! Thank you!!! Thank you!!


----------



## Zack Barresse (Jul 25, 2004)

Hi there, and welcome to the board!

That is strange behavior indeed. What we need to do is isolate the problem. So, does this happen with any file you use? Or just this file? And if just this file, is the the file that gives your co-workers the same problems? Do your co-workers have the same problems with other excel files as well? And is it just Excel? Can you try to reproduce the error with, say, a Word document?

If it isn't too much trouble, could you upload the file here for us to look at?

If this is an application problem (i.e. with Excel, not the file individually), some things I would look at:
Uninstalling all add-ins, closing/re-opening Excel, try file(s) again for same circumstances
Open Excel in safe mode (google: excel safe mode switch), try file(s) again for same circumstances
Ensure you have the latest service packs and updates for your Office version (which you said was 2003)
Try a Detect and Repair from the Help menu
Re-install Excel (very last case scenario, if we know it is an application-level problem)

But we need to figure out where the problem is stemming from first. So, let's determine if this is the file, certain files, your application, yours and others applications, etc.


----------



## ssobel (Feb 22, 2008)

Where do I start? It happens with most of the files I create - not just one. Excel has already been un and re-installed. It happens to files I create from scratch, and especially to those I create from an export from FileMaker (which exports into an Excel format.) I'll try the Detect and Repair. I've got the latest updates. Thanks!!


----------



## Zack Barresse (Jul 25, 2004)

I would just go down the list. Start with the add-ins I described above though. Also, if you would like to post a sample file to the thread so that we can look at it, that would probably help.


----------



## jimr381 (Jul 20, 2007)

I am guessing that Filemaker Pro is exporting as a CSV file and it is warning him that it will not retain the formating once he saves it in Excel. Change the Save As type to an Excel Workbook and tell us if that fixes it. F12 is the hotkey for save as in case you like the keyboard way of doing things.


----------



## ssobel (Feb 22, 2008)

Actually, FileMaker exports into an *.xls format now as well........thats what I use....


----------



## jimr381 (Jul 20, 2007)

Can you open up the Excel file and hit "F12" for save as? Then I would like you to tell me what the save as type is set to.


----------



## slurpee55 (Oct 20, 2004)

ssobel said:


> I've searched high and low on this one: I create an Excel 2003 document - its got the usual colors, number formats, date formats, etc. I save it, then send it via Outlook to someone - they open it and the formatting is gone - but not all of it! If I re-open the saved doc, correct the missing lines, formats, colors, etc., save it, then re-open, most of it is saved, but again not all. VERY FRUSTRATING. There is one pattern I can identify so far - if a cell is blank, when I re-open the spreadsheet, all formatiing on that cell is gone - no borders or colors. This happens to me and a few of my co-workers, but not all.
> 
> Any suggestions? I'm an advanced Excel user, but this has be stumped.
> 
> Thanks!!!!


If you were a newbie, I would have thought of it being a text/csv vs xls format problem, but that seems unlikely.
What I would like to know is not what is lost, but what is being retained as far as formatting is concerned?
Also, does it only happen when you email it to someone via Outlook?


----------



## ssobel (Feb 22, 2008)

Boy, do I appreciate the help! 

When I hit F12 it's saving as an *.xls file. I have actually saved a document repeatedly, both with "save" and with "save as", and with a new file name as well, just to re-open it (after closing and reopening Excel, and from the "recent files" menu and via "File, Open")

This problem happens when I save a document, and then open it again - without even emailing it to anyone else. Some of the formatting is retained - some of the color on cells, some of the borders, some of the date formats. No real pattern as to what is saved or what is not saved. 

I'll try to include an example, but its tough. I'll send 2 - one with what it looks like when saved correctly, and one as it appears after I save it once. Let s see if that helps!


----------



## Zack Barresse (Jul 25, 2004)

Did you try everything else I suggested? The add-ins would be the first thing I would have checked..


----------



## ssobel (Feb 22, 2008)

The add in's that are installed are not in use. i have to get my office tech to remove them....I'll do that...

Can someone give me a hint on how to attach a file to this thread so others can see the examples I want to provide?


----------



## Zack Barresse (Jul 25, 2004)

When you click Post a Reply, below the message body there is a button labeled Manage Attachments. It is a common dialog box and has a Browse button (search for the file to attach), then click the Upload button once you've got the full path/name of the file from the Browse button. Once you see the attachment appear in the list you can close that dialog box. Then just hit Submit Reply as per usual.


----------



## slurpee55 (Oct 20, 2004)

ssobel , if you can't attach yet (there may not be now, but there used to be a minimum number of posts needed before you could post an attachment - at least I think there was) - well, at any rate, email me via my profile and I will respond. You can then send me the file and I will post it, if that is needed.


----------



## slurpee55 (Oct 20, 2004)

It doesn't sound like the poster is losing these files, or that they are corrupt - they just lose their formatting, which is incredibly odd.
Hey, I managed to duplicate it somewhat! Are you using AutoFormat and then using an add-in? I used AutoFormat to set the sheet as a table, then used ASAP to color every nth row. It looked fine, but when I saved it and reopened, much of the coloring from ASAP was gone - but not all.


----------



## ssobel (Feb 22, 2008)

Didn't think I'd stump everyone! No add-in's involved. No tables either. here's a sample. i exported this data from FileMaker in an Excel format. I added the color and lines, then saved it. This time the only thing missing are some of the lines around the blank boxes. Frequently, date formats and colors are missing as well from some, but not all of the sheet.


----------



## Zack Barresse (Jul 25, 2004)

I cannot find anything in the file itself. I'm thinking that it is with your system. Try disabling everything and trying it again. You said there were no add-ins, did you check? Turn off all anti-virus programs. Sometimes even certain peripherals (hardware) will produce erroneous results. With it not being the file, and us not being where you are at, there is little we can do. Check everything, even the most minute details, network connections, printers, processes running in the background, messenger programs, close down everything except Excel, then try it.


----------



## slurpee55 (Oct 20, 2004)

Or blow up the machine....some days, that seems like the best answer....


----------



## ssobel (Feb 22, 2008)

Thanks to everyone for their help...I may have the techs in the office blow it out and rebuild - alot of work for a formatting problem but it's really annoying and I work in Excel all day long...I'm going to give FileMaker a call first. The other person having this issues uses FM as well. If - WHEN - it's resolved I'll post again.


----------



## Zack Barresse (Jul 25, 2004)

Signs are pointing to FM sounds like. I would be tickled to hear the outcome of this.


----------



## SpenserDC (Apr 2, 2008)

I was experiencing the exact problem described here with one exception; I noticed that I was losing formatting not just on blank cells, but on two columns I'd edited in MS Word. I was finally able to correct the problem by selecting the entire worksheet, selecting "copy" and then using the "paste special..." feature to paste just the values in. Since then, I've been able to fix the formatting and then save and re-open it while retaining the formatting.


----------



## TC_Applications (Jul 3, 2008)

I received the attached file from a user experiencing this problem. They would change number formats, put in borders, center, etc., but when they saved and re-opened the file, the formatting would be gone. I figured it was user error until they sent the file. The first thing I checked is that it wasn't a csv file. I applied some formatting myself, saved it, and sure enough it was all gone when I re-opened the file. 

After a few minutes of trying to figure this out, I noticed that the Excel Styles for the sheet were not the defaults and there was a ton of them. The customized "Normal" style in the workbook didn't have any formatting and it seems like the workbook was defaulting to that style each time it opened. 

A few minutes on Google and I found a macro from Microsoft that will delete all Styles in a workbook, then reapply the default styles. The MS page said it was for Excel 2002, but it worked just fine on Excel 2007. 

http://support.microsoft.com/kb/247982

I ran the macro on the file and now everything is working as it should and the formatting is getting saved like normal.


----------

