You can hide rows and columns in Excel, and it’s usually easy to unhide those rows or columns later. But, if you run into problems, these quick tips might help you show those rows and columns again.
Unhiding Rows and Columns
If you notice that rows are hidden in an Excel workbook, you can usually unhide them by following these steps:
- Select the rows on either side of the hidden ones
- Next, right-click on one of the selected rows
- In the pop-up menu, click the Unhide command
Trouble Unhiding Top Rows
Occasionally, you can run into problems when you need to unhide rows or column on an Excel worksheet.
For example, if Row 1 is hidden, or the top few rows are hidden, how can you unhide them?
Here are the steps to show the hidden top rows on a worksheet:
- At the left side of the Excel sheet, press on the first visible row button
- In the animated screen shot below, Row 6 is the first visible row
- Next, drag up, onto the Select All button.
- Tips: As you drag up, a small popup appears, to show the number of rows you’ve selected.
- Then, right-click the first visible row button
- In the pop-up menu, click Unhide
This animated gif shows the steps for selecting the hidden rows at the top of a worksheet, and unhiding them
Another Hidden Top Rows Fix
If you weren’t able to show the hidden top rows, the problem might be caused by some of the worksheet has been locked in place, with the Freeze Panes command.
To see if freezing is the problem of hidden rows or columns, follow these steps:
- Select any cell on the the worksheet
- On the Excel Ribbon, click the View tab
- If there is an Unfreeze Pane command in the drop down list, click that, to unfreeze the locked sections
The “hidden” rows or columns might reappear after that, when you scroll to the top or left of the worksheet.
Warning for Hidden Rows
Perhaps the biggest problem with hidden rows or columns in an Excel workbook is that you might not even know that things are hidden! I’m sure you’ve read the horror stories in the news about that kind of problem.
To help you spot hidden rows or columns, I’ve made a sample workbook that uses formulas and conditional formatting to alert you when things are hidden.
There are details on how it works in this blog post: Excel Hidden Data Warning.
In the screen shot shown below,
- Rows 12 and 13 are hidden
- Formula in cell B2 shows that 2 rows are hidden
- Columns E and F are hidden
- Conditional formatting in cell D1 shows that columns are hidden, to the right of the coloured cell
Get the Hidden Data Warning File
To get the hidden rows and columns warning workbook, go to the Conditional Formatting Examples page on my Contextures site.
In the “Get the Sample Files” section, look for File #3: Hidden Data Warning. The zipped Excel file is in xlsx, and does not contain any macros.
Video: Trouble Unhiding Top Excel Rows
This short video shows you a quick way to see those hidden rows at the top of your Excel worksheet.
________________________
Warning for Hidden Rows and Columns in Excel
________________________
I usually select a range in the Name box that includes the hidden rows (or columns), e.g. a4:a6. I then change the row height to something other than zero. I need to change row height fairly often so it’s on my QAT.
Thanks, Dave, and that’s a great tip for unhiding rows/columns.
And I’ve put lots of commands in the QAT over the years, but never the Row Height!
Hi Debra
I am using the Excel Loan Payment Schedule Template. It is wonderful, thank you! I am wondering how I could make one adjustment by adding an additional principal payment column to this spreadsheet?
Thanks for your help. The text at
“At the left side of the Excel sheet, press on the first visible row button
In the animated screen shot below, Row 6 is the first visible row…” Worked a treat.
You’re welcome, Frank, and thanks for letting me know which part of the instructions helped you. I appreciate it!