Prevent Excel auto-formatting cells

ben501

I've got a spreadsheet with columns of data. 4 of the first 7 rows of each column (rows 1,3,5,7) have headings which I've formatted in bold, underlined & central.

Whenever I start entering data on another column the text is formatted the same as the headings for rows 8,9 & 10. I have to keep going back and removing the formatting. I've tried setting the format before I start but it still changes it. I've had a look at the options and can't find an option to stop it. Also asked Google the question with no luck.

Turning to the good folks of MSE. I'm sure there's something simple I'm overlooking but how get it to leave the text unchanged?

Finknottle

One quick way to fix this is to reset formatting for the whole worksheet, and then just format the top few rows where you want bold, underlined, centered, etc.

To select the whole worksheet, you can click in the grey rectangle at the very top, to the left of column A and above row 1. The whole worksheet will dim or "grey out" to indicate it's been selected. Then right mouse anywhere on the worksheet and select "format cells" to reset the format (presumably to normal type, no underline, not centred).

This will reset the whole worksheet, including your column headings. You then just have to select the top few rows where your headings are, and reintroduce your special formatting just for those rows.

Everywhere else on the worksheet should now retain your default format unless you specifically change it for any cell or cells.

As to why the problem happened, is it possible you selected whole columns when you were setting your heading formats, instead of just the first few rows?

ben501

Thanks for the reply however it's not down to any cells I've formatted.


I've open a new sheet and entered heading the same format as my workbook.


Column D I formatted cells 1-7, column F just cells 1,3,5 & 7.

When I enter more text it's automatically formatted based on the previous cells. I just don't want them re-formatting. I've tried selecting row 8 downward & removing any formatting but as soon as I enter text it's changed again.:wall:





I just want it like this but without having to constantly remove the formatting

Finknottle

OK, got it. How irritating.

I think it's Excel trying to be helpful because you're entering into the next cell immediately following the cell(s) that you've set a format for. It thinks you want to continue in the same format.

Try File -> Options -> Advanced

Under "Editing Options" uncheck the box for "Extend data range formats and formulas"

I just tried it on your test data and it worked for me.