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.
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.
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.
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.
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.
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.
5. Unhide all rows
You may unhide all rows in Excel using a few different techniques. Here are some approaches you can try:
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'.
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:
Rows.EntireRow.Hidden = False
If your workbook contains many worksheets and you want to make all of their rows visible, type the following code:
Dim sheet As Worksheet
For Each sheet In Worksheets
sheet.Rows.EntireRow.Hidden = False
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'.
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.
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.
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
- What Is Category Management? (With Benefits And Steps)
- What Is Underemployment? A Complete Guide With Types
- 13 Metrics For Software Quality (Plus Key Benefits)
- How To Develop An Employee Referral Program (With Tips)
- What Is A Time And Materials Contract? (With Benefits)
- 9 Popular Incident Management Tools (And How To Choose One)
- 16 Practical Tips On How To Be A Successful Business Leader
- What Are Data Marts? (Definition And How To Utilise)
- 16 Important Paramedic Skills (With Average Salary)
- What Is Due Diligence? (With Objectives And Examples)
- What Is a Bachelor in Fine Arts Degree? Definition and Careers
- Athletic Trainer Skills: Definition, Examples And Tips