How to insert or delete rows in protected sheet?
As we all known, when a worksheet is protected, we are unable to make any changes to locked cells, for example, we can’t insert or delete rows from the protected sheet. Are there any good methods for us to insert or delete rows in a protected sheet?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Please do the following step by step to finish this job in Excel.
1. Select the entire rows that you want to allow inserting or deleting rows, and right click, then choose Format Cells from the context menu, see screenshot:
2. In the Format Cells dialog, under the Protection tab, uncheck the Locked section to unlock the selected cells, see screenshot:
3. Then click OK to close this dialog, and then click Review > Protect Sheet to protect the sheet, see screenshot:
4. And in the Protect Sheet dialog box, in the Allow all users of this worksheet to list box, please check Insert rows and Delete rows, and then enter and confirm the password, see screenshot:
5. Then click OK to close the dialogs, and now, the worksheet has been protected, but, you can insert or delete the rows within the selected rows you specified, see screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoThe first example actually does not work as described. I have unlocked entire rows and then protected sheet as described, but it still does not allow me to delete a row
- To post as a guest, your comment is unpublished.· 3 years agoWhat you're actually doing here is unlocking all the cells so protecting the worksheet has no practical use. This therefore isn't the answer to the problem.