Hide and Unhide Rows and Columns in Excel

To unhide rows in Excel, select the rows on both sides of the hidden ones, right-click and choose Unhide. Hiding works the same way in reverse, and both have keyboard shortcuts. This guide covers hiding and unhiding rows and columns, the awkward cases like row 1 and column A, and how hidden rows affect your totals.

How to tell that rows or columns are hidden#

Hidden rows and columns don't show any warning, but the headers give them away. Look for a jump in the row numbers (row 4 followed by row 7) or the column letters (C followed by F). Between them, the border line is slightly thicker.

Filtered rows look similar, but there's a difference: when a filter is on, the row numbers turn blue and the header row shows filter buttons. Filtered rows come back with Data > Clear, not with Unhide. The Filter a table lesson covers filters in detail.

How to hide rows and columns#

1. Select the rows or columns. Click a row number or column letter; hold Shift and click another to select a block, or hold Ctrl (Mac: ⌘) to pick separate ones.
2. Right-click any selected header and choose Hide.

Keyboard shortcuts on Windows:

The shortcuts work on whole rows or columns, but you don't need to select the whole row first: Ctrl+9 hides the rows of whatever cells are selected. To select whole rows from the keyboard, press Shift+Space; for columns, Ctrl+Space. The Select ranges with Shift lesson practises both.

The ribbon route is Home > Format > Hide & Unhide, which lists Hide Rows, Hide Columns, Unhide Rows and Unhide Columns.

How to unhide rows in Excel#

1. Select the rows above and below the hidden rows. For hidden rows 5 and 6, select rows 4 to 7.
2. Right-click any of the selected row numbers.
3. Choose Unhide.

Columns work the same way: to show hidden columns D and E, select columns C to F, right-click a column letter and choose Unhide.

You can also point just below the thick border in the headers until the pointer becomes a split double arrow, then drag down to show the hidden rows again.

Why Ctrl+Shift+0 doesn't unhide columns#

On many Windows computers, Ctrl+Shift+0 does nothing because Windows reserves Ctrl+Shift for switching keyboard languages. Instead of changing system settings, use the right-click Unhide command or Home > Format > Hide & Unhide > Unhide Columns.

How to unhide all rows and columns at once#

1. Click the Select All button, the small triangle where the row numbers and column letters meet. Or press Ctrl+A (Mac: ⌘A) once or twice until the whole sheet is selected.
2. Right-click any row number and choose Unhide.
3. Right-click any column letter and choose Unhide.

This is a good first step when you receive a file from someone else and want to see everything in it.

How to unhide row 1 or column A#

When the first row or column is hidden, there's nothing above or to the left to select. Use the Name Box:

1. Click the Name Box (left of the formula bar), type A1 and press Enter. The hidden cell is now selected, even though you can't see it.
2. Go to Home > Format > Hide & Unhide > Unhide Rows (for row 1) or Unhide Columns (for column A).

When Unhide doesn't work#

If the rows stay hidden, check these causes:

• They are filtered, not hidden. Blue row numbers mean a filter. Use Data > Clear, or press Ctrl+Shift+L to turn filters off.
• The row height is tiny. A row set to a height of, say, 0.5 looks hidden but isn't. Select the rows around it and set Home > Format > Row Height to 15.
• The sheet is protected. Unhide is greyed out on a protected sheet that doesn't allow formatting. Go to Review > Unprotect Sheet. Our guide to protecting a sheet and locking cells explains the settings.
• The rows are grouped and collapsed. If you see a + button in the margin, click it to expand the group.

Do hidden rows count in SUM?#

Yes. SUM adds every cell in its range, whether you can see it or not. That can surprise you when you hide rows and the total doesn't change.

To total only the visible rows, use SUBTOTAL with function number 109:

=SUBTOTAL(109,B2:B7)

109 means SUM, ignoring hidden rows. With the sample sales in B2:B7, the visible total is 89,300 with nothing hidden; hide the Online row and it drops to 67,650. (Function number 9 also sums, but it only skips rows hidden by a filter, not rows you hid by hand.)

When you copy a range with hidden rows, Excel copies the hidden rows too. To copy only what you see, select the range and choose Visible cells only in Go To Special, or press Alt+; on Windows, then copy.

Hide or group?#

For rows you hide and show often, such as monthly detail under a quarterly total, grouping is easier. Select the rows and go to Data > Group (Windows shortcut: Alt+Shift+→). A + and − button appears in the margin, so anyone can expand or collapse the detail with one click.

Remember that hiding is not protection. Anyone can unhide rows, so don't rely on it to keep data private. Hidden rows and columns also don't print, which is useful for working notes; see how to print a sheet neatly.

Quick hide and unhide is the kind of skill that makes you look fluent in a test; our list of shortcuts interviewers notice has more, and Excel skills for admin jobs covers the tasks where you'll use them.

Practise it#

Try the SUBTOTAL exercise on this page, then practise selecting whole rows and columns with Shift+Space and Ctrl+Space in a live spreadsheet. The Filter a table lesson shows how filtering hides rows and how SUBTOTAL totals only what's visible.