How to Unhide All Rows in Excel: The Complete Guide

Few things are more frustrating than opening a spreadsheet and realizing that some of your data has simply vanished, only to later discover it wasn’t deleted at all, just hidden. Whether you accidentally hid rows yourself, inherited a workbook from a colleague with hidden sections, or applied a filter that tucked certain rows out of view, Excel gives you several reliable ways to bring hidden rows back into view, whether it’s a single row or your entire worksheet at once.

In this guide, we’ll walk through every major method for unhiding rows in Excel, from the simplest right-click approach to keyboard shortcuts, ribbon commands, and troubleshooting for stubborn cases where rows remain hidden even after trying the standard fix. We’ll also cover the difference between hidden rows and filtered rows, since they behave differently and require different solutions. This guide applies to both Excel for Windows and Excel for Mac, along with a quick look at Google Sheets.

Why Rows Get Hidden in the First Place

Before jumping into the fixes, it helps to understand the common reasons rows end up hidden, since this affects which method will actually solve your problem.

Manually hidden rows: Someone (possibly you) selected specific rows and used the Hide Rows command, often to temporarily declutter a view or hide sensitive information without deleting it.

Filtered data: Applying a filter to a data range temporarily hides rows that don’t match your filter criteria. These rows aren’t technically “hidden” in the traditional sense, but they appear the same way visually.

Grouped and collapsed rows: Excel’s Group feature (commonly used for outlines or subtotal summaries) can collapse entire sections of rows, which look similar to hidden rows but require a different un-collapsing method.

Row height set to zero: In rare cases, a row’s height may have been manually set to 0 pixels rather than using the Hide command directly, which produces the same visual effect but occasionally isn’t caught by the standard Unhide command depending on how it was originally set.

Frozen panes or extreme zoom settings: Occasionally, what looks like a missing row is actually a display issue caused by frozen panes or an unusual zoom level, rather than a genuinely hidden row.

Method 1: Unhide All Rows Using Select All

This is the fastest and most reliable way to unhide every hidden row in your entire worksheet in one action.

1. Click the Select All button, the small triangle in the top-left corner of your worksheet where the row numbers and column letters meet, or press Ctrl + A.
2. Click Format in the Cells group.
3. Hover over Hide & Unhide.
4. Click Unhide Rows.

This method selects every cell in the worksheet first, ensuring that no matter where your hidden rows are located, they’ll all be revealed simultaneously.

Method 2: Unhide Rows Using Right-Click

If you already know roughly where your hidden rows are located, right-clicking directly on the surrounding row numbers is often the quickest approach.

1. Click and drag to select the row numbers immediately above and below the hidden row(s). For example, if row 5 is hidden, select rows 4 through 6.
2. Right-click anywhere within your selected row numbers.
3. Choose Unhide from the context menu.

This method works well for small, isolated cases where you can visually identify the gap in row numbering that indicates a hidden row.

Method 3: Using Keyboard Shortcuts

Excel includes dedicated keyboard shortcuts for unhiding rows, which can be faster than navigating through menus once you’ve memorized them.

On Windows:

1. Select the rows surrounding the hidden row(s), or press Ctrl + A to select the entire worksheet.
2. Press Ctrl + Shift + 9 to unhide rows.

On Mac:

1. Select the rows surrounding the hidden row(s), or press Cmd + A to select the entire worksheet.
2. Press Cmd + Shift + 9 to unhide rows.

This shortcut is worth memorizing if you frequently work with hidden rows, since it eliminates the need to navigate through the ribbon or right-click menu entirely.

Method 4: Unhide Rows Using the Name Box

If you know exactly which row is hidden but the right-click method isn’t working as expected, you can use Excel’s Name Box to directly select the hidden row before unhiding it.

1. Type the row reference of the hidden row, for example, A5, and press Enter. This selects the hidden row even though it’s not visible.
2. Go to Home > Format > Hide & Unhide > Unhide Rows, or use the keyboard shortcut Ctrl + Shift + 9 (Windows) or Cmd + Shift + 9 (Mac).

This method is particularly useful for unhiding a single specific row without affecting other hidden rows elsewhere in the worksheet that you may want to keep hidden.

How to Unhide Row 1 Specifically

Row 1 is a special case because it doesn’t have a visible row above it to select alongside it, which trips up a lot of users trying the standard right-click method.

1. Type A1 and press Enter to select the hidden first row.
2. Alternatively, using Ctrl + A to select the entire worksheet before unhiding, as described in Method 1, will also successfully reveal row 1 along with any other hidden rows.

How to Distinguish Between Hidden Rows and Filtered Rows

It’s easy to confuse hidden rows with filtered rows, since both cause rows to disappear from view, but they require different solutions.

Signs you’re dealing with filtered data:

• Row numbers appear in blue rather than black.
• There’s a filter dropdown arrow icon visible in your header row.
• The Data tab shows “Filter” highlighted or active.

To remove a filter and restore all rows:

1. Go to the Data tab.
2. Click Clear in the Sort & Filter group to remove all active filters, or click the dropdown arrow on the relevant column header and select Clear Filter From [Column Name].

If your rows are still missing after clearing filters, they’re likely genuinely hidden rather than filtered, and you should return to the unhide methods described above.

How to Unhide Collapsed Grouped Rows

If your worksheet uses row grouping (commonly seen in financial models or reports with subtotal sections), the rows aren’t technically “hidden” in the traditional sense, and the standard Unhide command won’t reveal them.

1. Look for small plus (+) or minus (-) icons to the left of your row numbers, which indicate grouped sections.
2. Click the plus icon to expand a specific collapsed group.
3. To expand all groups at once, go to the Data tab, click the small arrow in the Outline group, and choose Show Detail, or click the numbered buttons (1, 2, 3) that appear above the group icons to control the level of detail shown.

If you want to remove grouping entirely rather than just expanding it temporarily, select the grouped rows, go to Data > Ungroup, and choose Rows.

How to Unhide Rows When the Row Height Is Set to Zero

In rare cases, particularly in workbooks that have been edited through automated scripts or imported from other software, a row’s height may be set directly to 0 rather than using the standard Hide command. The standard Unhide command usually still resolves this, but if it doesn’t, try this alternative:

1. Select the rows surrounding the affected area, including a few extra rows above and below to be safe.
2. Go to Home > Format > Row Height.
3. Enter a specific value, such as 15 (Excel’s typical default row height), and click OK.

This manually forces the row height back to a visible size, which resolves stubborn cases where the standard Unhide command doesn’t seem to have any visible effect.

How to Unhide Rows in Excel for Mac

The process for Excel on Mac closely mirrors Windows, with minor differences in menu naming.

1. Select the rows surrounding the hidden row(s), or press Cmd + A to select the entire worksheet.
2. Go to the Home tab, click Format, hover over Hide & Unhide, and click Unhide Rows.
3. Alternatively, use the keyboard shortcut Cmd + Shift + 9.

Right-click functionality also works identically to Windows, allowing you to select surrounding rows and choose Unhide directly from the context menu.

How to Unhide Rows in Google Sheets (Free Alternative)

If you’re working with a shared file in Google Sheets rather than Excel, the process is slightly different but achieves the same result.

1. Select the row numbers immediately above and below the hidden row(s).
2. Look for small arrow icons that appear between the row numbers, indicating a hidden row, and click the arrow to reveal it.
3. Alternatively, select a broader range of rows, right-click, and choose Unhide rows from the context menu.
4. To unhide all rows in the entire sheet, select all rows using the corner button (or Ctrl + A / Cmd + A), then right-click and select Unhide rows.

Google Sheets is free with a Google account and handles hidden rows in a very similar way to Excel, making it easy to switch between the two if you’re collaborating with someone using a different platform.

Pro Tips for Managing Hidden Rows

• Use grouping instead of hiding for organized data. If you frequently hide and unhide the same sections, Excel’s Group feature (Data tab > Group) offers a cleaner, more visual way to collapse and expand sections using plus/minus icons, rather than relying on the Hide command repeatedly.
• Check for filters before assuming rows are hidden. Since filtered rows and hidden rows look identical at first glance, always check for blue row numbers or an active filter dropdown before spending time troubleshooting the wrong problem.
• Use Ctrl + A first when troubleshooting. If you’re not sure exactly where hidden rows are located, selecting the entire worksheet before running Unhide Rows guarantees you’ll catch every hidden row, regardless of position.
• Label intentionally hidden rows. If you’re sharing a workbook with hidden rows that should stay hidden (such as sensitive calculations), consider adding a note or comment nearby so collaborators understand the hidden data is intentional rather than assuming it’s an error.
• Save a backup before mass-unhiding in unfamiliar workbooks. Save a copy before unhiding everything, just in case you need to reference the original hidden structure later.

Common Mistakes to Avoid

• Trying to right-click directly on a hidden row’s exact position. Since the row itself isn’t visible, you need to select the visible rows immediately surrounding it, not attempt to click on the hidden row directly.
• Confusing grouped/collapsed rows with hidden rows. Using the standard Unhide command won’t expand a collapsed group; you need to use the plus icons or the Ungroup command instead.
• Forgetting that row 1 requires a different approach. Since there’s no row above row 1 to select alongside it, use the Name Box method or Select All rather than trying to highlight a row above it that doesn’t exist.
• Assuming a missing row was deleted rather than hidden. Before assuming data loss, always check for hidden or filtered rows first, since accidental deletion and accidental hiding look identical from the surface but require completely different recovery approaches.
• Not checking whether AutoFilter is still active after unhiding. Even after successfully unhiding rows, an active filter can cause certain rows to disappear again the next time filter criteria are applied, which can be mistaken for the unhide process not working correctly.

Conclusion

Unhiding rows in Excel is usually a quick fix once you understand which method applies to your specific situation, whether that’s a simple right-click on the surrounding rows, a keyboard shortcut, selecting the entire worksheet to catch every hidden row at once, or expanding a collapsed group rather than using the standard Unhide command. The key is correctly identifying whether you’re dealing with genuinely hidden rows, filtered data, or grouped sections, since each requires a slightly different approach to resolve.

With the methods covered in this guide, you should be well equipped to recover hidden data quickly, whether you’re working in Excel for Windows, Excel for Mac, or the free Google Sheets alternative. And going forward, using grouping instead of repeated hiding, along with a quick filter check before troubleshooting, can save you time the next time a row seems to have mysteriously disappeared.

Frequently Asked Questions

1. Why can’t I see the hidden row number to right-click on it directly? Hidden rows don’t display their row number at all, which is exactly why the standard method requires selecting the visible rows immediately above and below the hidden one, rather than trying to click on the hidden row itself.

2. What’s the fastest way to unhide every hidden row in a large worksheet? Press Ctrl + A (Windows) or Cmd + A (Mac) to select the entire worksheet, then go to Home > Format > Hide & Unhide > Unhide Rows, or use the keyboard shortcut Ctrl + Shift + 9 (Cmd + Shift + 9 on Mac).

3. Why does unhiding rows not work even after I tried the standard method? This often happens when the rows are actually part of a collapsed group rather than traditionally hidden, or when a filter is active. Check for plus/minus grouping icons and blue filtered row numbers before assuming the standard Unhide command has failed.

4. Is there a difference between hiding a row and setting its height to zero? Functionally, they look nearly identical, but they’re technically different settings. The standard Unhide command usually resolves both cases, though manually adjusting row height back to a standard value (such as 15) can serve as a backup fix if Unhide doesn’t seem to work.

5. Can I unhide just one specific row without revealing other hidden rows nearby? Yes, use the Name Box to type the specific row reference (such as A5) and press Enter to select just that row, then apply Unhide Rows, leaving any other hidden rows elsewhere in the sheet untouched.

6. Why do some of my rows have blue numbers while others are black? Blue row numbers indicate that a filter is currently hiding other rows based on specific criteria, while black numbers represent your normal, unfiltered view. This is a visual cue that filtering, not manual hiding, is responsible for missing rows.

7. Does unhiding rows in Excel also work the same way in Excel Online? Yes, the core Unhide functionality works the same way in Excel Online through the Home tab’s Format menu, though the exact menu layout may look slightly different compared to the desktop application.

8. Will unhiding rows affect any formulas that reference the hidden data? No, formulas continue to calculate correctly whether a row is hidden or visible, since hiding only affects the visual display of a row, not its actual data or its inclusion in formula calculations elsewhere in the workbook.