MAP, SCAN, and REDUCE in Excel: Modern Array Logic for Power Users

Coding Liquids blog cover featuring Sagnik Bhattacharya for MAP, SCAN, and REDUCE in Excel, with dynamic-array logic visuals.
Coding Liquids blog cover featuring Sagnik Bhattacharya for MAP, SCAN, and REDUCE in Excel, with dynamic-array logic visuals.

MAP, SCAN and REDUCE are Excel functions that run a LAMBDA over an array: MAP transforms each item, SCAN returns a running result at every step, and REDUCE collapses many inputs into one accumulated result. They are not the first functions most people should learn, but they become powerful once you already understand dynamic arrays.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

The practical win is not cleverness. It is expressing transformation logic more directly and with fewer helper ranges.

What do MAP, SCAN and REDUCE do in Excel?

MAP, SCAN and REDUCE are Excel functions that apply a LAMBDA to an array, each returning a different shape of result. Use MAP when you want to transform each item in an array, SCAN when you want a running result at every step, and REDUCE when you want one accumulated result from many inputs. They are most useful in formula-heavy models where helper columns are getting out of hand. They are available in Excel for Microsoft 365 and Excel 2024.

  • You already work comfortably with dynamic arrays.
  • A model is collecting too many helper columns.
  • You want clearer logic around transformations and running calculations.

What is each of the three functions for?

MAP changes items, SCAN shows the running journey, and REDUCE gives you the final rolled-up answer. Thinking about them this way is more helpful than memorising syntax alone.

Why use MAP, SCAN and REDUCE in practical models?

MAP, SCAN and REDUCE help you keep logic close to the formula instead of scattering it across a sheet. That can make a model easier to inspect once you are comfortable reading array formulas.

If you want to practise formulas like these with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

When should you not use MAP, SCAN or REDUCE?

Avoid MAP, SCAN and REDUCE when your team is uncomfortable maintaining advanced arrays, because a helper column may still be the kinder choice. Good spreadsheet work is not about showing off. It is about making the workbook sustainable.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Worked example: running margin control

A finance analyst wants a running margin view across monthly values. SCAN can produce the running output without a helper column, while REDUCE can produce one overall total from the same logic.

Common mistakes

  • Using advanced functions when a simpler formula is easier to maintain.
  • Skipping named logic with LET or LAMBDA when formulas become hard to read.
  • Assuming power-user functions automatically improve the workbook.

What should you learn after MAP, SCAN and REDUCE?

For a more reusable next step after MAP, SCAN and REDUCE, read LET and LAMBDA. If the real need is summary reporting rather than array transformation, GROUPBY may be more relevant.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Frequently asked questions

What do MAP, SCAN and REDUCE each do?

MAP transforms every item in an array, SCAN returns a running result at each step, and REDUCE rolls many inputs into one accumulated result. A simple way to remember it: MAP changes the items, SCAN shows the journey step by step, and REDUCE gives you the final total, all driven by a LAMBDA you write.

When should I use them?

Use them in formula-heavy models where helper columns are multiplying and you already work comfortably with dynamic arrays. They let you keep the logic in one formula instead of scattering it across the sheet, which can make a model easier to inspect, provided the people maintaining it are comfortable reading array formulas.

What is a simple REDUCE example?

A simple REDUCE example is accumulating a value across a list, such as a running compound total or concatenating items, where each step feeds the next. REDUCE plus a LAMBDA expresses that in a single cell, and the result is one final value rather than a spilled list of every step.

How do they work with LAMBDA?

Each one takes a LAMBDA that describes what to do per element or per step. That is where the real power is: you define the per-item logic once and apply it across the array. MAP and SCAN return arrays, while REDUCE returns the single accumulated result.

When should I not use them?

Do not use them when your team is not comfortable maintaining advanced array formulas, because a helper column can be the kinder, more sustainable choice. Good spreadsheet work is about maintainability, not cleverness, so reach for a simpler formula first whenever it does the same job.

How do they combine with GROUPBY or FILTER?

They combine well. You can MAP over a FILTER or UNIQUE result, or post-process a GROUPBY output. Keep the logic close to the formula and feed one spill result into the next. FILTER, UNIQUE and GROUPBY each return an array, so MAP, SCAN or REDUCE can take their output directly.

Related guides on this site

If you want to keep going without opening dead ends, these are the most useful next reads from this site.