Software optimize

How to unhide row 1 and column A in an Excel sheet

The typical unhiding techniques don't work everywhere in Excel. Learn an easy to remember method for unhiding row 1 and column A.

Hiding and unhiding rows and columns is a straightforward and easy task, unless you're trying to unhide row 1 or column A. When unhiding a row or column, you select the adjacent rows or columns, but there's no row above row 1 or column to the left of column A, which presents a bit of a conundrum. Today, I'll show you how to unhide row 1 and column A.

First, let's run through the instructions for hiding and unhiding rows and columns, just in case this task is new to you. To hide a row or column, you click the header to select the entire row or column, right-click the selection, and choose Hide from the resulting context menu. To hide multiple noncontiguous rows or columns, select the rows or columns and choose Hide & Unhide from the Format dropdown in the Cells group. In Excel 2003, choose Column or Row from the Format menu, and then select Hide.

You can quickly discern that a row or column is hidden by the missing header. Unhiding is also simple. Select the adjacent columns or rows, right-click, and select Unhide from the resulting context menu. In the sheet below, I selected columns C and E to unhide column D. Use the same process to unhide multiple non-contiguous rows and columns as you would a single row or column, just select a larger range.

It's a simple task, but a problem arises when you want to unhide row 1 or column A because there's no row or column above or to the left, respectively.

When row 1 or column A is hidden, enter A1 in the Name Box, and press Enter. Excel will select cell A1, but you won't see that happen, because the cell's hidden. Next, click the Format dropdown in the Cells group (on the Home tab) and choose Hide & Unhide. Then, select Unhide and Excel will expose the hidden row or column. In Excel 2003, use the Name Box to select cell A1. Then, choose Row or Column from the Format menu, and select Unhide.

There are a couple of tricks for unhiding, but I've found most users forget them because they don't use them often enough to commit them to memory. This technique seems easy for most users to remember. What's your favorite trick for unhiding row 1 or column A?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

18 comments
rejlapointe
rejlapointe

Assume Column A and Row 1 are hidden. Ctrl-G (GoTo). Type address A1. OK. Press Ctrl-Shft-9 (UnHide Row) then Ctrl-Shft-0 (UnHide Column). Voila!

rogyaeger
rogyaeger

I'm an instructor of MS Office for a local community college and I had a few students get some of their rows (1 through 4 mostly) hidden and could not retrieve them. Neither could I, until I removed the "Freeze Panes" that had been created as a part of the exercise. Always another gotcha to getcha!

Prendo
Prendo

the missing header??? Really??? How much time do you have, and how good is your attention to detail?? I've always found that it's better to let the file tell you about hidden columns. For example, I'd key this formula into a cell in column A: =IF(CELL("width",A1)=0,1,0) This will result in 1 if the column is hidden. Copy this accross ALL columns (use CTL+SHIFT+RICHTARROW or END, SHIFT+RIGHTARROW to highlight all, then paste). Select the row of formulae by clicking once in the row number, then look at the autostats in the status bar. The SUM stat will tell you how many columns are hidden. You can quickly narrow down where they are by deleting half the formulae at a time and re-checking the sum stat for changes.

Inori
Inori

I'm thinking this may be version specific, because the first time I encountered this, I had a user who had managed to hide the first column of a report (he knew it was there the last time he used it), and I tried selecting all, and selecting there backwards (to the left), and about three other things that I can no longer remember, and nothing worked. If it wasn't that the data showed up on the printout, we wouldn't have known that he hadn't deleted it. Eventually, the only thing that worked was to use the GoTo command to get there (A1), and the Unhide command once in the cell. He might also have had Freeze Panes on, which would have prevented everything else from helping.

LaurenAWhite
LaurenAWhite

In addition to the problem of unhiding column A (which I usually unhide by hovering my cursor over the left border of B until I get the double-sided arrow, then just click and drag to the right to expand the hidden column A) - I've also run across columns or rows being 'hidden' because of how Freeze Panes was applied. If you scroll to the right on a worksheet until column A is no longer visible and then apply freeze panes, then you cannot scroll back to see column A until you unfreeze panes.

clcoronios
clcoronios

I select column B and drag left to include the row numbers ("column" to the left under the Select All icon) - then right click and select Unhide. Works the same for row 1.

DonG43
DonG43

My solution is just to never hide row 1 or column A. Thanks for the tip in how to unhide them anyway.

Sawedoff
Sawedoff

If you click in the "Select all" box (left of the column labels and above the row labels, it selects the whold worksheet. then Unhide will unhide A and 1. Of course, it also unhides all other rows and columns, so if that's not what you want, the F5 (GOTO) trick works best.

rgaudette
rgaudette

I find it as easy if not easier to use "group" It will hide the row or column but leaves a convenient "+" character to the side or top you can use to unhide that particular row or column..

AnswerMan
AnswerMan

I have used Excel for many things. What situation would require hiding Col A Row 1?

dylanstip
dylanstip

In Excel 2007 you can just hover over the left hand side of column B and right click and click unhide..... The same as you usually would

hjertet
hjertet

What about the shurtcuts for the same thnings? Hide column: Ctrl+0 Show column: Shift+Ctrl+0 Hide row: Ctrl+9 Show row: Shift+Ctrl+9 At least with a danish keyboard From the Heart

alecpjd
alecpjd

Once the cursor is in A1, using F5 or the name box, just use Ctrl + shift + ) to unhide column A, or Ctrl + shift + ( to unhide row 1. (I think they were two of TechRepublic's keyboard shortcuts). They work even though the cursor is hidden.

zimmerwoman
zimmerwoman

I use custom views extensively so if I hide columns for some reason, I can always revert to "normal" view which has everything displayed. Then I use "X presentation", "Y presentation", "manager review" and several others. When I get spreadsheets in which Row1, ColA is hidden, I use F5 "go to" that cell, then format the column width to 5 and the row height to 5. I use that because sometimes people don't actually hide the row/column, but instead set the dimensions to zero, in which case unhiding is not useful because it will make them appear in their formatted settings. If that is 0 X 0, there is still more work to do.

Spitfire_Sysop
Spitfire_Sysop

I once overused cell hiding to make a report look nice. I wanted it to show only the print area. It looked like a professional report. Something very strange occurred at some point and I am only left with my speculation of what it could be. At first I noticed that the document was very slow. Slow to open, slow to scroll, slow to do anything at all really. Then after I saved it was clear that my 2MB report had ballooned to 12MB. Then I tried to fix it. The columns would unhide but the rows... The rows never came back. I held a little funeral as I explained to my spreadsheet that it was over. I would move whoever was left to a new home in a new spreadsheet and leave this terrible mess behind. It's the least I could do after they lost so many of their uniformly divided brethren. So what happened? I hid the rows by selecting all the unused rows and I hid the columns by selecting whole columns. Where these two commands intersected I believe a perverse cell hiding orgy took place where each cell was told to hide multiple times in some way that Microsoft never intended. This duplicitous duplication is irreversible. I do not recommend it even though it sure did look nice.

damiross
damiross

Unhiding column A or row 1 is no different from unhiding other rows. Just click on B or 2 and drag to the left or up respectively. Keeping the cursor in the highlighted row or column (a 2R or 2C will show), right click, select unhide.

SirWizard
SirWizard

As Sawedoff suggests, click the intersection box of the row headers and column headers to select all rows and columns. With everything selected, not only can you hide or unhide everything at will, you can double-click the border edge of any row/column to resize all rows/columns to automatically fit the largest entry in that row/column. To autosize a single row/column double-click the bottom/right edge of the box containing the header number/letter.

erapgs
erapgs

Just tried and it works the way you told!!! dragging to the left or up works to hide and unhide!!!