Using Copilot to Understand, Modify, and Document Complex Excel Formulas

Complex nested formulas can be intimidating. We have all opened a workbook and stared at a formula bar full of nested IFs, VLOOKUPs, and IFERRORs and had absolutely no idea what was going on. That is exactly the kind of situation where Copilot in Excel shines.

In this tutorial, we walk through three exercises that show how to use Copilot to explain a complex formula in plain English, modify it with a simple prompt, rewrite it using the LET function for clarity, and then generate a full documentation sheet for future users.

By the end, we will have a much better handle on how Copilot can make our formula work faster AND more maintainable.

Exercise 1: Explain and Modify a Complex Formula

Consider the following worksheet. We have an Employee ID in cell C10, and a formula in cell C11 that returns a category. The formula bar shows something like this:

Since we are about to look closely at IFERROR, here is a quick refresher. It returns a value we specify when a formula produces an error, otherwise it returns the formula’s result. The function arguments are:

• value: the formula or expression to evaluate
• value_if_error: the value to return if the formula produces an error

Before diving in, a quick look at the IF function. It returns one value when a condition is true and another value when it is false. The function arguments are:

• logical_test: the condition to evaluate
• value_if_true: the value returned when the condition is true
• value_if_false: the value returned when the condition is false

For those new to VLOOKUP, it searches for a value in the leftmost column of a range and returns a value from a specified column in the same row. The function arguments are:

• lookup_value: the value to search for in the first column of the range
• table_array: the range that contains the data
• col_index_num: the column number in the range from which to return a value
• range_lookup: TRUE for approximate match, FALSE for exact match

=IFERROR(IF(VLOOKUP(C10,Data!A:D,4,FALSE)>1000,IF(VLOOKUP(C10,Data!A:D,3,FALSE)="Active","High","Medium"),"Low"),"N/A")

That is a mouthful. To understand what this formula is actually doing, we need to look at the Data worksheet. Let’s take a look:

The Data sheet contains Employee ID, Name, Status, and Salary. So the formula is doing some kind of salary and status lookup to return a category. But the exact logic is still buried inside all those nested functions. This is the perfect job for Copilot.

Step 1: Ask Copilot to Explain the Formula

With cell C11 selected, we open Copilot and type: “explain this formula”. Copilot reads the active cell and returns a plain-language breakdown. And bam:

Copilot tells us the formula is a status classifier. It looks up the employee ID in the Data sheet, reads the salary from column 4 and the status from column 3, and then applies this logic: if the salary is greater than 1,000, it checks the status. Active returns “High”, anything else returns “Medium”. If the salary is not greater than 1,000, it returns “Low”. If the lookup fails entirely, it returns “N/A”.

Copilot also flags something important: the formula compares against 1,000, not 100,000. If we intended a 100k threshold, we need to change it. That is exactly what we will do next.

Step 2: Ask Copilot to Modify the Threshold

We stay in the Copilot panel and type: “change the threshold to 100k”. Copilot processes the request and shows us a preview of the updated formula:

Hit Enter, and bam… Copilot updates cell C11 directly in the worksheet with the revised formula:

=IFERROR(IF(VLOOKUP(C10,Data!A:D,4,FALSE)>100000,IF(VLOOKUP(C10,Data!A:D,3,FALSE)="Active","High","Medium"),"Low"),"N/A")

Copilot confirms the update and notes that Employee E003 is still “Medium” because the salary of $92,000 is below the new 100k threshold. We click Done, and the formula is live. And that is how we can use Copilot to explain and modify complex formulas. Mission accomplished!

Exercise 2: Rewrite the Formula Using LET

Now that we have a working formula, it is time to make things more interesting. That nested formula is still very hard to read at a glance. The structure buries the logic inside layers of parentheses, and if we need to change something six months from now, we will be back to square one trying to decode it.

Consider the following worksheet for Exercise 2. It contains the same formula, still complex and hard to follow at a glance:

Excel has a LET function designed exactly for this situation. It lets us assign friendly names to values and sub-expressions, then use those names in the final calculation. The result is a formula that reads almost like a short program.

Here is a quick refresher on the LET function. It assigns names to values or formulas within a single formula, then returns the result of a final expression using those named values. The function arguments are:

• name1: the first name to assign
• value1: the value or formula assigned to name1
• name2, value2, …: additional name-value pairs (optional)
• calculation: the final expression that uses the defined names and returns the result

We open Copilot and type: “convert this formula into a LET function so that it is easier to understand and maintain”. Copilot generates a rewritten version and applies it to C11:

=LET(
  employeeID, C10,
  salary, VLOOKUP(employeeID, Data!A:D, 4, FALSE),
  status, VLOOKUP(employeeID, Data!A:D, 3, FALSE),
  highThreshold, 100000,
  category, IF(salary>highThreshold, IF(status="Active", "High", "Medium"), "Low"),
  IFERROR(category, "N/A")
)

Now when we inspect the formula, the logic is clear at a glance. We can see exactly what each piece does: employeeID is the value in C10, salary and status come from VLOOKUP, highThreshold is set to 100,000, and category applies the IF logic using those friendly names. The final IFERROR wraps category to catch any lookup failures. Easy to read AND maintain.

Exercise 3: Generate Documentation with Copilot

With Exercise 2 complete, we can move on to the final exercise. Even with a clean LET formula, the next person to open this workbook may still have questions. What does the category mean? What are the thresholds? What data does the formula rely on?

We have a list that looks like this for Exercise 3, with the same LET formula in C11 and no documentation anywhere in sight:

The goal here is to ask Copilot to create an entire documentation sheet, automatically. We open Copilot and type: “add a new sheet named Admin that explains this formula (purpose and arguments)”:

We hit Enter, and bam:

Copilot creates a brand new Admin sheet. It includes the formula source reference, a plain-English purpose statement, a table breaking down every LET variable with its value and what it does, and a final output table showing exactly when the formula returns High, Medium, Low, or N/A. That is documentation we would have spent a long time writing by hand, generated in seconds.

(Note: the formula that Copilot placed in the Admin sheet is live, so it evaluates rather than displaying as text. A quick fix is to add a leading apostrophe to convert it to a text string, which keeps it readable without executing.)

Summary

In this tutorial, we used Copilot in Excel to tackle complex formulas in three different ways. First, we asked Copilot to explain a deeply nested IFERROR, IF, and VLOOKUP formula in plain language, and then modify it by updating the salary threshold with a single prompt. Second, we asked Copilot to rewrite that formula using the LET function, which assigns friendly names to values and makes the logic far easier to follow. Third, we asked Copilot to auto-generate a complete documentation sheet, including purpose, argument breakdowns, and output conditions, so future users can understand the workbook without any help.

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

Sample File

FAQs

What version of Excel is required to use Copilot with formulas?

Copilot in Excel requires a Microsoft 365 subscription with a Copilot license. It is not available in Excel 2019, Excel 2021, or standalone perpetual license versions. The LET function also requires Excel 365 or Excel 2021.

Does Copilot always select the active cell when explaining a formula?

Yes. When we open Copilot and type something like “explain this formula”, Copilot uses the currently selected cell as the context. So we want to make sure the cell with the formula we care about is selected before launching the prompt.

Can Copilot modify formulas directly in the worksheet, or does it just suggest changes?

Copilot can modify formulas directly. When it proposes a change, it shows a preview with highlighted cells and a Done or Undo button. We confirm the change by clicking Done, and Copilot writes the updated formula into the cell.

What is the LET function and why is it useful?

LET assigns names to values or formula results within a single formula. Instead of repeating the same VLOOKUP expression multiple times, we define it once with a name like “salary” and reuse that name throughout. This makes complex formulas shorter, easier to read, and easier to update.

Is Copilot always accurate when it explains or modifies a formula?

Not always. Copilot is a large language model, and its explanations or suggested formulas can occasionally be wrong. We should always verify the output against expected results, especially before using a Copilot-modified formula in a production workbook. The Copilot pane itself reminds us that AI-generated content may be incorrect.

Can we ask Copilot to create a new worksheet tab?

Yes. As we saw in Exercise 3, Copilot can add a new sheet by name and populate it with content. We simply describe what we want in a natural language prompt, and Copilot handles the sheet creation and data entry.

What happens if the Copilot-generated formula in the Admin sheet is live and we do not want it to evaluate?

We can add a leading apostrophe before the equal sign to convert the formula to a text string. This prevents Excel from evaluating it and simply displays the formula text, which is exactly what we want in a documentation sheet.

Can Copilot convert other complex formulas to LET, not just VLOOKUP-based ones?

Absolutely. LET works with any formula that has repeated expressions or complex nested logic. Copilot can rewrite INDEX MATCH combinations, SUMIFS with repeated range references, or any other long formula by assigning names to the repeated parts and simplifying the final expression.

Do we need to describe the formula in our prompt, or does Copilot just read the cell?

Copilot reads the active cell automatically, so we generally do not need to paste or describe the formula in our prompt. Short prompts like “explain this formula” or “convert this to a LET function” are enough because Copilot already knows which cell we are working with.

Is Copilot inside Excel the same as using ChatGPT or Copilot in a browser?

They are related but different. Copilot inside Excel has direct access to our workbook data, the active cell, sheet names, and table structures. That context makes it far more useful for formula work than a general-purpose chat interface where we would have to copy and paste everything manually.