Most Excel users I know learned formulas the same way I did — one function at a time, stacked on top of whatever they already knew. Dynamic array functions don't replace those skills; they just make a lot of the workarounds unnecessary. I've been running my self-updating top-five lists with TAKE and DROP for a while, and the same shift has happened with the four functions below. Each one reduced a multi-step routine I used to perform reflexively to a single formula.
Excel finally fixed its biggest data entry problem, and it's a lifesaver
One click in the Data tab can catch almost all issues.
FILTER replaced an entire ritual of helper columns and array formulas
One formula now does what multiple functions used to split between them
Pulling matching rows in older Excel meant building a nested INDEX, MATCH, SMALL, and IFERROR formula and entering it with Ctrl + Shift + Enter. It worked, but maintaining it later was a problem. The other option was to apply AutoFilter, copy the visible rows, and paste them elsewhere as static values. That was also fine until the source data changed.
The FILTER function does the same job in one line. In my sales spreadsheet, which has 32 rows across regions, product categories, and salespeople, pulling every Electronics sale from the West region looks like this:
=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))
The first argument is the range you want to return. The second is the condition, and the multiplication between the two checks acts as an AND — both have to be true. Switching the asterisk to a plus sign makes it an OR, and the result spills automatically. Adding a new row in the source updates the spilled output the moment you press Enter.
You can wrap FILTER in IFERROR with a third argument to handle empty results. =FILTER(range, condition, "No matches") keeps your sheet from showing #CALC! errors when no rows qualify.
UNIQUE turned my three-step deduplication routine into a single cell
Remove Duplicates is good, but it has never updated itself for me
The Remove Duplicates option under the Data tab is fine for a one-off cleanup. The catch is that it produces a static list. When you copy a column to a new location, run the dialog, or sort the results, you'll have to redo all of it the next time someone adds a row. I've done that more times than I care to count.
UNIQUE skips every step of that. Pointing it at the salespeople column in my sales spreadsheet looks like this:
=UNIQUE(D2:D33)
The result spills out John Smith, Sarah Johnson, Mike Wilson, Lisa Brown, David Chen, Emma Davis, Tom Rodriguez, and Amy Foster. That's eight names with no dialog box. Wrapping it as =SORT(UNIQUE(D2:D33)) returns the same list alphabetized. The output remains connected to the source, so adding a new name to the data automatically extends the spilled list.
This setup also makes a clean source for a data validation dropdown list. If you reference the spilled range with a hash like D35#, your dropdown grows on its own as new names appear in the source data.
SORTBY ended my copy-paste-and-sort shuffle for good
Sorting a view without touching the source data
Sorting in Excel has always carried a small risk. Rearranging the source could break formulas that referenced fixed rows, so my fallback was to copy a chunk somewhere safe and sort the copy. SORT helped, but only when I was happy displaying every column I sorted on.
SORTBY removes that constraint. It sorts one range using values from another range, and the second range doesn't have to appear in the output. I used the following formula to rank salespeople by revenue in my dataset:
=SORTBY(D2:D33, G2:G33, -1)
The first argument is what you want returned (salespeople), the second is what to sort by (sales revenue), and -1 sets the order to descending. The names come back ranked from highest sales amount to lowest, and the sales amount column never appears in the output unless you ask for it.
It also pairs cleanly with FILTER. Wrapping FILTER(D2:G33, B2:B33="North") inside SORTBY returns only the North region's records, sorted high to low, in one shot. The source data stays untouched.
SORTBY accepts multiple sort levels. You can add more range/order pairs to sort by region first, then by sales revenue within each region.
SEQUENCE replaced fill handles and the ROW tricks I used to be proud of
Generating a series without dragging a single cell
Generating a numbered series used to mean dragging the fill handle until I lost patience or writing =ROW(A1) and copying it down. Both work, but neither resizes when the underlying data changes.
SEQUENCE handles the same task in a single cell, and it generates rows, columns, or full grids depending on the arguments. The syntax is:
=SEQUENCE(rows, [columns], [start], [step])
To number the eight unique salespeople in my data starting at 1001, I used the following formula:
=SEQUENCE(8, 1, 1001, 1)
That returns 1001 through 1008 in a single column. Setting columns to 1 and step to 1 keeps it as a vertical list with consecutive integers. Where SEQUENCE earns its place is inside other functions. Building a 31-day date range for January 2026 takes one formula:
=DATE(2026, 1, SEQUENCE(31))
Feeding the spilled array into TEXT, INDEX, or any other function expecting a range collapses what used to need a helper column. I covered this in more detail when I wrote about using SEQUENCE to fill date columns, and it still saves me time on every calendar or schedule I build.
Old Excel still works, but I'm not opening that toolbox again
The functions I want to dig into next
None of the older methods is broken. They just take longer and produce files that are harder to maintain, for both me and anyone else opening the workbook later. For me, the shift is what I reach for by default. New spreadsheets start with dynamic array thinking, and the old toolbox only comes out when I'm editing someone else's file. Next on my list are TEXTSPLIT for breaking apart strings, VSTACK and HSTACK for combining ranges, and PIVOTBY and GROUPBY for skipping pivot tables entirely. Each release seems to retire another habit, which is fine by me.