See all How-To Articles

How to Hide Rows Based on Cell Value in Excel & Google Sheets

In this tutorial, you will learn how to hide rows based on values they contain in Excel and Google Sheets.

 

number filters excel

 

Hide Rows Based on the Value of a Cell

In Excel, you can use filters to hide rows in a table. To exclude cells with certain values, set an appropriate filter. To see how this works, let’s start with the data range pictured below:

 

hide rows based on cell value initial data

 

Hide Cells Less Than

To hide rows in this table with Total Sales (Column G) less than $400, filter Column G and display values greater than 400.

1. First, turn on filtering. Click on any cell in the data range (B2:G16) and in the Ribbon, go to Home > Sort & Filter > Filter.

 

turn on filter excel

 

2. Click on the filter button next to Total Sales (cell G2), go to Number Filters, and choose Greater Than…

 

filter greater than

 

3. In the filter pop-up window, enter 400 and click OK.

 

filter greater than 2

 

As a result, rows with values less than 400 in Column G are hidden.

 

filter greater than 3

 

Hide Cells Equal To

Now let’s use the initial data range (with all filters cleared) to hide rows with the SKU (Column B) 1234. Use a number filter again to filter and display values in Column B that don’t equal 1234.

1. Click on the filter button next to SKU (cell B2), go to Number Filters, and choose Does Not Equal…

 

filter does not equal to

 

2. In the pop-up window, enter 1234 and click OK.

 

filter does not equal to 2

 

Now, rows with the SKU 1234 (3 and 10) are hidden while all other rows are displayed.

 

filter does not equal to 3

 

Hide a Number of Rows Based on Values

You can also filter the top n rows by Total Sales (Column G) and hide all rows with Total Sales not in the top 5. Go back to the initial, unfiltered data range and follow these steps:

1. Click on the filter button next to Total Sales (cell G2), go to Number Filters, and choose Top 10…

 

filter top 10 1

 

2. In the pop-up window, enter 10, and click OK.

 

filter top 10 2

 

Now, the rows with the top 5 values of Total Sales are displayed, and all rows where values are not in the top 5 are hidden.

 

filter top 10 3

 

Other options for number filters include:

  • equals,
  • less than / less than or equal to,
  • between,
  • below/above average, and
  • custom filter.

NOTE: You can also use VBA code to filter numbers and hide rows based on a cell value.

Hide Rows Based on the Value of a Cell in Google Sheets

You can hide rows based on cell value in Google Sheets in almost the same way. Let’s use the same example to filter Total Sales (Column G) and display values greater than $400. Doing this hides rows where the Total Sales value is less than $400.

1. To create a filter, click anywhere in the data range (B2:G16) and in the Menu, go to Data > Create a filter.

 

google sheets create a filter

 

2. Click on the filter button next to Total Sales (cell G2) and go to Filter by condition. In the drop-down list, choose Greater than.

 

google sheets filter greater than

 

3. Enter 400 in the text box and click OK.

 

google sheets filter greater than 2

 

As a result, rows with Total Sales less than $400 are hidden; only those with a value greater than $400 are displayed.

 

google sheets filter greater than 3

 

Like Excel, Google Sheets has more options to filter by number:

  • is equal to / not equal to,
  • greater than or equal to,
  • less than / less than or equal to,
  • is between / is not between, and
  • custom formula.