Hide Rows in Excel Using Shortcuts, Commands and VBA

Method 1 – Use Format Menu to Hide Rows

• Click on the row number of a row >> Drag down the cursor or hold the SHIFT key to select contiguous multiple rows.
• To hide rows 10-12, select rows 10,11,12 >> Go to the Home tab >> Expand the Cells command >> Expand the Format command >>Click on the Hide & Unhide option >> Select the Hide Rows option.

• You can hide the 10th, 11th, and 12th rows.

Method 2 – Hide Rows Using the Context Menu

• Select rows 10 to 12 >> Right-click on the Mouse to get the Context menu >> Click the hide option from the Context menu list.

• There are no rows 10th, 11th, and 12th.

Method 3 – Apply Excel Keyboard Shortcut to Hide Rows

• To use the keyboard shortcut, you have to select the rows first >> Press CTRL + 9 keys.

• Get the output hiding rows.

Note: Shortcut for Windows: CTRL + 9 and for Mac: ^ + 9.

Method 4 – Hide Rows via Excel Group Command

• Select rows (10-12) >> Go to the Data tab >> Expand the Outline command >> Click on the Group command.

• A Minus (-) sign will be available at the end of the 12th row >>Click on the Minus sign.

• Get the following output where the selected rows 10-12 are hidden.

Method 5 – Create a Toggle Button to Hide Rows

• Go to Developer tab >> Then Insert button >> Pick the Toggle Button from the ActiveX Controls section.
• Locate the Toggle button and re-configure with the VBA code by double-clicking the button.

• Enter the following VBA code in the worksheet and hit Run.

Private Sub ToggleButton1_Click()
Dim xRow As String
xRow = "10:12"
If ToggleButton1.Value Then
    Application.ActiveSheet.Rows(xRow).Hidden = True
    ToggleButton1.Caption = "Unhide Row"
Else
    Application.ActiveSheet.Rows(xRow).Hidden = False
    ToggleButton1.Caption = "Hiding the Selected Rows"
End If
End Sub

• Rows 10-12 are no longer visible once we click on the Hide Rows button.

• You’ll get the hidden rows again by clicking the Unhide Rows button.

Method 6 – Hide Rows Applying Excel VBA

• Open a module by clicking Developer > Visual Basic.

Go to Insert > Module.

We will show you three applications with VBA Macro.

6.1. Hide a Single Row

As shown in the following picture, it would be best to hide a single row, e.g., row 10. insert the following code in the VBA module and press the F5 button or hit the Run icon.

Sub Hide_Singe_Row_VBA()
Rows("10").Hidden = True
End Sub

6.2. Hiding Multiple Adjacent Rows Applying VBA Code

In case we must hide multiple contiguous rows, e.g., rows 10-12. By inserting the following VBA code into the created module and clicking on the Run icon or pressing the F5 button, we obtain the result, as you can see in the image.

Sub Hide_Adjacent_Rows_VBA()
Rows("10:12").Hidden = True
End Sub

6.3. Hiding Multiple Non-Adjacent Rows Applying VBA Code

If you want to hide multiple rows but they are not contiguous, e.g., rows 5-6 & rows 9-11, you can use this method by inserting the following code in the VBA module and pressing the F5 button or hitting the Run icon.

Sub Hide_NonAdjacent_Rows_VBA()
Rows("5:6").Hidden = True
Rows("9:12").Hidden = True
End Sub

How to Hide Rows Containing Blank Cells in Excel

Method 1 – Apply Filter Feature

• To filter the B4:E14 range, select the B4:E4 range >> Go to the Data menu >> Expand the Sort & Filter command >> click on the Filter command.

• Expand the drop-down box >> De-select the Blanks option from the list.

• Rows 8th and 12th containing blanks are hidden.

Method 2 – Use the Find and Replace Tool

• Select the B4:E14 range >> Press Ctrl + F to open the Find and Replace dialog.
• From the Find and Replace dialog, go to the Find tab >> Leave blank the Find what field >> Click the Find All button.
• Thus 2 cells having blanks appears.

• Select the blanks D8 and D12 >> Go to the Home tab >> Expand the Cells command >> Expand the Format command >> Click on the Hide & Unhide option >> Select the Hide Rows option.

• Obtain the result hiding the rows containing blank cells.

Method 3 – Apply Go To Special Tool

• Select the B4:E14 range >> Press the Ctrl + G to open the Go To dialog >> Click the Special button.

• Go To Special dialog appears. Choose Blanks >> Select the OK button.

• Select the blanks D8 and D12 >> Go to the Home tab >> Expand the Cells command >> Expand the Format command >> Click the Hide & Unhide option >> Select the Hide Rows option.

• Obtain the outcome by hiding the blank cells.

How to Unhide Rows in Excel

Method 1 – Use Format Command

• Select the B4:D14 range >> Go to the Home tab >> Expand the Cells command >> Expand the Format command >> Click the Hide & Unhide option >> Select the Unhide Rows option.

• Obtain unhidden rows.

Method 2 – Use Context Menu

• Select rows 9 to 13 >> Right-Click the mouse to get the Context menu >> Click the Unhide option from the Context menu list.

• See in the image we obtain unhidden rows.

Method 3 – Apply Keyboard Shortcut

• Select rows 9 to 13 >> Press the Ctrl + Shift + 9 keys to unhide the rows.

• Achieve the unhidden rows, as you can observe in the image.

Frequently Asked Questions (FAQs)

Q1. How do I hide specific rows in Excel?

Using the Find and Replace dialog, you can find the specific data from the data range and hide the rows. From the Find and Replace dialog, go to the Find tab >> Insert text/numbers in the Find what field >> Click the Find All button. Specific cells will be shown >> Then, press Ctrl + 9 to hide rows.

Q2. What is the shortcut to hide rows or columns in Excel?

Press Shift + 9 to hide rows, whereas Shift + 0 for columns.

Q3. How do you hide rows based on conditions in Excel?

Users can automatically hide rows based on conditions by using the Excel VBA code with suitable conditions. Using Filter or Find tool, one can manually figure out cells based on condition and hide rows.

Q4. Can Conditional Formatting hide rows in Excel?

The Conditional Formatting can’t hide or change height rows. Rather it allows specific conditions to highlight cells. To hide rows based on condition, you require to use a VBA code.

Hide Rows in Excel: Knowledge Hub

• Rows Not Showing but Not Hidden
• Unhide All Rows Not Working
• How to Hide the Same Rows Across Multiple Excel Worksheets
• How to Automatically Hide Rows with Zero Values in Excel
• Shortcut to Unhide Rows in Excel

Download Practice Workbook

<< Go Back to Rows in Excel | Learn Excel