Track advertising budget lines and insertion orders with Copilot in Excel

What This Does

You keep one table with a row for every budget line or insertion order, and Copilot adds the working columns around it: what is left to spend, a summary by promotion code, and a highlight on any line where invoices have passed the approved figure. The numbers in the table stay yours. Copilot builds the formulas, and you check them.

Before You Start

• Excel is open on a desktop, Mac or web version. Microsoft's Copilot in Excel page lists edit, plan and chat modes on those versions.
• You can see the Copilot icon in the lower-right corner of Excel. If you cannot, Microsoft's page says Copilot may not be in your subscription or may be switched off by your organization. Ask IT, and check Microsoft's licensing pages rather than guessing at a plan.
• You have the approved figures from finance or from each signed insertion order or estimate. Copilot never supplies an amount.
• You know your company's AI policy. Budgets and vendor rates are confidential, so use promotion code names and vendor codes ("Agency B", "Printer C") in the table, not real names, if your policy asks for that.

Steps

1. Set up the table by hand

Type these six headers in row 1 and fill in the rows yourself: Promo (code), Vendor (code), Line (short label), Approved, Invoiced To Date, Status. Approved comes from finance or the signed order. Invoiced To Date comes from the invoices you have matched. Status is a short word such as Open, Closed or Disputed. Microsoft's page does not say the data must be a formatted table or saved to a particular location, but a clean block of rows with one header row gives Copilot the least to guess at.

2. Open Copilot and choose a mode

Select the Copilot icon in the lower-right corner of Excel. Microsoft's page says it opens in edit mode by default, where Copilot changes your workbook directly. Plan mode shows a plan for you to confirm before anything changes, and chat mode answers in the pane without touching the workbook. For your first run, pick plan mode or chat mode so you see what Copilot intends. Switch to edit mode once you trust the pattern. You can stop Copilot at any point with the Stop button next to the chat box.

3. Ask for the columns, the summary and the highlight

Send these as separate prompts:

1. "Add a column called Remaining that equals Approved minus Invoiced To Date for each row. Do not change any other cell."
2. "Create a PivotTable on a new sheet that shows the sum of Approved, Invoiced To Date and Remaining for each Promo (code)."
3. "Apply conditional formatting that highlights the whole row when Invoiced To Date is greater than Approved."

Microsoft's page lists formulas, PivotTables and conditional formatting among the things Copilot in Excel does.

4. Verify before you rely on it

Click into the Remaining column and read the formula in the first row and the last row. It should subtract the invoiced cell from the approved cell in the same row. Then total the Approved column yourself, compare it with finance's figure for each promotion, and compare each line with its order or estimate. If a number differs, fix the source cell by hand. Do not ask Copilot to type or correct an amount.

Real Example

Scenario: Two promotions, SPRING-A and FALL-B, share vendors. All names and amounts below are invented for illustration and carry no currency symbol.

What you do: Run the three prompts from step 3.

What you should get: Remaining of 8,000, negative 500, 25,000, 9,000, 0 and 4,000 down the rows. The Printer C shelf talker row is highlighted because 12,500 is more than 12,000. The Media D banner row shows nothing invoiced yet. The PivotTable should show SPRING-A at 67,000 approved, 34,500 invoiced and 32,500 remaining, and FALL-B at 53,000 approved, 40,000 invoiced and 13,000 remaining. If your PivotTable differs from those sums, one of the formulas or the table range is wrong.

Tips

• Keep this workbook in your company's Microsoft 365 storage. The amounts do not belong in a free chatbot or in any other tool's sheet.
• Ask Copilot about the highlighted row only after you have read the invoice. An overage can be a late credit, a rate change or a typo, and Copilot cannot tell which.
• Save a copy of the workbook before the first edit-mode prompt, so you can compare the two if something changes that you did not expect.

Tool interfaces change. If a button has moved, look for similar Copilot options in the same area of the ribbon or the lower-right corner.