Skip to main content

How to hide rows based on cell value in Excel?

Author: Sun Last Modified: 2020-04-26

Supposing you have a range of data, and you want to hide the rows based on a column value, for instance, if the cell value of the column is smaller than 3000, then hide the row of the cell as below screenshot shown.


In Excel, you can use the Filter function to filter and hide the rows based on cell value.

1. Select the data you want to filter out, and click Data > Filter. See screenshot:

doc hide rows based on value 2

2. Then click on the down arrow to display the filter drop down list, and click Number Filters (or Text Filters) > Greater Than (you can choose other criterion you need from the submenu). See screenshot:

doc hide rows based on value 3

3. In the popping dialog, type the criterion into the textbox next to is greater than. See screenshot:

doc hide rows based on value 4

4. Click OK. Now the only the data greater than 3000 is shown, and the rows whose data is smaller than 3000 are hidden.

doc hide rows based on value 5


Moreover, if you are interested in VBA code, here I can introduce a VBA code to hide rows based on cell value.

1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.

2. Then click Insert > Module to open a new Module window, and paste below VBA code into it.

VBA: Hide rows based on cell value.

Sub HideRow()
'Updateby20150618
Dim Rng As Range
Dim WorkRng As Range
Dim xNumber As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNumber = Application.InputBox("Number", xTitleId, "", Type:=1)
For Each Rng In WorkRng
    Rng.EntireRow.Hidden = Rng.Value < xNumber
Next
End Sub

3. Then press F5 key to run the VBA, then select the data range you want to hide rows into the popping dialog (excluding headers). See screenshot:

doc hide rows based on value 6

4. Click OK, and type the criterion number into the second dialog. See screenshot:

doc hide rows based on value 7

5. Click OK. Now the rows whose data is smaller than 3000 are hidden.

Tip: If you want to hide rows which are greater than 3000, just change Rng.EntireRow.Hidden = Rng.Value < xNumber to Rng.EntireRow.Hidden = Rng.Value > xNumber, or if you want to hide rows whose data is equal to 3000, change to Rng.EntireRow.Hidden = Rng.Value = xNumber.


Quickly select cells based on criteria in Excel

With Kutools for Excel's Select Specific Cells, you can select cells based on one or two criteria once time. 
doc select specific cells
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

IF you do not like to enable Filter function, neither to VBA, here I introduce you a handy tool – Select Specific Cells of Kutools for Excel to quickly select entire rows based on cell value, then you can hide them.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After free installing Kutools for Excel, please do as below:

Tip. If you want to have a free trial of the Extract Text function, please go to free try Kutools for Excel first, and then go to apply the operation according above steps.

1. Select the data range and click Kutools > Select > Select Specific Cells. See screenshot:

doc hide rows based on value 14

2. In the Select Specific Cells dialog, check Entire row under Selection type section, then select the criterion you need from Specific type list, then type the number or text into the textbox. See screenshot:

doc hide rows based on value 9

3. Click Ok > OK to close dialogs. Now the rows whose data is smaller than 3000 are selected, and you just need to right click at the row header to show the context menu, and click Hide. See screenshot:
doc hide rows based on value 10

Now the rows whose data is smaller than 3000 are hidden.

doc hide rows based on value 11

Tip:

1. If you want to hide rows which including a certain value, you can select Contains and type the certain value into the text box.

doc hide rows based on value 12

2. If you want to hide rows which including a value greater than but less than values, you can select Greater than and Less than, then type the values into the two box, and check And.

doc hide rows based on value 13

With Kutools for Excel’s Select Specific Cells utility, you can identify specific cells’ location, select entire rows or columns based on cell value and so on. Click here to know more about this utility.

Kutools for Excel: 300+ functions you must have in Excel, 30-day free trial from here.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Yes, "Entire Row" is selected.
This comment was minimized by the moderator on the site
All rows remain selected without using Kutools. As soon as I use Kutools and select header row, all other rows but the header row de-select.
This comment was minimized by the moderator on the site
Are you sure check the Entire row option in the dialog? If you check cell option, the selected cells will be unselected when you right click at one row.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-select-specific-cell-1.png
This comment was minimized by the moderator on the site
Hi - I'm using a newer Lenovo PC with Windows 10 Professional and Microsoft 365 Apps for business. Excel Version 2210 (Build 15726.20202 Click-to-Run).
This comment was minimized by the moderator on the site
I have tried the operation in the same version with you, there is no any problem. Could you try to manually select several rows (without using Kutools for Excel), then right click at one row header, see all rows are remained selecting?
This comment was minimized by the moderator on the site
I have done exactly what you've demonstrated. However, when I right-click on the row header, all other rows become de-selected and only that one row remains selected to be hidden or deleted. No matter what I do, the other rows do not stay selected when right-clicking on the header. I'm at a loss to know what to do now.
This comment was minimized by the moderator on the site
I am so sorry for that. Could you tell me what Excel version and system you use?
This comment was minimized by the moderator on the site
Hello,

That is exactly what I've been doing, however, it only hides the row on which I have clicked the row header. All other selected rows remain unhidden, and are deselected at the same time. I will try it again following your gif exactly. Thanks for your reply.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations