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.
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.
Subsequently, you’ll get the following where the selected rows are hidden.
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.
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.
Immediately, you’ll get the following blank sheet where no rows are visible.
However, you may use the Context Menu to Unhide Rows again if you need.
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.
After pressing the shortcut, you’ll get the following output.
Luckily, there is also another shortcut for unhiding rows.
- Excel VBA: Unhide All Rows in Excel (5 Practical Examples)
- How to Hide Blank Rows in Excel VBA (4 Useful Methods)
- How to Hide and Unhide Rows in Excel (6 Easiest Ways)
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.
➯ Meanwhile, a Minus (-) sign will be available at the end of row 11.
➯ Now, click on the sign.
Shortly, you’ll get the following output where the selected rows 7-11 are hidden.
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.
➯ After doing that, draw a sizeable box like the following one.
➯ 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
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.
➯ 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.
Finally, you’ll get such output that the selected rows 4-6 are no more visible.
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.
⇰ Secondly, go to Insert > Module.
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.
Just copy the following code.
Sub Hide_Singe_Row_VBA() Rows("4:4").Hidden = True End Sub
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.
6.2. Hiding Multiple Adjacent Rows Applying VBA Code
When you have to hide multiple contiguous rows e.g. rows 7-10.
You may copy the following code into the created module.
Sub Hide_Adjacent_Rows_VBA() Rows("7:10").Hidden = True End Sub
Thereafter, run the code and the output will be like the following.
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.
Then copy the following code.
Sub Hide_NonAdjacent_Rows_VBA() Rows("5:6").Hidden = True Rows("9:11").Hidden = True End Sub
Finally, run the code. The output will be as follows.
Also, you may use the VBA code to unhide rows.
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.
- [Fixed!] Excel Rows Not Showing but Not Hidden (3 Reasons & Solutions)
- Hidden Rows in Excel: How to Unhide or Delete Them?
- Hide Duplicate Rows Based on One Column in Excel (4 Methods)
- Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)
- Hide Rows Based on Cell Value with Conditional Formatting in Excel
- Unhide All Rows Not Working in Excel (5 Issues & Solutions)