Use Excel's Copilot to Clean and Recode Survey Data

What This Does

Excel's Copilot can write the formulas you need to recode variables, remove speeders, flag attention check failures, and merge data columns — without you having to remember the right Excel syntax.

Before You Start

• You have Microsoft 365 with Copilot enabled (check with your IT department or plan)
• Your raw survey data is open in Excel as a spreadsheet
• Data is in a table format with column headers in row 1

Steps

1. Find the Copilot button

Look for the Copilot button in the Excel ribbon — it's in the Home tab on the right side, or search for "Copilot" in the ribbon search bar. Click it to open the Copilot panel on the right side of your screen.

What you should see: A chat panel opens on the right with a text input box at the bottom.

2. Describe what you want to do

Type your data task in plain English. No need to know the formula — just describe the outcome you want.

What you should see: Copilot generates a formula and shows a preview of what it will do.

3. Review and insert the formula

If the preview looks right, click Insert to add the formula to a new column. If it's wrong, describe the correction: "That's close, but I need it to return 'exclude' instead of 0."

4. Apply for other cleaning tasks

Repeat for each cleaning step: removing speeders, creating banner variables, merging dataset columns.

Real Example

Scenario: You have 800 raw survey responses in Excel. Column D is survey duration in minutes. You need to flag anyone under 3 minutes as a speeder. Column K is your NPS score (0-10) and you need to recode it to Promoter/Passive/Detractor.

What you type in Copilot:

1. "In column E, create a formula that marks respondents in column D as 'speeder' if their value is less than 3, otherwise 'valid'"
2. "In column L, create a formula that recodes column K: 9-10 = Promoter, 7-8 = Passive, 0-6 = Detractor"

What you get: Working IF/IFS formulas inserted into columns E and L, applied across all 800 rows automatically.

Tips

• Be specific about column letters: "column D" is clearer than "the duration column"
• If you want the formula explained, ask: "What does this formula do, in plain English?"
• For complex VLOOKUP or INDEX/MATCH tasks, describe both the lookup table and the target table so Copilot understands the structure

Tool interfaces change — if Copilot has moved, look for similar AI/Copilot options in the Home or Insert tabs.