Hiding and unhiding rows in Excel is a beneficial characteristic that helps the users to hide the certain rows in the worksheet making it comfortable to pay attention only on related data or keep the work sheet clean. The rows become unseen on the worksheet and are left out from computing and publishing. Equally, unhiding rows make them seen or visible and return their function back.

How to Hide rows in Excel Sheet: Easy Step-by-Step Guide

• Launch Excel and add a worksheet where you want to hide the rows.
• Select the rows which you want to hide.

• If you want to hide a single row click, on the row number at the left end of the row. To hide many rows, press the “Ctrl” key when selecting each row.

• When the rows are selected, right-click on any of the selected row numbers. A context menu will display.

• In the context menu, choose the “Hide” option. This will hide the selected rows from view.

How to Hide rows using the ribbon in Excel Sheet?

• Launch Microsoft Excel and then the worksheet where you want to hide rows.
• Select the rows you want to hide. To select a single row, click on the row number at the left end of the row. For multiple rows, hold down the “Ctrl” key while selecting each row.
• When the rows selected, open the “Home” tab on the Excel ribbon.
• In the “Cells” group, find the “Format” button. It will seem like a paint bucket and has an arrow in the lower-right corner.
• Click on the arrow in the lower-right corner of the “Format” button. A drop-down menu will display.
• From the drop-down menu, select “Hide & Unhide.” Another menu will appear to the side.
• In the side menu, choose the “Hide Rows” option.

How to Hide rows in Excel Sheet Using Right-Click Menu?

Follow the steps to hide the rows using Right-click menu:

• Open Microsoft Excel and then the worksheet where you want to hide rows.
• Select the rows you want to hide. To select a single row, click on the row number at the left end of the row. For multiple rows, hold down the “Ctrl” key when selecting each row.
• Once the rows are selected, right-click on any of the selected row numbers. A context menu will display.
• In the context menu, choose the “Hide” option. This will make the selected rows invisible.

How to Hide rows in Excel Sheet Using Shortcut Key?

If you’d want to do a long process you may hide the selected rows rapidly by using shortcut key ctrl+9.

We can also unhide the rows using various ways .you can use the method of your choice. You can unhide all hidden rows, only specific rows or the first row in a sheet.

How to Unhide rows by using the ribbon in Excel Sheet?

We can unhide rows in Excel by following these steps:

• Press the “Home” tab.
• Go in the “Cells” group and open the “Format” button.
• Click to “Hide & Unhide” option.
• Press “Unhide Rows“.

After this rows will become unhidden.

How to Unhide rows using the context menu in Excel Sheet?

To unhide rows follow these steps:

• Press on the row number of the row you want to unhide.
• Right–click on the row number.
• Select “Unhide” from the menu.

The row will be unhidden.

How to Unhide rows in Excel Sheet Using Shortcut Key?

Pro Tips: We can also use shortcut: Ctrl + Shift + 9 key to unhide the rows.

How to show hidden rows by using double-clicking in Excel Sheet?

The quickly way to unhide rows in Excel is to double click them. The benefit of this method is that you don’t need to select anything. What you have to do is drag the mouse over the hidden row headings, and when the mouse pointer turns into a split two-headed arrow, double click.

How to unhide all rows using Excel Sheet?

If you want to unhide all the rows you need to select all the rows.

Click the Select All button (this button looks like a little triangle at the upper left corner of a sheet, in the intersection of the row and column headings):

Pro Tips: Click the Select All shortcut: Ctrl + A.

How to unhide all cells using Excel Sheet?

We can unhide all cells in Excel by using 2 different methods:

Method 1: Using the keyboard shortcut

• Select all the worksheet by pressing Ctrl+A.
• Then Press Ctrl+Shift+9.

All hidden cells will be unhidden.

Method 2:  Using the ribbon

• Select all the worksheet by pressing Ctrl+A.
• Press on the “Home” tab.
• Then click on the “Cells” group, then click on the “Format” button.
• Go to “Hide & Unhide” option.
• Click “Unhide All”.

All hidden cells will be unhidden.

How to unhide specific rows using Excel Sheet?

There are many ways to unhide specific rows in Excel. Following are 3 methods to unhide the specific rows:

Method 1: Using the right-click menu.

• Press on the row number of the first row you want to unhide.
• Hold down the Shift key.
• Click on the row number of the last row you want to unhide.
• Right-click anywhere in the selected rows.
• Select “Unhide” from the menu.

The selected rows will be unhidden.

Method 2: Using the Format menu.

• Press on the row number of the first row you want to unhide.
• Hold down the Shift key.
• Press on the row number of the last row you want to unhide.

• Press on the “Home” tab.                                                                                          
• Then click on the “Cells” group and then click on the “Format” button.
• In the “Format Cells” dialog box, bring the mouse on the “Visibility” option.
• In the “Visibility” section, click on “Unhide Rows” option.

The selected rows will be unhidden.

Method 3: Using the keyboard shortcut

• Click on the row number of the first row you want to unhide.
• Hold down the Shift key.
• Click on the row number of the last row you want to unhide.
• Press the Ctrl and Shift keys.
• Press the “9” key.

The selected rows will be unhidden.

How to unhide top rows in Excel Sheet?

It is very easy to unhide the top row in the Excel.

We have to select cell A1. Type A1 in the Name Box, and press Enter.

You can also use this method, go to the Home tab > Editing group, click Find & Select, and then click Go To.

The Go To dialog window pops up, you type A1 in the Reference box, and click OK.

Pro Tip: The 3th method is to use shortcut key Ctrl + Shift + 9.

Pro Tip: Click on format>unhide Rows on the ribbon.

The 4th method is very quick you just have to drift over the hidden row heading, and when the mouse pointer turns into a split two-headed arrow, double click:

Pro Tips and tricks for hiding and unhiding rows in Excel Sheet:

There are some tricks and tips for make your work easy and simple.

• To hide a row, select the row (or a cell in the row) you want to hide. Go to the Home tab, in the Cells group, press Format. In the Format submenu, click Hide & Unhide. Select Hide Rows.
• To unhide a row, select the rows (or cells in the rows) above and below the hidden row. On the Home tab, in the Cells group, press Format. In the Format submenu, press Hide & Unhide. Choose Unhide Rows.
• You can also hide rows by using the keyboard shortcut Ctrl+9.
• If you want all the rows to hide in a worksheet, select the entire worksheet (by clicking the top-left corner of the worksheet) and then press Format > Hide & Unhide > Hide Rows. To unhide all rows, select the entire worksheet and then click Format > Hide & Unhide > Unhide Rows.
• You can also hide rows by using the Filter command. To do this, click the Filter button in the Data group on the Home tab. Then, select the rows you want to hide and click the Hide button. To unhide rows, click the Filter button again and then select the rows you want to unhide.
• You can hide rows temporarily by using the Freeze Panes command. To do this, click the Freeze Panes button in the View group on the View tab. Then, select the row or column you want to freeze. To unfreeze the rows or columns, click the Freeze Panes button again.

There are some more tips for hiding and unhiding rows in Excel:

• When you hide a row, the data in the row is still kept in the worksheet, but it is not visible.
• When you unhide a row, the data in the row will be displayed again.
• You can hide and unhide rows as many times as you need to do.
• You can hide and unhide rows in many worksheets at the same time.
• You can hide and unhide rows in a saved workbook.

How to hide rows containing blank cells in Excel Sheet?

1. Here some ways to hide rows containing blank cells in Excel:
2. Select the range which have empty cells.
3. On the Home tab, in the Editing group, click Find & Select > Go To Special.

• In the Go To Special dialog box, select the Blanks radio button, and click OK.
• Press Ctrl + 9 to hide the corresponding rows.

 You can also use the keyboard shortcut Ctrl+9 to hide rows.

• To unhide rows, press Ctrl+Shift+9.
• You can hide rows temporarily by using the Filter command. To do this, click the Filter button in the Data group on the Home tab. Then, select the rows you want to hide and click the Hide button. To unhide rows, click the Filter button again and then select the rows you want to unhide.

How to hide rows based on cell value in Excel Sheet?

Here are two methods to hide rows based on cell value in Excel:

Using the Filter command:

1. Select the area of rows which you want to filter.
2. Go to home tab and press data group and then click the Filter button.
3. Click the drop-down menu that appears, select the column which you want to filter by.

• Deselect the box next to the value that you want to hide.
• Press OK.

Using conditional formatting:

• Select the area of rows that you want to format.
• Press the Conditional Formatting button in the Home tab.
• In the drop-down menu that appears, select New Rule.             

• In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
• In the Format values where this formula is true text box, type the following formula:

=C9=”value to hide”

• Replace “value to hide” with the real value that you want to hide.
• Click Format.
• In the Format Cells dialog box, you can make any changes to the formatting that you want to apply to the hidden rows.
• Click OK twice.

The rows that follow the conditions in the formula will be hidden.

Hide unused rows so that only working area is visible:

We can use the following step unused rows in Excel so that only the working area is visible:

• Select the row under the last row with data. To select the whole row, click on the row header.
• Press Ctrl+Shift+Down arrow to prolong the selection to the bottom of the sheet.
• Press Ctrl+9 to hide the selected rows.

Pro Tip: The hidden rows will still present in the worksheet, but they will be invisible. You can unhide them by pressing Ctrl+9 again.

We have some additional tips for hiding rows in Excel:

• You can also hide rows by right-clicking on the row header and selecting “Hide” from the menu.
• To hide multiple rows at once, select the rows you want to hide and then press Ctrl+9.
• You can unhide hidden rows by following the same steps you used to hide them.
• If you want to hide all rows that do not contain data, you can use the “Go To Special” command.

To do this:

• Click on the “Home” tab and
• Then click on “Find & Select” > “Go To Special“.
• In the “Go To Special” dialog box, select the “Blanks” radio button and then
• Click on “OK“.
• This will select all blank rows in the worksheet. You can then hide the selected rows by pressing Ctrl+9.

Example:

• If you want to see rows 1-10. Selects the rows 11-20 and press Ctrl+9. The selected rows are hidden.
• Now you can see only the rows 1-10. The hidden rows are still present in the worksheet, but they are invisible. The user can unhide the hidden rows by pressing Ctrl+9 again.

How to locate all hidden rows on Excel Sheet?

Follow the steps for how to locate all hidden rows on a sheet:

• Press on the “Home” tab.                                                                                          
• Click on “Find & Select” > “Go To Special” in the Editing group.

• In the “Go To Special” dialog box, select the “Visible cells only” radio button and then click on “OK”.

Now all visible cells in the worksheet will be selected. The borders of rows and columns that are adjacent to hidden rows and columns will be marked with a white border.

You can now easily identify the hidden rows by looking for the white border.

Example:

If you want to see rows 1-10 visible.  Clicks on the “Home” tab and then clicks on “Find & Select” > “Go To Special“. In the “Go To Special” dialog box, the user selects the “Visible cells only” radio button and then clicks on “OK“. All visible cells in the worksheet are selected. The borders of rows and columns that are adjacent to hidden rows and columns are marked with a white border.

The user can now easily recognize the hidden rows by looking for the white borders.

How to copy visible rows in Excel Sheet?

Here are some steps of how to copy visible rows in Excel:

• Select the rows you want to copy.
• Press on the “Home” tab.                                                                                       
• In the “Clipboard” group, click on the “Copy” button.

• Select the cell where you want to paste the copied rows.
• Click on the “Home” tab.
• In the “Clipboard” group, click on the “Paste” button.

The visible rows will be copied and pasted to the selected cell.

Best tips for copying visible rows in Excel Sheet are as below:

• You can also use the shortcut key Ctrl+C to copy and Ctrl+V to paste.
• You can copy multiple rows at once by selecting them with the mouse or by pressing Ctrl while clicking on the row headers.
• You can copy all visible rows in a worksheet by clicking on the “Home” tab and then clicking on “Find & Select” > “Go To Special“. In the “Go To Special” dialog box, select the “Visible cells only” radio button and then click on “OK“. All visible cells in the worksheet will be selected.  Then you can copy the selected cells by pressing Ctrl+C and paste them to the desired location by pressing Ctrl+V.