Excel MAP

Turn a whole list into a new list — without dragging formulas down 500 rows.

So What’s MAP, Anyway?

Okay, imagine you have a list of numbers — test scores, prices, whatever. And you want to do the SAME thing to every single item. Like, add 10 points to each score, or double each price, or turn every name into uppercase.

Normally, you’d write a formula in the first cell, then drag that little green square all the way down to the bottom of your list. And if you have 500 rows, that’s 500 cells with formulas. It works, but it’s messy and your workbook starts to feel sluggish.

The Basic Idea

MAP works with a buddy — LAMBDA. You give MAP an array (like A1:A10), and you give it a LAMBDA that says “here’s what to do with each item.” MAP goes through the list one by one, does the thing, and returns a new array of results.

The Structure (It’s Not Scary)

That’s it. You feed it a range, you tell it what to do with each piece, and it gives you back a new list. No dragging. No copy-pasting. No “oh no, I forgot to lock the cell references.”

Why Bother With MAP?

Easy Dipping Your Toes

Example 1: Add 10 to Every Score

You’ve got a list of test scores in A1:A5. You want to give everyone a 10-point curve. No problem.

Boom. You get a new array with all the scores bumped up. If you had 500 scores, it’s the same formula.

Example 2: Double Every Price

Your boss says “double all the prices in column B.” Don’t panic. Just MAP it.

One formula covers the whole list. And if the boss changes their mind and wants 1.5x instead? Change the 2 to 1.5 and you’re done.

Example 3: Convert Names to Uppercase

You’ve got a column of names in all lowercase or mixed case. You want them ALL CAPS because you’re angry at the data.

Returns a new list like “JOHN”, “JANE”, “BOB”. Works on text, works on numbers that are stored as text, works on everything.

Medium Getting Comfortable

Example 1: Calculate Grades from Scores

You’ve got a list of percentages in A1:A20. You want to turn them into letter grades. Instead of a nested IF in every cell, you MAP it with a LAMBDA that does the grading.

Now you’ve got a dynamic list of grades that updates if any score changes. No dragging, no mess.

Example 2: Extract First Name from “Last, First”

Remember that mess of names like “Smith, John”? MAP can clean the whole column at once.

Same logic as before, but it processes every cell in B1:B50 in one shot. Clean, quick, no copy-pasting.

Example 3: Square Root of Each Number

You’ve got a list of numbers and you need the square root of each one. MAP makes it trivial.

You can even combine it with other functions. Want the square root of the absolute value? =MAP(A1:A100, LAMBDA(n, SQRT(ABS(n)))). Easy.

Hard Flexing Your MAP Muscles

Example 1: MAP with Multiple Arrays

MAP can take multiple arrays at the same time! You just list them, and the LAMBDA gets an element from each array. This is huge.

Say you have prices in A1:A10 and quantities in B1:B10. You want the total cost for each row (price × quantity).

Boom! One formula gives you the total for every row. No helper columns, no dragging. And if your lists are different sizes? MAP stops at the smallest one, so you don’t get errors.

Example 2: MAP + LET for Readable Calculations

Sometimes your calculation gets messy. You can use LET inside your LAMBDA to define variables and keep it clean.

Let’s calculate a discount: if the price is over $100, take 10% off; otherwise, 5% off.

Now you can actually read what’s happening. The LET makes it obvious: rate, amount, final price. MAP does the heavy lifting of applying it to every row.

Example 3: MAP with a Custom LAMBDA Function

You’ve already got a custom function in Name Manager — maybe you made a GRADE function from the LAMBDA tutorial. Now you can use it inside MAP!

Wait, that’s it? Yep. You built a custom function, and MAP just applies it to every cell in the range. This is where Excel starts to feel like a real programming language.

But Wait — MAP Isn’t Recursive

Just so we’re clear: MAP itself doesn’t do recursion. It’s a “loop” that goes through each element once. If you need recursion (like calling itself over and over), that’s pure LAMBDA territory. But MAP + LAMBDA is already powerful enough for 90% of what you’d ever want to do.

If you need to do something where each step depends on the previous one (like a running total), MAP isn’t the right tool — you’d look at SCAN or REDUCE. But that’s a story for another day.

Cheat Sheet: MAP Rules

1. MAP always needs a LAMBDA. You can’t use MAP without it. They’re best friends.
2. Your LAMBDA’s parameters match the arrays. One array = one parameter. Two arrays = two parameters. Easy.
3. The result spills. MAP returns an array that “spills” into the cells below and to the right. Make sure you have room.
4. You can use any Excel function inside. SUM, AVERAGE, LEFT, RIGHT, UPPER, LOWER, IF, IFS, SWITCH — they all work.
5. MAP stops at the smallest array. If you give it A1:A10 and B1:B5, it’ll only process 5 rows. No errors, just stops early.
6. Use LET inside for complex logic. It keeps your LAMBDA readable and maintainable.

Stuff That’ll Trip You Up (It’s Okay, We’ve All Been There)

• Forgetting the LAMBDA. =MAP(A1:A10) without a LAMBDA? Excel gives you a #CALC! error. You need both.
• Mismatched parameters. You pass two arrays but your LAMBDA only takes one parameter? Error. Match them up.
• Not leaving room for the spill. If there’s data in the cells where MAP wants to spill, you get a #SPILL! error. Clear some space.
• Using MAP on a single cell. It works, but it’s overkill. MAP shines on ranges.
• Expecting MAP to change the original data. MAP returns a new array; it doesn’t modify the source. That’s by design.

Now It’s Your Turn

Close this page (well, after you read this), open Excel, and try these:

1. Add 5 to every number in A1:A20 — should take you 10 seconds.
2. Convert every name in B1:B30 to proper case (first letter capitalized) using PROPER.
3. Multiply two columns: A1:A10 × B1:B10 using MAP with two arrays.
4. Create a LAMBDA that checks if a number is even (ISEVEN), then MAP it over a range.
5. Combine MAP with LET — calculate a 15% tip for a list of bills, but also return the total (bill + tip).

If you can do all five without looking back at the examples, you’ve officially leveled up. MAP will save you so much time once it clicks.