How To Unhide All Rows In Excel: A Step-By-Step Guide

By Indeed Editorial Team

Published 27 September 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Functions like inserting, deleting, hiding and unhiding rows or columns can be helpful when working with Excel spreadsheets. It lets you focus on the important data by hiding rows to ensure you display only the relevant rows and columns on the screen. If you often work with Excel spreadsheets, it is important for you to know how to unhide Excel rows so you can monitor your data and access it when needed. In this article, we explain why it is important to unhide all rows in Excel and provide you with a step-by-step guide on how to do so.

Related: What Is MS Office? (Plus Other Helpful FAQs To Review)

Why Is It Important To Unhide All Rows In Excel?

If you learn how to unhide all rows in Excel, working across teams can become easier. When working in teams, you might share, change or double-check data by sending Excel files back and forth. While they were working on the file, your coworkers might have hidden rows to simplify their analysis of the data by obscuring useless rows, calculations or any other information that might make the file harder to read. If certain files have hidden rows, you may have difficulty accessing the complete information if required.

You can do this by identifying the numbers demarcating each row and finding out if some numbers are missing. The missing numbers represent the hidden rows. You can also detect hidden rows by looking for brief vacant areas in the space between rows or by utilising additional Excel tools. This lets you access the data you may require for finishing your task by unhiding the hidden rows.

Related: How To Learn Excel For Office Work: A Complete Guide

How To Unhide Rows In MS Excel

Follow these steps for unhiding rows both totally and selectively:

1. Find the hidden rows

Find the 'Editing functions' by selecting the 'Home' tab from Excel's top command tabs, which are often found on the right-hand side. You can view the hidden rows by selecting the 'Find & Select' option from 'Editing functions', going to 'Special' and selecting the 'Visible cells only' option. This is going to highlight the hidden rows enclosed in a white border. If your operating system permits it, you can also use a keyboard shortcut like 'Ctrl + G' to locate the concealed rows.

Related: Excel Interview Questions (With Example Answers)

2. Fix the row heights

After finding the hidden rows, you may see more rows that resemble concealed rows which you did not find during your initial search. Instead of being concealed rows, these rows might be plain rows with a low row height. The rows may not be visible if the height is too low, for instance, between 0.08 and one. By right-clicking on the row and adjusting the row height to match the other rows in your layout, you can make these rows visible again.

Related: What Is The Quick Analysis Tool In Excel? And How To Use It

3. Clear all filters

Your initial search for hidden rows might not turn up rows that you or your coworkers have filtered out, preventing you from unhiding them. When your row numbers are blue rather than black, you can distinguish them as filtered rows. By selecting the 'Clear' button next to the filter symbol in the top command ribbon, you can remove these filters.

Related: Calculating Break-Even Analysis In Excel: A Complete Guide

4. Save your progress

It is important to save your work as distinct files with time stamps before and after making significant adjustments. By saving your work, you make sure that you can access any lost data and, if required, refer to the file's original layout. Save your current file before unhiding your rows so that you have a backup in case of emergencies.

Related: A 7-Step Guide To Creating A Bubble Chart In Excel

5. Unhide all rows

You may unhide all rows in Excel using a few different techniques. Here are some approaches you can try:

Select-all method

There is a triangle icon on the upper left corner of your spreadsheet, between the first row and the 'A' column. Select the entire spreadsheet by clicking this icon. Then choose the 'Home' tab, select the 'Format' icon, go to 'Hide and unhide' and opt to 'Unhide rows' from the drop-down option that appears.

Keyboard shortcut method

You can select the entire document using the keyboard shortcuts 'Ctrl + A' or 'Command + A', depending on your operating system. You may also select the entire spreadsheet using the triangle icon from the previous method. After that, you may unhide every row in your spreadsheet by pressing 'Ctrl + Shift + 9' or 'Command + Shift (↑) + 9'.

Related: Computer Skills: Definition And Example

Context menu method

To select all worksheet rows, click the 'Select all' option or the triangle button in the upper left corner. Right-click on the mouse after that. Click the 'Unhide' button now. The Excel worksheet's hidden rows can be easily unhidden using this technique.

VBA macros method

To reveal all the hidden rows across the workbook, utilise Virtual Basic for Applications (VBA) codes. This implies that you can see every hidden row on every worksheet in your workbook. First, open the VBA editor by pressing 'Alt+F11' on your keyboard. Next, choose 'Insert' and then choose 'Module'. Enter the following code to reveal all the rows in a worksheet:

Sub Display_all_hidden_rows()

Rows.EntireRow.Hidden = False

End Sub

If your workbook contains many worksheets and you want to make all of their rows visible, type the following code:

Sub Display_all_rows_in_Workbook()

Dim sheet As Worksheet

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

End Sub

Next, save the document. After that, to display the Macro dialogue box, press 'Alt+F8' on your keyboard. Choose 'Display all rows in Workbook' or 'Display all hidden rows' next. Then select 'Run'.

Related: 30 Of The Most Advanced Formulas And Functions In Excel

6. Unhide selective rows

Sometimes it may be necessary to unhide only a specific individual or group of rows rather than revealing all of them in the entire spreadsheet. To unhide rows selectively, follow any of these methods:

Double click method

This approach is rather easy to use. After discovering the hidden rows, move your cursor over the overlap of two rows. A split two-headed arrow is going to appear. Double-click that arrow to reveal the invisible rows. The biggest advantage of this approach is that you can skip highlighting the rows individually. You can reveal both visible and invisible rows with one double click.

Related: Top Basic Computer Interview Questions (With Sample Answers)

Highlight method

By clicking on the first row in the range you want to select, then holding down the 'Ctrl' key as you click on the last row, you can choose a group of rows from any range. From there, you can right-click on your choice and select the 'Unhide' option. You can also use any of the other unhiding methods discussed so far.

Top row method

If the first row remains concealed after implementing the two methods above, you can manually unhide it. To do this, select the 'Home' tab, followed by the 'Editing options' and then press on the 'Go to' option. You can now perform a reference search when a window opens up. Enter 'A1' to designate the first row and hit 'Ctrl + Shift + 9' to reveal the first row.

Related: 20 Advanced Excel Skills For The Workplace (With Examples)

7. Troubleshoot for errors

After you have successfully unhidden the rows, go back and check your rows and data to ensure you did not delete anything by mistake. If you encounter any problems, you can reverse your changes by pressing 'Ctrl + Z' until you have reversed any modifications that might have lost data or resulted in a formatting error. You can also check the step that might have produced the error by using the previously saved files. Try again to see if by using a different approach, you can successfully unhide all the rows successfully.

Please note that none of the companies, institutions or organisations mentioned in this article are associated with Indeed.

Explore more articles