Get the Hide & Unhide Excel Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice hiding and unhiding rows and columns right away
- Follow the examples without setting up the sheet yourself
- Includes hidden rows, filtered rows, and a protected sheet to troubleshoot
Enter your email below for instant access.
Managing data in Excel often means hiding and unhiding rows and columns. This helps you focus on specific information or access hidden data.
Another common formatting step is merging cells for headers and labels. Just note that merged cells can cause issues when hiding or unhiding rows.
How to Find Hidden Rows in Excel
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 (Row 1) in Excel
If the first row (row 1) or top rows are hidden in your Excel worksheet, it can be tricky to unhide.
Here are the easiest ways to unhide the first row in Excel.
Method 1: Unhide the First Row with Mouse Selection
- Find the first visible row: Look at the row numbers on the left. Click the number of the first row you can see (in our example it’s row 3).
- Drag upward to include the hidden row: With the row selected, hold down the left mouse button and drag upward. This action selects the hidden rows as well.
- Unhide the rows: Right-click on the first visible row. From the menu, select Unhide. Your first row will now reappear.
Method 2: Unhide the First Row with the Name Box
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.
Want to practice all three unhide methods? Download the free workbook with hidden rows ready for you to reveal.
Featured Course
Excel Essentials for the Real World
How to Unhide All Rows at Once
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.
Why Unhide Doesn’t Work: Rows Hidden by Filters
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.
Why Can’t I Unhide Rows in Excel? (Fix)
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.
If the sheet is password-protected and you’ve forgotten the password, see our guide on how to unprotect an Excel sheet without a password.
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.
Protecting a sheet also affects other features like drop-down lists and data validation. Make sure to check the appropriate boxes for the features you want users to access.
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.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
