You want to build custom formulas in Excel that handle complex logic without repeating calculations. Copilot can help you create and debug LAMBDA and LET functions faster by generating structured formula suggestions. LAMBDA lets you define reusable custom functions, while LET assigns names to intermediate values so formulas stay readable. This article explains how to combine Copilot with both advanced functions to reduce errors and automate repetitive tasks.
Key Takeaways: Copilot and LAMBDA/LET in Excel for Business Users
- Copilot pane > Formula suggestions: Type a plain‑English description of your calculation and Copilot generates a LAMBDA or LET formula for you.
- LET function syntax =LET(name1,value1,calculation): Assign names to intermediate values to make formulas easier to read and faster to calculate.
- LAMBDA function syntax =LAMBDA(parameter1,calculation): Create a custom function you can reuse anywhere in the workbook without writing the same logic again.
How Copilot Supports LAMBDA and LET in Excel
Copilot in Excel can interpret natural language requests and convert them into formulas. When you ask for a calculation that involves multiple steps or repeated logic, Copilot often suggests a LET function to define intermediate variables. For example, if you type “calculate total sales after tax and discount,” Copilot may generate a LET formula that names the subtotal, discount amount, and tax amount before computing the final result.
For LAMBDA, Copilot helps you define a custom function that you can call by name in any cell. You describe the input parameters and the calculation, and Copilot outputs the LAMBDA syntax. You then paste the formula into the Name Manager to register the custom function. Once registered, you can use the function like any built‑in Excel function.
Both LAMBDA and LET require a Microsoft 365 subscription with the latest Excel version. Copilot is available in Excel for Windows (current channel) and Excel for the web. You must have a Copilot for Microsoft 365 license to access Copilot in Excel.
Steps to Generate a LET Formula With Copilot
Follow these steps to create a LET formula using Copilot in Excel.
- Open the Copilot pane
In Excel, go to the Home tab and click the Copilot button on the ribbon. The Copilot pane opens on the right side of the window. - Describe your calculation
In the Copilot text box, type a plain‑English description of what you want. For example: “Calculate profit margin as (revenue – cost) / revenue, and show the result as a percentage.” - Review the generated formula
Copilot displays a proposed formula. It often uses LET to define intermediate values. For the profit margin example, the formula might look like:=LET(revenue, A2, cost, B2, profit, revenue - cost, profit / revenue). - Insert the formula into a cell
Click the Insert button below the formula suggestion. Copilot places the formula into the active cell. Adjust cell references if needed. - Test and adjust
Press Enter to calculate the result. If the output is incorrect, type a follow‑up request in Copilot such as “change the cell reference from A2 to A2:A10” to update the formula.
Steps to Create a LAMBDA Function With Copilot
Creating a LAMBDA function involves generating the formula with Copilot and then registering it in the Name Manager.
- Open the Copilot pane and describe the custom function
Click Copilot on the Home tab. Type a request like: “Create a LAMBDA function that converts Fahrenheit to Celsius.” - Copy the generated LAMBDA formula
Copilot returns a formula such as:=LAMBDA(fahrenheit, (fahrenheit - 32) 5 / 9). Select the text and copy it. - Open the Name Manager
Go to the Formulas tab and click Name Manager. In the dialog, click New. - Name the function
In the New Name dialog, type a name such as “FtoC” in the Name box. Make sure the Scope is set to Workbook. - Paste the LAMBDA formula
In the Refers to box, paste the LAMBDA formula you copied from Copilot. Click OK and then Close. - Use the custom function
In any cell, type=FtoC(A2)where A2 contains a Fahrenheit value. The custom function calculates the Celsius equivalent.
Common Mistakes When Using Copilot With LAMBDA and LET
Copilot generates a LET formula but the result is wrong
Copilot may misinterpret your description. For example, if you say “calculate total after tax,” Copilot might apply a tax rate you did not specify. Check the named values in the LET formula. Adjust the calculation step by using a follow‑up request like “use 8% tax rate” to refine the formula.
LAMBDA function returns a #NAME? error
This error appears when the LAMBDA function is not registered in the Name Manager. Make sure you pasted the formula into the Refers to box of a new name. Also verify that the name you typed in the cell matches the name you registered exactly, including case.
Copilot does not suggest LAMBDA or LET
Copilot may generate a simpler formula when the calculation is not complex enough to need LAMBDA or LET. To force the use of LET, include the word “LET” in your request. For example: “Use LET to calculate the average of three columns.” To force LAMBDA, say “Create a LAMBDA function that…”
Custom LAMBDA function disappears after closing the workbook
LAMBDA functions are stored in the workbook. If you open a different workbook, the custom function is not available. Save the workbook that contains the LAMBDA definition. To reuse the function in other files, copy the sheet to the target workbook or save the workbook as an add‑in (.xlam).
Copilot With LET vs Copilot With LAMBDA: When to Use Each
| Item | LET Function | LAMBDA Function |
|---|---|---|
| Purpose | Name intermediate values in a single formula | Define a reusable custom function |
| Reusability | Only within the formula where it is written | Across the entire workbook after registration |
| Complexity | Simplifies long formulas with repeated calculations | Handles multi‑step logic that requires input parameters |
| Copilot trigger phrase | “Use LET to…” or “define intermediate values” | “Create a LAMBDA function that…” |
| Example use case | Calculate net price with tax and discount in one cell | Convert Celsius to Fahrenheit in any sheet |
Use LET when you need to break a single complex formula into readable parts. Use LAMBDA when you want to create a custom function that you can reuse without copying the formula each time.
You can now use Copilot to generate LET and LAMBDA formulas in Excel without writing the syntax from scratch. Start by describing your calculation in the Copilot pane, then insert or register the formula as shown above. For advanced work, try combining both functions: define a LAMBDA that internally uses LET to name intermediate values. This keeps even the most complex custom functions clean and maintainable.