Use Excel Copilot to Build Reporting Formulas Without Looking Them Up

What This Does

Instead of Googling VLOOKUP syntax or COUNTIF examples, you describe what you want in plain English and Copilot writes the exact formula — ready to paste into your workbook.

Before You Start

• You have Microsoft Excel open with your data workbook
• You're signed into a Microsoft 365 account with Copilot (Business Standard or above)
• Your spreadsheet has clear column headers
• The Copilot panel is open (Home ribbon → Copilot button on the right side)

Steps

1. Open the Copilot panel

Click the Copilot button on the right side of the Home ribbon. The Copilot chat panel opens on the right side of the screen.

2. Describe your formula need

Type a plain English description of what you want to calculate. Include column names as they appear in your header row.

What to type: "Write a formula that looks up the value from the Account Number column in Sheet2 and returns the corresponding Balance value from column C of Sheet2."

Or: "Write a SUMIF formula that adds up all values in the Amount column where the Status column says 'Open'."

3. Get the formula

Copilot returns the exact formula with your column references already filled in. It also explains briefly what the formula does.

4. Apply it

Click Insert formula if prompted, or copy the formula and paste it into the target cell in your worksheet. Drag the fill handle down to apply it to all rows.

Real Example

Scenario: Your daily exception report has Account IDs in column A. You have a reference sheet with Account IDs in column A and Advisor Names in column B. You want to pull the advisor name next to each exception automatically.

What you type: "Write a formula that looks up the Account ID in column A of this sheet against column A in Sheet2, and returns the value from column B of Sheet2."

What you get: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) — ready to paste, with an explanation of what each part does.

Tips

• If your data is a Table object (blue banded rows with a header), mention it: "I'm using a Table called ExceptionLog" — Copilot will use structured references like [@AccountID] instead of cell references
• For complex multi-condition formulas, describe each condition separately: "Show FAIL if column B is empty AND column C is greater than 5 days ago"
• If the formula doesn't work as expected, paste it back into Copilot and ask "Why doesn't this formula work?" with a description of what's happening

Tool interfaces change — if the Copilot button has moved, search for "Copilot" in Excel's Help menu.