Excel / Auto Hide Rows

Copper Contributor

Does anybody now how to create a formula that Automatically hides an entire row if one of the cells in that row has a certain value?

6 Replies

@Taylor_Watson 

 

Depends in part on what you mean by "Hides" and on the bigger picture into which this request fits.

 

Assuming that it's the value in the cell in column A, which, if it's 3, is cause for none of the other cells to be displayed, you could always add an IF condition to each of the other cells in the row:

e.g., in cell B2, which now has =[some formula]

=IF(A2=3,"",[some formula])

That formula would replace the value of [some formula] with a blank cell if column A contains 3

and so on across the row

 

A more healthy way to handle this would be to leave all the rows on your core table as they are, but have a separate worksheet that uses FILTER to display rows that don't have 3 in column A.

=FILTER(OrigTable,OrigTable!A2:A34<>3)

 

https://exceljet.net/excel-functions/excel-filter-function

 

But those are just for starters. Please come back with further info on the bigger picture that lies behind your question if those two suggestions miss the mark.

 

 

FOR EX.

Below represents 4 rows and 2 columns of data that are populated from a different tab inside my spreadsheet.

I want to create a formula that automatically hides the entire row labeled E2 b/c there is not a number in the second column.

Is there an easy formula for this?

 

TAKEOFF 1                                                                          3,554Tons
~Piers 3,553.5Tons
E1                                                                    3,553.53Tons
E2                                                                                -  Tons

@Taylor_Watson 

 

There is nothing in what you just posted that suggests you even read the prior response. Did you? Did you try either of the ideas presented there? So far as I know there is no formula, per se, that would hide the row although--as suggested in my prior response--it depends on what you mean by "hide."

 

The FILTER function would do it, on a separate page. And there was even a link to a page with instructions on how to use it.

 

Otherwise you're looking at a macro or a VBA routine, and that's not my field of expertise.

@Taylor_Watson  To expand on  @mathetes  response.  you say, "are populated from a different tab".  How is that being done? cell-based formula? pivot table?  Because if it is the former then mathetes suggestion is perfect because you can just add a conditional to that formula to ignore that data if the corresponding data in column X is blank.  You should be able to achieve similar results using a pivot table but use the filter option instead of an in-cell formula.  Maybe if you supplied a sample sheet we could aid better.

But as mathetes said, there is no in-cell formula to hide a row.  In-cell formula ONLY determine what to display in the cell in which it is located and has no ability to act on anything else.

That all said, there are 2 other options: VBA (also called macros), or Conditional Formatting

Using VBA you can create code to hide that row, but would need to define the 'trigger' action (i.e. when should the code run and perform a check. )

Using conditional formatting you can check if that cell is blank and format that whole row to have the same text and background color.  This will 'hide' the information in that row but not truly hide the row as the row will still be present, but it will look blank.

Hello! Sorry to awaken an older discussion. But I am facing the same issue and tried the FILTER function and absolutely love the functionality. However, I am trying to fill a cell that is merged and causing a Spill error. Due to the cells above I need to be able to maintain this merged cell. Can you think of any tricks to help me with this? I feel like I am so close to a solution with the FILTER function! Thank you! @mathetes 

@JonMarshA 

 

I'm sorry to have taken so long to get back. We've been on vacation in another country and I went, quite intentionally, without a computer. Only a phone, and that only for email and texts and calls, and those on a limited bases.

 

So with that, if you still are patiently waiting, would it be possible for you to post a copy of your workbook or a reasonable facsimile (in order to avoid disclosing private or confidential info), on OneDrive or GoogleDrive? Then paste a link here that grants access.