--- title: "REDUCE: How to Build Running Calculations Across Any Dimension in Excel" date: 2026-06-08T00:00:00Z updated: 2026-06-14T05:05:49Z tags: ["REDUCE", "LAMBDA", "SCAN", "Dynamic Arrays", "Iterator Functions", "Modern Excel Functions", "Excel", "VBA", "MAP", "FILTER", "Running Totals", "Accumulator"] canonical: https://bianca.codes/blog/reduce-running-calculations-across-any-dimension-excel/ --- # REDUCE: How to Build Running Calculations Across Any Dimension in Excel _REDUCE is the most powerful of the functional array functions and the most overlooked. The lambda which means you can accumulate strings, and replace calculations that used to need VBA._ There is a category of Excel problem that has always lived just past the edge of what formulas could do. Not "sum this column" - that's SUM. Not "sum it cumulatively" - that's SCAN, covered in [the 11 May Magic Monday](#). The harder category: "walk through this data row by row, carry something forward at every step, and give me the final result, where the thing being carried forward isn't necessarily a number." For most of Excel's history, that sentence ended one way: write a VBA loop. A variable outside the loop, updated inside the loop, returned at the end. The pattern was so common it was practically the reason VBA existed in most workbooks. REDUCE brings that exact pattern into the formula bar, and because the carrying-forward logic is a LAMBDA you define, it can be as complex as you need. Numbers, text, whole arrays - if a LAMBDA can build it, REDUCE can accumulate it. ## The mental model REDUCE is SCAN with the working thrown away. Both functions walk an array one element at a time, holding an accumulator - a running result that gets updated at every step. SCAN returns the accumulator's value at _every_ step, which is why it's the running total function. REDUCE returns only the _final_ value. Feed 1, 2, 3, 4 into SCAN with a "+ rule" and you get {1, 3, 6, 10}. Feed the same thing into REDUCE and you get 10. That sounds like REDUCE is the weaker sibling. It's the opposite, and the reason is the accumulator itself. SCAN's output has to be the same shape as its input - one intermediate result per element - which quietly constrains what the accumulator can be. REDUCE has no such constraint. Its accumulator can be a number, a string that grows at every step, or an entire array that gains rows as the iteration proceeds. The accumulator is whatever your LAMBDA returns, and the LAMBDA can return anything. That's the move. REDUCE isn't "the one that gives you the final total." It's the general-purpose loop, and the other array functions are special cases of it. You could write MAP, or SCAN, or FILTER as a REDUCE with the right LAMBDA. You wouldn't, because the specialised versions are clearer. But when the problem doesn't fit any of the specialised shapes, REDUCE is the one that's left, and it fits everything. ## The syntax ```vba =REDUCE(initial_value, array, LAMBDA(accumulator, value, expression)) ``` Three pieces. The initial value is what the accumulator starts as - 0 for a sum, an empty string for text, a seed row for an array. The array is what you're walking through. The LAMBDA takes the accumulator so far and the current element, and returns the new accumulator. The simplest case, a conditional sum that ignores errors as it goes: ```vba =REDUCE(0, A2:A100, LAMBDA(acc, val, IF(ISNUMBER(val), acc + val, acc))) ``` The same shape, accumulating text instead: ```vba =REDUCE("", B2:B20, LAMBDA(acc, val, IF(acc = "", val, acc & ", " & val))) ``` And the same shape again, where the accumulator is an array that grows by one stacked block per step: ```vba =REDUCE(seed, items, LAMBDA(acc, val, VSTACK(acc, some_transformation(val)))) ``` Same three arguments every time. The LAMBDA is where all the variation lives. ## A real example: exploding quantities into rows The scenario: a picking list. Column A is an item code, column B is a quantity. The label printer needs one row per physical unit - if the list says `WIDGET-04` with a quantity of 3, the output needs `WIDGET-04` three times. Anyone who has dealt with label printing, raffle entries, or seat allocations knows this table shape, and knows that Excel has historically had no formula answer to it. This was a VBA job, or a copy-paste-and-sort job, every single time. With REDUCE building an array as its accumulator: ```excel =DROP( REDUCE("seed", SEQUENCE(ROWS(tblPick)), LAMBDA(acc, i, VSTACK( acc, IF(SEQUENCE(INDEX(tblPick[Qty], i)) > 0, INDEX(tblPick[Item], i)) ) ) ), 1) ``` What's happening at the interesting parts: the array being walked is `SEQUENCE(ROWS(tblPick))` - the row _numbers_, not the rows themselves, so the LAMBDA can pull both the item and the quantity for each row with INDEX. The `IF(SEQUENCE(qty) > 0, item)` trick builds a column of the item code repeated quantity times. VSTACK welds that block onto the bottom of the accumulator. And the DROP at the end removes the `"seed"` value the accumulator started with, because REDUCE needs _something_ to stack the first real block onto. Add a row to the picking table, and the output rebuilds itself. There is no loop to rerun, no macro security warning, no "enable content" banner. The thing that made this a VBA problem - the output being a different length than the input, determined by the data itself - is exactly the thing REDUCE's free-form accumulator handles. ## What it doesn't do REDUCE gives you no intermediates. If you want the running result at every step - a balance after every transaction - that's SCAN, and no amount of cleverness makes REDUCE the better choice there. It carries exactly one accumulator. If you need to track two things at once - a total _and_ a count, say - you have to pack them into a single value (a two-element array via HSTACK, or a delimited string) and unpack them inside the LAMBDA. It works, and it reads badly. If your accumulator needs three or more components, that's usually the sign the job belongs in Power Query. The array-building pattern has a performance edge to respect. VSTACK inside REDUCE copies the accumulated array at every step, so the cost grows faster than the row count does. On a few hundred rows it's instant. On fifty thousand rows it's a spinning cursor, and Power Query's equivalent patterns will beat it comfortably. The formula bar is the right place for this pattern at worksheet scale, not at warehouse scale. And it can't stop early. REDUCE visits every element, every recalculation, even if the answer was settled by row 3. There's no break statement in a LAMBDA. ## Frequently Asked Questions ### **What's the difference between REDUCE and SCAN in Excel?** Both walk an array with an accumulator. SCAN returns the accumulator at every step, producing an output the same size as the input - that's what makes it the running total function. REDUCE returns only the final accumulator value, but in exchange the accumulator can be any shape, including a growing array. Use SCAN when the intermediates are the answer; use REDUCE when only the destination matters. ### **Does REDUCE work in older versions of Excel?** No. REDUCE requires Microsoft 365 or Excel 2024. In Excel 2019, 2021, and earlier perpetual licences, the function doesn't exist and any formula using it returns #NAME?. If your workbook needs to open cleanly in older versions, this entire family of functions is off the table. ### **Can the accumulator really be an array?** Yes, and this is the single most useful thing about REDUCE. The accumulator is whatever your LAMBDA returns, so a LAMBDA that returns VSTACK(acc, new\_rows) makes the accumulator an array that grows at every step. This is how REDUCE builds outputs that are a different length than their inputs - the thing that previously forced you into VBA or Power Query. ### **Why does the array-building pattern start with a seed I have to DROP?** REDUCE requires an initial value, and when the accumulator is an array, the first VSTACK needs something to stack onto. The seed fills that role, and DROP removes it from the final result. There are variations that avoid the DROP by special-casing the first iteration inside the LAMBDA, but they're harder to read than the seed-and-DROP pattern and save you nothing meaningful. ### **Is REDUCE faster than a VBA loop?** For most worksheet-scale jobs, the difference is irrelevant - both are instant. The honest comparison isn't speed, it's everything else: REDUCE recalculates automatically when the source changes, needs no macro-enabled file format, triggers no security warnings, and travels with the workbook. VBA still wins when the job needs to escape the grid entirely. Inside it, the loop has been replaced. ### **When should I use Power Query instead of REDUCE?** When the data is large, when the accumulation logic needs more than one or two tracked values, or when the result feeds a data model rather than a worksheet. List.Accumulate in Power Query is the same fold pattern with better performance characteristics at scale. REDUCE is the right tool when the answer needs to live on the sheet and update with it. ## Where this fits in the modern Excel kit The three-minute taste of REDUCE - collapsing a column into one delimited string - is the [Jun 6 Click Bait](#). The function REDUCE is most often confused with has its own deep-dive in the [11 May Magic Monday on SCAN](#). And for the transformation half of the family, where the array goes in and the same-shaped array comes out, the [Jun 1 Magic Monday on MAP](#) is the companion piece. Between MAP, SCAN, and REDUCE, the loop - the actual, honest, carry-something-forward loop - now lives in the formula bar.