For years, the answer to "I want to do the same operation to every cell in a range" was the same. Write the formula in one cell. Click the bottom-right corner. Drag it down. Or, if you were feeling fancy, double-click the fill handle and let Excel guess where the column ended. Either way, the workbook ended up with thousands of identical formulas, each one a copy of the same logic with different cell references.
Then the dynamic array engine arrived, and the answer changed. You no longer needed one formula per cell. One formula could spill across a whole range. But the operation in that formula was still limited to whatever Excel's built-in functions could do. If you wanted to apply a custom transformation to every element, you were back to dragging.
MAP closes the gap. It takes your LAMBDA and runs it on every element in an array. No helper columns, no dragging formulas, no VBA loops. It's the Excel equivalent of a functional programming pattern, and once you've used it, going back to column-by-column formulas feels like a step backwards.
The mental model
MAP is the answer to: "I have an array, and I want a new array where every element is the result of running some function on the original."
That's it. The shape goes in, the same shape comes out, and every position in the output is the function applied to the corresponding position in the input.
If you've used Array.map in JavaScript, or map in Python, or Select in C# LINQ, the model is identical. You hand MAP two things: an array, and a function. MAP walks the array, applies the function to each element, and gives you back the transformed array as a spilled range. The original array is untouched. The output is a new array of the same shape.
The shift from drag-fill to MAP is not just about saving clicks. It is about treating "the transformation" as a separate thing from "where the transformation happens." The LAMBDA describes what to do to one element. MAP handles where that element comes from and where the result goes. The cell references are out of your formula. The logic is the formula.
The syntax
The function signature is short:
=MAP(array, LAMBDA(element, expression))The first argument is the array. The second is a LAMBDA that takes one parameter, which is each element of that array as MAP walks through it.
The simplest case: square every number in a range.
=MAP(A2:A10, LAMBDA(x, x^2))The LAMBDA parameter is named x. The expression is x^2. MAP runs this once per element, so A2 becomes A2^2, A3 becomes A3^2, and so on. The result spills down from wherever you wrote the formula.
A second example: turn every value into its absolute value, then format it as a string with a currency prefix.
=MAP(B2:B100, LAMBDA(v, "$" & TEXT(ABS(v), "#,##0.00")))Same shape. The LAMBDA does whatever you want; MAP applies it.
A third example, with two arrays in parallel. MAP can take multiple arrays of the same shape, and the LAMBDA gets one parameter per array. Each iteration receives the matching elements from each array.
=MAP(A2:A10, B2:B10, LAMBDA(price, qty, price * qty))The LAMBDA gets price from the first array and qty from the second. The result is the per-row total, spilled down. This is the genuinely interesting case, because it replaces the very common pattern of "helper column C, equals A times B, drag down, then sum C" with a single formula.
A real example: cleaning a column of free-text dates
The scenario: someone has handed you a column of dates that were typed by hand, so they're text strings like "15-Jan-2026", "15 January 2026", "2026-01-15", and a few cells with extra spaces or trailing whitespace. You want a column of actual Excel dates.
The drag-fill approach: write a DATEVALUE formula in one cell, wrap it in IFERROR, trim the whitespace, drag down across 5,000 rows, watch the cell references update. Then realise the format changed three weeks ago and the formula is now wrong, and now you have 5,000 cells to update.
The MAP approach:
=MAP(A2:A5001, LAMBDA(d, IFERROR(DATEVALUE(TRIM(d)), "")))One formula. Spills down 5,000 rows. If the cleaning logic changes, you change it once, in one place. The cell references are not part of the formula. There is no concept of "the formula in row 2,847" because there is only one formula.
The output is a dynamic array. If the source range grows, the formula has to be pointed at the new range. If you want it to grow automatically, anchor it on a structured table or a LET with a dynamic range. The Build It post on dynamic data validation lists covers that pattern.
What it doesn't do
MAP only does one-to-one transformations. The input array has 100 elements; the output array has 100 elements. If you want to filter the array down (some elements survive, others don't), MAP is the wrong tool. That's FILTER. If you want to roll the array up into a single value, like summing or averaging, MAP is the wrong tool. That's REDUCE. If you want to apply a running total or a cumulative operation where each step depends on the previous, that's SCAN.
MAP also can't see the index of the element it's processing. The LAMBDA gets the value, not the position. If you need to know "this is the third element," you build the index yourself, usually with a parallel SEQUENCE array passed as the second argument:
=MAP(A2:A10, SEQUENCE(ROWS(A2:A10)), LAMBDA(value, position, position & ": " & value))Awkward, but it works. Most of the time, if you need the index, that's a sign the transformation isn't really row-by-row and a different function is the right answer.
The other thing MAP can't do is short-circuit. It evaluates the LAMBDA on every element, every time. There's no "stop early" condition. If your LAMBDA is expensive, MAP is expensive, multiplied by the array length.
Frequently Asked Questions
Does MAP work in older versions of Excel?
No. MAP requires Microsoft 365 or Excel 2024. Anyone opening your workbook in Excel 2019 or older will see #NAME? wherever your MAP formula sits. The function does not exist in those versions and there is no compatibility layer. If you share workbooks across versions, MAP is not the answer for those files.
Is MAP faster than dragging a formula down?
Sometimes yes, sometimes no, and it depends on what the LAMBDA does. The big performance wins come from MAP letting you remove helper columns and replacing many formula cells with one. The actual per-element cost is similar to a dragged formula. The win is structural, not raw speed.
Can MAP work across two dimensions at once?
Yes. If you pass it a 2D range, the LAMBDA runs on every cell in that range and returns a 2D result of the same shape. The only constraint is that all arrays passed to a single MAP call must have the same dimensions.
What's the difference between MAP and BYROW or BYCOL?
MAP runs the LAMBDA on each individual element. BYROW runs it on each row as an array. BYCOL runs it on each column as an array. Use MAP when the transformation is per-cell. Use BYROW or BYCOL when the transformation needs to see a whole row or column at a time, like averaging a row or finding the max of a column.
Can I nest a MAP inside another MAP?
Yes, and this is one of the more powerful patterns. The outer MAP walks the rows, the inner MAP walks the columns within each row, and the result is a 2D array built by the nesting. It is also the pattern most likely to make your formula unreadable, so wrap each MAP in a LAMBDA you've named in Name Manager if you want to keep your future self sane.
Where this fits in the modern Excel kit
The three-minute version of how to write your first MAP is the Jun 2 Build It on dynamic validation lists, which uses MAP under the hood. For why MAP exists at all and why Excel is shipping functions that look like programming-language primitives, the Jun 3 Hot Take is the post. For the function that makes MAP possible in the first place, the May 4 Magic Monday on LAMBDA is the prerequisite reading.
