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.

How to Find Hidden Rows

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

Find hidden rows in Excel

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“.
Use go to special to find hidden rows

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”.
unhide rows in excel

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.”
unhide rows in excel

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.
unhide top row in excel
  • 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.
unhide top rows in excel

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.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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).
unhide all rows in excel
  • 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”.
hide rows in excel

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.”
how to hide rows in excel

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.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.