If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to remove all formatting
I am working on office 2007 prof - my spreadsheet should be read by
colleagues using 2003 and ideally 2000 versions. While I was able to save the file as xls in the past, I am now getting error messages re too many formats being used. Running the compatability chequer, I have removed conditional formatting from two columns taking care of one listed issue. The remaining one stating too many formats does not help me. How can I deal with it? Is there any function to remove all formatting. My spreadsheet would look terrible but at least everyone has access to the data in there. Any other way to sensibly work with or in older versions. |
#2
|
|||
|
|||
how to remove all formatting
hi
xl03 allows for 4000 different format cobinations. xl07 uped that to 64000 due to the larger sheets but since you are saving as an 03.xls for those with 03 and 00 you are stuck with the 4000 ceiling. since it is a ceiling, the only real fix is dont' exceed the limit. see this site for more info and possible fixes. http://support.microsoft.com/default...b;en-us;213904 appies to 03 back to 97 i think...whenever excel went to 64536 rows. regards FSt1 "Tom" wrote: I am working on office 2007 prof - my spreadsheet should be read by colleagues using 2003 and ideally 2000 versions. While I was able to save the file as xls in the past, I am now getting error messages re too many formats being used. Running the compatability chequer, I have removed conditional formatting from two columns taking care of one listed issue. The remaining one stating too many formats does not help me. How can I deal with it? Is there any function to remove all formatting. My spreadsheet would look terrible but at least everyone has access to the data in there. Any other way to sensibly work with or in older versions. |
#3
|
|||
|
|||
how to remove all formatting
Hi,
thanks for that - that explains a lot. However after reading the link info and trying to follow the instructions I am still baffled. Using the 2003 version, my file opened after advising that there were too many formats and that some things will be lost. It looks blank now with all the obvious formatting gone. However, if I want to redo the sheet now, it still does not allow me to save any new formatting. I am willing to remove all formatting to start with a clean sheet but despite the blank look of the sheet, there appears to be underlying formatting. How can I get rid of it? Cheers "FSt1" wrote: hi xl03 allows for 4000 different format cobinations. xl07 uped that to 64000 due to the larger sheets but since you are saving as an 03.xls for those with 03 and 00 you are stuck with the 4000 ceiling. since it is a ceiling, the only real fix is dont' exceed the limit. see this site for more info and possible fixes. http://support.microsoft.com/default...b;en-us;213904 appies to 03 back to 97 i think...whenever excel went to 64536 rows. regards FSt1 "Tom" wrote: I am working on office 2007 prof - my spreadsheet should be read by colleagues using 2003 and ideally 2000 versions. While I was able to save the file as xls in the past, I am now getting error messages re too many formats being used. Running the compatability chequer, I have removed conditional formatting from two columns taking care of one listed issue. The remaining one stating too many formats does not help me. How can I deal with it? Is there any function to remove all formatting. My spreadsheet would look terrible but at least everyone has access to the data in there. Any other way to sensibly work with or in older versions. |
#4
|
|||
|
|||
how to remove all formatting
In fact I just tried to use the autoformat function and have "none" set up
for the whole spreadsheet and the answer is still "too many different cell formats". Seems a bit silly to tell me this but not to allow to simplify the worksheet. |
#5
|
|||
|
|||
how to remove all formatting
hi
the sheet may look blank but did you just delete data. deleting data does not delete formating. that is the most common mistake that users make. they end up with "blank" cells but the cells still carry the previous formating. and technically you can't remove all formating. each cell must have at least the default formating. select the sheet by clicking the small square at the upper right of the sheet. left of the column header and above the row headers. right click the selected sheet and click format cells. set the number format to general set the fond to Arial(or your faviorate.) remove all font colors(set to automatic) remove all borders(set to none) remove all background colors(set to no fill_ you might even unwrap all cells and unmerge cells(if there are any) after all that, the sheet should be fairly default. Regards FSt1 "Tom" wrote: In fact I just tried to use the autoformat function and have "none" set up for the whole spreadsheet and the answer is still "too many different cell formats". Seems a bit silly to tell me this but not to allow to simplify the worksheet. |
#6
|
|||
|
|||
how to remove all formatting
FSt1, thanks but when I try to do exactly that I get the answer "too many
different cell formats" - it seems something is preventing me from making the changes even though I want to redo the sheet in the basic formatting as per your instructions. "FSt1" wrote: hi the sheet may look blank but did you just delete data. deleting data does not delete formating. that is the most common mistake that users make. they end up with "blank" cells but the cells still carry the previous formating. and technically you can't remove all formating. each cell must have at least the default formating. select the sheet by clicking the small square at the upper right of the sheet. left of the column header and above the row headers. right click the selected sheet and click format cells. set the number format to general set the fond to Arial(or your faviorate.) remove all font colors(set to automatic) remove all borders(set to none) remove all background colors(set to no fill_ you might even unwrap all cells and unmerge cells(if there are any) after all that, the sheet should be fairly default. Regards FSt1 "Tom" wrote: In fact I just tried to use the autoformat function and have "none" set up for the whole spreadsheet and the answer is still "too many different cell formats". Seems a bit silly to tell me this but not to allow to simplify the worksheet. |
#7
|
|||
|
|||
how to remove all formatting
|
#9
|
|||
|
|||
how to remove all formatting
Have you tried selecting all cells and EditClearFormats
Gord Dibben MS Excel MVP On Thu, 22 Apr 2010 20:24:01 -0700, Tom wrote: FSt1, thanks but when I try to do exactly that I get the answer "too many different cell formats" - it seems something is preventing me from making the changes even though I want to redo the sheet in the basic formatting as per your instructions. "FSt1" wrote: hi the sheet may look blank but did you just delete data. deleting data does not delete formating. that is the most common mistake that users make. they end up with "blank" cells but the cells still carry the previous formating. and technically you can't remove all formating. each cell must have at least the default formating. select the sheet by clicking the small square at the upper right of the sheet. left of the column header and above the row headers. right click the selected sheet and click format cells. set the number format to general set the fond to Arial(or your faviorate.) remove all font colors(set to automatic) remove all borders(set to none) remove all background colors(set to no fill_ you might even unwrap all cells and unmerge cells(if there are any) after all that, the sheet should be fairly default. Regards FSt1 "Tom" wrote: In fact I just tried to use the autoformat function and have "none" set up for the whole spreadsheet and the answer is still "too many different cell formats". Seems a bit silly to tell me this but not to allow to simplify the worksheet. |
#10
|
|||
|
|||
how to remove all formatting
Sorry, I can highlight the whole spreadsheet by clicking the arrow in the top
left corner (using Prof 2007) but where is the edit button/function? Clicking format only provides me with the editing individual settings but no clear all function. "Gord Dibben" wrote: Have you tried selecting all cells and EditClearFormats Gord Dibben MS Excel MVP On Thu, 22 Apr 2010 20:24:01 -0700, Tom wrote: FSt1, thanks but when I try to do exactly that I get the answer "too many different cell formats" - it seems something is preventing me from making the changes even though I want to redo the sheet in the basic formatting as per your instructions. "FSt1" wrote: hi the sheet may look blank but did you just delete data. deleting data does not delete formating. that is the most common mistake that users make. they end up with "blank" cells but the cells still carry the previous formating. and technically you can't remove all formating. each cell must have at least the default formating. select the sheet by clicking the small square at the upper right of the sheet. left of the column header and above the row headers. right click the selected sheet and click format cells. set the number format to general set the fond to Arial(or your faviorate.) remove all font colors(set to automatic) remove all borders(set to none) remove all background colors(set to no fill_ you might even unwrap all cells and unmerge cells(if there are any) after all that, the sheet should be fairly default. Regards FSt1 "Tom" wrote: In fact I just tried to use the autoformat function and have "none" set up for the whole spreadsheet and the answer is still "too many different cell formats". Seems a bit silly to tell me this but not to allow to simplify the worksheet. . |
|
Thread Tools | |
Display Modes | |
|
|