Office Settings & Configuration

Allow Users to Hide Rows and Columns on a Protected Excel Sheet

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Allow users to hide and unhide rows and columns while a worksheet remains protected.

Allow Users to Hide Rows and Columns on a Protected Excel Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Unprotect the sheet if necessary

Open the worksheet and select Review > Unprotect Sheet. Enter the password if prompted.

2
Open protection settings

Select Review > Protect Sheet to open the protection options.

3
Allow formatting

Under the actions allowed for users, select both Format rows and Format columns. These permissions also allow other row and column formatting changes.

4
Apply protection

Choose any other permissions you need, enter a password if desired, and click OK. Confirm the password if prompted.

5
Test visibility controls

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.

Enable row and column formatting when protecting the sheet
Permissions are separate: Format rows controls row visibility, while Format columns controls column visibility. Enable both to allow users to hide and unhide both.
Protect worksheets in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Office, choose Spreadsheet, and open your Excel workbook.
  2. 2. Unprotect if needed: Select Review > Unprotect Sheet and enter the password if the worksheet is already protected.
  3. 3. Set permissions: Select Review > Protect Sheet, then enable Format rows and Format columns in the allowed actions list.
  4. 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.
Allow row and column visibility changes on a protected worksheetOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheets in a free Office suite
microsoft office alternative - wps office

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.