Generate Procurement Formulas in Excel with Copilot

What This Does

Copilot can write Excel formulas for you in plain English (VLOOKUP replacements, spend variance calculations, on-time delivery percentages, and savings calculations) without you needing to remember formula syntax.

Before You Start

• You have Microsoft Excel open with your spend or PO data loaded
• Your data has clear column headers (formatting it as an Excel Table via Insert → Table helps, but Copilot reads plain ranges too)
• You're logged into Microsoft 365 with Copilot enabled
• Time needed: 5-10 minutes
• Cost: Copilot access on your account (most organizations buy the Microsoft 365 Copilot add-on; smaller ones may have free basic access; ask IT if you're not sure)

Steps

1. Open Copilot in Excel

Click the Copilot icon in the lower-right corner of the Excel window. The Copilot panel opens on the right. Microsoft moved Copilot off the Home ribbon in 2026, so this corner button is now the entry point. Pressing F6 cycles focus to it, and the newer Alt + C (Windows) and Cmd + Ctrl + I (Mac) shortcuts are rolling out. If you prefer it near the ribbon, right-click the corner button and choose Move to ribbon.

2. Describe the formula you need in plain English

Click in the Copilot text box and describe what calculation you want. Example: "Create a formula that calculates the percentage of purchase orders delivered on time, where 'Actual Delivery Date' is less than or equal to 'Required Delivery Date'."

3. Review the suggested formula

Copilot generates the formula and explains what it does in plain language. You'll see a preview of the formula before inserting it.

4. Insert the formula

Click Insert or Add to a new column to place the formula in your sheet. Copilot will suggest which column to add it to.

5. Ask for additional formulas as needed

Continue asking for related calculations: "Now create a formula that shows each vendor's average days early or late" or "Calculate total savings as the difference between initial quoted price and final PO price."

Real Example

Scenario: You have a PO report with vendor name, ordered amount, invoiced amount, and original quoted price. You want to calculate savings captured for each PO.

What you type: "Create a formula that calculates cost savings for each row: savings = (Quoted Unit Price × Ordered Quantity) minus (PO Unit Price × Ordered Quantity). Show as a positive number for savings."

What you get: An exact formula like =([@[Quoted Unit Price]]-[@[PO Unit Price]])*[@[Ordered Qty]] inserted into a new column labeled "Savings Captured."

Tips

• If a formula gives unexpected results, describe the issue to Copilot: "The formula is returning negative numbers for savings. That seems backwards, can you fix it?"
• Use Copilot to generate XLOOKUP formulas instead of VLOOKUP. They're more reliable and Copilot writes them correctly.
• Ask Copilot to "explain what this formula does" on any formula in your sheet. This is useful for inheriting a spreadsheet from a colleague.

Tool interfaces change. If a button has moved, look for the Copilot icon in the lower-right corner of the window or similar AI options nearby.