Inherited workbooks rarely arrive with a map. You may see a monthly report, several hidden-looking helper columns, and formulas copied down hundreds of rows—but no clear explanation of what drives the final numbers.
You can use Copilot to document Excel formulas faster, especially when a formula is long or the workbook has changed hands. But treat its response as a draft. The reliable workflow is to ask Copilot for a plain-English explanation, then verify the actual references, hard-coded assumptions, and downstream outputs in Excel.
Quick Solution: create a verified formula inventory
Start with the formulas that feed visible reports, totals, dashboards, or decision-making cells. Do not try to document every copied formula first.
- Select an important formula cell and ask Copilot to explain what it calculates, its inputs, and any assumptions it can identify.
- Paste the response into a documentation sheet as a draft description.
- Use Formulas > Trace Precedents and Trace Dependents to confirm where the formula gets its values and where its result is used.
- Check for typed numbers, named ranges, and table references in the formula.
- Record the verified result in a short formula inventory.
Microsoft says Copilot in Excel can explain formulas in selected cells, including formulas in large or inherited workbooks. Microsoft also advises reviewing, editing, and verifying AI-generated output, which is exactly the right rule for workbook documentation. See Microsoft’s Copilot data insights guidance.
What to record for each key formula
Create a new worksheet named Formula Map. One row per distinct business formula is usually enough at first. If the same formula is filled down a table, document the pattern once and note the range it covers.
| Location | Formula | Verified purpose | Inputs and assumptions |
|---|---|---|---|
| Summary!F12 | =SUMIFS(Sales[Net Sales],Sales[Region],$B$4,Sales[Month],$B$5) |
Total net sales for the selected region and month | B4 is region; B5 is month; Sales table must contain matching values |
| Sales!H2:H500 | =IF(G2="","",G2*0.08) |
Commission at 8% of eligible sales | Hard-coded rate 0.08; blank sales return blank commission |
This layout separates what the formula appears to do from what you have checked. It also makes embedded assumptions visible to the next owner.
How to use Copilot without making it the source of truth
Select one formula cell at a time, beginning with a report total or a calculation you cannot explain quickly. Ask a specific question rather than requesting a vague workbook summary.
- “Explain this formula in plain English. List each input cell, table column, named range, and any typed constants.”
- “What changes if the value in B4 changes? What output cells might be affected?”
- “Draft a one-sentence description for a formula inventory. Mark anything you cannot determine from the formula alone.”
Copilot is useful here because it can turn syntax into a readable first draft. Its workbook changes remain editable in Excel, but that does not make an explanation automatically correct or complete. Keep control of the workbook and verify the draft before labelling it final; Microsoft describes this editable, user-controlled approach in its Copilot in Excel getting-started documentation.
Document Excel formulas: a worked example
Suppose cell Summary!F12 contains this formula:
=SUMIFS(Sales[Net Sales],Sales[Region],$B$4,Sales[Month],$B$5)Copilot may draft: “This formula adds net sales for the region and month selected on the Summary sheet.” That is a helpful start. Now verify it.
1. Read the formula structure
| Part | Meaning |
|---|---|
Sales[Net Sales] |
The values being added |
Sales[Region] and $B$4 |
Only rows whose Region matches the selected region qualify |
Sales[Month] and $B$5 |
Only rows whose Month matches the selected month qualify |
The dollar signs make B4 and B5 absolute references. If the formula is copied, those input cells stay fixed. The table references are structured references; they expand with the table and are usually easier to audit than ordinary column ranges. Use this guide to understand Excel structured references if the table syntax is unfamiliar.
2. Verify the inputs in the sheet
Go to Summary!B4 and Summary!B5. Check what users can enter or select there. A formula can be technically correct but still return the wrong business answer if a month is stored as text in one place and as a real date in another.
Then select F12 and choose Formulas > Trace Precedents. Excel draws arrows to the direct referenced inputs. Follow any worksheet-arrow icon to inspect references on other sheets.
3. Verify the output path
With F12 still selected, choose Formulas > Trace Dependents. If the cell feeds a dashboard KPI, a chart helper cell, or another summary calculation, add that to the inventory. This tells the next owner why the formula matters.
For difficult nested logic, use Formulas > Evaluate Formula to step through the current calculation. For a fuller process, see how to audit a complex Excel formula.
Find assumptions Copilot may not flag clearly
A formula explanation can describe arithmetic correctly while missing the business rule hidden inside it. Look specifically for these items.
| What to inspect | Example | What to document |
|---|---|---|
| Typed number | =G2*0.08 |
Commission rate is fixed at 8% |
| Typed text criterion | =COUNTIF(D:D,"Open") |
Only the exact status “Open” is counted |
| Named range | =Revenue*TaxRate |
What TaxRate refers to and where it is maintained |
| Error handling | =IFERROR(XLOOKUP(...),0) |
Missing matches become zero, potentially hiding data issues |
Use Formulas > Name Manager to inspect named ranges. For typed constants, search formula cells and review the formula bar; a separate check for hard-coded numbers in Excel formulas is especially valuable in financial and operational models.
Use native auditing tools for evidence
Copilot can help you read a formula. Excel remains the better tool for proving the workbook’s actual calculation path.
| Tool | Use it to verify | Practical note |
|---|---|---|
| Trace Precedents | Cells and ranges that feed the selected formula | Repeat to follow a multi-step chain |
| Trace Dependents | Cells that use the selected result | Useful for prioritizing high-impact formulas |
| Show Formulas | Formula patterns across a worksheet | Use Ctrl+` to toggle; do not edit while reviewing unless intended |
| Evaluate Formula | How Excel calculates the current result | Best for nested IF, lookups, and text logic |
| Name Manager | Definitions behind named ranges | Check whether names point to the expected sheet and range |
If you are testing an AI-suggested rewrite rather than just documenting an existing formula, test it beside the original with known cases first. This Copilot formula testing workflow helps keep an explanation exercise from becoming an unreviewed model change.
Common documentation mistakes
| Issue | Cause | Solution |
|---|---|---|
| Description says “total sales” | It omits criteria and period filters | Name the measure, each criterion, and the input cells |
| One copied formula is documented 500 times | Documentation follows cells, not patterns | Document the first formula and its fill range, then note exceptions |
| A zero is treated as a genuine result | IFERROR masks a missing lookup or broken reference |
Test a known missing value and record the intended error behavior |
| Copilot’s wording is accepted unchanged | The explanation was not checked against the workbook | Confirm precedents, dependents, names, and constants before approval |
| Documentation goes stale next month | No owner or review point is assigned | Add an owner and review the Formula Map when the workbook process changes |
Final check before you hand over the workbook
- Have you documented every formula that feeds a decision, report, or external file?
- Does each description name the output, inputs, criteria, and any embedded assumption?
- Have you traced both precedents and dependents for high-impact cells?
- Have you checked named ranges, external references, and error-handling formulas?
- Is each Copilot-generated description marked as verified only after you checked it in Excel?
The best result is not a long AI summary. It is a short, maintained Formula Map that lets the next owner identify what matters, what can change, and what must be tested.