How To Clear Too Many Cell Formats In Excel
- Home
- Software
- Microsoft Office
Ok, let's see how well I can explain this.
I have a user who went to open an Excel file this morning and all the formatting was gone. He's using Excel 2007 but the file was saved as an Excel 97-2003 file in compatibility mode. The file was created over a week ago and never had any problems until today. The shadow copy created at noon yesterday still had all the formatting in place. The only thing changed since then was adding a couple more tabs.
I restored the file from the backup run last night at midnight and opened it on a machine with Office 2003. That's when I got the error message that there are "Too many different cell formats". I know what that means but when I open the file up in Office 2007, I don't get that error. All the formatting is just gone.
So my question is this, since the file was saved in compatibility mode as a 97-2003 workbook, is it experiencing that same "Too many different cell formats" error but 2007 doesn't display it, it just erases all the formatting? If so, if we take the shadow copy with the formatting and save it as a 2007 workbook, will we not have the formatting issue anymore?
What does it hurt to try saving it as 2007 with a different name?
Excel 2003 allows 4000 cell formats/styles while excel 2007 allows 64000. Saving as a 2007 workbook should solve your problem
11 Replies
What does it hurt to try saving it as 2007 with a different name?
Excel 2003 allows 4000 cell formats/styles while excel 2007 allows 64000. Saving as a 2007 workbook should solve your problem
Sounds like problems that I've had in my firm. Your working for a CPA firm, right?
I had issues with ProSystem fx Engagement - moving up to 5.7 solved that. There were additional errors sometimes as well.
I haven't moved up to the 6.x release - waiting until after this season.
My guess is that, YES if you save a shadow copy as a 2007 doc, it should have the formatting. You may find that you have to go back a few copies before it works. My experience with Excel has been that the errors start and correct themselves for a few revisions and then it just craps out.
Good luck - Our engineering spread is now over 15MB (Yes MB) and we run into issues constantly - we are rebuilding it as we speak - New custom app.
Speak with ProSystem support - You will continue to get errors until you update and they are not predictable.
ZW wrote:
Yeah, a CPA firm, however, the above mentioned Excel file had nothing to do with Engagement.Sounds like problems that I've had in my firm. Your working for a CPA firm, right?
I had issues with ProSystem fx Engagement - moving up to 5.7 solved that. There were additional errors sometimes as well.
I haven't moved up to the 6.x release - waiting until after this season.
lovedayt wrote:
That's what I'm having the user try. I've not figured out why they seem to insist on saving things in compatibility mode when everyone in the office uses 2007, but I'm sure CPA's know more about that stuff than I do! :-) We'll see what happens!What does it hurt to try saving it as 2007 with a different name?
Excel 2003 allows 4000 cell formats/styles while excel 2007 allows 64000. Saving as a 2007 workbook should solve your problem
agree with ZW. Had weird issues with excel until the update with Engagement. We are 5.7 as well. I know this isn't the time to be messing around with tax software!
Brandon4257 wrote:
Yeah, a CPA firm, however, the above mentioned Excel file had nothing to do with Engagement.
I don't know if it makes a difference, because Engagement has addins into Excel - call their support, they are very good
All of my other Excel issues were PPC related - and they are not as forthcoming with their issues. As a matter of fact, ProSystem support directed me to PPC for certain issues.
you could try starting excel in "safemode" to see if it loads properly.
office 2007 does some strange things with files.
Where did you get that format limit? We were having the exact same issue(Engagement, custom cell formats) and everything I came across had the limit for 2007 being 4000 also. I even used a program that told me how many custom formats a sheet had, (Can't remember the name for the life of me), and these counts were under 5000 and verified with a vba that counted them too.
I ended up having to use this as a macro on the sheets:
Sub StyleKill()
Dim styT As Style
Dim intCount As Integer
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
styT.Delete
intCount = intCount + 1
End If
Next styT
MsgBox intCount
End Sub
to remove any custom formatting. It cleared up the issue. We eventually found the issue was caused by copying/pasting cells from a website that had hundreds of possible styles, and the number just accumulated as the more was pasted.
+1000 on waiting on 6.x for all the TB issues that they haven't cleared up yet. 6.1.2 supposedly has it fixed, but I'm not holding my breath much.
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.
How To Clear Too Many Cell Formats In Excel
Source: https://community.spiceworks.com/topic/95277-too-many-different-cell-formats-error
Posted by: tidwellhisquam.blogspot.com
0 Response to "How To Clear Too Many Cell Formats In Excel"
Post a Comment