Formula to Hide Rows in Excel (7 Methods)

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.


Download Practice Workbook

Download this practice workbook below.


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.

Formula to hide rows in excel


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.

Using Ribbon to Find and Hide Rows

Read More: How to Hide Rows in Excel (6 Effective Methods)


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.

 Implying Context Menu to Find and Hide Rows

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

 Implying Context Menu to find and Hide Rows


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.

Formula to hide rows in excel

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


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.

Read More: How to Hide Blank Rows in Excel VBA (4 Useful Methods)


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.

Hide Rows Based on a Cell Value

  • 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.

Hide Rows Based on a Cell Value


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.

Hide Rows by using filter tool

  • 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.

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


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.

Conditional Formatting to Hide Rows

  • 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.

Conditional Formatting to Hide Rows

  • 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.

Conditional Formatting to Hide Rows

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

Conditional Formatting to Hide Rows

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

Conditional Formatting to Hide Rows

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.

Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel


 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.

Embedding VBA Macro to Hide Row

  • 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.

Utilizing VBA Macro

  • 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.

Read More: VBA to Hide Rows in Excel (14 Methods)


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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo