Forum Discussion
Excel / Auto Hide Rows
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,554 | Tons |
~Piers | 3,553.5 | Tons |
E1 | 3,553.53 | Tons |
E2 | - | Tons |
- mathetesSep 17, 2021Silver Contributor
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.
- mtarlerSep 17, 2021Silver Contributor
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.
- JonMarshAMay 15, 2024Copper Contributor
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
- mathetesMay 31, 2024Silver Contributor
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.