How to Unhide Rows in Excel (8 Quick Ways)

On the off chance that there is data on our worksheet we don’t have to see, we can hide those which we would rather not see. Then, when we want that information, we can disclose it by following a few strategies. In this article, we will learn how to unhide rows in excel.


Download Practice Workbook

You can download the workbook and practice with them.


8 Quick Ways to Unhide Rows in Excel

We will utilize this basic dataset which contains some item names and the complete sell information in the long stretch of January to March.

8 Quick Ways to Unhide Rows in Excel

1. Show Hidden Rows Using Context Menu in Excel

Generally, It’s quite easy to spot the hidden rows in a spreadsheet. The way to find them is to look for a double line among the rows heading. Let’s go through the following steps to see how this feature works to unhide rows in excel.

Show Hidden Rows Using Context Menu in Excel

Steps:

➣To begin, choose the rows that you want to reveal. It’s as simple as picking one row above and one row below the row or rows you want to see. The Unhide option can be found by right-clicking on the mouse.

Show Hidden Rows Using Context Menu in Excel

➣This makes the hidden rows unhide again. The rows are then shown on the spreadsheet.

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


2. Unhide Rows by Double Clicking

In many cases, the quickest way to reveal the hidden rows is by double-clicking. The beauty of this strategy is that it eliminates the necessity for selection.

Steps:

➣Simply move the mouse over the hidden rows headings and double-click until the mouse pointer becomes a split two-headed arrow.

Unhide Rows by Double Clicking

➣The rows now appear in the workbook.

Unhide Rows by Double Clicking


3. Excel Unhide Rows with Format Feature

Similar to the above dataset, we can also unhide rows from the Format feature in the ribbon.

Steps:

➣We’ll choose the rows we wish to unhide again in the same way we did before. To Unhide rows, go to the Home tab > Cells section > select Format > then Hide & Unhide from the menu. Then, click Unhide Rows.

Excel Unhide Rows with Format Feature

➣Finally, the hidden rows are visible again.

Excel Unhide Rows with Format Feature

Read more: How to Hide and Unhide Rows in Excel (6 Easiest Ways)


4. Unhide Specific Row Using Name Box in Excel

It’s quite easy to spot the hidden rows in a spreadsheet using the name box.

Name Box: In Excel, the Name Box alludes to an infobox on one side of the formula bar. The Name Box typically shows the location of the “dynamic/active cell” on the worksheet.

Unhide Specific Row Using Name Box in Excel

Steps:

➣In the Name Box next to the formula bar, type cell B6 which we want to unhide, and then press Enter. In the following picture, the green line means cell B6 is now selected.

➣Then, simply go to the Home tab > Format > Hide and Unhide > Unhide Rows.

Unhide Specific Row Using Name Box in Excel

➣Now, we can see the whole row for cell B6 is visible.

Unhide Specific Row Using Name Box in Excel


Similar Readings


5. Disclose Rows with Keyboard Shortcut

We can show all the hidden rows with a keyboard shortcut.

Steps:

➣Firstly, select the rows including the row above and below we want to unhide. Then, press t Ctrl, Shift, 9 these three keys together from the keyboard.

➣Pressing this key combination displays any hidden rows that intersect the selection.

Disclose Rows with Keyboard Shortcut

Read More: Shortcut to Unhide Rows in Excel (3 Different Methods)


6. Make Rows Visible by Changing the Excel Row Height

We can easily unhide the rows by changing the excel row height.

Steps:

Click the top left corner and select the whole spreadsheet.

Visible Rows by Changing the Excel Row Height

➣After that, go to the Home tab in the ribbon then the Format option. This option is found in the top-right of the Excel window in the “Cells” portion of the toolbar. There will be a drop-down menu. Select Row Height.

Visible Rows by Changing the Excel Row Height

➣This will open a pop-up window with a blank text field in it. Enter a row height into the pop-up windows text field. Here, I’m entering 18.

Visible Rows by Changing the Excel Row Height

➣This will apply the changes to all the rows in the table and show all the rows that have been “hidden” by the height property.

Visible Rows by Changing the Excel Row Height


7. Show All Hidden Rows in Whole Excel Spreadsheet

Assume we need to unhide all rows in the whole spreadsheet.

Steps:

➣Click here, in the top left corner, to unhide all rows in the whole spreadsheet. The entire sheet has been highlighted at the point.

Show All Hidden Rows in Whole Excel Spreadsheet

➣Select the Unhide option from the right-click menu on the row headings section, and all rows in the spreadsheet will become visible.

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


8. Unhide Rows Using VBA Code in Excel

Likewise the above dataset, we are going to use a VBA code to unhide the rows in excel.

Steps:

➣To start with, we must choose the rows we need to unhide. We can do it by choosing one row above and one beneath the row or rows we need to unhide. Go to the Visual Basic from Developer tab in the ribbon. This will open the visual basic editor.

Unhide Rows Using VBA Code in Excel

➣Click the Insert drop-down and select ‘Module’. This will insert a new module window.

Unhide Rows Using VBA Code in Excel

➣Or, just right-click on the spreadsheet bar. Go to View Code.

➣After that, write the VBA Code.

VBA Code:

Sub UnhideAllRows()
Selection.EntireRow.Hidden = False
End Sub

➣Copy and paste the VBA code in the window. Then click on Run or use the keyboard shortcut (F5) to execute the macro code.

Unhide Rows Using VBA Code in Excel

➣And finally, all the hidden rows will unhide in the worksheet.

Read More: VBA to Hide Rows in Excel (14 Methods)


Check the Number of Hidden Rows

Excel has a feature called ‘Inspect Document‘ that allows you to quickly scan a workbook and get information about it. One of the things you can do using ‘Inspect Document‘ is to quickly see how many hidden columns or rows are in the worksheet. This could be beneficial if you receive a workbook from someone and want to inspect it fast. The steps to examine the total number of hidden columns or hidden rows are as follows.

Steps:

  • Start by opening the workbook.
  • Select the File tab.
  • Go to the Info section.
  • Click the ‘Check for Issues‘ drop-down (next to the Inspect Workbook text) in the Info choices.
  • Click on Inspect Document.

Check the Number of Hidden Rows

  • Make sure the Hidden Rows and Columns option is enabled in the Document Inspector. Then, click the Inspect button.

  • This will show you the total number of hidden rows and columns.

Check the Number of Hidden Rows


Reasons Why Rows in Excel Cannot be Unhidden

If you find difficulty in unhiding columns in your worksheets, it’s most probable in light of one of the accompanying reasons.

  • Protected Worksheet

The first thing to check when the Hide and Unhide functions in Excel are disabled (grayed out) is worksheet protection.

Check the Review tab > Changes group to see if the Unprotect Sheet button is available (this button appears only in protected worksheets; in an unprotected worksheet, there will be the Protect Sheet button instead). So, if the Unprotect Sheet button appears, click it.

Click the Protect Sheet button on the Review tab, choose the Format rows box, then click OK to keep the worksheet protected but allow hiding and unhiding rows the selection.

  • Small Row height, but Not Zero

Check the height of the rows. If the worksheet isn’t protected but specific rows can’t be unhidden.

  • Filtered out Rows

When your worksheet’s row numbers become blue, it means that some rows have been filtered out. Simply remove all filters from a sheet to reveal such rows.

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


Tips:

We can unhide all cells in excel. To unhide all rows and columns, select the entire spreadsheet as clarified above, and afterward press Ctrl + Shift + 9 to show stowed away rows and Ctrl + Shift + 0 to show stowed away columns.


Conclusion

By following these methods, you can easily unhide rows in your workbook. All these methods are simple, fast, and reliable. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo