How to lock borders for a sheet while allowing drag and drop?

[MHinds642]

I have an Excel sheet that is modified daily and would like to lock only the cell borders to their original location. I would like the cell borders to remain in their original location while allowing contents of the cells to be drag/dropped. Currently the borders drag/drop with the cells when moving. I guess this would be similar to setting up a template where the original format is locked but information is able to be added to cells. The copy/paste/delete we are currently using is highly inefficient.

Thanks

[#2]

@MHinds642 

In Excel, you can lock the cell borders to their original location while allowing the contents of the cells to be drag-and-dropped by using a combination of cell protection and worksheet protection.

Here is how you can achieve this:

1. Protect Cells with Borders:

1. Select the cells whose borders you want to lock in their original location.
2. Right-click on the selected cells and choose "Format Cells."
3. In the "Format Cells" dialog box, go to the "Protection" tab.
4. Uncheck the option that says "Locked." This will allow the cell contents to be edited even when the sheet is protected, but it will still keep the borders from moving.
5. Click "OK."

1. Protect the Worksheet:

1. Go to the "Review" tab in the Excel ribbon.
2. Click on "Protect Sheet."
3. In the "Protect Sheet" dialog box, set a password if you want to restrict who can unprotect the sheet. If not, you can leave it blank.
4. Choose the options you want to allow users to do while the sheet is protected. For your case, make sure the "Select locked cells" option is checked.
5. Click "OK" and confirm the password if you set one.

Now, your sheet is protected, but users can still select and edit the cell contents (including drag-and-drop) without moving the cell borders. The cell borders will remain in their original locations. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@NikolinoDE 

Did not work. Borders still move with drag and drop. Tried twice in different documents with the same result. Thanks for the try.

[#4]

@MHinds642 

You can lock cell borders in their original location while allowing the contents of the cells to be dragged and dropped by using the following steps:

1. Freeze Panes for Row and Column Headers (Optional):
• If your sheet has row and column headers, you may want to freeze them so they remain visible while scrolling. You can do this by going to the "View" tab and using the "Freeze Panes" option.
2. Protect the Worksheet:
• Go to the "Review" tab in Excel.
• Click on "Protect Sheet" or "Protect Workbook" depending on your needs. If you only want to protect the borders on a specific sheet, select "Protect Sheet."
3. Set Protection Options:
• In the "Protect Sheet" or "Protect Workbook" dialog box, you will have various options to choose from. You can set a password if you want to restrict access.
• To allow specific actions while protecting the sheet, make sure to check "Allow all users of this worksheet to" and select the following options:
• Format cells
• Format columns
• Format rows
4. Uncheck "Select locked cells":
• In the same dialog box, you will see an option for "Select locked cells." Uncheck this option to prevent users from selecting locked cells.
5. Lock the Borders:
• Now, go to the cells where you want to lock the borders. You can do this by selecting the cells, right-clicking, and choosing "Format Cells."
• In the "Format Cells" dialog, go to the "Border" tab.
• Apply the desired border styles to the cells, and then click "OK."
6. Protect the Sheet:
• After setting the border styles, go back to the "Review" tab, and click "Protect Sheet" again.
• Make sure to enter the password if you set one earlier and confirm the protection.

Once you have protected the sheet with the options mentioned above, users will be able to edit the contents of the cells and drag and drop them, but the cell borders you have styled will remain locked in their original location.

Remember to keep a record of the password used to protect the sheet in case you need to make changes in the future. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.