Formula to Hide Rows in Excel (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, while we want to view as many rows or information as possible, at the same time we want to hide some of the unimportant rows. Maybe because some of them become redundant, some have unnecessary value, some have blank cells, etc. How we can use the formula or methods to hide rows in Excel are discussed here in detail.

Table of Contents hide

Download Practice Workbook

7 Easy Ways to Find and Hide Rows in Excel

1. Using Ribbon to Hide Rows

2. Utilizing Context Menu

3. Applying Keyboard Shortcut

4. Hide Blank Rows

5. Hide Rows Based on a Cell Value

5.1 Use of Formula and Filtering

5.2 Using Filter Command

6. Conditional Formatting to Hide Rows

7. Embedding VBA Macro to Hide Rows in Excel

Conclusion

Download Practice Workbook

Download this practice workbook below.

Formula to Hide Rows.xlsm

7 Easy Ways to Find and Hide Rows in Excel

For demonstration purposes, we are going to use the below dataset. In this dataset Date of purchase, region, product and quantity are denoted from cell B4 to cell E4. If you look closely then you will notice that the rows 7,10, 14, and 15 are hidden. How we managed to hide those rows are discussed here with adequate examples.

1. Using Ribbon to Hide Rows

Hiding rows using the Ribbon is easier compared to other alternatives.

Steps

First, select all the rows one by one that needs to be hidden by pressing Ctrl, in this case, it is row 8 and 13.
And then from the Home tab, go to Format in the Cells group.
After clicking Format, a new options menu will open, from that menu choose Hide and Unhide and then click Hide Rows.

After clicking Hide Rows you will notice rows 8 and 13 are now not visible.

2. Utilizing Context Menu

The context menu is another simple method to hide rows in Excel efficiently.

Steps

At first, you need to select the rows that need to be hidden and right-click on your mouse. After that, a context menu will open.
From the context menu, select Hide.

After clicking Hide you will notice that row 6 is now not visible.

3. Applying Keyboard Shortcut

Applying a simple keyboard shortcut can drastically reduce the time required for hiding the rows in Excel.

Steps

In the beginning, select the rows that need to be hidden, in this case, it is rows 7, 10, 14, 15, and 18.
After selecting the rows, press ‘Ctrl+9’, which is the shortcut for hiding rows in Excel.

After pressing the keyboard shortcut, you will notice rows 7, 10, 14, 15, and 18 are now not visible.

Similar Readings

4. Hide Blank Rows

Using this method, you can select all blank rows at once and able to hide them.

Steps

Select the range of cells that contains the blank cells, in this case, the range is cell B5 to cell E18.
Then, from the Home tab, go to Find & Select from the Editing group.
After selecting Find & Select, click on Go To Special.

A new window will open, from that window, select Blanks. And then click on OK.

After clicking OK, you will notice that all the blank rows in the range of cells B5:E18 are now selected.

After selecting the blank rows, press ‘Ctrl+9’.
Then you will notice that rows 9 and 14 are now not visible.

5. Hide Rows Based on a Cell Value

We can apply conditions on data and then apply filters upon them to hide rows. Filters can be applied standalone or with functions. Both of them are described below.

5.1 Use of Formula and Filtering

We will use the IF function in combination with the Filter command to hide rows according to our needs.

Steps

In the beginning select cell F5, enter the following formula:

=IF(E9<50,"Hide Row",E9)

After entering the formula, you will notice F5 will show Hide Row as it is less than 50.

Drag the Fill Handle icon in the corner of cell C5 to cell C18, and all the values that are below 50 now will show Hide Row in the range of cells F5:F18.
Select the table and go to Data > Filter.

Next, a drop-down icon will appear on every column header. From the Region column. Click the drop-down icon.
A new context menu will appear. From that menu, select all the other options except Hide Rows and click OK.

After clicking OK, you will see all rows with the Hide Rows values are now hidden by using the formula embedded with the IF function in Excel.

5.2 Using Filter Command

Using the Filter command is another straightforward approach to hiding rows according to conditions.

Steps

At first, select the range of cells B5:E18 and go to Data > Filter.

After that, there will be drop-down icons on every column header.
Click on the filter icon on the Region column.

After clicking the drop-down Icon, a new options menu will appear, from that menu keep only West Click OK after this.

After clicking OK, you will notice that the only entries from the West in the Region column showing, the remaining columns have become hidden.

6. Conditional Formatting to Hide Rows

Conditional Formatting is a great way to hide rows in Excel as it provides ample flexibility compared to the others.

Steps

At first, select the range of cells B5:E18 and then go to the Home tab. Select Conditional Formatting > Formatting.
After that, click on New Rule.

A new window will appear. In that window, select Use a Formula to determine which cell to format in the Select a Rule Type.
And then in the Edit the Rule Description box type:

=$C5="East"

Then click on Format.

After clicking the Format, another window will open, then from the Number tab go to Custom in the Category Then type “;;;” in the type field. Click OK after that.

Clicking OK will return you to the previous window. Click OK in that window.

After clicking OK, you will notice that the rows that contain East in the Region column are now not visible.

Note:

No rows in this method are not visible, all of the text in rows now actually turned white, that’s why it gives the impression of being hidden.

7. Embedding VBA Macro to Hide Rows in Excel

Using a simple VBA Macro can drastically reduce the time to replace line break with your desired character.

Steps

First, go to the Developer tab, then click Visual Basic.

Then click Insert > Module.

In the module window, enter the following code:

Sub Hide_rows()
Dim hr As Range
Set hr = Application.Range("B10:B12")
hr.EntireRow.Hidden = True
End Sub

Note:

Here, we will hide rows from 10 to 12. To hide rows as your demand, you need to edit the range of cells.

Then close the Module window.
After that, go to the View tab > Macros(Double click)

After double-clicking Macros, select the macro that you created just now from the new window. The name here is Hide_rows. Then click Run.

After clicking Run, you will notice rows from 10 to 12 are not visible.

In this way, you can use the formula to hide rows in Excel with ease.

Conclusion

To sum it up, the problem formula to hide rows in Excel is resolved here in 7 different ways. Starting from using the menu options, context menu, Short cut, selecting a blank row, continue to using conditional formatting then finally ended up using VBA Macros. Among all of the methods used here, using the menu and context option is the easier to understand and simple one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enabled workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Formula to Hide Rows in Excel (7 Methods)

Download Practice Workbook