Use Excel Copilot to Analyze Data and Write Formulas
What This Does
Copilot in Excel lets you ask data questions in plain English ("which projects are over budget?"), generate complex formulas from a description, and get a narrative summary of what your data shows, without writing a single formula yourself.
Before You Start
- You have Microsoft 365 with Copilot enabled (Business or Enterprise plan)
- Your data is in an Excel Table format (click anywhere in your data → Insert → Table). Copilot requires Table format
- You have Excel open (desktop or web)
Steps
1. Format your data as a Table
Click anywhere inside your data. Go to Insert → Table (or press Ctrl+T). Check "My table has headers" and click OK.
What you should see: Your data gets alternating row colors and filter dropdowns appear in the header row. Troubleshooting: Copilot won't work on plain cell ranges. The Table format is required.
2. Open Copilot
Click the Copilot button in the Home ribbon (sparkle icon). The Copilot panel opens on the right.
What you should see: A chat interface with example prompts like "Show data insights" or "Highlight rows where..."
3. Ask a data question in plain English
In the Copilot chat box, type:
"Which projects have completion percentage below 50% and a go-live date within the next 30 days?"
or:
"Show me the average story points per sprint grouped by development team."
What you see: Copilot returns the answer or inserts a new sheet with the filtered/sorted view.
4. Generate a formula
Click on an empty cell where you want the formula. In Copilot, type:
"Write a formula that calculates days between the Start Date in column C and today, and shows 'Overdue' in red if it's more than 30 days."
What you get: Copilot writes the formula and optionally suggests conditional formatting rules.
5. Get a data summary
Ask Copilot:
"Summarize the key insights from this requirements tracking data. What patterns stand out?"
What you get: A 3–5 sentence narrative summary of what the data shows, useful for pasting into a status update.
Real Example
Scenario: You have a requirements tracking spreadsheet with 120 rows: requirement ID, priority, status, assigned team, and estimated completion date.
What you type:
"Highlight all High priority requirements that are still In Progress and past their due date. Then give me a count by team."
What you get: Red highlighting on overdue high-priority rows, plus a new summary table showing each team's count of overdue items, ready to screenshot for your steering committee deck.
Tips
- Always convert your data to a Table first. Copilot is much more accurate with properly formatted tables.
- If you want a formula explained, paste it into Copilot and ask "What does this formula do?" This is useful for understanding formulas left by previous analysts.
- For requirements traceability matrices, ask: "Add a column that shows 'Covered' if a test case exists in column G, otherwise 'Uncovered.'"
Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.