How to Unhide All Rows in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

This tutorial will teach you to unhide single and multiple (continuous and non-continuous) rows in Excel.

How to Unhide All Rows in Excel

It is quite interesting to unhide all the rows from the worksheet, as it saves you time and effort. You can use the following steps:

  1. You need to perform a keyboard shortcut to select all the rows in the spreadsheet. Press and hold the ‘Ctrl’ key, the ‘Shift’ key, and the ‘Space’ key on your keyboard simultaneously. This will highlight all the rows in your spreadsheet.
    7-all-hidden-rows-from-sheet
  2. Once all the rows are selected, move your cursor to the row numbers displayed on the far left of your Excel window. After that, go to the Excel Ribbon ⇢ Home Tab ⇢ Format. Look for the option that says “Hide & Unhide”. It is usually located somewhere in the middle of the menu.
    8-click-on-format-select-hide-unhide
  3. Click on the “Unhide” option. This will unhide all the rows in your spreadsheet.
    9-select-entire-sheet-click-unhide-rows
  4. Once you click on it, you will get all the hidden rows back to the worksheet.
    10-all-rows-available

Following these steps, you can easily unhide all the rows in your Excel spreadsheet. Remember that the ‘Ctrl + Shift + Space’ keyboard shortcut will select all cells in the worksheet, not just the currently visible rows.

Steps to Unhide a Single Row in Excel

You can use the following steps:

  1. First, you must find the missing row number from its row header.
  2. After that, move your cursor to the hidden row number, and this will show you the drag sign.
  3. Next, click on it and hold the mouse.
  4. And by holding it, expand the hidden row. You will be able to see that hidden row.

However, you can also “Unhide” that row by right-clicking on the space between the rows. And then click on the “Unhide” option from it to unhide the hidden row. See the below steps.

excel-unhide-rows-instantly-witjh-mouse
  1. First, you must locate the hidden row. The row numbers before and after the hidden row will be consecutive, with the number of the hidden row missing. For instance, if you see row numbers 4 and 6 with no row 5 in between, then row 5 is hidden.
  2. The next step is to select the rows above and below the hidden row. You can do this by clicking and dragging your cursor over the two rows. For instance, if row 5 is hidden, you would select rows 4 and 6.
  3. After selecting the rows, right-click on the selected row numbers. This action will open a context menu with various options. It would help to look for the “Unhide” option, usually in the middle of the menu.
  4. Select unhide option. This action will unhide the selected row, allowing you to view and edit the data. Finally, confirm that the previously hidden row is now visible. It should appear between the two rows you initially selected.

However, you can also “Unhide” that row by right-clicking on the space between the rows.

right-click-on-this

And then click on the “Unhide” option from it to unhide the hidden row.

click-unhide-to-unhide-row

Unhide the Multiple Rows in Excel (Adjacent Rows)

When you want to unhide the multiple continuous rows at once, then proceed with the following steps:

  1. Again, the first step is to search out the range of rows hidden on the worksheet.
    3-missing-multiple-rows
  2. Moving ahead, select the single row above the hidden rows and then hold the shift key to select another row below them from the row header.
    4-above-and-below-line
  3. After this, right-click on any of the selected rows and choose the “Unhide” option.
    5-right-click-and-unhide
  4. Now, all the data is visible on the sheet as the multiple hidden rows turned to unhidden.
    6-all-hidden-rows

If you want to unhide the single hidden row from the multiple rows, take the cursor to the hidden row header.

After that, double-click on it, which will show you the single row that was hidden earlier. In this way, you can unhide rows one by one.

Why Unhiding Rows in Excel Important

Several potential challenges can arise when all rows or some of the rows are hidden in Excel:

1. Formula Complications:

Excel allows you to create formulas referencing data in different rows.

However, if these rows are hidden, the results of these formulas can become confusing as the data they’re referencing isn’t immediately visible.

This could lead to misunderstandings about how a particular result was calculated or make it challenging to troubleshoot formula errors.

2. Printing Issues

When you print a spreadsheet with hidden rows, those rows won’t appear in the printout.

This might cause issues if the printed document is supposed to include all data for comprehensive review or physical record-keeping.

Important data might be left out of printed reports, leading to incomplete information being disseminated.

3. Editing Difficulty

Another potential challenge arises when remembering that specific rows are hidden.

Suppose you need to see all the data, which can lead to confusion and possible errors if you try to edit or update the data.

For instance, you might accidentally duplicate data, unaware that the original data is hidden.

Hide Rows in Excel Quickly

  • Identify the Rows you want to Hide: Navigate through your spreadsheet and find the rows you want to hide.
  • Select the rows: Click and drag your mouse across the row numbers of the rows you want to hide. You can select a single, multiple, or non-adjacent row by holding down the Ctrl key as you click on the row numbers.
  • Right-click on the selected rows: After you’ve made your selection, right-click on one of the chosen row numbers. This will open a context menu. Click on “Hide” from the popup menu: In the right-click menu, find and click on the “Hide” option after you right-click. This will instantly hide the selected rows from view.
  • Save your changes: Remember to save your document after hiding the rows.
Last Updated: March 24, 2024