Hide Excel Rows Based on Cell Value (3 simple ways)

You are currently viewing Hide Excel Rows Based on Cell Value (3 simple ways)
Merci de noter cette article

In this tutorial, we show you easy ways to hide Excel rows based on the value of a cell.

It can be hard to work with big datasets that have thousands of rows.

In these situations, it can be like looking for a needle in a haystack to find data that fits certain criteria. But we can hide rows in Excel based on the value of a cell by using the filter, conditional formatting, formulas, and VBA macros.

Here are the ways mentioned above to hide Excel rows based on certain conditions. You can use whatever method is easiest for you.

Three simple ways to hide rows in Excel based on the value of a cell

You might have a data range and want to hide rows based on the value of a column. For instance, hide the row if the value of the column cell is « 0« , as shown in the screenshot below:

Hide Excel Rows based on cell value

join us on youtube

Method 1: Hide Excel rows based on a condition with a filter

Using filters in Excel is a really easy way to hide rows. Here are the steps you must take:

  1. To start, select your entire dataset.
  2. Next, go to the « Data » tab and click the « Filter » button. It is in the section « Sort and filter« .
hide excel rows based on cell value
  1. Each cell in the header row should now have a small arrow button. In fact, the purpose of these buttons is to help you filter your cells. So you can click any button to choose a filter for the column it goes to.
hide excel rows

For example, in our case, we want to hide the rows where the quantity value = 0. Follow these steps to get this done:

  1. So, click on the arrow next to the word « Quantity« .
  2. Then, remove the check mark from the box next to « 0« .
  3. Last, click « OK » to put the changes into effect.
hide excel rows based on condition
  1. You should now only see rows with numbers greater than 0. Now, you should be able to hide all other rows.
hide excel rows based on condition

Note: To make the method easier to understand, we show it with a few rows and columns. You can use this method for as many columns as you like. So, depending on your data type, you can hide as many rows as possible.

Method 2: Excel Hide Rows With Conditional Formatting

The previous methods hide the entire row, making it look like it doesn’t exist. But this method only hides the line’s content.

In reality, conditional formatting only changes the format of a cell or a group of cells. To put it another way, it doesn’t change the cell itself. But Excel’s conditional formatting will let you hide row contents based on the value of a cell.

With conditional formatting, you can hide rows in Excel based on certain conditions. Here are the steps:

  1. First, choose your data set, but leave out the headers.
  2. Click on the « Home » tab next. Then, in the « Styles » group, click « Conditional Formatting« .
hide excel rows based on cell value with conditionnal formating
  1. Then, when the menu comes up, click « New rule« .
  1. The « New Formatting Rule » window shows up as a result. Choose « Use a formula to determine which cells to format » in the « Select a Rule type » section.
  1. Then, under « Edit the rule Description« , type the following:
=$C2=0
  1. Now, click the « Format » button. The « Format Cells » window will open.
  1. Open the « Number » tab in the « Format Cells » window. Then, under « Category » choose « Custom » and type 3 semicolons (i.e. ;;;) under « Type« .
  2. The last step is to click « OK » to use the format.
  3. When you click « OK, » Excel takes you back to the « New Formatting Rule » box. Click « OK » once more.

If you do steps 1 through 9, everything in column C that matches 0 will be hidden.

You can change the formula for conditional formatting to make it work for you. You can change the value 0 to any other value, for example.

Method 3: Hide rows based on the value of a cell with Excel VBA

For the third method, you need to know how to code. This method is for VBA users who are familiar with macros and a little bit of code. In fact, VBA gives you a lot more options and freedom to change your data so it works exactly the way you want it to.

In this example, we are working with the following data set:

Excel VBA: Code for a macro to hide rows

The code below will only show rows that have information about the « Otmane » provider and hide all other rows:

Sub HideRow()
LineStart = 2
LineEnd = 16
ColumnNumber = 2
For i = LineStart To LineEnd
If Cells(i, ColumnNumber).Value <> "Otmane" Then
Cells(i, ColumnNumber).EntireRow.Hidden = True
Else
Cells(i, ColumnNumber).EntireRow.Hidden = False
End If
Next i
End Sub

This macro goes through each cell in column B and hides rows that don’t have the  » Otmane  » value. So it will look at each cell in rows 2 through 16 and change the « Hidden » attribute of the rows you want to hide.
Copy and paste the code above into the VBA editor to use it. This is how:

  1. First, click on « Developer » and then « Visual Basic« . If you don’t have the « Developer » tab, press « Alt + F11« .
  1. Because of this, the VBA editor window opens. On the left, in the « Project Explorer« , you can see all of your files and folders. Click « View » and then « Project Explorer » if you don’t see the Project Explorer.
  1. Next, make sure « ThisWorkbook » is chosen in the VBA project that has the same name as your Excel workbook.
  1. After that, click « Insert » and then « Module« . You should see a new window for the module pop-up.
  1. Now, copy and paste the lines of code above into the new module window. See the picture below:
  1. Close the window for VBA.

Now you can use your macro.

In our example, we want to hide the rows where column 2 doesn’t have the value « Otmane« . But you can change the value of ColumnNumber to the number of the column that has the values you want.

Excel VBA: Running Conditionally Hide Rows Macro

You only have to run your macro when you want to use it. This is how:

  1. Go to the « Developer » tab to get started. Follow these steps to show the « Developer » ribbon if you don’t see it:
    1. Right-click on the ribbon in Excel.
    2. Then, from the menu that pops up, choose « Customize the Ribbon« .
    3. Then, in the « Excel Options » window that opens, check the « Developer » option under the « Main tabs« .
    4. Last, click « OK« .
  1. Next, in the « Code » group, click the « Macros » button.
Exécuter une macro Excel
  1. This will open the « Macro » window, where you can see the names of all the macros you’ve made so far.
Boite de dialogue Macro Excel
  1. Choose the macro that we just made. Its name will be shown as « HideRows« .
  2. Click the « Run » button to finish.
Macro pour masquer des lignes Excel - hide excel rows based on cell value

Because of this, all rows where the value in column B is not « Otmane » will be hidden.

Excel VBA Hide Excel Rows: Code Explained

Here’s how the code above works, line by line:

  • In line 1, we said what the function’s name is.
Sub HideRow()
  • In rows 2, 3, and 4, we set up variables to hold the first row of the dataset, the last row of the dataset, and the index of the criteria column, respectively. We set the start line to 2 so that headers would not be included. Then, for the column index, we set it to 2 because the « Supplier » column is in the second spot.
LineStart = 2
LineEnd = 16
ColumnNumber = 2
  • In rows 5 through 11, we try to loop through each cell in column « 2 » (or column B) of the active worksheet. If a cell doesn’t have the value « Otmane, » we set the « Hidden » property of the whole row to « True. » In other words, it tells Excel that the whole row should be hidden.
For i = LineStart To LineEnd
If Cells(i, ColumnNumber).Value <> "Otmane" Then
Cells(i, ColumnNumber).EntireRow.Hidden = True
Else
Cells(i, ColumnNumber).EntireRow.Hidden = False
End If
Next i
  • Line 12 just shows that the HideRow function is over.
End Sub

Show Rows based on the value of an Excel cell

We’ve seen how to hide rows by using VBA. But what if we want to see rows that have been hidden?

It’s not hard to do this. You only need to change one small thing about the last function. The code for the new macro is as follows:

Sub ShowRows()
LineStart = 2
LineEnd = 16
ColumnNumber = 2
For i = LineStart To LineEnd
Cells(i, ColumnNumber).EntireRow.Hidden = False
Next i
End Sub

Here, we change all of the rows’ « Hidden » properties to « False« . So, no matter what the value is, all rows are shown.

This macro can be run in the exact same way as HideConditionLine.

Show excel rows based on cell value

Conclusion

In this tutorial, we showed you how to hide rows based on the value of a cell by using filters and Excel VBA.

We hope that we were able to explain the idea behind the code so that you can change it and use it in your own programs.

Other tutorials you may like

How to Show and Hide Gridlines in Excel.