Excel – Moving cells down columns without losing formatting in column in Microsoft Excel

microsoft excelworksheet-function

When I move cells down a formatted column in Microsoft Excel in order to insert information in the column above the moved cells, Excel removes the formatting from the cells into which I am inserting information, & I have to reformat them.

This is on my budget sheets. I often discover receipts from previous days in a calendar month & so have to move my date & amount spent down a column to insert the earlier spending above. When I do, the new dates entered are no longer centered, & the new figures entered are no longer formatted as money. It's so frustrating.

On one budget sheet this did not happen to a certain point in the columns, so I know it's possible. Yet if I copy a portion of a column that is retaining the formatting into a portion of the column that is not retaining the formatting, the function of retaining the formatting will not copy over.

I have not been able to find the answer to my problem anywhere. Can anyone help?

Best Answer

  1. Right click on the column letter header and choose "format cells" OR select specific cells you want to lock then right click them and choose "format cells"

  2. Click the "protection tab" and UNCHECK the "locked" box.

  3. Click on the "Review" Heading at the top menu.

  4. Click "protect sheet".

  5. Check EVERY box, except for "format cells, format columns, format rows". Leave password blank.

You can now insert new rows that will keep the formatting of the locked column, but you will still be able to edit the column's contents.