How to Hide and Unhide all Rows in Excel (2024)

Download the practice workbook 👉 HERE and follow along.

Managing data in Excel often means hiding and unhiding rows and columns. This helps you focus on specific information or access hidden data. Here’s an easy guide on how to do it.

You can spot hidden rows by noticing gaps between row numbers.

If your worksheet contains hundreds or thousands of rows, finding hidden ones can be difficult. Use this simple trick to easily locate all hidden rows:

Step 1: Open Go To Special

• Go to the Home tab.
• In the Editing group, click “Find & Select” > “Go To Special“.
• Or press the keyboard shortcut Ctrl + G to open the ‘Go To’ dialog box, then click “Special“.

Step 2: Select Visible Cells Only

• In the Go To Special window, select “Visible cells only” and click OK.

This will select all visible cells and mark the rows adjacent to hidden rows with a white border, making it easy to identify where hidden rows are located.

How to Unhide Rows in Excel

When working with teams, you often share Excel files to update or check data. Sometimes, coworkers hide rows to make the file easier to read by removing unused rows or calculations. To unhide all rows in Excel, use one of these simple methods.

Option 1: Using the Ribbon Menu

• Select the range that includes the hidden rows.

• Go to the “Home” tab and click “Format” in the “Cells” group.
• Hover over “Hide & Unhide” and select “Unhide Rows”.

Option 2: Using the Right-Click Context Menu

• Select the row numbers that include hidden rows or columns.
• Right-click on the selected area.
• Click “Unhide.”

Option 3: Using Keyboard Shortcuts

• Select the rows above and below the hidden row(s).
• Press Ctrl + Shift + 9.

💡 The same methods also work to unhide columns in Excel.

How to Unhide the First Row

If the first row (row 1) or top rows are hidden in your Excel worksheet, it can be tricky to unhide.

Here’s a simple way to do it:

Step 1: Select the First Row(s)

• Click on the Name Box (next to the formula bar).
• Type “A1” and press Enter. This selects the first cell.

• If multiple top rows are hidden, type the range in the Name Box (e.g., “1:3” for rows 1 to 3) and press Enter.

Step 2: Unhide the Rows

• On the Home tab, click “Format” in the Cells group.
• Under “Visibility”, choose “Hide & Unhide” and select “Unhide Rows”.

This will unhide the first row or the specified top rows in your Excel worksheet, ensuring you can access all your data.

How to Unhide all Rows in Excel

To unhide all rows in an Excel sheet, follow these simple steps:

Step 1: Select All Rows

• Method 1: Click the Select All button (the triangle at the upper left corner of the sheet).

• Method 2: Press Ctrl + A. If this doesn’t select the entire sheet, press Ctrl + A again.

Step 2: Unhide All Rows

Use any of these methods to unhide rows in Excel.

• Keyboard Shortcut: Press Ctrl + Shift + 9.
• Context Menu: Right-click anywhere on the sheet and select “Unhide”.
• Ribbon: Go to the Home tab, click “Format”, then select “Unhide Rows”.

By following these steps, you can quickly unhide all rows in your Excel worksheet, making sure you can see all your data.

Note on Filters Hiding Rows

Sometimes, filters can hide rows in your Excel sheet. The usual methods of unhiding rows won’t work in this case.

To unhide rows hidden by filters, you need to turn off the filter:

• Go to the Data tab.
• Click on the “Clear” button to turn off the filter. This will reveal all the hidden rows.

How to Hide Rows in Excel

Option 1: Using the Ribbon Menu

• Select the row(s) you want to hide.
• Go to the “Home” tab.
• Click “Format” in the “Cells” group.
• Hover over “Hide & Unhide.”
• Click “Hide Rows”.

Option 2: Using the Right-Click Menu

• Select the row numbers you want to hide.
• Right-click on the selected row numbers or column letters.
• Click “Hide.”

Option 3: Using Keyboard Shortcut

• Select the row(s) you want to hide
• Use the keyboard shortcut Ctrl + 9.

💡 The same methods also work to hide columns in Excel.

Cannot Unhide Rows?

If the Hide and Unhide features are disabled (greyed out), check if the worksheet is protected:

Go to the Review tab.

• In the Changes group, look for the “Unprotect Sheet” button.
• If you see “Unprotect Sheet”, click on it to remove protection.
• If you see “Protect Sheet” instead, the sheet is not protected.

Allow Hiding and Unhiding with Worksheet Protection

If you want to keep the worksheet protected but still allow hiding and unhiding rows, follow these steps:

• Click the “Protect Sheet” button on the Review tab.
• Select the “Format rows” box.
• Click OK.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to hide and unhide rows or columns in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.