What's the difference between SCAN and REDUCE?

SCAN and REDUCE are complementary functions. REDUCE returns only the final accumulated value, while SCAN returns an array containing all intermediate accumulation values at each step. Use REDUCE when you need just the final result; use SCAN when you need the entire progression. For example, REDUCE might give you the total sales (one number), while SCAN shows cumulative sales for each month (array of numbers).

Can I use SCAN with multiple columns?

SCAN processes a single array at a time, but you can nest SCAN functions or combine it with other array functions. For multiple-column processing, consider using BYROW to apply SCAN logic to each row, or create a helper formula structure. Alternatively, flatten your data into a single array using TOCOL or similar functions before applying SCAN.

How do I return only the final SCAN result?

Wrap your SCAN formula with INDEX to extract the last value: =INDEX(SCAN(...), ROWS(array)). Alternatively, use REDUCE if you only need the final accumulated value. Another approach is to use TAKE(..., -1) to get the last element of the SCAN array result.

Does SCAN work with text values?

Yes, SCAN can work with text through your LAMBDA function logic. You can concatenate text, count occurrences, or perform any text manipulation within the LAMBDA. For example, =SCAN("", A1:A10, LAMBDA(acc, text, acc & text)) concatenates all text values. The key is defining appropriate operations for text within your LAMBDA function.

What happens if my array is empty?

If the array parameter is empty or contains no elements, SCAN returns an empty array. If you specified an initial_value, some versions may return just the initial value. Always test edge cases with empty data ranges, and consider using IFERROR or IF statements to handle empty arrays gracefully in your business logic.

How to Use the SCAN Function in Excel 365: Advanced Array Processing with Accumulation

Advanced

=SCAN([initial_value], array, lambda)

The SCAN function represents a significant advancement in Excel's array processing capabilities, enabling users to perform cumulative calculations across arrays with unprecedented flexibility. Introduced in Excel 365, SCAN works by applying a LAMBDA function to each element in an array while maintaining an accumulator value that carries results forward through iterations. This powerful function transforms how professionals handle sequential data processing, financial calculations, and complex business logic that requires maintaining state across multiple iterations. Unlike traditional array formulas that process elements independently, SCAN creates a running total or accumulated result, making it invaluable for scenarios requiring cumulative sums, running products, or any operation where each result depends on previous calculations. The function's integration with LAMBDA functions provides unprecedented control over custom logic, allowing you to define exactly how each element should be processed and how the accumulator should be updated. Whether you're calculating compound interest, tracking inventory changes, or building complex financial models, SCAN offers a elegant, formula-based solution that eliminates the need for helper columns or VBA macros.

Syntax & Parameters

The SCAN function follows the syntax: =SCAN([initial_value], array, lambda). The initial_value parameter is optional and represents the starting point for your accumulator. If omitted, SCAN uses the first array element as the initial value. The array parameter is required and specifies the range or array you want to process sequentially. The lambda parameter is mandatory and must be a LAMBDA function that accepts exactly two arguments: the accumulator (current running value) and the current array element. The LAMBDA function should return the new accumulator value for the next iteration. When constructing your LAMBDA function, the first parameter represents the accumulator value carrying forward from the previous iteration, while the second parameter represents the current element being processed. For example, in =SCAN(0, A1:A10, LAMBDA(acc, val, acc+val)), the accumulator starts at 0, and each iteration adds the current value to the accumulator. SCAN processes elements sequentially from left to right (or top to bottom for vertical arrays) and returns an array containing all intermediate accumulator values. This means if you scan 10 cells, you'll receive 10 results showing the accumulation at each step. Understanding this output structure is crucial because SCAN displays every step of the accumulation process, not just the final result, making it perfect for creating running totals or tracking progression.

initial_value

Initial value

Optional

array

Array to scan

lambda

LAMBDA function

Practical Examples

Running Total of Monthly Sales

=SCAN(0, B2:B13, LAMBDA(acc, sales, acc + sales))

This formula starts with an initial value of 0 and adds each month's sales figure to the accumulator. The result displays cumulative sales for each month, showing Month 1's total, Month 1+2's total, and so on through the entire year. This allows stakeholders to see both individual performance and cumulative growth in a single view.

Compound Interest Calculation

=SCAN(10000, SEQUENCE(24), LAMBDA(acc, month, acc * 1.008))

Starting with an initial investment of 10,000, this formula applies a monthly growth rate of 0.8% (1.008 multiplier) for 24 months. Each iteration multiplies the previous balance by the growth factor, creating a compound growth effect. The SEQUENCE(24) function generates the iteration counter, though the month parameter isn't used in the calculation—it simply triggers 24 iterations.

Inventory Tracking with Adjustments

=SCAN(500, C2:C20, LAMBDA(acc, adjustment, MAX(0, acc + adjustment)))

Beginning with 500 units, this formula processes daily adjustments (positive for additions, negative for removals). The MAX(0, ...) ensures inventory never goes negative, a practical business requirement. Each result shows the inventory level after each transaction, providing a complete audit trail of inventory changes throughout the tracking period.

Key Takeaways

SCAN applies a LAMBDA function iteratively across an array while maintaining an accumulator value, returning all intermediate results
Unlike REDUCE which returns only the final value, SCAN displays the complete accumulation progression, making it ideal for running totals and trend visualization
The LAMBDA function must accept exactly 2 parameters (accumulator and current value) and return a single value for the next iteration
SCAN is exclusive to Excel 365 and offers elegant solutions for complex sequential calculations without requiring helper columns or VBA
Combine SCAN with other array functions like FILTER, SEQUENCE, and TRANSPOSE to build sophisticated data analysis workflows

Pro Tips

Use INDEX with SCAN to extract specific accumulation steps: =INDEX(SCAN(...), 5) returns the 5th accumulated value without displaying the entire array.

Impact : Reduces formula complexity in downstream calculations and enables targeted analysis of specific accumulation milestones.

Combine SCAN with IFERROR to handle errors gracefully: =SCAN(0, A1:A10, LAMBDA(acc, val, IFERROR(acc + val, acc))) skips problematic values while maintaining accumulation.

Impact : Makes formulas more robust and production-ready, preventing entire calculations from failing due to single problematic data points.

Create reusable LAMBDA functions as named formulas to use with SCAN: define AccumulateSum as LAMBDA(acc, val, acc+val), then reference it in SCAN formulas.

Impact : Improves formula readability, enables consistency across workbooks, and simplifies maintenance when logic needs updating.

Leverage SCAN's array output with other array functions: =SUM(SCAN(0, A1:A10, LAMBDA(acc, val, acc+val))) or =AVERAGE(SCAN(...)) to derive statistics from accumulation progression.

Impact : Enables sophisticated analysis like identifying average accumulation rate or finding peak accumulation values without additional helper columns.

Useful Combinations

SCAN with FILTER for Conditional Accumulation

=SCAN(0, FILTER(B2:B20, C2:C20="Active"), LAMBDA(acc, val, acc + val))

Combines SCAN with FILTER to accumulate only values meeting specific criteria. This formula sums only sales from 'Active' entries, creating a running total of filtered data. The FILTER function pre-processes the array before SCAN receives it, enabling conditional accumulation logic.

SCAN with SEQUENCE for Iterative Calculations

=SCAN(100, SEQUENCE(12), LAMBDA(acc, period, acc * 1.05))

Uses SEQUENCE to generate a specified number of iterations, perfect for time-series calculations like depreciation or growth projections. SEQUENCE creates the iteration control, while SCAN applies the transformation repeatedly. This pattern is ideal for financial modeling without needing actual data in each cell.

SCAN with TRANSPOSE for Column-wise Accumulation

=TRANSPOSE(SCAN(0, TRANSPOSE(A1:E1), LAMBDA(acc, val, acc + val)))

Applies SCAN across columns by transposing the data, processing horizontally instead of vertically. This combination allows accumulation logic to work with row data, useful for period-by-period analysis across columns representing time periods or categories.

Common Errors

#VALUE!

Cause: The LAMBDA function has incorrect parameter count or returns an incompatible data type. SCAN expects the LAMBDA to accept exactly 2 parameters and return a single value per iteration.

Solution: Verify your LAMBDA function has exactly two parameters (accumulator and current value). Ensure the function returns a single value, not an array. Check that all operations within LAMBDA are compatible with the data types you're processing.

#NAME?

Cause: The SCAN function is not recognized, typically because you're using an Excel version prior to Excel 365 or the function name is misspelled.

Solution: Confirm you're using Excel 365 (subscription version). SCAN is not available in Excel 2019 or earlier versions. Check that the formula uses 'SCAN' exactly, not 'Scan' or other variations (though Excel typically auto-corrects case).

#REF!

Cause: The array parameter references cells that have been deleted or the LAMBDA function references undefined named ranges or deleted cells.

Solution: Verify all cell references in your array parameter still exist. Check that any named ranges or cell references within the LAMBDA function are valid. Use absolute references ($A$1:$A$10) if you plan to copy the formula to prevent reference shifts.

Troubleshooting Checklist

1.Verify you're using Excel 365 (not 2019 or earlier) - SCAN is exclusive to Excel 365 subscription
2.Confirm your LAMBDA function has exactly 2 parameters: accumulator and current element
3.Check that LAMBDA returns a single value per iteration, not an array or multiple values
4.Ensure your array parameter is a valid range or array formula result, not empty or containing errors
5.Validate that initial_value (if provided) is compatible with your accumulation logic and data types
6.Test with a simple formula first (like =SCAN(0, A1:A5, LAMBDA(a,v,a+v))) before building complex logic

Edge Cases

Single-cell array or single value passed to SCAN

Behavior: SCAN returns the single value processed through the LAMBDA once; if initial_value is provided, it returns initial_value processed through LAMBDA with the single array element

Solution: Verify your array contains the data you expect; use COUNTA or ROWS to check array dimensions before SCAN

This is valid behavior but often indicates data selection issues in larger formulas

LAMBDA function that returns different data types (sometimes number, sometimes text)

Behavior: SCAN may return mixed data types in the result array, or Excel may coerce values causing unexpected results

Solution: Ensure LAMBDA always returns the same data type; use TEXT() or VALUE() functions to enforce consistent types

Excel's type coercion can mask data type inconsistencies; explicit type conversion prevents surprises

Very large arrays (10,000+ rows) with complex LAMBDA logic

Behavior: SCAN may calculate slowly or consume significant memory; performance degrades as array size and LAMBDA complexity increase

Solution: Test performance with sample data; consider breaking calculations into smaller chunks or using REDUCE if only the final value is needed

SCAN's full array output means memory usage scales with both array size and formula complexity; optimize LAMBDA logic for efficiency

Limitations

•SCAN is exclusive to Excel 365 subscription versions and not available in Excel 2019, 2016, or earlier; this limits deployment in organizations using perpetual license models
•SCAN always returns an array of all intermediate values, consuming memory and display space; if you need only the final result, REDUCE is more efficient and appropriate
•The LAMBDA function must accept exactly 2 parameters and return a single value; complex multi-parameter logic requires nesting multiple functions rather than direct LAMBDA expansion
•SCAN processes arrays sequentially only (left-to-right or top-to-bottom); parallel or non-sequential processing requires alternative approaches or helper columns

Compatibility

✓ Excel

Since Excel 365 (2021 or later subscription version)

=SCAN([initial_value], array, lambda) - identical syntax across all Excel 365 versions

✓Google Sheets

=SCAN([initial_value], array, lambda) - Google Sheets supports SCAN with identical syntax

Available in Google Sheets with full compatibility; LAMBDA support is required for SCAN to function

✗LibreOffice

Not available

Frequently Asked Questions

Master advanced Excel formulas like SCAN with ElyxAI's AI-powered formula assistant. Get instant help with syntax, optimization, and real-world applications to accelerate your data analysis skills.

REDUCE

BYROW