5 Quick Ways to Unhide All Rows in Excel Instantly

Discovering how to efficiently unhide all rows in Excel can save you time and improve your productivity when working with large datasets. Here are five methods to unhide rows in Excel instantly:

Using the Context Menu

The simplest way to unhide rows is through Excel’s context menu:

• Select the rows above and below the hidden rows by dragging from the row numbers.
• Right-click to open the context menu, and click on “Unhide.”

💡 Note: If the rows are not consecutive, you’ll need to repeat this process for each hidden range.

Using Excel Ribbon Commands

Another user-friendly approach involves using Excel’s ribbon:

1. Select the rows adjacent to the hidden ones.
2. Navigate to the Home tab.
3. Within the Cells group, click the Format button.
4. Under Visibility, select Hide & Unhide, then click Unhide Rows.

This method is particularly handy when you’re already navigating through Excel’s various options.

Using Shortcut Keys

Keyboard shortcuts provide the quickest route to unhide rows:

• Highlight the rows around the hidden ones.
• Press Alt+Shift+Right Arrow to select the hidden rows.
• Then press Ctrl+Shift+(9) to unhide them.

Remember, these keyboard shortcuts are designed to work seamlessly with Excel’s default key bindings.

Using VBA Macro

If you frequently work with large datasets, a VBA macro might be the most efficient solution:

1. Open the Visual Basic Editor by pressing Alt+F11.
2. Go to Insert > Module to create a new module.
3. Paste the following VBA code:

   
   Sub UnhideAllRows()
       On Error Resume Next
       Cells.EntireRow.Hidden = False
       On Error GoTo 0
   End Sub
   
   

4. Close the VBA editor, then create a button on your sheet by going to Developer > Insert > Button.
5. Assign the macro ‘UnhideAllRows’ to the button.

🧐 Note: Ensure the Developer tab is enabled in Excel options.

Using the Name Box

The Name Box provides an indirect method to unhide rows:

• Type the range of cells where you believe hidden rows might exist into the Name Box.
• Press Enter. Excel will select the range.
• Right-click and choose Unhide from the context menu.

This method is useful when you’re not sure where the hidden rows are located within your dataset.

Summing up, whether you're an occasional Excel user or deeply involved in data analysis, knowing how to unhide all rows in Excel can significantly enhance your efficiency. Choose the method that best fits your workflow, from simple right-click operations to creating custom macros.

Why do rows become hidden in Excel?

+

Rows might be hidden intentionally by users to organize data, during pivot table operations, or accidentally during data manipulation.

Can I use the “Go To” feature to unhide rows?

+

Yes, you can use the “Go To” feature by pressing Ctrl+G and entering a range to select hidden rows, then unhide them with a right-click.

What if my keyboard shortcuts don’t work?

+

Ensure your keyboard shortcuts are not disabled in Excel settings, or check if your Excel version supports the shortcut keys provided.