Use Copilot in Excel to build a weights-versus-limits check and compare its formulas with yours

What This Does

You keep a working check next to the portfolio system: which positions sit above the strategy's single-position limit, and which sector totals sit above the sector limit. Copilot in Excel can write the formula columns and highlight the breaches from a plain description. This guide builds that check on an invented file for "Strategy S", then compares Copilot's formulas and results with the formulas and results given here. The guide's formulas are the reference. Copilot is the assistant that gets you to the same place faster, and you confirm it got there.

The strategy's own guidelines and your firm's compliance or risk system are the record. This is a working check on invented data. Real holdings go into Copilot only if your firm approved it for that data.

Before You Start

• Excel for Microsoft 365 with Copilot. Microsoft's help page says Copilot might not be included with your Microsoft 365 subscription or available under your organization's settings. Look for the Copilot icon in the lower-right corner of Excel. If it is not there, the page points to its "Learn which Copilot license you have" link, and your IT admin can confirm the license. Pricing and plan details are on Microsoft's own pages.
• A blank workbook. Microsoft's page lists Copilot in Excel for Windows, Mac and the web with edit, plan and chat modes, while Excel on iPad and the phone apps offer fewer modes. Use a desktop or web version for this guide.
• A CCO answer on which AI tools the firm approved and how chats are kept, plus a look at your organization's data settings for Copilot.

Data caution. The file in this guide is invented, and that is deliberate. Real position weights are undisclosed holdings, and trade ideas, models, investor names, account numbers and fee terms sit in the same category. Material non-public information never goes into any AI tool. Keep real holdings out of Copilot unless the firm approved it for that data and your CCO allows it. If your holdings come from a licensed terminal or data service, the licence terms decide whether they can go into a workbook that Copilot reads. Column names and invented rows are a safe default.

Steps

1. Build the invented file

In a blank workbook, type these headers in row 1: A1 Holding, B1 Sector, C1 Weight, D1 Flag. Weight is percent of portfolio. Enter the invented rows below, with the weights as numbers.

The ten holdings total 95.0 and with cash the file totals 100.0.

Add the limits. In G1 type "Position limit" and in H1 type 12.0. In G2 type "Sector limit" and in H2 type 35.0. These limits are invented. Use your own strategy's guidelines for real work.

Then add the sector block. In F4 to H4 type "Sector", "Total weight" and "Flag". In F5 to F8 type S1, S2, S3 and S4.

2. Open Copilot and pick a mode

Select the Copilot icon in the lower-right corner of Excel. Microsoft's page says Copilot opens in edit mode by default, where it changes the workbook directly. Plan mode shows its plan first so you can confirm the approach before anything changes, and chat only mode answers without editing. For this guide, plan mode is a good habit because you see the approach before it touches your cells. To stop Copilot while it works, select the Stop button on the right side of the chat input field.

3. Ask for the position flag

Enter this prompt:

In D2:D11, show OVER LIMIT when the weight in column C is above the position limit in H1, and leave the cell blank otherwise. Use an absolute reference to H1. Do not change columns A to C or the limits in G1:H2.

When Copilot finishes, click into D2 and read the formula. The reference formula is:

=IF(A2="","",IF(C2>$H$1,"OVER LIMIT",""))

Copilot's wording may differ. What matters is the logic: the comparison is strictly greater than, so a weight exactly at the limit does not flag. If Copilot used "greater than or equal to", ask it to correct the formula, or paste the reference formula yourself and fill it down to D11.

4. Ask for the sector totals

Enter:

In G5:G8, total the weights in column C for each sector code in F5:F8 using the sector codes in column B of rows 2 to 11. In H5:H8, show OVER LIMIT when the total is above the sector limit in H2 and blank otherwise.

The reference formulas are, for G5 and H5:

=SUMIF($B$2:$B$11,F5,$C$2:$C$11)

=IF(G5>$H$2,"OVER LIMIT","")

Fill both down to row 8. The range stops at row 11, so the cash row is outside the sector totals, which is why they add to 95.0.

5. Ask for the highlighting

Enter:

Highlight rows 2 to 11 in A:D in light red where column D says OVER LIMIT, and highlight any cell in H5:H8 that says OVER LIMIT.

Microsoft's page says Copilot can apply conditional formatting. Check the rule in the usual Excel conditional formatting menu afterwards, so you know what it applies to.

6. Compare the results

Your workbook should show exactly this:

If anything differs, read the formula Copilot wrote and fix the logic. Then change Company G's weight to 12.1, confirm that D8 flags, and change it back to 12.0.

7. Use the same pattern on the real file, carefully

Rebuild the check in your own workbook only if the firm approved Copilot for that data. Otherwise copy the reference formulas by hand into your real file, which needs no AI at all. Either way, run the same six comparisons first on the invented file so you trust the logic.

Real Example

Scenario: Before the investment committee meets, you want a second look at weights against Strategy S's limits. You build the invented file and ask Copilot for the flags.

What you do: Paste the prompts from steps 3 to 5.

What you get: A Flag column and a sector block, with Company B and Company E flagged and S1 flagged. Company G, at exactly the limit, stays blank, and the other sector totals stay blank. The formulas are plain and readable, which is the reason to use them over a one-off answer in chat. Your performance system and compliance system remain the record.

Tips

• Ask for the formula, not just the answer. A chat reply of "Company B is over" cannot be audited. A formula in column D can.
• Go a step past the flag: ask Copilot to add a headroom column in E showing the position limit minus the weight (=IF(A2="","",$H$1-C2)). Company B should read -0.5, Company E -1.0 and Company G 0.0. A zero or negative number tells you more than a blank.
• Check totals with plain arithmetic: =SUM(C2:C11) should give 95.0 and =SUM(C2:C12) should give 100.0. If they do not, a weight was typed as text.
• Copilot lists and calculates what you point it at. It does not pick positions, sizes or limits. Those stay with you and the investment committee.

Tool interfaces change. If the Copilot icon has moved, look for a Copilot button near the lower-right corner of the Excel window.