How to delete rows and columns in a protected sheet?

Question

In a new workbook I selected the entire sheet, right click and Selected format cells and unchecked Locked option.

Now I right click on the sheet name selected Protect Sheet after selecting Insert Rows, Insert Columns, Delete Rows and Delete Columns option.

But still I am not able to delete a row or column in protected sheet.

I also tried by selecting all options while protecting the sheet but still I am not able to delete rows or columns. How do I achieve this?

Answer 1

Hi Jaineel N,

Welcome to Microsoft Community.

This looks quite unusual. I tested this on Microsoft 365 for Excel and was unable to reproduce your issue. Please follow my steps again, and I have included some screenshots for clarity on what I did:

1. Select all cells in the entire worksheet > Format Cells > Protection > Uncheck Locked.
2. Under the Review tab in the ribbon > Protect Sheet.
3. Check the boxes for Select locked cells, Select unlocked cells, Insert columns, Insert rows, Delete columns, and Delete rows, then click OK to protect the sheet.

When I right-click on the second column, I can see that my Delete button is enabled.

Deleting the second column yields the expected result.

Similarly, the Insert option also works as expected.

If you get different results when following the steps I shared, please try creating a new blank workbook to see if you can reproduce the issue.

Additionally, please share your Excel version information with me. If this problem is due to an Office update patch, I need to report this information to the development team.

What version of Office am I using? - Microsoft Support

I hope my explanation makes sense, and I look forward to your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

Dear I am still not able to delete rows.

I am using Microsoft office 365.

I tried in the new excel file as well but still it is not working.

Screen 1

Screen 2

Screen 3 (Insert rows do work)

Delete not working

Answer 3

I apologize for the late reply; we took a few days off for Christmas. But I'm back now.

The issue you're encountering stems from not selecting an entire row or column. When you only select a single cell, the default behavior of the Delete button on the menu bar is to delete the content within the selected cell, which is not permitted when the worksheet is protected.

If you need to delete an entire row or column, you must select the entire row or column. At this point, the Delete button will become available for use:

Answer 4

HI all

what about when you also have protected columns in the sheet?

I have a file which has columns protected and I need to add/delete rows.

Following the suggested procedure, I cannot delete selected rows.