How to show hidden rows

To show hidden rows in Excel, you can follow different methods depending on how the rows were hidden (via right-click, filtering, or manually). Here’s a comprehensive breakdown of how to unhide rows, with examples for each scenario.

Unhiding Rows Using the Mouse (Right-Click Method)

If you have hidden rows in a specific range, you can use the right-click method to unhide them. This is useful when the rows are hidden via simple right-click and hide commands.

Example

Let’s assume rows 5 to 10 are hidden in your worksheet.

1. Select the Range: Click on the row number 4 (just before the first hidden row) and drag your selection down to row 11 (just after the hidden rows). This will select rows 4 to 11.
2. Right-Click: Right-click on the selection, and from the context menu, choose Unhide.
3. Result: The hidden rows (5, 6, 7, 8, 9, 10) will now be visible.

Unhiding Rows Using the Excel Ribbon

If you don’t want to right-click or the rows are not easily accessible through the mouse, you can unhide rows from the Home Ribbon using the following steps:

Example

Assuming rows 5 to 10 are hidden.

1. Select the Rows: Select row 4 and row 11 (just before and just after the hidden rows).
2. Navigate to the Ribbon:
   • Go to the Home tab on the ribbon.
   • In the Cells group, click on the Format dropdown.
3. Unhide: Under Visibility, choose Hide & Unhide, and then select Unhide Rows.
4. Result: Rows 5 to 10 will be displayed.

Unhiding Rows Using Keyboard Shortcuts

Another efficient way is by using keyboard shortcuts, especially if you’re working with large datasets.

Example

Let’s assume rows 5 to 10 are hidden.

1. Select Rows Around the Hidden Rows: Select the row numbers 4 and 11 (above and below the hidden rows).
2. Use the Shortcut:
   • Press Ctrl + Shift + 9 (on Windows) or Command + Shift + 9 (on Mac).
3. Result: The rows from 5 to 10 will become visible.

Unhiding Rows Using the Go To Feature

Sometimes, rows are hidden in a way that you can’t see them or they are far apart from the visible data. In such cases, the Go To feature can be used to help you locate and unhide them.

Example

Assuming rows 5 to 10 are hidden.

1. Go To the Hidden Row Range:
   • Press Ctrl + G (or F5) to open the Go To dialog box.
   • Type 5:10 in the reference box and press Enter. This will select rows 5 to 10, even if they are hidden.
2. Unhide the Rows:
   • With rows 5 to 10 selected, right-click and choose Unhide.
3. Result: The hidden rows will be revealed.

Unhiding Rows in Filtered Data

If rows are hidden due to applied filters, you need to clear the filter to see them.

Example

Assuming rows 5 to 10 are hidden due to an active filter.

1. Remove the Filter:
   • Select any cell in the range where the filter is applied.
   • Go to the Data tab on the ribbon.
   • Click on Clear under the Sort & Filter group. This will remove the filter and show all rows.
2. Unhide Rows:
   • Select the rows surrounding the hidden rows (e.g., select rows 4 and 11).
   • Right-click and select Unhide.
3. Result: All hidden rows will now be visible.

Using VBA (Visual Basic for Applications)

For advanced users, VBA can be used to unhide rows programmatically, which is especially helpful when working with large or complex worksheets.

Example

Let’s assume you want to unhide rows 5 to 10 using VBA.

1. Open the Developer Tab:
   • Press Alt + F11 to open the VBA editor.
2. Insert VBA Code:
   • In the editor, go to Insert > Module.
   • Paste the following code into the module window:
   • Sub UnhideRows() Rows("5:10").Hidden = False End Sub
3. Run the Code:
   • Press F5 or click Run to execute the script.
4. Result: Rows 5 to 10 will be unhidden programmatically.

Checking for Row Height (if manually hidden)

If the rows are hidden due to their height being set to zero, you can reset the height manually or via VBA.

Example

Let’s assume rows 5 to 10 are manually hidden with zero height.

1. Select the Rows:
   • Click and drag to select rows 4 to 11.
2. Adjust Row Height:
   • Right-click the selected rows, choose Row Height, and set it to a value like 15 or your desired height.
3. Result: The rows will be visible with the adjusted height.

Alternatively, use VBA:

Sub UnhideRowsByHeight()
    Rows("5:10").RowHeight = 15 ' Adjust row height
End Sub