The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Excel’s SCAN function applies a LAMBDA calculation to each item in an array and returns every intermediate result. Use it when you need to see an accumulation develop—such as a running total—rather than just its final value.
How SCAN processes an array
SCAN keeps an accumulator: a value that changes as the function moves through the array. For each item, its LAMBDA receives the current accumulator and the item, calculates a new accumulator, and returns that result as part of the output array.
Microsoft documents the syntax as:
=SCAN([initial_value], array, LAMBDA(accumulator, value, body))
initial_valuesets the accumulator’s starting state.arrayis the data SCAN processes.- The
LAMBDAnames the accumulator and current value, then uses its body to calculate the next state.
Example: calculate a running total
If cells A1:A3 contain 2, 3, and 4, enter:
=SCAN(0,A1:A3,LAMBDA(a,v,a+v))
The results are 2, 5, and 9. The first result is 0 + 2; the next adds 3 to the prior total; the last adds 4. Each output is the accumulator after processing that input. Microsoft documents the same accumulator pattern in its SCAN function examples.
Recommended Free Tools
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Other calculations SCAN can build
Intermediate products
To multiply each value by the accumulated product so far, Microsoft gives this example:
=SCAN(1,A1:C2,LAMBDA(a,b,a*b))
With an initial value of 1, each returned item is the product accumulated through that point in the array.
Progressively concatenated text
To build text one value at a time, Microsoft’s example is:
=SCAN("",A1:C2,LAMBDA(a,b,a&b))
The empty string is the starting accumulator for this text calculation. Microsoft advises using "" as the initial value when working with text.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteSCAN or REDUCE: which should you use?
| Function | What it returns | Use it when |
|---|---|---|
SCAN |
Each intermediate accumulator value | You need to see the progression, such as every running total. |
REDUCE |
The final accumulated value | You need only the result after the whole array has been processed. |
Both functions apply a LAMBDA across an array, but expose different results. Microsoft’s REDUCE documentation describes its final-result behavior.
Availability and errors
Microsoft’s detailed English SCAN support page lists Excel for Microsoft 365, Microsoft 365 for Mac, Excel 2024, and Excel 2024 for Mac. Microsoft’s Australian SCAN page also lists Excel for the web, while its alphabetical function reference marks SCAN with “(2024).” These references differ in scope, so check the documentation or function availability for your own Excel edition rather than assuming every version includes it.
Rank #4
Microsoft says an invalid LAMBDA or an incorrect number of parameters produces #VALUE! (“Incorrect Parameters”). Check that the formula supplies the expected arguments and that the LAMBDA calculation is valid. A separate, general LAMBDA issue can produce #CALC! if a LAMBDA is entered in a cell without being called; Microsoft explains this in its LAMBDA function documentation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Free tools Windows power users keep installed
One-click scans. No signup required.
