Simplify Formulas with Copilot

Have you ever opened an old workbook and stared at a formula packed with nested RIGHT, LEN, FIND, INDEX, and MATCH functions, wondering if there is a cleaner way? There is. Copilot in Excel can read those legacy formulas, figure out exactly what they do, and replace them with modern Excel functions that are easier to read, maintain, and update.

In this tutorial we walk through three exercises that show exactly how that works, converting a complex string formula, a classic lookup, and a multi-nested reshape formula, each into a single modern function.

Exercise 1: Replace a Legacy String Formula with TEXTAFTER

Let’s say we have a sales data table with a Sales Rep column, a Product column, and an Amount column. In the Legacy column, a formula extracts the last name from each full name. The formula we have looks like this:

=RIGHT(B8,LEN(B8)-FIND(" ",B8))

This pattern uses RIGHT to pull characters from the right side of the cell, LEN to count all characters, and FIND to locate the space. We subtract the space position from the total length to get the number of characters to extract. It works great, and Excel users have been writing formulas like this for decades.

But, what if we wanted to replace this with something simpler? Well, let’s ask Copilot. We open the Copilot pane and type the following prompt into the Edit box:

replace this legacy formula with fast modern functions

Copilot reasons through the formula, identifies a modern alternative, and fills in the Modern column. Bam:

=TEXTAFTER(B8," ")

In case we’re new to the TEXTAFTER function, it returns everything that appears after a specified delimiter inside a text string. The function arguments are:

• text: the text string to search within
• delimiter: the character or string that marks the split point
• instance_num: which occurrence of the delimiter to use (optional)
• match_mode: 0 for case-sensitive, 1 for case-insensitive (optional)
• match_end: whether to treat end of string as a delimiter (optional)
• if_not_found: value to return if the delimiter is not found (optional)

Copilot even verified that all modern results match the legacy formulas before presenting the suggestion. We click Done, and we can compare the two columns side by side.

One function instead of three nested functions. That is a meaningful simplification, especially when we need to hand this workbook off to a colleague or come back to it six months from now.

Exercise 2: Replace INDEX/MATCH with XLOOKUP

Now that we’ve modernized a string formula, it is time to tackle a classic lookup. Consider the following worksheet:

We have an Employee ID and the legacy formula in row 8 retrieves the matching salary from the lookup table. The legacy formula is:

=INDEX(F13:F17,MATCH("EMP-103",B13:B17,0))

INDEX/MATCH is a powerful combination, and plenty of workbooks still rely on it. But it requires two functions working together, which adds mental overhead when reading or editing the formula. Let’s ask Copilot to modernize it. We open Copilot and send the same type of prompt:

replace this legacy formula with fast modern excel functions

Copilot comes back quickly with a recommendation. Hit Enter, and bam:

=XLOOKUP(B6,B13:B17,F13:F17)

A quick look at the XLOOKUP function. It searches a range for a value and returns a corresponding result from another range. The function arguments are:

• lookup_value: the value to search for
• lookup_array: the range to search in
• return_array: the range to return a result from
• if_not_found: value to return when no match is found (optional)
• match_mode: 0 for exact match, -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard (optional)
• search_mode: 1 to search first to last, -1 to search last to first (optional)

Copilot replaced the nested INDEX/MATCH with a single XLOOKUP and confirmed the result of 68,500 matches the legacy formula. XLOOKUP is easier to read AND easier to maintain, since the lookup array and return array are explicit arguments rather than a position calculated by MATCH.

Exercise 3: Replace a Nested Reshape Formula with WRAPROWS

Let’s bring this tutorial home with the most complex example. We have a list that looks like this:

The data arrives as a vertical list in column B. The Legacy formula reshapes it into a four-row, three-column grid. The formula uses IFERROR, INDEX, ROW, and COLUMN together with some positional math to place each item into the right cell:

=IFERROR(INDEX($B$7:$B$18,(ROW(A1)-1)*3+COLUMN(A1)),"")

This kind of formula is genuinely hard to decipher at a glance. If anything changes about the data layout, updating it requires careful thought. Let’s open Copilot again and ask it to find a better way:

replace this legacy formula with fast modern excel functions

Copilot reasons through the transformation logic and identifies the perfect modern replacement. We fill the formula in, and bam:

=WRAPROWS(B7:B18,3)

Here’s a quick refresher on the WRAPROWS function. It takes a one-dimensional range or array and wraps it into a two-dimensional array with a specified number of columns per row. The function arguments are:

• vector: the one-dimensional range or array to wrap
• wrap_count: the number of values per row
• pad_with: value to use for any empty positions in the last row (optional)

One function. Two arguments. Copilot confirmed it spills the same four-by-three result as the legacy formula. That is a dramatic simplification compared to the original nested formula.

Summary

So those are three different examples for how Copilot can help us simplify legacy Excel formulas.

• In Exercise 1, Copilot replaced a multi-function string formula using RIGHT, LEN, and FIND with the clean TEXTAFTER function.
• In Exercise 2, it swapped out a classic INDEX/MATCH combination for the more readable XLOOKUP.
• In Exercise 3, it took a deeply nested IFERROR/INDEX/ROW/COLUMN reshape formula and replaced it with a single WRAPROWS call.

If we have historical workbooks filled with complex nested formulas, Copilot can help us modernize them quickly, producing results that are easier to understand, maintain, and update over time. Mission accomplished!

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

Do we need a special version of Excel to use Copilot?

Yes. Copilot in Excel requires a Microsoft 365 subscription with a Copilot license attached to the account. It is not available in standalone Excel 2019 or Excel 2021.

Do TEXTAFTER, XLOOKUP, and WRAPROWS work in all Excel versions?

XLOOKUP was introduced in Excel 365 and Excel 2021. TEXTAFTER and WRAPROWS require Excel 365 or Excel for the web. They are not available in Excel 2019 or earlier standalone versions.

Should we always trust Copilot’s formula suggestions?

Not blindly. Copilot is a great starting point, but we should always verify the output matches the expected results. In the exercises above, Copilot actually verified the results itself, which is helpful. Still, spot-checking a few rows manually is a good habit before accepting any AI-generated formula.

What prompt works best when asking Copilot to simplify a formula?

A straightforward prompt like ‘replace this legacy formula with fast modern functions’ or ‘replace this legacy formula with fast modern Excel functions’ worked well in all three exercises. Keeping the prompt simple and direct tends to give the clearest results.

Can Copilot modernize any legacy formula, or just certain types?

Copilot handles a wide range of formula patterns, but it works best when a modern function equivalent actually exists. For highly custom or business-logic-specific formulas, Copilot may not find a cleaner alternative. No worries, we can always keep the original formula if Copilot can’t improve on it.

What is the advantage of TEXTAFTER over the RIGHT/LEN/FIND approach?

TEXTAFTER is far more readable. We pass the text and the delimiter, and TEXTAFTER handles the position math internally. The old RIGHT/LEN/FIND approach required us to manually compute how many characters to extract, which adds cognitive load for anyone reading or editing the formula later.

Why is XLOOKUP considered an improvement over INDEX/MATCH?

XLOOKUP consolidates what took two nested functions into one. The lookup array and return array are explicit arguments, so the formula is self-documenting. XLOOKUP also includes a built-in if-not-found argument, eliminating the need to wrap the whole thing in IFERROR.

Can WRAPROWS handle lists that don’t divide evenly into rows?

Yes. The optional pad_with argument lets us specify what to display in any empty cells in the final row. We can pass an empty string, a zero, or any value that makes sense for our data.

Does Copilot place the modernized formula automatically, or do we have to accept it?

Copilot places a preview of the formula in the worksheet and shows the suggested formula in the pane. We then click Done to accept it or Undo to revert. This gives us a chance to review the result before committing to the change.

Can we use this same approach on an entire column of legacy formulas at once?

In the first exercise, Copilot filled the modern formula across all rows automatically, not just the first cell. Copilot scanned the data range and applied the formula throughout, which is a nice time-saver compared to manually filling down after the fact.