How To Unhide All Rows in Excel (With 3 Methods)

Updated September 29, 2023

When creating Excel spreadsheets, functions like inserting, deleting, hiding and unhiding rows or columns can be very useful. Hiding rows in Excel allows you to reduce the amount of data or blank rows on your screen to help you focus on the data that you need to work with. Knowing how to unhide your rows is important so you can keep track of your data and can access it when needed. In this article, we discuss why you might unhide all rows in Excel and how to do so.

Why unhide all rows in Excel?

When working with teams, you may send Excel files between one another to share, update or check your data. Your coworkers might have hidden rows while they were working on the file to streamline their view of the data by hiding unused rows, calculations or information that could cause the file to be less readable. You can identify hidden rows by looking at the spacing between rows to find small blank segments or through other Excel functions. By unhiding the hidden rows, you can access the data you need to complete your work.

How to unhide all rows in Excel

Consider these steps and methods to unhide all rows:

1. Locate all hidden rows

At the top command tabs in Excel, click on the Home tab and find the Editing functions commonly located on the right-hand side. From the Editing functions, click on Find & Select, then Go To Special and select Visible cells only, which shows you the hidden rows placed within a white border. You may also use a keyboard shortcut such as Ctrl + G if your operating system allows that keyboard function.

2. Fix row heights

After locating the hidden rows, you may see some rows that look like hidden ones that weren't identified in your initial search. These rows can be unhidden rows that have a small row height instead of a hidden row. You can fix these rows by right-clicking on the row and changing the row height to match the rest of your rows.

3. Remove Excel filters

Rows that you or your coworkers have filtered out may not show up in your initial search for hidden rows, which prevents you from unhiding them. You can identify filtered out rows when your row numbers are blue rather than black. Remove these filters by finding the filter icon in the top command ribbon and clicking on the function next to it called Clear.

4. Save your work

It's an important practice to save your work as separate files with time stamps before and after making major edits. Saving your work ensures that you can access any lost information or refer to the original formatting of the file if you plan to restore it. Before unhiding your rows, save your current file in case problems occur during the process.

5. Unhide all rows

There are a few methods that you can use to unhide all of your rows on your Excel spreadsheet. Here are some of the common ways you can try:

Use the select all button

In the top left corner of your spreadsheet, there's a triangle icon in between the first row and A column. Clicking on this icon, select the entire spreadsheet, click on the Home tab, Format icon and select to unhide rows from the drop-down menu that appears.

Use keyboard shortcuts

Depending on your operating system, you can use the keyboard shortcuts Ctrl + A or Command + A to select the entire document. Then you can press Ctrl + Shift + 9 to unhide all rows on your spreadsheet.

Use the context menu

You can select a set of rows between any range by clicking on the row you want to start with and press Ctrl as you click on the last row in your range. From there, you can right click on your selection and click on the function called Unhide.

6. Review your rows

Review your rows and data after unhiding them to make sure you haven't mistakenly deleted content and that you've successfully unhidden the rows. A common issue that you might encounter is that the first row remains hidden. In these cases, you can unhide the first row by double-clicking on the hidden row.

You can also locate the hidden first row by going to the Home tab, then the Editing functions and Go To. This opens a window that allows you to search for a reference. Type in A1 to indicate the first row and unhide the row with the keyboard shortcut method and pressing Ctrl + Shift + 9.

7. Troubleshoot any errors

If you're still experiencing errors, you can undo your changes by pressing Ctrl + Z until you've undone the updates that may have deleted data or created a formatting issue. You can also use your previously saved files to check what step might have caused the error. You can retry unhiding all rows in Excel with a different method to see if another way can successfully unhide your rows without errors.

8. Unhide specific rows

If methods to unhide all rows at once are ineffective for you, you can also select particular rows to unhide. You or a coworker may have hidden some rows because they were unused and don't contain important information, so you can choose to unhide rows that show relevant data. You can go through your spreadsheet and double click on the rows you want to unhide as you go down the document.

Frequently asked questions

Why isn't Unhide working?

If you've hidden the first row and you want to unhide it and others following it, left-click the first row shown and move your cursor upward, hovering until a drop-down menu appears. You can then select Unhide. If you're still having issues, navigate to View and then Freeze Panes to ensure you've unfrozen any previously frozen panes. This may help you access your missing rows.

How do you unhide all columns in Excel?

Like rows, you can unhide all columns by selecting your whole worksheet, right-clicking and selecting Unhide. You can also unhide specific columns by selecting the columns on either side of your hidden column, navigating to Format and selecting Unhide Columns.

What does it mean if the Unhide option is gray?

If the Unhide option in your Format list is gray, that means it's unavailable. This means you may be working on a protected worksheet. Navigate to Review, select Changes and, if you see Unprotect Sheet, select it. This typically makes the Unhide button available for you to click. If you're already working on an unprotected worksheet, a gray Unhide button could also indicate that there are no hidden items to unhide.

Please note that none of the products mentioned in this article are affiliated with Indeed.