how to remove all formatting

"Clear All" is the far right (under Clear) of the Home ribbon in Excel 2007.



--
Regards
Dave Hawley
www.ozgrid.com
"Tom" wrote in message
...
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.

.

Thanks - found that and used clear all format. When saving as xls format, I
am still getting "too many formats" and the compatability report states 1
occurrence but does not tell me where. I don't have access to 2003 today to
test but Version 2000 does not allow me to open this stating too many formats.

"ozgrid.com" wrote:

"Clear All" is the far right (under Clear) of the Home ribbon in Excel 2007.



--
Regards
Dave Hawley
www.ozgrid.com
"Tom" wrote in message
...
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.

.

On 4/23/2010 8:39 PM, Tom wrote:
Thanks - found that and used clear all format. When saving as xls format, I
am still getting "too many formats" and the compatability report states 1
occurrence but does not tell me where. I don't have access to 2003 today to
test but Version 2000 does not allow me to open this stating too many formats.

"ozgrid.com" wrote:

"Clear All" is the far right (under Clear) of the Home ribbon in Excel 2007.



--
Regards
Dave Hawley
www.ozgrid.com
wrote in message
...
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,
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.

.


You might try copying the entire worksheet to a second worksheet, then
use Paste Special to paste only those characteristics you need. You can
paste special multiple times to get several of these items -- formulas,
values, comments, validation, all except borders, column widths,
formulas and number formats, values and number formats.

When satisfied that all the information you need has been transferred to
the second sheet, delete the first sheet, then save your workbook.

You might even be able to paste special to a new blank workbook instead.

Bill

Thanks Bill - that will work . I just did a test with only pasting the values
and this saved fine and can be opened in Version 2000, so at least I can
access the information from a site does not run the later version .

Thanks to everyone for their help.

Tom


"Billns" wrote:

On 4/23/2010 8:39 PM, Tom wrote:
Thanks - found that and used clear all format. When saving as xls format, I
am still getting "too many formats" and the compatability report states 1
occurrence but does not tell me where. I don't have access to 2003 today to
test but Version 2000 does not allow me to open this stating too many formats.

"ozgrid.com" wrote:

"Clear All" is the far right (under Clear) of the Home ribbon in Excel 2007.



--
Regards
Dave Hawley
www.ozgrid.com
wrote in message
...
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,
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.

.


You might try copying the entire worksheet to a second worksheet, then
use Paste Special to paste only those characteristics you need. You can
paste special multiple times to get several of these items -- formulas,
values, comments, validation, all except borders, column widths,
formulas and number formats, values and number formats.

When satisfied that all the information you need has been transferred to
the second sheet, delete the first sheet, then save your workbook.

You might even be able to paste special to a new blank workbook instead.

Bill
.

Many people seem to run into this problem in both 2003 and 2007. In Excel
2007 most often the issue is related to the exessive number of unused often
corrupted styles and not so much cell unique cell format combos (hard to
imagine someone can actually hit over 64,354 combos). I wrote
a utility to fix XL2007 OOXML files that can also be saved down to XL2003
after the clean up.
Here is the link to the blog post:
http://sergeig888.spaces.live.com/bl...9444!534.entry

Requres .Net3.5 and MS Excel 2007. Will fix xlsx or xlsm files. Won't work
on binary xlsb or password protected files (read-only passwords are OK).

No need to run the risk of further corrupting your file by using Open Office
like some people recommend in other Excel forums. Utility is free and comes
as is.