How to Hide and Unhide Rows in Microsoft Excel

When working with large, complex spreadsheets, your screen can quickly become overwhelmed with data. You might have rows containing intermediary calculations, old historical records, sensitive information, or reference data that you do not want to delete but also do not need to view right now.

Instead of deleting this data, Microsoft Excel allows you to temporarily hide rows. Hidden rows remain fully functional — formulas referencing them will still calculate correctly — but they disappear from your screen and will not appear if you print the document. This makes hiding rows one of the most practical features for managing large datasets.

How to Hide a Single Row

1. Open your Microsoft Excel spreadsheet.
2. Look at the far-left edge of your screen where the row numbers (1, 2, 3, etc.) are displayed. This is the row header.
3. Click on the number of the row you want to hide. The entire row will highlight in blue.
4. Right-click directly on the highlighted row number.
5. In the context menu that appears, click on Hide.

The row will immediately disappear. You will notice a gap in the numbering sequence on the left side (for example, it will skip from row 4 directly to row 6), and a slightly thicker border line will appear between the adjacent rows, indicating that data is hidden there.

How to Hide Multiple Rows

Consecutive Rows

1. Click on the number of the first row you want to hide.
2. Hold down the Shift key and click on the number of the last row in the range. All rows between them will be selected.
3. Right-click on any highlighted row number.
4. Click Hide.

Non-Consecutive Rows

1. Click on the first row number you want to hide.
2. Hold down the Ctrl key and click on each additional row number you want to hide.
3. Right-click on any of the highlighted numbers.
4. Click Hide.

All selected rows will be hidden in a single action, regardless of their position in the spreadsheet.

How to Hide Rows Using the Ribbon Menu

If you prefer using the ribbon rather than right-clicking:

1. Select the row or rows you want to hide by clicking their row numbers.
2. Click on the Home tab in the ribbon.
3. In the “Cells” group, click on Format.
4. Under the “Visibility” section, hover over Hide & Unhide.
5. Click Hide Rows.

How to Unhide a Specific Row

When you need to view or edit hidden data again, restoring the row requires selecting the rows surrounding the hidden one.

1. Look at the row numbers on the left side of the screen and find the gap where the hidden row exists (for example, between rows 4 and 6).
2. Click on the row number above the hidden row (row 4).
3. Hold the Shift key and click the row number below the hidden row (row 6). Both visible rows and the hidden row between them are now selected.
4. Right-click anywhere on the highlighted row numbers.
5. From the context menu, click Unhide.

The hidden row will instantly reappear in your spreadsheet.

How to Unhide Row 1

Unhiding the very first row can be confusing because there is no row above it to select. Here is how to do it:

1. Click on the Name Box (the small box to the left of the formula bar that typically shows a cell reference like “A2”).
2. Type A1 and press Enter. This selects cell A1 even though the row is hidden.
3. Click on the Home tab.
4. Click Format in the “Cells” group.
5. Hover over Hide & Unhide.
6. Click Unhide Rows.

Row 1 will reappear at the top of your spreadsheet.

How to Unhide All Rows at Once

If you have hidden rows scattered throughout a large document and need to reveal everything:

1. Click the Select All button — the small triangle icon in the very top-left corner of the spreadsheet, where the row numbers and column letters intersect. This selects the entire worksheet. Alternatively, press Ctrl + A.
2. Right-click on any row number on the left side of the screen.
3. Click Unhide.

Every hidden row in the entire sheet will instantly reappear.

Using Keyboard Shortcuts

Excel provides built-in keyboard shortcuts for hiding and unhiding rows:

Action	Shortcut
Hide selected rows	Ctrl + 9
Unhide selected rows	Ctrl + Shift + 9
Hide selected columns	Ctrl + 0
Unhide selected columns	Ctrl + Shift + 0

To use the unhide shortcut, you must first select the rows surrounding the hidden row (as described in the unhide steps above), then press the keyboard shortcut.

Important Considerations

• Formulas continue to work: Hidden rows are not excluded from formula calculations. A SUM or AVERAGE formula that includes hidden rows will still produce the correct result. If you specifically need to exclude hidden rows from calculations, use the SUBTOTAL function instead (for example, =SUBTOTAL(109, A1:A100) sums only visible cells).
• Printing behaviour: Hidden rows are automatically excluded when you print. This is useful for creating clean reports that omit raw data or working calculations.
• Sorting caution: Be careful when sorting data that contains hidden rows. Excel will move the hidden rows along with the visible data, which can produce unexpected results. Unhide all rows before sorting to avoid confusion.
• Filtering alternative: If you frequently need to show and hide rows based on specific criteria, consider using Excel’s AutoFilter feature (Data > Filter) instead of manually hiding rows. Filters are more flexible and easier to manage for dynamic datasets.
• Protection: If you want to prevent other users from unhiding rows, you can protect the sheet via Review > Protect Sheet. Ensure the “Format rows” option is unchecked in the protection settings.

Mastering row visibility is an essential spreadsheet skill that helps you maintain clean, focused views of your data without permanently removing any information.