For years,building dashboards in Excel meant juggling helper columns, dragging formulas across endless cells, and praying your pivot table wouldn’t collapse under its own weight. But that’s changed. With the arrival of dynamic arrays, Excel has introduced a smarter, cleaner way to work: spill formulas.

Using just one formula, you can create an entire table, generate a filtered view, calculate a running total, or build a compact list that updates itself every time your data changes. If you’re ready to let Excel do the heavy lifting while you focus on insights, these might be the functions you didn’t know you needed.

UNIQUE

The smart way to build dynamic lists

The UNIQUE function returns a list of distinct values from a range or array. You can use it to pull product names, departments, months, or any other category from your source data. As your dataset updates, the extracted list refreshes automatically, making it ideal for dropdown menus, pivot tables, or charts that expand when new entries are added.

Here’s the syntax:

=UNIQUE(array, [by_col], [exactly_once])

Thearray argument specifies the range to evaluate. The optionalby_col argument determines how the comparison is made: set it to TRUE to compare columns or FALSE to compare rows. If you leave it out, Excel compares rows by default. The final optional argument,exactly_once, returns only the values or rows that appear a single time in the range when set to TRUE.

Imagine you have a list of product names in column A, imported from a form where entries often repeat. With UNIQUE, you can instantly transform that messy list into a clean dropdown source or slicer that updates as new values arrive:

=UNIQUE(A:A, FALSE, TRUE)

This formula returns only the distinct regions from your dataset, spilling them automatically into the cells below. If you prefer to display the list in alphabetical order, wrap it with SORT:

=SORT(UNIQUE(A:A, FALSE, TRUE))

Once your UNIQUE results are in place, you can reference them as the source for a data validation list. Your dropdown will now expand dynamically as your data grows—no extra work required.

FILTER

Instant, auto-updating data views

Dashboards exist to answer questions. Often, those questions are conditional: "Show me only the high-value deals" or "What are my Q4 revenue figures?" With FILTER, you can apply one or more criteria to a range and return only the rows that meet the conditions of your questions.

Here’s the basic syntax:

=FILTER(array, include, [if_empty])

Theinclude argument specifies which rows or columns to filter by. Theif_empty argument tells Excel what to display if no results are found; it’s not compulsory to use this argument, but I’ve made it a habit to add it, either as a blank ("") or a message like “none.”

Here’s an example using an actual dataset:

=FILTER(A2:N100, D2:D100="Online", "")
=FILTER(A2:N100, D2:D100=O2, "")

In these examples, Excel scans the range A2:N100 and returns only the rows where the Sales Channel column (column D) equals “Online.” To make it easier to change the filter, you canreference a cell in Excel (like O2) with the condition specified.

You can also combine FILTER with SORT to arrange your results alphabetically or numerically. And by using the addition (+) or multiplication (*) operators inside the include argument, you can apply multiple OR/AND criteria in the same formula:

=SORT(FILTER(A2:N100, (D2:D100=O2)*(I2:I100>1500)))

This example returns all products ordered online with over 1,500 units sold, sorted alphabetically. Link it to a chart or a dashboard element, and the view will always reflect the latest numbers—no refresh button required.

BYROW

Smarter calculations per row without helper columns

Once you’ve extracted or cleaned your source data, you often need to perform a calculation for each row. That might mean multiplying quantity by price, calculating a percentage change from one period to the next, or applying logic that varies by row. The BYROW function is designed precisely for these kinds of tasks.

It appliesa LAMBDA function to each row of a specified array and returns the results as a vertical spill. Here’s the syntax:

=BYROW(array, lambda(row))

BYROW takes anarray (or range), applies your custom calculation to each row, and spills the results in a single-column array. To achieve this, thelambda argument must contain a valid LAMBDA function that performs a calculation on a single row and returns one value. If it outputs multiple values (for example, trying to sort the row), Excel will return a #CALC! error.

Meanwhile, you can name therow argument anything you like (r, data, or row). It’s simply a placeholder for the current row being processed.

For instance, you can use BYROW to find the highest value in each row without adding helper columns:

=BYROW(AM2:AR100, LAMBDA(r, MAX(r)))

This formula looks at theUnits Sold,Unit Price,Unit Cost,Total Revenue,Total Cost, andTotal Profit columns for each row of my sales records and tells me which of those six values is the largest for that particular sales transaction.

In the same dataset, you can also use BYROW to calculate a per-row margin by subtracting unit cost from unit price:

=BYROW(AN2:AO100, LAMBDA(r, INDEX(r, 1, 1) - INDEX(r, 1, 2)))

Essentially, use BYROW when you want a single formula that delivers one result per row without adding or altering your original data.

REDUCE

Summaries that adapt automatically

While BYROW processes data row by row, REDUCE condenses an entire array into a single accumulated result by repeatedly applying a LAMBDA function to each value. Here’s the syntax:

=REDUCE([initial_value], array, lambda(accumulator, value, body))

You begin with aninitial_value—typically zero (for addition) or one (for multiplication)—and then move through each element in thearray, updating theaccumulator as you go. By the time the function reaches the last value, you’ll have your final result. Here’s an example using an actual dataset:

=REDUCE(0, AP2:AP100, LAMBDA(total,value, IF(value>100000, total+value, total)))

This formula scans every value in the Total Revenue column and adds it to the running total only if it exceeds 100,000. Whilea SUMIF could handle a simple condition like this, REDUCE is preferable when your logic becomes more complex. You can incorporate multiple rules, apply nested conditions, or build tailored calculations that evolve with your data.

On a dashboard, REDUCE can help you compute metrics such as total high-value transactions, average order value for active customers, or the cumulative impact of returned orders. And it does so in a single self-updating cell that automatically adjusts as your dataset changes.

SCAN

See change happen over time

While REDUCE returns only a single accumulated result,SCAN returns all the intermediate results along the way. Here’s the syntax:

=SCAN ([initial_value], array, lambda(accumulator, value, body))

Like REDUCE, it starts with an optionalinitial_value that defines the starting point for theaccumulator. If you’re working with text, you can set an empty string ("") as the initial value. Here’s an example of a similar formula to the one I used to perform the REDUCE function:

=SCAN(0, AP2:AP100, LAMBDA(total,value, IF(value>100000, total+value, total)))

Like the REDUCE function, this formula walks through every value in the Total Revenue column, adding each amount to the running total only if it exceeds 100,000. But unlike REDUCE, which hides all the work and shows only the final total, SCAN will show you every step of the accumulation.

For dashboards, this opens up amazing new possibilities. You can feed SCAN results directly into charts that visualize not just the final number, but the journey to it. A line chart driven by a SCAN formula will update automatically as new data arrives, showing your trend in real time. It’s especially effective in rolling forecasts or progressive dashboards, where seeing the movement and momentum matters just as much as the outcome.

A dashboard that updates itself

These functions are the building blocks of dashboards that run themselves. You define the logic once, and Excel takes care of the rest. As your data grows, your filters adapt. When new categories appear, they automatically populate your dropdowns. When transactions change, your summaries update in real time.

There areother spill functions worth exploring—SORT, XLOOKUP, SEQUENCE, and more—but these are the ones that solve my biggest dashboard challenges day after day. They’re the difference between spending hours tweaking ranges and actually analyzing what your data is saying.

You can focus on insights, and let Excel handle the busywork.