How to Use Dynamic Array Functions FILTER and CHOOSECOLS in Excel for Mac

Select your source data and format it as a Table (e.g., 'Table1'). Designate a specific cell for your filter criteria, such as cell K3.

In an empty destination cell, type the formula: =CHOOSECOLS(FILTER(Table1,Table1[Filter]=$K$3,"Wrong"),3,4,5,6,7). This formula filters 'Table1' where the 'Filter' column matches cell K3, and then outputs only columns 3, 4, 5, 6, and 7 from the result.

Ensure you use absolute references (like $K$3 instead of K3) for your criteria cell. This prevents the reference from shifting if you move or copy the formula to another location.

Highlight the maximum expected range of your spilled array and apply your desired cell formats (e.g., number, currency, date, or borders) manually. The formatting will stay in the cells regardless of how the array resizes.

Do not place your SUM or SUMIF formulas immediately below the spilled data if the dataset might grow over time. Instead, place totals in fixed rows above the array, to the right of the array, or on a completely separate summary sheet.

If you need to calculate totals based on the dynamic results, use the spill operator (#). For example, if your CHOOSECOLS formula is in cell A1, use =SUM(A1#) to sum the entire dynamic array automatically as it expands or shrinks.