Recurring reports drift when each update brings a slightly different formula, number format, chart, or summary layout. Copilot can help with the edit, but it needs clear workbook instructions—and those instructions still need a human review.
Microsoft’s workbook-rules feature uses a visible worksheet named .Rules. The important setup detail is easy to miss: put each rule in column A. Do not build the instructions as a multi-column policy table and assume Copilot will read the other columns.
Quick Answer: create a visible .Rules sheet and list rules in column A
Insert a worksheet named .Rules. Keep it visible. Starting in A1, enter one clear instruction per cell in column A.
Then use a narrow request such as:
Read the visible .Rules sheet. Update tblSales with this month's rows and update the Summary sheet. Keep existing formulas unless a rule says otherwise. Propose any chart changes for review.Important: workbook rules are guidance, not protection or enforcement. Ask Copilot to follow the rules, then check the changed cells, formulas, formats, and charts before sharing the report.
Copy-ready .Rules template
Enter the following text in .Rules!A1:A10. Each line is a separate cell in column A. Keep the sheet visible; hidden .Rules sheets are not used by Copilot.
Only edit the Data and Summary sheets. Do not edit Archive.
Add new source rows inside the existing tblSales Excel Table.
Do not rename sheets, tables, columns, named ranges, or validation lists.
Summary revenue must use =SUM(tblSales[Amount]).
Summary margin equals (total revenue - total cost) / total revenue.
If total revenue is zero, return Check revenue instead of a margin.
Display currency as $#,##0;($#,##0).
Display percentages as 0.0%.
Keep the existing chart type and source range. Propose chart changes for review.
A human must verify totals, one margin calculation, and chart ranges before sharing.You may use column B for your own notes, owner names, or examples. However, keep the actual instructions in column A. Do not convert the rule list into an Excel Table unless your tested workbook setup still leaves every rule as a clear value or formula result in column A.
Why workbook rules reduce report drift
A prompt describes today’s task. A .Rules sheet records the standard for every monthly update: what may change, how key measures are calculated, and what needs review.
Microsoft documents workbook rules, including rules written as text in column A and formula-driven rules that return guidance. See Copilot in Excel workbook rules for the current product guidance.
Use the rules as an operating checklist, not as a substitute for workbook controls. Protection, data validation, stable Tables, and worksheet formulas remain the reliable Excel tools for enforcing structure and calculation logic.
Complete monthly-report example
Assume your Data sheet contains an Excel Table named tblSales with these columns.
| Month | Region | Amount | Cost |
|---|---|---|---|
| April | East | $6,000 | $4,200 |
| April | West | $4,500 | $3,150 |
| April | North | $5,000 | $3,500 |
On Summary, set up maintained formulas rather than asking Copilot to recreate the calculations each month.
Enter this in B2 for total revenue:
=SUM(tblSales[Amount])Enter this in C2 for total cost:
=SUM(tblSales[Cost])Enter this in D2 for total margin:
=IF(B2=0,"Check revenue",(B2-C2)/B2)| Summary cell | Expected result |
|---|---|
B2 |
$15,500 |
C2 |
$10,850 |
D2 |
30.0% |
The margin formula calculates from totals: ($15,500 - $10,850) / $15,500. It does not average individual row percentages, which can distort the overall result.
Use Copilot in a controlled workflow
1. Define stable workbook objects first
Use meaningful names such as Data, Summary, Archive, and tblSales. Excel Tables are a strong default for recurring data because formulas and formats can extend with new rows. If needed, first set up auto-expanding Excel Tables.
2. Ask for one bounded change
Avoid prompts such as “clean up the workbook.” Name the source, destination, and limits instead. Use these Copilot prompt patterns for Excel when you need a repeatable request structure.
Read the visible .Rules sheet. Add the supplied monthly records to tblSales. Check that Summary formulas still reference tblSales. Apply the required number formats. Do not make chart changes; describe any chart issue for review.Where your Copilot experience offers planning before editing, review the proposed steps before accepting a multi-step change. Microsoft describes Copilot edit, plan, and chat experiences in its getting started guidance and explains workbook editing in Edit with Copilot in Excel.
3. Recalculate or refresh deliberately
For normal worksheet formulas, check that calculation is up to date after the edit. In desktop Excel, press F9 to recalculate if needed. If the workbook uses queries or connections, use Data > Refresh All where that refresh is configured.
For a controlled refresh of a connection-based report, use the workbook’s established refresh process and then verify the Summary results. Do not treat a Copilot edit as proof that external data refreshed.
4. Verify the report before publishing
Check one source amount, the total, one percentage, and the chart range. Also inspect the formula bar in key Summary cells; a visible result can look correct while referencing the wrong range.
What belongs in .Rules, and what belongs in Excel
| Put this in .Rules | Build this into the workbook |
|---|---|
| Allowed sheets and requested edit boundaries | Core formulas and structured references |
| Required labels, formats, and chart conventions | Excel Tables and named ranges |
| Approved calculation method | Data validation and worksheet protection |
| Items that must be proposed or reviewed | Conditional formatting and audit checks |
A rule states intent. A formula performs the calculation. Keep essential logic in cells so it works consistently for Copilot, coworkers, and manual updates.
Availability and limits to check first
- Copilot features depend on your eligible Microsoft 365 license, account, and Excel environment.
- Edit, plan, and chat options can differ by platform and rollout. Check the current Microsoft guidance linked above for your version.
- A visible
.Rulessheet helps provide guidance; it does not stop a user or Copilot from making an incorrect change. - Keep a version or separate copy before accepting a broad workbook edit, especially for shared or high-risk reports.
Troubleshooting
| Problem | Cause | Direct fix |
|---|---|---|
| Copilot appears to ignore the rules | The sheet is hidden, named differently, or rules are not in column A | Name the sheet .Rules, keep it visible, and place one instruction per cell in column A. |
| The instructions are in a multi-column table | The rule text is mainly in columns B or C | Move each actionable instruction to column A. Keep optional documentation outside the actual rule list. |
| Margin shows 0.0% | Revenue is zero, or an error was hidden with IFERROR |
Use =IF(B2=0,"Check revenue",(B2-C2)/B2) when zero revenue needs review. |
| Totals omit new records | The source is a fixed range | Add records to an Excel Table and use =SUM(tblSales[Amount]). |
| A chart changed unexpectedly | A broad request allowed visual redesign | State “do not change charts” in the prompt and rules, then restore or review the chart manually. |
Recurring report verification checklist
- Rules:
.Rulesis visible and each instruction is in column A. - Scope: Only the intended sheets, Tables, and report blocks changed.
- Data: New rows are inside the correct source Table.
- Formulas: Totals and ratios use the stated method and intended fields.
- Formats: Currency, dates, percentages, decimals, and negatives match the rules.
- Visuals: Chart type, title, and source range remain correct.
- Review: A human checks exceptions before the report is shared.
For a wider review process, use the safe Copilot workflow checklist. If your report uses color-based signals, document the required result and maintain the underlying rule with Excel conditional formatting.
FAQ
Does a .Rules sheet replace a good Copilot prompt?
No. The sheet holds standing standards. Your prompt should still name the current task, data period, and allowed scope.
Can I hide the .Rules sheet after setup?
No. Keep it visible if you want Copilot to use it.
Should I put every formula in .Rules?
No. Keep essential formulas in the report itself. Use .Rules to document the approved calculation method and review requirements.
Pingback: Excel’s =COPILOT Function Is Retiring: Replace AI Classification Formulas Without Losing Auditability