Excel Formula Completion - Using XLOOKUP
I tested how well Excel's formula completion with Copilot handles a multi-step calculation that would normally require a nested function. The goal was simple: calculate profit by subtracting the total cost of units sold from total revenue, with unit costs stored in a separate lookup table. Instead of writing the full formula by hand, I typed a single equals sign and let Copilot generate the formula for me. The result was impressive.
Note: Microsoft 365 Copilot License is required (Paid version of Copilot)
Problem setup: a common real-world scenario
Imagine a spreadsheet with a product list in column A and total revenue for each product in column C. Unit quantities are in column D. Unit costs are not next to the main table; they live in a separate table elsewhere in the sheet, with product names as the lookup column and unit price in the adjacent column.
To compute profit, I need to subtract the total cost (units times unit price) from total revenue. Manually, that would look like this:
=C2 - D2 * XLOOKUP(A2, I:I, J:J, "not found")
That formula uses XLOOKUP to find the unit price for the product listed in A2, multiplies that price by the unit quantity in D2, then subtracts the result from revenue in C2.
Where Formula Completion with Copilot help
Typing that formula by hand requires attention to detail. If the lookup range is wrong, or you forget to multiply units by price, you get the wrong result. Predictive formulas remove most of that friction. By having descriptive column headers and a clear layout, Copilot can infer intent and produce not only the correct structure but the exact formula I would usually type.
Why descriptive headers matter
- Clarity: Column headers like "Product", "Revenue", and "Units Sold" make it clear what each column contains.
- Context for Copilot: Copilot uses the headers to infer relationships between columns and where lookup values live.
- Reduce trial and error: Better headers mean fewer corrections and less manual range hunting.
If headers are vague or missing, Copilot's predictive formulas are more likely to guess incorrectly or return a generic suggestion you still have to fix.
Excel File used for Formula Completion XLOOKUP
How I tested it
I typed the word Profit into the new column header to indicate what I wanted to calculate.
Instead of writing the XLOOKUP and multiplication, I simply typed the equals sign in the Profit cell and waited for the formula completion suggestion. Copilot proposed the exact calculation I described: subtracting units times a looked-up unit price from total revenue. It created the XLOOKUP that referenced the product name in column A and returned the price from the lookup table columns.
Validating the result
After accepting the suggestion, I hit Enter and confirmed the profit value was correct. For example:
- Row 2: Revenue = 320, Units = 2, Unit cost = 100 -> Profit = 320 - (2 * 100) = 120
- Row 3: Revenue = 600, Units = 3, Unit cost = 110 -> Profit = 600 - (3 * 110) = 270
I then copied the formula down the column to fill profit values for all rows. Copilot had already constructed the formula correctly so it behaved like any normal Excel formula when filled down.
Tweaks and best practices
Copilot's suggestion used "not found" as the XLOOKUP default for missing values. I prefer to handle missing lookups explicitly, depending on the scenario. Two common adjustments:
Use explicit absolute references for the lookup ranges so filling down doesn't change the lookup area:
=C2 - D2 * XLOOKUP(A2, $I:$I, $J:$J, "not found")
Wrap the XLOOKUP in IFERROR or IFNA to return 0 or a blank when no match exists:
=C2 - D2 * IFNA(XLOOKUP(A2, I:I, J:J), 0)
These simple changes make the formula more robust in larger spreadsheets.
Why this matters for everyday Excel work
Predictive formulas cut down the time spent typing and reduce the chance of syntax errors. For any workflow that needs to pull values from lookup tables—such as inventory, financial models, or commission calculations—this feature saves steps and helps maintain consistency across the workbook.
Key benefits I noticed:
- Speed: One keystroke triggered a complete, correct formula.
- Accuracy: Copilot produced the correct nesting of multiplication and lookup functions.
- Compatibility: The feature works in Excel on the web and in the desktop app, though it requires a Copilot license.
Practical tips before you rely on Copilot
- Keep your data organized with clear, descriptive headers.
- Verify suggested formulas on a couple of rows before filling down.
- Decide how you want to handle missing lookup values and adjust the suggestion accordingly.
- Use absolute references for lookup ranges when appropriate.
- Remember, a Copilot license is required to access formula completion.
Final thoughts
Automatic formula generation for complex functions like XLOOKUP demonstrates how AI can streamline routine spreadsheet work. It does not replace understanding formulas, but it reduces repetitive typing and common mistakes. With descriptive headers and a quick sanity check, predictive formulas can be trusted to do heavy lifting in many day-to-day Excel tasks.
Try it with a small test table first. Confirm the output on a couple of rows, adjust for missing values if needed, then fill down. Once you get comfortable, predictive formulas will become a reliable tool in your Excel toolbox.
Resources
- M365 Copilot Chat vs. M365 Copilot
- M365 Copilot pricing page
- Microsoft Formula Completion page
Microsoft 365 Copilot Chat (free) vs. Microsoft 365 Copilot (Paid)
Chris Menard
Microsoft 365 Copilot Chat vs. Microsoft 365 Copilot
Microsoft 365 Copilot pricing - Microsoft page
Microsoft Formula Completion Blog Post
Microsoft has introduced Formula Completion in Excel, a Copilot-powered feature designed to make writing formulas faster and easier. Traditionally, creating formulas required remembering syntax, selecting the correct functions, and troubleshooting errors, which could slow down workflows.
Formula Completion addresses these challenges by proactively suggesting and autocompleting formulas as soon as you type “=”, using the context of your worksheet—such as headers, nearby cells, and tables. Each suggestion includes a preview of the result and a natural language description of the formula’s intent, helping users build accurate formulas with confidence.
