Excel / Auto Hide Rows

Question

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?

mathetes · Answer

Taylor_Watson&nbsp;&nbsp;Depends in part on what you mean by "Hides" and on the bigger picture into which this request fits.&nbsp;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 3and so on across the row&nbsp;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&lt;&gt;3)&nbsp;https://exceljet.net/excel-functions/excel-filter-function&nbsp;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.&nbsp;

taylor_watson · Answer

&nbsp;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?&nbsp;TAKEOFF 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3,554Tons~Piers&nbsp;3,553.5TonsE1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,553.53TonsE2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Tons

mathetes · Answer

Taylor_Watson&nbsp;&nbsp;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."&nbsp;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.&nbsp;Otherwise you're looking at a macro or a VBA routine, and that's not my field of expertise.

mtarler · Answer

Taylor_Watson&nbsp; To expand on&nbsp;&nbsp;mathetes&nbsp; response.&nbsp; you say, "are populated from a different tab".&nbsp; How is that being done? cell-based formula? pivot table?&nbsp; 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.&nbsp; You should be able to achieve similar results using a pivot table but use the filter option instead of an in-cell formula.&nbsp; 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.&nbsp; 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 FormattingUsing 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.&nbsp; 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.

jonmarsha · Answer

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!&nbsp;mathetes&nbsp;

Forum Discussion

Excel / Auto Hide Rows

Share

Resources

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