How to Unhide All Rows in Excel (All Possible Ways)

In Microsoft Excel, hiding and unhiding rows is an everyday task. You will face many instances where you will have to hide or unhide rows in Excel. We hide rows to show only the essential data from a spreadsheet. Sometimes our spreadsheet contains blank cells. For this reason, we hide those rows or columns. But, to extract those, we have to find the hidden rows/columns. In this tutorial, you will learn to unhide all rows in Excel with suitable examples and proper illustrations.

Download Practice Workbook

Download this practice Workbook.

How to Locate Hidden Rows

Before you start to unhide all rows in Excel, you have to first locate all the hidden rows. After that, you can proceed to unhide all rows from the whole spreadsheet.

Have a look at the dataset:

Here, you can see rows 6, 7, and 11 are missing. You can find them manually. But, if you have a large dataset, it may be challenging for you.

You can follow these simple steps to locate all hidden rows in Excel.

📌 Steps

• First, go to the Editing group from the Home tab.
• After that, click on Find & Select.

• Now, click on Go To Special.

• From the Go To Special dialog box, select Visible cells only. Next, click on OK.

After that, you will see Excel has selected all cells. But, it marked the rows contiguous to invisible rows with a white border.

4 Ways to Unhide All Rows in Excel

Before you start to unhide all hidden rows from a spreadsheet or workbook, select all rows. Two ways you can follow here:

A note to remember is, in Microsoft Excel, this shortcut acts differently in distinct circumstances. If you point the cursor in a blank cell, this shortcut will select the whole spreadsheet. But, if the cursor is in one of the adjacent cells with data, it will only select the dataset. To choose the whole worksheet again, press Ctrl+A again.

In the following sections, I will provide you with four easy ways to unhide all hidden rows in Excel. I recommend you learn and apply all the methods to your spreadsheet. It will definitely develop your Excel knowledge.

We will use this dataset in the following sections:

Here, rows 6, 7, and 11 are hidden. We will unhide all the rows using the following methods.

1. Using Ribbon to Unhide All Rows

Now, the most used method to unhide all hidden rows is using the ribbon. Follow these steps :

📌 Steps

• First, click on the Select All button to select all rows from the worksheet.

• After you have selected all rows, go to the Cells group from the Home tab.
• Now, click on the Format button.

• Next, click on the Hide & Unhide After that, click on Unhide Rows button.

In the end, it will unhide all the rows from the whole Excel spreadsheet.

Read More: How to Unhide Multiple Rows in Excel (9 Methods)

2. Unhide All Rows by Context Menu in Excel

You can also unhide all rows from the context menu after right-clicking the mouse. It is also pretty simple to unhide all the hidden rows or columns.

📌 Steps

• First, click on the Select All button to select all rows from the worksheet.

• After that, right-click on your mouse.

• Now, click on Unhide button.

Finally, this method will successfully unhide all the hidden rows from the Excel worksheet.

Read More: Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)

3. Unhide All Rows in Excel Using Keyboard Shortcut

Another beneficial way to unhide all rows is using the keyboard shortcut. People don’t use this method too often. But, I recommend you learn and apply this method also. It will come in handy in the future.

The Shortcut:

📌 Steps

• First, click on the Select All button to select all rows from the worksheet.

• After you have selected all rows, press Ctrl+Shift+9 simultaneously.

After that, this shortcut will display all the hidden rows from the worksheet.

Read More: Hidden Rows in Excel: How to Unhide or Delete Them?

4. Unhide All Rows Using VBA Macros

Now, if you are a VBA freak like me, you can use VBA codes to unhide all the hidden rows. The real benefit of using the VBA code is you can unhide all rows across the workbook. That means you can display all hidden rows from all the worksheets from your workbook.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• To unhide all rows from a worksheet, type the following code:

Sub Display_all_hidden_rows()

Rows.EntireRow.Hidden = False

End Sub

Sub Display_all_rows_in_Workbook()

Dim sheet As Worksheet

For Each sheet In Worksheets
 sheet.Rows.EntireRow.Hidden = False
Next sheet

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select Display_all_hidden_rows / Display_all_rows_in_Workbook.
• Then, click on Run.

Finally, these VBA codes will successfully expose all the hidden rows in your Excel worksheet/workbook. I suggest you give this a try.

Unhide Particular Rows in Excel

In the previous sections, we showed you to unhide all hidden rows in your Excel spreadsheet. Now, you may not unhide all the rows. But, you want to unhide specific rows. You can follow all the steps from above. We are just showing some simple ways again.

Here, we are unhiding just rows 6 and 7.

1. Show Hidden Rows by Double Clicking

This method is pretty simple. After you have found the hidden rows, hover the mouse over the interaction of the rows. You will see a split two-headed arrow.

Double click on that arrow.

After that, it will uncover the invisible rows 6 and 7. The main benefit of this method is you don’t have to highlight the rows. Just one double click will unhide all of them.

2. Show Hidden Rows by Highlighting Rows

Another way to uncover particular rows from a worksheet is by highlighting the rows. This method resembles the methods that we showed earlier.

First, highlight the rows on either side of the row you want to unhide.

Then follow any method to unhide rows that we showed earlier. If you haven’t read them, please read them first. We are using the context menu here to unhide rows.

Right-click on the mouse. After that, you will see the context menu.

Now, click on Unhide button.

It will successfully unhide rows 6 and 7 respectively. Follow any of these methods to unhide specific rows.

How to Unhide Top Rows in Excel

From these examples, you have seen that we had hidden rows in the middle. Now, what if the rows are located at the top like the following?

Here, we have hidden row 1. To unhide this, type “A1” in the Name Box and press Enter.

Now, Cell A1 is selected. In this time, follow any of the methods that we showed earlier. We are using the keyboard shortcut.

After you have selected Cell A1, press Ctrl+Shift+9. It will unhide that particular row from the top.

Unhide All Rows Not Working in Excel

Sometimes, you may face some troubles unhiding the hidden rows in your Excel worksheet. There might be three reasons for that.

1. Your Sheet is Protected

The first reason for unhiding rows not working might be for a protected sheet. If your sheet is protected, you can not unhide rows using these methods. Unprotect your worksheet first.

📌 Steps

• First, go to the Review tab.

• From the Protect group, click on Unprotect Sheet/ Unprotect Workbook.
• Type the password to unprotect it.
• If you desire to maintain the worksheet security but permit hiding and unhiding rows, click the Protect Sheet button on the Review tab, check the Format rows box, and click OK.

Read More: [Fix]: Unable to Unhide Rows in Excel (4 Solutions)

2. Rows’ Heights Are Too Small

Another thing that might give you trouble is the row height. If the height of the rows is too small, it won’t be visible. Between 0.08 and 1, the row appears to be concealed but honestly it is not. You can’t unhide rows using the previous method. Follow these simple steps instead:

📌 Steps

• First, highlight the rows that seem hidden.

• Then, right-click on your mouse.

• After that, click on the Row Height button.

• Then, give any visible row height. We are giving 20.
• Finally, click on OK. It will display those rows.

3. Some Rows are Filtered Out

If you see the row numbers are in blue color that means a filter is applied to your worksheet. In this situation, the traditional method won’t work.

Just remove the filter from the worksheet to unhide all the hidden rows in Excel.

Read More: How to Hide and Unhide Excel Worksheets from a Workbook

Unhide Columns in Excel

Similar to the unhiding rows, you can also unhide columns in your Excel spreadsheet.

Have a look at the following dataset:

Here, you can see, we have hidden the columns C and E. Now, you can follow the previous methods to unhide columns.

1. Unhide All Columns Using Excel Ribbon

📌 Steps

• First, click on the Select All button to select all columns from the worksheet.

• After you have selected all columns, go to the Cells group from the Home tab.
• Now, click on the Format button.

• Next, click on the Hide & Unhide After that, click on Unhide Columns button.

In this way, you can unhide all columns in your Excel worksheet.

Read More: Hide Duplicate Rows Based on One Column in Excel (4 Methods)

2. Unhide All Columns Using Context Menu

📌 Steps

• First, click on the Select All button to select all columns from the worksheet.

• After that, right-click on any column with your mouse.

• Now, click on Unhide

3. Use of Keyboard Shortcut to Unhide Columns

The Shortcut:

📌 Steps

• Click on the Select All button first to select all columns from the worksheet.

• After you have selected all columns, press Ctrl+Shift+0 altogether.

Finally, this shortcut will unhide all the hidden columns from the worksheet.

4. VBA Codes to Unhide All Columns

You can also apply VBA codes to unhide columns from the spreadsheet or the whole workbook.

📌 Steps

• At first, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• To unhide all columns from a worksheet, type the following code:

Sub Display_all_hidden_columns()

Columns.EntireColumn.Hidden = False

End Sub

Sub Display_all_columns_in_Workbook()

Dim sheet As Worksheet

For Each sheet In Worksheets
 sheet.Columns.EntireColumn.Hidden = False
Next sheet

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select Display_all_hidden_columns / Display_all_columns_in_Workbook.
• Then, click on Run.

VBA codes are one of those easy ways to unhide rows/columns from the entire workbook. SO, if you have a large number of worksheets in your workbook, use the VBA codes to unhide them all.

Read More: VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)

💬 Things to Remember

✎ If you are facing trouble unhiding the top rows or columns, just select all of them and apply a keyboard shortcut.

✎ If the row height is less than 0.08, you can unhide them with these methods without adjusting the height.

✎ Try to select the whole worksheet with the Select All button.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to unhide all rows in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• [Fixed!] Excel Rows Not Showing but Not Hidden (3 Reasons & Solutions)
• VBA to Hide Rows Based on Cell Value in Excel (14 Examples)
• How to Hide Blank Rows in Excel VBA (4 Useful Methods)
• Hide Rows Based on Cell Value with Conditional Formatting in Excel
• How to Hide Rows Based on Cell Value in Excel (5 Methods)