Allow Users to Hide Rows and Columns on a Protected Excel Sheet
Question details
Allow users to hide and unhide rows and columns while a worksheet remains protected.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet owner wants to protect a sheet but permit users to change row and column visibility.
- Observed behavior
- Users can hide and unhide rows and columns when the corresponding formatting permissions are enabled during sheet protection.
Open the worksheet whose permissions you want to change. If it is already protected, you will need to unprotect it first and enter its password if prompted.
Enable row and column formatting when protecting the sheet
Use Excel's sheet protection settings to let users hide and unhide rows and columns without removing protection.
Open the worksheet and select Review > Unprotect Sheet. Enter the password if prompted.
Select Review > Protect Sheet to open the protection options.
Under the actions allowed for users, select both Format rows and Format columns. These permissions also allow other row and column formatting changes.
Choose any other permissions you need, enter a password if desired, and click OK. Confirm the password if prompted.
Right-click a row or column header and choose Hide. To restore it, select the headers on both sides of the hidden area, right-click, and choose Unhide.

Allow row and column visibility changes in WPS Spreadsheet
Use WPS Spreadsheet's sheet protection options to protect a worksheet while allowing users to format, hide, and unhide rows and columns.
- 1. Open the workbook: Open WPS Office, choose Spreadsheet, and open your Excel workbook.
- 2. Unprotect if needed: Select Review > Unprotect Sheet and enter the password if the worksheet is already protected.
- 3. Set permissions: Select Review > Protect Sheet, then enable Format rows and Format columns in the allowed actions list.
- 4. Protect and test: Apply protection, then right-click a row or column header and choose Hide. Select the surrounding headers and choose Unhide to test restoration.

Frequently Asked Questions
Can users hide columns without being allowed to hide rows?
Yes. In Review > Protect Sheet, select Format columns but leave Format rows unchecked.
How do I change permissions on an already protected sheet?
Select Review > Unprotect Sheet and enter the password if prompted. Then select Review > Protect Sheet and choose the new permissions.
How can I unhide a row on a protected sheet?
If Format rows is allowed, select the row headers above and below the hidden row, right-click the selection, and choose Unhide.
Does allowing row and column formatting only permit hiding?
No. The Format rows and Format columns permissions also allow other formatting changes, such as changing row heights and column widths.




