# Write complex Excel formulas with Copilot, then prove they work

> How r/excel users actually use Copilot for formulas: describe each field, take the draft as a starting point, test it on awkward rows, and check every reference before filling down.

- **Category:** [Excel](https://stepgleam.com/categories/excel)
- **Difficulty:** Intermediate
- **Time:** ~30 minutes
- **Tools:** Copilot in Excel, Excel (Microsoft 365)
- **Reliability score:** 80/100
- **Steps:** 6
- **Last updated:** 2026-10-26
- **Published:** 2026-10-20

## Tools used

- [Copilot in Excel](https://support.microsoft.com/en-us/excel/copilot/get-started-with-copilot-in-excel) — Suggests and explains formulas; flags issues
- [Excel (Microsoft 365)](https://support.microsoft.com/en-us/excel) — Where the data and formulas live

## What you'll get

A formula you have tested on edge-case rows, with references you have traced, filled down only after it passes.

## Who this is for

Excel users who can read a formula but would rather not hand-build long nested ones.

## Before you start

On r/excel, people who use Copilot for formulas are blunt about it. It *"rarely gets it right first time"*, it sometimes *"picks the wrong reference cells"*, and one user said it once invented a function that doesn't exist. They still use it, as a starting point they then check. This guide follows that pattern, plus a practitioner's test-before-fill-down method.

## Steps

### Step 1: Describe each field and the result you expect

**Tool:** Copilot in Excel

Open Copilot (**Home → Copilot**) and explain what each relevant column contains and what the formula should produce, including what to do with blanks. One user says writing the prompt takes a while because they *"contextualise the data in each field"* and spell out the expected result. Another found it works best when you pass a cleaned range and a defined rule set rather than the whole chaotic sheet.

**Why this works:** Copilot fills gaps with guesses. Every field you describe is one less guess.

**Prompt:**

```
Column B is the deal value (number, sometimes blank). Column C is the region (text). In D, return the commission: 5% under 50,000, 7% from 50,000 to 99,999, 10% at 100,000 or more, and blank if B is blank. Suggest a formula and explain how it works.
```

[Open step 1](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-1)

### Step 2: Treat the suggestion as a draft, not an answer

**Tool:** Copilot in Excel

Read the explanation Copilot gives with the formula. One user says it is *"pretty good at getting close and putting me in the right track, but not accurate enough to produce the end result"* on complex asks. Microsoft's own page for this feature says to *"review, edit, and verify anything Copilot creates."*

**Why this works:** Expecting a draft keeps you checking instead of trusting.

[Open step 2](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-2)

### Step 3: Test it in a temporary column on 5 to 10 awkward rows

**Tool:** Excel

Leave the original column alone. Insert a temporary **Test result** column and pick 5 to 10 rows that include normal, blank, zero, error and boundary cases (for example exactly 50,000 and 100,000). Enter the formula in the first test row and compare every result with the answer you expect.

**Why this works:** The rows that break formulas are blanks, exact cutoffs and numbers stored as text, not the first normal row.

[Open step 3](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-3)

### Step 4: Trace every reference the formula uses

**Tool:** Excel

Select the test cell, press **F2** to see highlighted ranges, and use **Formulas → Trace Precedents**. Confirm each reference points at the column you meant. r/excel users report Copilot *"frequently seems to just pick the wrong reference cells even when explicitly told which to use."*

**Why this works:** A wrong reference produces plausible numbers, which is the most dangerous kind of wrong.

[Open step 4](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-4)

### Step 5: When it breaks, ask Copilot to troubleshoot, not rewrite

**Tool:** Copilot in Excel

If a long formula fails, paste it back and ask what is wrong. One user says this is where it shines: it can quickly find *"where you may have missed a comma"*, and it is *"pretty good at finding what is wrong, not great at providing solutions"* as complexity grows.

**Why this works:** Narrow debugging requests get better answers than "write it again".

**Prompt:**

```
This formula returns #VALUE! on row 14, where B14 is "50,000" stored as text. Explain why and give the smallest fix: <paste formula>
```

[Open step 5](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-5)

### Step 6: Fill down only after the test rows pass

**Tool:** Excel

When every test row matches, fill down or convert to a calculated column, then delete the test column. One user who relies on Copilot for large formulas says they know enough to *"check it works manually and how to read and interpret what it's doing"*. That is the bar to meet before you ship it.

**Why this works:** Filling down an unchecked formula multiplies one error across thousands of rows.

[Open step 6](https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot#step-6)

## Pro tips

- Keep your test rows on a small QA sheet if the formula feeds a recurring report.
- If Copilot suggests a function you don't recognise, check that it exists in your Excel version before using it.

## Common mistakes

- Testing only the first row.
- Overwriting the original column before comparing results.
- Trusting the explanation text without tracing the ranges.

## FAQ

### Where is Copilot in Excel?

Select the Copilot icon in the ribbon or the lower-right corner of Excel. It needs an eligible Microsoft 365 plan.

### Is Copilot good enough to skip learning formulas?

The r/excel users quoted here say no. It speeds you up if you can read and check what it produces.

---

Canonical guide: https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot
Read this guide as Markdown: https://stepgleam.com/guides/write-complex-excel-formulas-with-copilot.md