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 is formatted as an Excel Table (Insert → Table)
• You're logged into Microsoft 365 with Copilot enabled
• Time needed: 5-10 minutes
• Cost: Included in eligible Microsoft 365 plans

Steps

1. Open Copilot in Excel

Click the Copilot button in the Home ribbon (right side, sparkle icon). The Copilot panel opens on the right.

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 sparkle icon in the Home or Insert tabs.