Warning for Hidden Rows and Columns in Excel

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

choose 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

Warning for Excel Hidden Rows


Original source: https://contexturesblog.com/archives/2022/04/21/warning-for-hidden-rows-and-columns-in-excel/?utm_source=rss&utm_medium=rss&utm_campaign=warning-for-hidden-rows-and-columns-in-excel

Close Menu