How to Unhide All Rows or Columns in Excel at Once

Unhide all rows and columns at once in Excel.

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)

unhide all rows, unhide, hide, rows, columns, excel, all rows, all columns
  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

Unhide all hidden rows by selecting the rows "around" the hidden row. Then right-click on any row number and click on "Unhide".
Unhide all hidden rows by selecting the rows “around” the hidden row. Then right-click on any row number and click on “Unhide”.

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

hide, unhide, manager, rows, columns, add-in, excel
Hide or unhide rows and columns comfortably with 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.


Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

4 comments

  1. I hid two rows and then moved to two other rows and hid them.
    I was able to unhide the last two rows but, I could not unhide the first two and the row numbers are missing too. So, I have rows 1,2,5,6. No 3,4.
    Help

  2. I want to hide row with conditional. example if given amount is 1000 & amount less than thousand hide row if amount exceeding from 1000 then row unhide. please guide

Leave a comment

Your email address will not be published. Required fields are marked *