0

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?

  • On an answer you commented "the Insert function to add a whole new row, but that doesn't work with my budget sheet, as that would mess up all the other months by adding blank cells in the middle of them. I just want to move a few cells down within a single month" ... do you have each month on a separate worksheet? Or do you have 5-column months arranged one after the other horizontally on one sheet? – Alex M Apr 8 '19 at 18:52
  • Well, if I had each month on a separate worksheet, then moving things around in one month would not affect the other months. I have all the months of a year on one single sheet. Each month is now 6 (not 5) columns, & the months are arranged one after the other. E.g., April is headed at the top & has 6 columns underneath it, May is listed to the right of April with 6 columns under it, etc (so I would think of them as arranged vertically, but I'm not sure what you were thinking). – Kristalyn Pac Apr 9 '19 at 20:08
  • "Well, if I had each month on a separate worksheet, then moving things around in one month would not affect the other months" it would if you had accidentally group-selected your worksheets. – Alex M Apr 9 '19 at 20:10
  • I'm afraid I don't even know what that means much less how to do it! But I will take your word for it. You obviously know Excel much better than I, & thank goodness, since you were able to offer me a solution! :) – Kristalyn Pac Apr 10 '19 at 21:55
0
  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.

| improve this answer | |
  • Thanks for trying! That would allow me to use the Insert function to add a whole new row, but that doesn't work with my budget sheet, as that would mess up all the other months by adding blank cells in the middle of them. I just want to move a few cells down within a single month, which is only 5 columns wide. With the protect sheet maneuver that you suggest I can't move a few selected cells down their columns at all, as it says those cells are now "read-only." I can only do it by using the Insert function, which, as I said, is impractical for my budget sheet as a whole. Any other ideas? – Kristalyn Pac Apr 19 '16 at 15:51
0

It should work fine if you use COPY (Ctrl + C) instad of CUT (Ctrl + X)

As I understand your issue, you have something that looks like this, per your comment on your question:

"Each month is now 6 (not 5) columns, & the months are arranged one after the other. E.g., April is headed at the top & has 6 columns underneath it, May is listed to the right of April with 6 columns under it, etc"

SAMPLE DATA:

enter image description here

And when you need to add a row to just one month, you cut and move down, which loses your formatting:

PROBLEM OUTPUT:

enter image description here

So I would suggest highlighting the data that needs to be moved down, using copy, going down a row, then using paste, then just overwrite the data in the new line:

1. Select, then copy:

enter image description here

2. Move down a line, then paste:

enter image description here

3. Then write in the new data on the new line:

enter image description here

How's that?

| improve this answer | |
  • I really appreciate it! I will possibly need to add a step of clearing the data that I moved from the original cells so that I don't get confused with all the text. I was hoping there was just some simple way to fix this, so I could move info around in a column with just one step & have all the formatting stay the same. But if there is not, I will just adapt & try this. That was super cool of you to take the time to explain this with the images & everything. Thank you so much! :) – Kristalyn Pac Apr 10 '19 at 21:54
  • "so I could move info around in a column with just one step & have all the formatting stay the same" There probably is, but this kind of workflow adjustment can be really personal. It might help if you edit your question to include screenshots of your actual step by step workflow (if you do, comment back here so I'll know to look again). It also goes to questions of table design - most experienced users wouldn't set up the sheet this way; either each month would be on a separate sheet or all records would simply be arranged in rows rather than split out each month. For reasons just like this. – Alex M Apr 11 '19 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.