I have an Excel table that has the following (plus more) data in it:
What I would like to do is filter the data to return all but the columns that have all 0 values in them, i.e. the return would be:
I can't for the life of me get my filter expression to return what I want, any help would be greatly appreciated!
With BYCOL and LAMBDA:
=FILTER(B1:I4,BYCOL(B2:I4,LAMBDA(a,SUM(a)))>0)
Or as @BigBen pointed out:
=FILTER(B1:I4,BYCOL(B2:I4,LAMBDA(a,OR(a)))
Since BYCOL and LAMBDA are not available to all office 365 users yet here is one that does not use it.
=FILTER(B1:I4,TRANSPOSE(MMULT(--((TRANSPOSE(B2:I4)<>0)),SEQUENCE(ROWS(B2:I4),,1,0))>0))
