In your apps · 5 min

Get Copilot to write the Excel formula for you

Excel, Copilot

Short answer: Format your data as a table in your workbook. Open Copilot and describe the new column in plain words, naming the columns to use. Review the formula and check a few rows before you trust it.

Last checked against Microsoft docs: Oct 21, 2026

You've seen people ask ChatGPT "what's the Excel formula for..." and paste it back. Here's how in Copilot: ask in Excel and Copilot adds the formula column right in your table.

A common situation (composite)

I need days overdue for each invoice.
I've searched for DATEDIF four times.
I just want the column.

What you get

Data: an Excel table.
Does: writes a formula and adds a column.
Output: a new calculated column.
How often: any time you need a new calculation.

Before you start

Needs: Microsoft 365 Copilot. Not sure what you have? Start here.
Where: Excel, Copilot.
Access: Copilot only uses content you already have permission to open. Use only information your company allows in Copilot.

The steps

Make sure the data is a table with clear column headers.
Open Copilot in the lower-right corner.
Paste the prompt with your column names.
Click a cell in the new column to read the formula. Check three rows by hand.

Copy this prompt

Add a column called [Days overdue] that shows how many days past [Due date] each row is, using today's date. Show 0 if it isn't overdue or if [Paid date] has a value. Explain the formula in one line.

(Prompt from stuckwithcopilot.com/guides/excel-formula-column/)

Data tip: use this with your work Copilot, and leave out passwords, payment details and anything your company marks restricted.

What a good result looks like

Three rows you check by hand match.

If a feature is missing

If Copilot can't see the data, check it's a table or a supported range: one header row, no blank or duplicate headers, no merged cells.

Make it smarter

Keep a short "What it got wrong" note. Paste it above the prompt next time, like "Don't invent dates".

Not sure what to ask? Copy the quick note for your Copilot admin.

Sales example: score your leads

A scoring column is just a formula with a few rules. Copilot writes it, you check five rows.

Scoring: give points for the traits your best customers share.
Full walkthrough: see rank your leads in Excel.

Prompt

Add a column called Score. Give 3 points if [Industry] is [list], 2 if [Company size] is over [number], and 2 if [Last activity] is within 30 days of today. Explain the formula in one line.

(Prompt from stuckwithcopilot.com/guides/excel-formula-column/)

Make it better

Run this next, in the same chat.

Add a second column that says Call today if Score is 5 or more and Last activity is within 14 days, and Later otherwise. Then sort by Score.

(Prompt from stuckwithcopilot.com/guides/excel-formula-column/)

Data tip: use this with your work Copilot, and leave out passwords, payment details and anything your company marks restricted.

Questions people ask

Get Copilot to write the Excel formula for you?

Format your data as a table in your workbook. Open Copilot and describe the new column in plain words, naming the columns to use. Review the formula and check a few rows before you trust it.

What do I need for this?

Microsoft 365 Copilot. If Copilot can't see the data, check it's a table or a supported range: one header row, no blank or duplicate headers, no merged cells.

Next step

Try it once, then make it yours.

Run the prompt on one real task this week. Then pick your next workflow.

See the workflow library ↗

Cite or share this guide

Get Copilot to write the Excel formula for you. Stuck with Copilot. https://stuckwithcopilot.com/guides/excel-formula-column/ Last checked Oct 21, 2026.

One Copilot idea a week

One idea a week that makes your job easier, makes you smarter, and gets your Copilot doing more for you. Something genuinely cool, not a hack. Built for people in sales, marketing and ops.

Official references

Last checked against Microsoft docs: Oct 21, 2026.