Use Copilot in Excel to draft growth-rate and index formulas, then check them

What This Does

Growth rates, rebased indexes and real (deflated) series are formulas you have written a hundred times, and a slip in one reference can sit unnoticed in a long workbook. Copilot in Excel can draft those columns from a plain request. You then compare every inserted formula with the correct one, and every result with a value you worked out by hand. The sheet is the record. Copilot is a typing aid.

Before You Start

• You use Excel with a Microsoft 365 plan that includes Copilot. Microsoft's page says Copilot may not appear if it is not included with your subscription or is turned off by your organization's settings. The page links to a "Learn which Copilot license you have" article. If the icon is missing, ask IT or check Microsoft's plan page. No price or plan name is given here, because it depends on your firm's agreement.
• You work on the invented practice series below, or on a public series you have logged. Client workbooks and non-public data stay out of this exercise unless IT and the engagement terms allow Copilot to process them. On litigation matters, ask counsel before Copilot touches a workbook. Check your firm's settings for how Copilot prompts and workbook content are handled.
• You know the answers in advance. This guide gives the correct results, so you test the tool, not the other way round.

Steps

1. Build the practice layout

Open a new workbook and type this table in A1:C6. All of it is invented, and Series A is nominal.

Put these headers in row 1 of the next columns: D "YoY growth", E "Rebased (2021 = 100)", F "Real Series A".

2. Open Copilot

Select the Copilot icon in the lower-right corner of Excel. Microsoft's page says Copilot in Excel offers edit, plan and chat modes, and opens in edit mode by default. Edit mode changes the workbook directly. Plan mode shows a plan first so you can confirm the approach before it edits. Chat mode answers in the pane and leaves the workbook alone.

For this exercise, choose plan mode if your version shows it. Read the plan before you let Copilot edit anything. To stop or pause at any time, select the Stop button at the lower-right corner of the chat input field.

3. Ask for the growth column

Type a request that names the cells, so Copilot has nothing to guess:

In D3:D6, calculate year-over-year growth of Series A in column B as the current year divided by the previous year, minus 1.
Leave a cell blank if either of the two values is blank.
Format column D as a percentage with two decimals.

4. Ask for the rebased and real columns

In E2:E6, rebase Series A to the first year: each value divided by the value in B2, times 100. Use an absolute reference to B2.
In F2:F6, calculate real Series A as column B divided by (column C divided by 100). Leave a cell blank if B or C is blank.

Microsoft's page says Copilot can generate data and formulas, perform calculations and apply formulas across multiple sheets. It does not promise any particular formula text, so expect different wording from run to run.

5. Read each formula before you trust it

Click the first cell in each new column and read the formula bar. The formulas you are checking against are:

A formula that looks different can still be right. Judge the logic, then judge the results in the next step. Common slips are a base cell that moves when the formula is filled down, a growth rate divided by the wrong year and an index multiplied where it should divide.

6. Check every result against the known values

Compare each cell. Round only what you display, not what you compute.

Optional check: compound annual growth from 2021 to 2025 is =(B6/B2)^(1/4)-1, which gives about 5.20 percent. If any value differs, open the formula, find the reference that is off and fix it by hand. Do not ask Copilot to fix its own error and then accept the new numbers without redoing this check.

Real Example

Scenario: You are updating a workbook for a project lead and need year-over-year growth and a real series on a public series you logged last week. Before you let Copilot near it, you run the same requests on the invented practice series.

What you type: The three requests from steps 3 and 4.

What you get: Three filled columns. In the practice sheet, D3 should show 5.56%, because 190 divided by 180 is 1.0556. F6 should show 210.00, because 220.5 divided by 1.05 is 210. Suppose Copilot returns 231.525 in F6. That is 220.5 multiplied by 1.05, so the formula multiplies where it should divide. You catch it only because you knew the answer.

Tips

• Ask for one column at a time while you build trust. A long multi-step request makes the review harder.
• Keep a copy of the formula text you accepted in your methods notes, so a reviewer sees what the sheet does without opening Copilot.
• Copilot never supplies the numbers. The data in columns A to C comes from your logged source, and every result comes from the cells.

Tool interfaces change. If a button has moved, look for similar Copilot options in the same menu area.