How to Unhide All Rows or Columns in Excel at Once

Let’s assume the following situation: You have received an Excel workbook from a colleague, client or anyone else but you have the feeling, that some rows or columns are hidden. By that, you’ve already done a good job because it is very difficult to spot from the small lines on the side or top that there are hidden rows or columns. But how do you unhide all rows and columns at the same time?

Method 1: Unhide all rows or columns manually

Hide rows and columns

Many people love the “Hide” function for hiding rows or columns, as it is very easy to use: (the numbers are corresponding with the image)

1. Mark the row(s) or column(s) that you want to hide.
2. Right-click on the row number or column letter and click on “Hide”.
3. Unfortunately, it has one big disadvantage: You can hardly recognize hidden rows or columns. It’s only symbolized by a thin double line between the row or column number.
4. A better way for hiding rows or columns is the Group function.

Unhide rows and columns

So, how to unhide all hidden rows?

1. Select the whole area in which you suspect hidden rows. Alternatively select the whole worksheet in the top left corner.
2. >Now double-click on the border between two row numbers (number 5 in the screenshot above). Each row has now it’s minimum size to cover all it’s contents. There is one disadvantage, though: The row height of all selected rows will be reset. So, if you have already set the row heights manually, it will be gone.
3. Instead of double-clicking according to the number two above you can right-click on the column or row header (that means the column letter above your hidden column or the row number on the left-hand side). Next, click on “Unhide”.

Method 2: Use Professor Excel Tools

The Excel add-in Professor Excel Tools provide a function for unhiding all hidden rows and columns on all sheets with one click. Alternatively only unhide the rows or columns on the selected or current sheet.

To use the function, click on “Hidden Rows and Columns” in the “Professor Excel” ribbon. Now you’ll see a window as shown on the screenshot on the right-hand side.