Filter in Excel

Question

In Excel, I see how to make a filter for something like >100, but can I make a filter for a group of cells: and(thisCell>cellAbove,thisCell>cellToLeft)

Answer 1

The following formula achieves the same effect without using a helper column. If the date is in C1:C12, then you can filter C2:C12 with the following formula.

=LET(a,C2:C12,x,--(a>C1:C11),y,--(a>B2:B12),FILTER(a,x*y,"zz"))

C1 is omitted from the filtering because there is no cell above it. However, if you want to test C1 against just B1, then use

=LET(a,C2:C12,x,--(a>C1:C11),y,--(a>B2:B12),s,FILTER(a,x*y,"zz"),IF(C1>B1,VSTACK(C1,s),s))

a is the array of data.

x and y are boolean arrays reflecting the results of the 11 vertical comparisons and the 12 horizontal comparisons.

Answer 2

Yes. Use a formula-based helper column and then filter on that column.

Example setup:

1. Suppose the data starts in B2.
2. In an empty helper column (say, E2), enter a formula that checks both conditions for that row, for example:

      =AND(B2>B1,B2>A2)
   

   Here:
   • B2 is this cell
   • B1 is the cell above
   • A2 is the cell to the left
3. Fill this formula down the helper column for all rows.
4. Turn on AutoFilter (Data → Filter).
5. Filter the helper column to show only TRUE.

This uses the AND logic to decide which rows meet the criteria, and the filter then hides all rows where the condition is FALSE.

---

References:

• Filter data in a range or table in Excel