To unhide every row in an Excel worksheet, select the entire sheet using the Select All button at the top-left corner of the grid, then choose Home > Format > Hide & Unhide > Unhide Rows. This works in Excel for Microsoft 365 on Windows, including when row 1 is hidden. Before you begin, make sure you are working in the correct worksheet and that you can edit it; a protected sheet may block the command.
What You’ll Need
- An Excel workbook open to the worksheet whose rows you want to reveal.
- Permission to edit the sheet. If it is protected, you may need the password to unprotect it.
- Optional: the row numbers or approximate location of the hidden rows, if you only want to unhide part of the sheet.
You do not need a formula, add-in, or macro. Unhiding rows changes their display, not the values in their cells. If rows are missing because a filter is applied or an outline is collapsed, those require a different action, covered below.
Step-by-Step
- Open the right worksheet. Click the worksheet tab at the bottom of the Excel window. If the workbook has several sheets, confirm the tab name before changing anything. Unhide Rows applies to the selected worksheet, not automatically to every sheet in the workbook. If you need to reveal rows on multiple sheets, repeat the process on each one.
- Select every cell in the worksheet. Click the small Select All button where the row numbers and column letters meet, at the top-left corner of the grid. It is the square or triangle-shaped button above row 1 and left of column A. The whole sheet should become selected. This is more dependable than selecting only visible rows, because it includes the rows that are hidden and works when the hidden area is not obvious.
- Open the Unhide Rows command. On the ribbon, click Home > Format > Hide & Unhide > Unhide Rows. Format is in the Cells group on the Home tab. Depending on your window width, the ribbon may compress the group, but the Format menu remains available from Home. Excel should display all rows that were hidden by row height.
- Check the row headings. Look down the numbered row headings on the left side of the grid. If the sequence is now continuous, such as 8, 9, 10, 11, the rows have been restored. Before unhiding, a gap such as 8 followed by 12 usually indicates that rows 9 through 11 were hidden. Scroll through the relevant part of the sheet too, since the hidden section may be far below the current view.
- Use a shortcut if you prefer. On Windows, select the worksheet or the relevant rows, then press Ctrl+Shift+9 to unhide rows. The shortcut may not work on some keyboard layouts or when another application intercepts it; use the ribbon command if nothing happens. On a Mac, the corresponding shortcut is generally Command+Shift+(. Keyboard layouts and Excel versions can affect shortcut behavior, so the menu path is the reliable fallback.
- Save the workbook if you want to keep the change. Press Ctrl+S on Windows or Command+S on Mac. Unhiding does not alter cell contents, but saving preserves the worksheet’s new display state for the next time you or a colleague opens the file.
If you know exactly which rows are hidden and do not want to affect the full sheet, select the row headings on both sides of the hidden section. For example, if row 10 is hidden, drag across the row headings for rows 9 and 11 so both are selected; then right-click either selected heading and choose Unhide. For a block of hidden rows, select the visible row heading immediately above and the one immediately below the block. If the first or last row is hidden and you cannot select a neighboring heading on both sides, use the whole-sheet method instead.
Common Mistakes
- Selecting only the visible data range. Selecting a table or a few cells may leave hidden rows outside that selection untouched. If your goal is all hidden rows on the current worksheet, click the Select All button before using Unhide Rows. In a large sheet, do not assume that selecting the current screen selects the entire worksheet.
- Using Unhide Rows to clear a filter. A filter hides rows that do not meet its criteria, but those rows are not hidden by row height. Unhide Rows will not bring filtered-out records back. Clear or change the filter instead, as described in Troubleshooting.
- Looking only for a visible gap in the row numbers. Hidden row 1 has no row above it to make the usual gap easy to spot. A row may also have a very small height, making it appear missing. Selecting the whole sheet and running Unhide Rows handles both ordinary hidden rows and many zero-height rows.
- Right-clicking a cell instead of a row heading. The Unhide option is available from the context menu for selected row headings. If you right-click inside a cell, you may see a different menu. Select the numbered headings along the left edge first.
- Assuming the command failed because the sheet looks unchanged. Rows may have been hidden on another worksheet, or the missing records may be filtered or grouped. Verify the active tab and identify what is making the rows disappear before repeating the same command.
Tips
- Use the Select All button for the most complete result. Ctrl+A can select a current data region or table first, depending on where the active cell is. Pressing it again may expand the selection, but behavior varies with the current selection. The Select All button at the grid corner is a clear way to target the complete worksheet.
- Unhide only a section when appropriate. If a workbook intentionally hides notes, calculations, or archived records, restoring every row may make the sheet harder to use. Select the neighboring row headings around just the needed section and use right-click > Unhide.
- Check for filters and outline controls before changing the layout. Filter arrows in the header row and plus or minus symbols beside the row numbers are clues that records may be filtered or grouped rather than manually hidden. Clearing a filter or expanding a group preserves the intended organization better than repeatedly changing row heights.
- Make the change on each relevant worksheet. The Select All button selects one sheet at a time. If several tabs need attention, repeat the steps for each. Avoid grouping multiple sheets unless you specifically intend to apply the same change to all of them; grouped sheets can also cause accidental edits to be repeated.
- Remember that unhide is a display change. It does not delete, move, or recalculate data. If you reveal rows and later want to conceal them again, select their row headings, right-click, and choose Hide, or use Home > Format > Hide & Unhide > Hide Rows.
Troubleshooting
Unhide Rows is disabled or nothing changes. First check whether the worksheet is protected. Go to Review > Unprotect Sheet. If Excel requests a password, you need the password or help from the workbook owner; there is no ordinary ribbon command that bypasses sheet protection. After the sheet is unprotected, select the whole worksheet and run Unhide Rows again. If the workbook opens in read-only mode, save an editable copy or ask for edit access.
The row numbers still skip after you unhide. Check that you selected the intended sheet and used the Select All button before choosing Unhide Rows. If the numbering still jumps, repeat the command once on that sheet. Then inspect the row headings around the gap: a collapsed outline may be hiding detail rows. Click the plus sign beside the grouped rows to expand that section. You can also select the relevant group and use the outline controls on the Data tab, where available.
Rows are still missing, but the numbering is continuous. Look for filter arrows in the header row. To show filtered-out records, click Data > Sort & Filter > Clear where that command is available, or open the filter menu for the relevant column and clear its selection. Clearing filters can reveal records that do not match the current criteria; it does not remove the records. If you need the filter, adjust its criteria instead of clearing it.
A row seems to have vanished even though there is no numbering gap. The row may have a very small height, or the text may be difficult to see because of formatting. Select the whole sheet, then choose Home > Format > Row Height and enter a reasonable height, such as 15, if you want to set a uniform height. Be aware that this changes the height of all selected rows and can override carefully formatted layouts. You can also select only the affected row headings and choose Home > Format > AutoFit Row Height.
The ribbon path looks different in Excel for the web or Mac. In Excel for the web, select the worksheet and look under Home > Format > Hide & Unhide; if the ribbon is simplified, expand it or use the row-heading context menu. On Mac, the Home tab also provides the Format and Hide & Unhide controls, though placement can vary by ribbon layout. In either version, selecting the entire worksheet first avoids missing rows outside the visible area.
Frequently Asked Questions
Can I unhide all rows in every worksheet at once?
There is not a single standard Unhide Rows command that automatically processes every worksheet in a workbook. Repeat the steps on each tab. You can group worksheets to make some changes together, but do so only if you understand that edits may apply to all grouped sheets.
Why does Excel skip row numbers?
A gap in the row headings usually means one or more rows are hidden. Select the whole sheet and choose Home > Format > Hide & Unhide > Unhide Rows. If the gap remains, check for protection or a collapsed outline.
Will unhiding rows restore deleted information?
No. Unhiding only reveals rows that are still in the worksheet but not currently displayed. Deleted rows or cell contents must be recovered from Undo, version history, a backup, or another copy of the workbook.
Does Unhide Rows also remove filters?
No. Filtered-out rows are controlled by filter criteria, not the row hide setting. Use the filter menu or the Data tab’s Clear command to show records excluded by a filter.
Can I unhide only one row?
Yes. Select the row headings immediately above and below it, right-click the selection, and choose Unhide. If the hidden row is the first or last row, select the entire worksheet and use the ribbon command.
Will other people see the rows after I unhide them?
They will see the changed layout when they open the saved workbook, provided they have access to that file and no filter or outline is hiding the records. Save the workbook after making the change.
Related
- Excel: Sum Values Based on Other Values
- Excel SUM Visible Rows: Add Filtered Data Correctly
- Excel Sum Text? What SUM Does With Text Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Export a CSV File in Excel Without Losing DataTo export a CSV from Excel for Microsoft 365 on Windows, open the workbook, select the worksheet you…
- How to Clear the Recipient Cache in Microsoft OutlookTo clear Outlook’s recipient cache, remove a suggestion from the address drop-down as you compose a message, or…
- Excel Automatically Splitting Text to Columns? Here’s WhyExcel does not normally split text into separate columns just because you type or paste a comma-separated phrase…
- Excel Data Validation: Can a Dropdown Hold Multiple Values?Yes, but it depends on your Excel version: standard Data Validation dropdowns select one item per cell, while…
- Excel Time Format for Milliseconds: Display and CalculateTo show milliseconds in Excel, select the time cells, press Ctrl+1, choose Number > Custom, and enter hh:mm:ss.000…
- Excel Dropdown List Without Blanks: Keep Options CleanYes. In Excel for Microsoft 365, use a FILTER formula to create a helper list containing only nonblank…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.