Summary

Hiding a row or column takes two clicks. Getting one back is where people lose twenty minutes, because Unhide does nothing at all when the rows were never hidden in the first place — they were filtered, or squashed to a sliver, or sitting on a protected sheet. This article covers hiding, the three ways to unhide including column A at the very edge of the sheet, and how to tell which of the four causes you are actually looking at when nothing reappears.

In this guide

• 1. Hide Rows or Columns
• 2. Unhide a Range in the Middle of the Sheet
• 3. Unhide Column A or Row 1 at the Very Edge
• 4. Reset Every Hidden Row and Column on the Sheet
• 5. Check Whether the Rows Are Filtered Rather Than Hidden
• 6. Restore a Row Height or Column Width Dragged to Zero
• 7. Rule Out a Protected Sheet or a Hidden Sheet

Before You Start

• Any version of Excel. The right-click Hide and Unhide commands work the same on Windows, Mac and the web.
• Edit permission on the workbook. No admin rights required.
• Nothing here deletes data. Hidden rows and columns keep their values and keep feeding every formula that refers to them.

Instructions

1. Hide Rows or Columns

Hiding takes the row out of view and leaves everything else about it untouched.

1. Click the row number down the left edge of the sheet to select the whole row, or the column letter along the top to select the whole column. Drag across several headers to take a block, or hold Ctrl and click to pick out rows that are not next to each other.
2. Right-click anywhere inside the selected headers. A menu opens.
3. Click Hide. The rows disappear and the numbering skips — you see 4, 5, then 12 — with a doubled line between the two headers that remain.

The ribbon route does the same thing and names what it is doing:

4. Select the rows or columns.
5. On the Home tab, click Format in the Cells group at the right-hand end of the ribbon.
6. Point to Hide & Unhide and click Hide Rows or Hide Columns.

Nothing is removed by this. The values stay in the file, =SUM() over the column still adds the hidden rows, and unhiding puts everything back exactly as it was — so hiding a column to tidy up a report before a meeting costs you nothing.

Tip: Ctrl + 9 hides the selected rows and Ctrl + 0 hides the selected columns.

Note: The matching unhide shortcuts, Ctrl + Shift + 9 and Ctrl + Shift + 0, are unreliable on Windows — the second one is claimed by the keyboard-layout switcher on many machines and does nothing in Excel. Use the menu routes in Step 2 instead.

2. Unhide a Range in the Middle of the Sheet

The trick is selecting across the gap, because Excel needs a row on each side of the hidden ones to work with.

1. Find the gap. Read down the row numbers until they jump — 5 straight to 12 — and look for the doubled line between those two headers. Columns show the same jump in their letters.
2. Click the header immediately before the gap and drag to the header immediately after it. In the example above, click row 5 and drag to row 12. Both visible rows highlight, and the hidden ones are caught in between.
3. Right-click anywhere in the selected headers and click Unhide. The missing rows reappear with their numbering restored.

Or from the ribbon:

4. With the same selection in place, click Format in the Cells group on the Home tab.
5. Point to Hide & Unhide and click Unhide Rows or Unhide Columns.

Note: Unhide greyed out on the right-click menu means one of two things: nothing inside your selection is hidden, so widen it; or the sheet is protected, which Step 7 covers.

3. Unhide Column A or Row 1 at the Very Edge

Selecting across the gap breaks down at the edge of the sheet — there is nothing to the left of column A to drag from. Select the cell by name instead.

1. Click into the Name Box — the small white box immediately to the left of the formula bar, which normally shows the address of the selected cell.
2. Type A1 and press Enter. Cell A1 is now selected even though you cannot see it; the Name Box confirms it by reading A1.
3. On the Home tab, click Format in the Cells group.
4. Point to Hide & Unhide and click Unhide Columns. Column A slides back into view.
5. For a hidden row 1, follow the same steps and click Unhide Rows instead.

Tip: There is a mouse route too. Move the pointer onto the left edge of the B column header until it turns into a vertical double line with arrows either side, then double-click. Column A returns at its stored width. It is quicker when it works and fiddly to aim at, which is why the Name Box is the version-proof answer.

4. Reset Every Hidden Row and Column on the Sheet

When you have inherited a workbook and have no idea what is missing, clear the lot in one pass.

1. Click the Select All triangle — the small grey wedge in the corner where the row numbers meet the column letters, above row 1 and left of column A. The entire sheet highlights.
2. On the Home tab, click Format in the Cells group, point to Hide & Unhide, and click Unhide Rows.
3. Repeat with Unhide Columns. Every hidden row and column on the sheet is now visible.

This is deliberately blunt: it also reveals working columns a colleague hid on purpose, so a report you were about to send now shows its calculations. Press Ctrl + Z to put them straight back, or re-hide the ones you did not want using Step 1.

Tip: Ctrl + A does the same as the Select All triangle, with one wrinkle — if the cursor is sitting inside a block of data, the first press selects only that block. Press it a second time to take the whole sheet.

5. Check Whether the Rows Are Filtered Rather Than Hidden

This is the commonest reason Unhide appears to do nothing: the rows were never hidden. A filter is removing them, and filters ignore Unhide entirely.

1. Look at the colour of the row numbers down the left edge. Filtered sheets show the surviving row numbers in blue rather than the usual black.
2. Look along the header row for a small funnel icon on one of the dropdown buttons. That marks the column doing the filtering.
3. Read the status bar along the bottom-left of the window. A filtered sheet reports something like 24 of 512 records found.
4. To bring every row back, open the Data tab and click Clear in the Sort & Filter group. All filters drop and every row returns; the filter dropdowns stay in place so you can filter again in one click.
5. To clear only one column's filter, click the funnel dropdown on that column's header and choose Clear Filter From followed by the column's name.
6. To remove the dropdowns altogether, click Filter on the Data tab. That toggles filtering off for the sheet and hides nothing.

Note: Clearing a filter changes what you can see and nothing else — no data moves, no sort order is applied, and the filter dropdowns remain available. Full detail on building filters in the first place is in How to Sort and Filter Data in Excel.

6. Restore a Row Height or Column Width Dragged to Zero

A row dragged almost to nothing looks hidden and is not, so Excel has nothing to unhide and reports no error.

1. Check the row numbers. If they run in unbroken sequence — 9, 10, 11 with no jump — nothing is hidden, and one of those rows has been squashed to a line you can barely see.
2. Select the rows either side of the squashed one by dragging down the row headers. The invisible row is caught in the selection.
3. On the Home tab, click Format in the Cells group and choose Row Height. A small dialog opens with a single box.
4. Type 15 and click OK. That is the standard height for the default font, and every selected row snaps to it.
5. For a column squashed the same way, choose Column Width from the same menu and enter 8.43.
6. Alternatively, double-click the boundary line between two row headers. The row resizes to fit its tallest content.

Note: Setting an explicit height overwrites any custom heights inside your selection, including deliberately tall rows holding wrapped text. Select the narrow band around the problem row rather than the whole sheet, and Ctrl + Z restores the previous heights if you take too much.

7. Rule Out a Protected Sheet or a Hidden Sheet

Two states stop unhiding cold, and both announce themselves once you know where to look.

The sheet is protected. Hide and Unhide are greyed out on the right-click menu, and the Hide & Unhide items under Format are greyed too.

1. Open the Review tab. If the button in the Protect group reads Unprotect Sheet rather than Protect Sheet, protection is on.
2. Click Unprotect Sheet. If no password was set, protection lifts immediately and the commands come back.
3. If a password box appears and the password is not yours, ask IT or the workbook's owner to unprotect the sheet, or to tick Format rows and Format columns in the sheet protection options so you can unhide without removing protection. Include the file path and the exact sheet-tab name in the request. How protection is applied in the first place is covered in How to Protect a Sheet or Workbook in Excel.

The whole sheet is hidden, not its rows. The tab is missing from the row of sheet tabs along the bottom of the window.

4. Right-click any sheet tab at the bottom of the window and click Unhide. A dialog lists every hidden sheet in the workbook.
5. Select the sheet you want and click OK. The tab reappears in its original position.
6. If the sheet you are looking for is not in that list, it was hidden at a level the Unhide dialog cannot reach. Ask the workbook's owner to restore it and name the sheet you need.

Troubleshooting

WARNING

Hiding is presentation, not redaction. Every hidden row, column and sheet travels inside the file, and anyone you send it to can bring them back with the same three clicks you used. Salary columns, cost prices and working notes are all still there and still readable. Before a workbook leaves your organisation, delete the data outright, or copy the visible cells into a new workbook and send that — How to Protect a Sheet or Workbook in Excel covers what protection does and does not conceal.

Symptom / Error	Potential Cause	Solution
Unhide runs but nothing appears	Rows are filtered, not hidden	Clear the filter with Data > Clear in the Sort & Filter group — Step 5.
Row numbers show in blue	A filter is active on the sheet	Same cause as above. Clear it with Data > Clear — Step 5.
Column A will not come back	Nothing to the left of it to select across	Type A1 into the Name Box, then Home > Format > Hide & Unhide > Unhide Columns — Step 3.
Row numbers run unbroken but a row is invisible	Height dragged almost to zero, so nothing is hidden	Select the band around it and set Row Height to 15 — Step 6.
Unhide is greyed out	Sheet is protected, or nothing hidden is selected	Widen the selection across the gap. If it stays greyed, check the Review tab for Unprotect Sheet — Step 7.
A whole sheet tab is missing	The sheet is hidden, not its rows	Right-click any sheet tab and click Unhide — Step 7.
Ctrl + Shift + 0 does nothing	Windows claims the combination for switching keyboard layout	Use Home > Format > Hide & Unhide > Unhide Columns — Step 2.
Hide is missing from the right-click menu	You right-clicked a cell instead of the header	Right-click the grey row number or column letter itself, not a cell inside the row — Step 1.

Related Articles

• How to Freeze Panes and Split Views in Excel
• How to Sort and Filter Data in Excel
• How to Print an Excel Spreadsheet on One Page
• How to Protect a Sheet or Workbook in Excel

Did this solve your problem?