VBA used to be the only way to handle iterative, multistep logic in Microsoft Excel. Not anymore. REDUCE brings the power of functional programming directly into your cells, allowing you to condense complex, messy data into single, clean results.
Whether you’re stripping unwanted characters or building a dynamic search engine, REDUCE lets you create sophisticated data engines that adapt as your spreadsheets grow.
The REDUCE function is available to those using Excel for Microsoft 365, Excel for the web, and the most up-to-date Excel mobile and tablet apps.
The REDUCE syntax: The snowball analogy
The REDUCE function might look intimidating because it requires a LAMBDA, but the logic is actually quite intuitive once you visualize it. Here’s the syntax:
=REDUCE(initial_value,array,LAMBDA(a,v,calculation))
To understand how it works, imagine a snowball rolling down a mountain:
- initial_value (optional): This is your starting point. It’s the original size of the snowball before it starts rolling. If you leave this blank, Excel will use the first item in your array as the starting value.
- array: This is the slope. It’s the range of cells you want the function to travel through.
- LAMBDA(a,v,…): This is the engine that tells the snowball what to do at every step. The accumulator (a) is the snowball itself, carrying the result from the previous row into the next. The value (v) is the fresh snow—the specific item in the current row the function is processing.
- calculation: This is the logic. You’re telling Excel to take the snowball (a), add the new snow (v), and pass the result to the next step.
The important thing to remember is that REDUCE has a memory. Unlike standard functions that look at every cell independently, it knows exactly what happened at each step.
Related
How to Use LAMBDA in Excel to Create Your Own Functions
Simplify and reuse your most complex formulas.
A simple warm-up: Using REDUCE for an iterative sum
Before I explain how to use REDUCE to tackle the “impossible” tasks, I’ll show you the snowball in action with some basic math. While you’d normally use the SUM function in the following example, using REDUCE to add a list of numbers illustrates how the accumulator (a) and value (v) pass the baton.
Imagine you have a formatted Excel table named T_Numbers containing the digits 1 through 5.
Here’s the formula to sum them:
=REDUCE(0,T_Numbers,LAMBDA(a,v,a+v))
This is what is happening:
Iteration
Accumulator (a)
Value (v)
Logic
1
The initial_value argument is 0.
1
0+1=1
2
1
2
1+2=3
3
3
3
3+3=6
4
6
4
6+4=10
5
10
5
10+5=15
The function finishes the list and returns the final result of 15.
REDUCE is a heavier lift for Excel than standard functions due to its iterative behavior. If a built-in function optimized for speed, like SUM or AVERAGE, can do the job, use it. Use REDUCE only when each step depends on the previous one.
In this simple example, the snowball was a number that grew through addition. However, the true power of REDUCE shines when you realize the accumulator (a) can hold any type of data. In the following real-world examples, I’ll show you how the accumulator can scrub text strings or track logical statuses.
Real-world example 1: The dynamic multi-character stripper
Suppose you have a list of codes in an Excel table and want to clean them by stripping all dashes, parentheses, dots, plus signs, and spaces.
You might consider nesting SUBSTITUTE functions inside one another to do this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Code],”-“,””),”(“,””),”)”,””),”.”,””),”+”,””),” “,””)
However, this formula is hard to read, prone to typos, and incredibly fragile. What’s more, if you later decide to also strip colons and hashtags from the codes, you have to add yet more nested SUBSTITUTE functions and parentheses.
Related
This single keyboard shortcut makes writing complex Excel formulas easy
Transform unreadable formula walls into clean, organized checklists.
With REDUCE, you can use the array argument to identify the characters you want to remove. This tells Excel to loop through a list of symbols and scrub the text clean one character at a time.
First, set up a table named T_Chars and list every symbol you want to remove. The sixth row contains the space.
Then, in the main table, type the following formula and press Enter:
=REDUCE([@Code],T_Chars[Characters],LAMBDA(a,v,SUBSTITUTE(a,v,””)))
Here’s how it works:
- The initial value (a): You start with the messy code in the current row ([@Code]). This is the snowball in its original state.
- The array (v): This is your T_Chars[Characters] column. Excel looks at every single symbol in the list, one by one.
- The loop: In round 1, REDUCE takes the code and substitutes the first character in the T_Chars[Characters] column (the dash) with nothing (“”). Then, in round 2, it takes that already-partially-cleaned code and removes the next character (the opening parenthesis). The function continues this cycle until it has exhausted every row in your T_Chars[Characters] column.
If you later decide you also want to remove colons and hashtags, you simply add those characters to the bottom of your T_Chars[Characters] column. Likewise, if you realize you want to keep all the parentheses, delete these rows from the table.
Real-world example 2: The multi-category search engine
Imagine you’re managing an inventory list and need to flag any item whose description contains the words “Hazardous,” “Flammable,” “Fragile,” “Corrosive,” or “Toxic.”
To do this using a nested OR statement, you would need to construct a complex formula like this:
=OR(ISNUMBER(SEARCH(“Hazardous”,[@Description])),ISNUMBER(SEARCH(“Flammable”,[@Description])),ISNUMBER(SEARCH(“Fragile”,[@Description])))
We use ISNUMBER in this formula as a shield. If the SEARCH function doesn’t find a word, it returns a #VALUE! error, which breaks the whole formula. ISNUMBER catches that error and converts it to a clean FALSE, allowing the formula to keep running.
While this works as-is, the formula is almost unbearably lengthy, and every time you need to add a new keyword, you have to manually open the formula, add a new ISNUMBER(SEARCH()) string, and balance the parentheses.
Related
The Beginner’s Guide to Boolean Logic in Microsoft Excel
Boost your Boolean boon.
Instead, with REDUCE, you can create a keyword list for the array argument. So, set up a table named T_Watchlist with a single Keyword column, and in your product table, type this formula:
=REDUCE(FALSE,T_Watchlist[Keyword],LAMBDA(a,v,a+ISNUMBER(SEARCH(v,[@Description]))>0))
Here’s what’s happening:
- The initial value (a): You start with FALSE—the product is considered safe until the loop proves otherwise.
- The array (v): This is your T_Watchlist[Keyword] column. Excel looks at every single word in the list, one by one.
- The logic: ISNUMBER(SEARCH(v,[@Description])) returns 1 (TRUE) if a match is found or 0 (FALSE) if it isn’t. The formula then adds that 1 or 0 to the accumulator (a). Because it ends with >0, the moment the loop finds a match and the accumulator hits 1, the result stays TRUE for the rest of the journey.
If you add 10 new keywords to the T_Watchlist[Keyword] column, REDUCE automatically detects the new rows, runs the extra loops, and updates your flags instantly. As a result, you’re moving from a nested OR formula that needs babysitting to a REDUCE formula that evolves as your criteria change.
Why use REDUCE instead of VBA?
You might wonder why you shouldn’t just use a VBA macro or Power Query for these tasks. While those tools are powerful, REDUCE offers three advantages:
- Native cloud support: VBA macros don’t work in Excel for the web or on the Excel mobile and tablet apps. If you share a macro-enabled workbook with someone who opens it in a browser, your solution will break. REDUCE is a native function that works across all the latest versions of Excel, regardless of the device or platform.
- Zero security friction: Many IT departments block XLSM (macro) files due to the security risks they can carry. Since REDUCE lives inside a standard XLSX file, you can rest assured that it won’t be blocked by firewalls or trigger any security warnings.
- Instant recalculation: Power Query is fantastic for massive datasets, but it requires a manual refresh to see changes. REDUCE, however, is reactive. The moment you add a new keyword to your watchlist or fix a typo in a product code, the results update instantly.
Related
Excel is officially a programming language: Here’s what that means
Your favorite spreadsheet app is now a functional programming environment, thanks to LAMBDA and recursion.
REDUCE isn’t always the answer
While REDUCE is incredibly powerful, it’s not a silver bullet for every situation. There are two scenarios where you should consider other tools:
- Massive datasets: Because REDUCE performs a calculation loop for every cell, it can significantly slow your spreadsheet if you’re running especially complex loops or working with very large datasets. In those scenarios, Power Query is usually the best option.
- Complex debugging: Unlike VBA, where you can step through code line by line, REDUCE lacks the specialized step-through debugging tools. While you can test small sections of a formula, it’s much harder to pinpoint exactly which row caused an error in a long loop.
By moving from nested strings to the clean logic of REDUCE, you make your formulas shorter and smarter. In fact, REDUCE is part of a trio of powerful LAMBDA functions designed to handle arrays within the grid. MAP is like an assembly line—use it when you want to apply the same logic to every single cell in the range and return a new list of the same size as the original. SCAN is more like a progress report—it’s the ideal function for creating a running total or a step-by-step history of a calculation.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
