Use Excel Copilot to Analyze Data and Write Formulas
For Business Analyst / Process Improvement Analysts ·
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 has one clear header row with descriptive column names (an Excel Table is helpful but no longer required)
- You have Excel open (desktop or web)
Steps
1. Format your data as a Table (optional but helpful)
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. Good to know: Copilot now reads plain cell ranges too, as long as there is one header row with clear column names and no merged cells or blank rows. A Table just keeps ranges stable as rows are added.
2. Open Copilot
Click the Copilot icon in the lower-right corner of the Excel window. The Copilot panel opens on the right. Microsoft moved Copilot off the Home ribbon in 2026, so the corner button is now the entry point; press F6 to reach it from the keyboard, or right-click it and choose Move to ribbon if you prefer the old spot.
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
- Clear, descriptive column headers matter most. Copilot reads them to understand your data, so "Estimated_Completion" beats "Col_E" every time.
- 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.