Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Displaying a Hidden First Row.

Displaying a Hidden First Row

by Allen Wyatt
(last updated January 31, 2015)

23

Excel makes it easy to hide and unhide rows using the menus. What isn't so easy is displaying a hidden row if that row is above the first visible row in the worksheet. For instance, if you hide rows 1 through 5, Excel will dutifully follow out your instructions. If you later want to unhide any of these rows, the solution isn't so obvious.

To unhide the top rows of a worksheet when they are hidden, follow these steps:

  1. Press F5. Excel displays the Go To dialog box. (See Figure 1.)
  2. Figure 1. The Go To dialog box.

  3. In the Reference field at the bottom of the dialog box, enter the number of the row range that you want to unhide. For instance, if you want to unhide rows 2 through 3, enter 2:3. Likewise, if you want to unhide row 1, enter 1:1.
  4. Click on OK. The rows you specified are now selected, even though you cannot see it on the screen.
  5. Display the Home tab of the ribbon.
  6. In the Cells group click the Format tool.
  7. Hover the mouse pointer over the Hide & Unhide option and then click Unhide Rows.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11104) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Displaying a Hidden First Row.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Read-Only Documents without a Password

Want to protect your document so it cannot be changed? There are a couple of ways you can do it, and not all of them involve ...

Discover More

Turning Off Paste Options

Paste information into a document and you'll immediately see a small icon next to the pasted information. This icon allows ...

Discover More

Intelligently Starting a New Paragraph

When using styles in a document, you can increase your productivity by letting Word know what paragraph style you expect to ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Using AutoComplete with Disjointed Lists

AutoComplete can help you to more quickly enter information in a worksheet. How it works, behind the scenes, can affect how ...

Discover More

How Many Rows and Columns Have I Selected?

Want a quick way to tell how may rows and columns you've selected? Here's what I do when I need to know that information.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2016-11-30 15:47:47

Browni

Thank you so much for posting this! The directions are easy to follow!


2016-11-10 09:32:00

Diane

I still cannot seem to see line 1-3


2016-10-17 01:51:16

Joselito

additionally, check if worksheet has panes frozen specially the upper first few rows, then maybe you can see the first few rows.


2016-03-02 10:36:21

Spamlet

I couldn't get my top rows to unhide with any of the standard methods, but found that, if you just click the 'page layout view' button on the bottom right toolbar, it unhides and unfreezes everything. Then you just click the normal view button. Easy!


2015-04-24 09:10:53

WM

Thank you Ferdie! I had frozen panes too.


2015-03-15 16:19:23

Brad

YES!!! I actually called Microsoft support and they couldn't tell me how to unhide the top rows. I wish I would have found this page first! It would have saved me a lot of time and frustration.


2015-02-24 07:50:29

Ferdie

OK Solution found!! I had FROZEN PANES set - took it off and unhiding worked!!


2015-02-24 07:33:49

Ferdie

I have the same experience as Koyaanisqatsi - no pointer shape changes anywhere taking place to the doublebar and 2 arrows as shown in the jpg picture. My sheet has rows 1-3 hidden somehow... None of the other options works for me aqs well. Using Excel 2010... Any other ideas?


2015-02-04 09:49:43

Mike

@Michael,
It all depends on your preference, Michael. I prefer not to touch my mouse if I don't have to.
I do appreciate the tip and all of the options that everyone suggested. That is why I watch this site, to learn new ways.
Mike


2015-02-04 06:11:26

Michael (Micky) Avidan

@Mike,
Too much energy vesting.
Placing the cursor on the upper border of cell A2 and double-clicking sounds (to me) much, much, easier.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-02-03 11:31:19

Mike

@Michael,
With rows 1,5-8 hidden:
F5 A 1 <Enter> Ctrl+Shift+9 will unhide row 1 without affecting 5-8.

Mike


2015-02-03 06:02:14

Michael (Micky) Avidan

@Mike,
How, exactly, does your (well known) short-cut suggestion help me if I have:
Row #1 Hidden + Rows 5-8 Hidden and all I want is to Un-Hide row #1 ?
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-02-02 10:29:12

Angie C.

Another way to unhide a top row is to do this: If rows 1 & 2 are hidden, select row 3 with the left mouse button and drag upwards. Then right click anywhere in row 3 and choose "unhide".
NOTE: You must right click in row 3 (and not in the column header 'A, B C' row).


2015-02-02 10:12:45

Mike

Thanks for the tips on various ways to 'go to' the hidden rows. I find using Ctrl+Shift+9 is a quick keyboard shortcut for unhiding defined rows.


2015-02-02 05:14:43

Bengt

I stand corrected. The mouse pointer should be positioned, like Richard pointed out, over the boundary line between this arrow and the first unhidden row. Thanks too to those who pointed to the double click- possibility. Didn't think of that.


2015-02-02 02:41:07

Richard Price

@Koyaanisqatsi,
The instruction "move the mousepointer to the top left corner of the sheet, the one that looks like an arrow pointing downwards to the right" is slightly misleading - if you hover right over that arrow nothing happens, as you said. What you actually need to hover over is the boundary line between this arrow and the first unhidden row.


2015-02-01 06:10:31

Michael (Micky) Avidan

@Koyaanisqatsi,
Take a good look at the picture and you'll see the "new" shape of the curser while hovering over the border above(!) row #2.
This is when you need to Double-Click.
http://srv2.jpg.co.il/10/54ce0606f0e0d.png
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-01-31 19:35:50

TERRY

YOUR TIP TALKS TO THE ISSUE BUT DOES NOT DISPLAY THE SOLUTION....
THANKS THOUGH.


2015-01-31 09:59:36

Russ

Bengt, no need to drag, just double click.

I am interested in the 2:3 or 1:1 possibilities, could help me, going to test.

Note: If rows 1-4 are hidden and the pointer is put on row five, moving up till the double lines with one up arrow and one down arrow appear a double click will return rows 1-4. However if rows 6,7,8 are hidden you can bring them back one at a time by going to row 5 or 9 and moving till the double line/double arrow appears and double clicking, second double click bring back another row, etc.
To bring back all row at once select both rows 5 and 9, get double line/double arrow and double click.

I would have preferred the one line return to have move one line from 5 to return 6, but it appears it always returns the highest line first.


2015-01-31 06:32:36

Paul

To select all the hidden rows at the top of a worksheet:
Put your mouse over the first visible row number and click and drag up to the select all button. With the hidden rows selected you can now follow steps 4,5,6 of the original tip.


2015-01-31 06:30:29

Koyaanisqatsi

There's no such pointer shape change when hoovering and/or mousedown on the mentionned corner in my version of Excel (2013). Did you configure your Excel in a special way, or is it older than 2013?


2015-01-31 06:09:47

Michael (Micky) Avidan

@Bengt,
When the shape looks like two parallel bars - try double clicking the left mouse button (No dragging needed).
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-01-31 04:54:45

Bengt

IMHO, there is a much simpler solution for the problem of unhiding hidden rows.
If you move the mousepointer to the top left corner of the sheet, the one that looks like an arrow pointing downwards to the right, you will notice that the pointer changes shape and looks like two parallel bars and two arrows, one pointing upwards, one downwards. If you then press your left mouse button and drag downwards, the hidden rows will be unhidden.

Bengt


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.