I have a spreadsheet that was created in excel 2003 and breaks due to cell range idea of conditional formatting that came in with 2007, unless its kept as an  .xls file where it all works properly.

However now I've been "upgraded" to excel 2010 opening the same spreadsheet sees every cell right aligned if it has any conditional formatting and nothing I do will change it.

I see lots have this issue and various ideas were given (none work for me) this seemed likely - go do dev tab, properties and change every sheet to True on enable conditional format (or what ever it was). But it was already all on true.

Then I see the first fix for excel 2010 in SP1 appeared to address the fault I have.  For some reason the IT team upgrading us are not installing with any Service Pack, so I installed SP2 today but it doesn't help.  If I Open the file in 2007 its all as you want.  On 2010 its broken.  Anyone know what I need to do to rectify this?

Based on my research, I tried to open a XX.xls in Excel 2010.

But I had not meet the issue.

As far as I know, you may try to do following steps:

1. Open the spreadsheet in Excel 2007
2. Save as the spreadsheet with .xlsx extensions
3. Open it in Excel 2010

I hope its helps.

Unfortunatley we can't use xlsx as it breaks all the conditonal formatting.  Thats a bug we never found a solution for when 2007 came out.  But by keeping xls format its OK.  Now with 2010 we get another bug.

for 2007 we had that workaround in 2010 we are completely stuck

Hi,
As far as I know, Excel2007 changed the saving format in Excel 2003.
Excel 2003 saved the file store in binary, and Excel 2007 stored in xml.
So there may be caused some unexpect problems.
I recommend that you can try to create a new file in Excel 2007.
1) Open the files in Excel 2003 and copy all the content
2) Paste the content to Excel 2007 and save as .xlsx
I hope its helps.

Hi, the first Excel fix in Service Pack 1 for OFFICE 2010

"Data is formatted incorrectly when conditional formatting rules that are based on standard deviation (specifically below 2 or 3 standard deviation) are applied"

this is not quite the issue I'm getting but remarkably similar. I'm getting

"Data is formatted incorrectly when opening a workbook that operated correctly in 2003 and 2007, conditional formatting rules are applied, but other formatting is ignored and can not be changed"

Many have tried to recreate the workbook in xlsx.  Within our business, the way multiple users, in multiple files, in multiple countries, apply conditional formatting rules to cells with targets in other cells (that are not in a contiguous list) all fail in 2007.  It continually removes our cell references and replaces them with a range we don't want and can't control.  This always breaks the formatting. 

We have all found the same work around by using xls but these now fail in Office 2010 for a different reason.

So it seems its clearly a bug in office 2010 as you can FIX it if you have an older copy of office

Somewhere on the web I found a fix that works (thanks)

Open the file using office 2003 and then save it as HTML

Open the new file (in webpage) then re save as .xls ( isnt really excel yet)  note the size is much smaller than it was (for me only 16kb not the 3 meg it started out as)

With the .xls extension it will re-open in office 2003 (say NO to convert additional garbage) then re save as an excel workbook... note the file size now back to over 3 meg but still 100kb less than the 3.3 meg original file it started out as....

Go back to office 2010 open the file and note conditionally formatted cells are centre aligned as per the formatting used

NOTE: this fix doesn't work with any newer version of excel. Office 2007 or 2010 will mess it up completely and if you have any macros / VBA in the workbook all newer versions of OFFICE will throw it all away for no good reason....  More evidence MS employ people who don't seem to understand how the products they make nowadays are supposed to work.