How to Hide Rows in Excel (6 Effective Methods)

Often we need to hide inessential rows to make the necessary rows worthy while using Microsoft Excel. Though you will find several methods scattered to do this task if you search. In this article, I’ll demonstrate 6 effective methods to hide rows in Excel sequentially with proper explanation. So, you may adjust the methods based on your requirement.


Download Practice Workbook


6 Effective Methods to Hide Rows in Excel 

In the first two methods, I’ll discuss the process of selecting rows along with the methods. Then, I’ll show you an effective shortcut and the way of creating a group of rows with a minus sign. Lastly, you’ll see the application of the VBA code in the last two methods.

Let’s dive into the methods.


1. Hiding Rows Using the Format Menu

You can use the Format menu to hide the rows quickly.

Before doing that you need to know the process of selecting a single row or multiple rows in Excel.

⧪ Initially, click on the row number of a row at the left side of the working sheet if you want to select the entire row.

⧪ Then drag down the cursor or hold the SHIFT key to select contiguous multiple rows.

⧪ But if you want to select non-contiguous multiple rows within a working sheet, keep pressing the CTRL key before selecting the rows.

The Way of Selecting Rows

Right now, if you want to hide rows 4-5 and rows 8-10, just follow the below process.

➯ At first, select the rows using the above-discussed process.

➯ Later go to the Format menu from the Cells ribbon in the Home tab.

➯ And, select Hide Rows from the Hide & Unhide option under the Visibility section.

Using the Format Menu

Subsequently, you’ll get the following where the selected rows are hidden.

Using the Format Menu

But if you need, you may visit the Format Menu to Unhide Rows article.

Read More: How to Unhide Rows in Excel (8 Quick Ways)


2. Hiding Rows by Right-Clicking (Using the Context Menu)

In this method, you’ll know how to hide all rows of a working sheet. Right before that, you need to explore the process of selecting all rows within a sheet.

⧪ Simply click on the upper-left small green colored triangle.

⧪ Else, press CTRL + A to select all rows as well as columns inside a working sheet.

The Way of Selecting All Rows

After selecting the rows, right-click on the selected rows (left side of the sheet where row number displays) and pick the Hide option from the context menu.

Right-Clicking the Context Menu

Immediately, you’ll get the following blank sheet where no rows are visible.

Right-Clicking the Context Menu

However, you may use the Context Menu to Unhide Rows again if you need.

Read More: How to Hide Rows Based on Cell Value in Excel (5 Methods)


3. Hiding Rows Using the Keyboard Shortcut

More importantly, you may utilize the keyboard shortcut for hiding rows fast

⧪ The shortcut for Windows: CTRL + 9

⧪ The shortcut for Mac: ^ + 9

To use the above keyboard shortcut you have to select the rows first.

Using the Keyboard Shortcut

After pressing the shortcut, you’ll get the following output.

Using the Keyboard Shortcut

Luckily, there is also another shortcut for unhiding rows.

Read More: Shortcut to Unhide Rows in Excel (3 Different Methods)


Similar Readings


4. How to Hide a Group of Rows with Minus Sign

Sometimes we have to work with a group of rows and need to hide grouped rows. Fortunately, you can do this by clicking on the minus sign only. The sequential process might be as follows.

➯ Primarily, select the rows that you want to hide (here I selected rows 7-11).

➯ Then go to the Data tab > Outline ribbon and click on the Group option.

Hiding a Group of Rows with Minus Sign in Excel

➯ Meanwhile, a Minus (-) sign will be available at the end of row 11.

➯ Now, click on the sign.

Hidie a Group of Rows with Minus Sign in Excel

Shortly, you’ll get the following output where the selected rows 7-11 are hidden.

Hide a Group of Rows with Minus Sign in Excel


5. Create a Button to Hide Rows When Clicked in Excel 

At this time, you’re going to witness an amazing tool by creating a button to hide rows.

If you click on the button, it will automatically hide your selected rows.

Let’s explore it in a step-by-step process.

➯ At the outset, go to Developer tab > Controls ribbon > Insert option.

➯ Later, pick the Toggle Button from the ActiveX Controls.

Create a Button to Hide Rows When Clicked in Excel

➯ After doing that, draw a sizeable box like the following one.

Create a Button to Hide Rows When Clicked in Excel

➯ Now, click twice on the created box (double-click). A code window will be opened.

➯ And copy the following code.

Private Sub ToggleButton1_Click()
Dim xRow As String

xRow = "4:6"


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

Hide Rows in Excel When Click on a Button

In the above code, I declare xRow as a String type. Then I set the value of xRow as “4:6” as I want to hide rows 4-6. Eventually, I use the ToogleButton.Value property to specify the object. Later, Application.ActiveSheet property is used to extract the value of the running sheet. Right then, Rows(xRow).Hidden is set to True to hide the selected rows, and the same condition (except the Hidden is set to False) was applied to the rest to unhide the rows.

➯ Immediately, you need to off the Design Mode (just click once) which is located at the Controls ribbon of the Developer mode.

Create a Button to Hide Rows When Clicked in Excel

➯ If you do the mentioned process, you’ll see a box showing “Hiding the Selected Rows” as shown in the following screenshot.

➯ Now, click on the box exactly.

Create a Button to Hide Rows When Clicked in Excel

Finally, you’ll get such output that the selected rows 4-6 are no more visible.

Create a Button to Hide Rows When Clicked in Excel

Happily, if you click on the box displaying “Unhide Row”, you’ll get the hidden rows again.

Related Content: [Fix]: Unable to Unhide Rows in Excel (4 Solutions)


6. Hide Rows Applying VBA Code

Last but not the least, if you want to apply the VBA code to hide rows, you can do that effectively as the code is quite simple.

To apply the code, you need to insert a module.

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

⇰ Secondly, go to Insert > Module.

How to Insert VBA Code

Now, we can use the VBA code in three ways.


6.1. Hiding a Single Row Applying VBA Code

Perhaps, you need to hide a single row e.g. row 4 as shown in the following picture.

Hiding a single Row using VBA

Just copy the following code.

Sub Hide_Singe_Row_VBA()

Rows("4:4").Hidden = True

End Sub

Hiding a single Row using VBA

In the above code, Rows (“4:4”) is used as I want to hide row 4 and I set Hidden to True to hide the selected rows.

Next, run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the following output.

Hiding a single Row using VBA

Read More: VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)


6.2. Hiding Multiple Adjacent Rows Applying VBA Code

When you have to hide multiple contiguous rows e.g. rows 7-10.

Hiding Multiple Adjacent Rows Using VBA

You may copy the following code into the created module.

Sub Hide_Adjacent_Rows_VBA()

Rows("7:10").Hidden = True

End Sub

Hiding Multiple Adjacent Rows Using VBA

Thereafter, run the code and the output will be like the following.

Hiding Multiple Adjacent Rows Using VBA

Read More: VBA to Hide Rows Based on Cell Value in Excel (14 Examples)


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.

Hiding Multiple Non-Adjacent Rows Using VBA

Then copy the following code.

Sub Hide_NonAdjacent_Rows_VBA()

Rows("5:6").Hidden = True
Rows("9:11").Hidden = True

End Sub

Hiding Multiple Non-Adjacent Rows Using VBA

Finally, run the code. The output will be as follows.

Hiding Multiple Non-Adjacent Rows Using VBA

Also, you may use the VBA code to unhide rows.

Read More: How to Unhide Multiple Rows in Excel (9 Methods)


Conclusion

In short, you may hide rows in excel utilizing the above methods. Hence, I hope that the article might be highly beneficial for you. However, if you have any queries and suggestions, share them below in the comments section.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo