Data cleaning in Excel: what works for survey data, and where it breaks

Most survey teams clean data in Excel at some point. SurveyLoopr, SurveyCTO, ODK Central and KoboToolbox all export files that open in it, everyone on the team already knows it, and a pilot of a hundred interviews fits on a screen. For one person cleaning one short survey, that can be enough. The trouble starts when fieldwork runs for weeks, new exports arrive every day, several people work on the file, and someone later asks why a value changed.

What Excel does well for survey data

• Filters and sorting. Filter a column to see every value one enumerator typed, or sort incomes from largest to smallest to find the extra zero at the top. It is the fastest way to look at a variable.
• Conditional formatting. Colour duplicate values in an ID column, or numbers above a limit, without changing them. Microsoft's own guide to removing duplicate values suggests highlighting or filtering first, before anything is deleted.
• PivotTables. Interviews per enumerator per day, or the median length by team, in a few clicks. Microsoft's PivotTable guide notes that a PivotTable has to be refreshed when data is added to its source, which is easy to forget on day twelve.
• Data validation. Microsoft describes data validation as a way to restrict what people enter in a cell, such as a dropdown list. It helps most on sheets people type into, like a corrections log, and less on an export that arrives already filled in.
• Power Query. Load each export through a query instead of pasting it. According to Microsoft's overview of Power Query, the editor records each step, runs every step again when the query is refreshed, and leaves the original source unchanged. It is the nearest thing Excel has to a cleaning script.

Where Excel breaks during fieldwork

An edit overwrites what the enumerator recorded

Type 6000 over 60000 and the 60000 is gone from the sheet. Unless someone saved a copy first, nobody can see later what the field recorded, and a reviewer can't tell a deliberate correction from a slip of the keyboard while scrolling. In survey data the recorded value is the evidence; a correction should sit beside it, never on top of it.

Nothing records why a value changed

Excel for Microsoft 365 has Show Changes, which lists who changed a cell, where, when and what the previous value was, for the past 365 days. That is a real help. What it can't hold is the reason, which is the part a client, a reviewer or a journal asks about: “confirmed by phone with the household” is a correction, “looked too high” is not.

The work is redone on every export

During fieldwork a new export arrives every day. Pasted into the workbook, it brings back every value that was corrected in yesterday's copy unless someone carries each correction across by hand. Filters, formats and checks done by eye are done again, slightly differently each day and depending on who does them. Power Query repeats the recorded steps by itself, but a correction to one interview still has to live somewhere that survives the next refresh.

Checks across interviews are hard to build

A filter looks at one column. The checks that find the worst problems compare interviews with each other: the same household interviewed twice, interview length against the median, an enumerator whose answers vary less than the rest of the team's, interviews that are near-copies of each other. Some can be written as formulas; most teams stop at duplicates and a few ranges. The high-frequency checks guide lists the full set.

It is hard to share safely

The export holds names, phone numbers and GPS points, and every copy sent by email carries them. Versions multiply (“clean_v3_final”), and two people correcting two copies end with two datasets that no single record explains. Remove personal data before a file leaves the team; the guide to de-identifying survey data covers how.

Remove Duplicates deletes the evidence

Microsoft's guide says plainly that removing duplicate values deletes them permanently, and advises copying the original range to another sheet first. In survey data a repeated ID needs a decision, not a deletion: it can be one form sent twice, two households under one ID, or the same household interviewed twice. See duplicate survey responses for what to do with each.

How to clean survey data in Excel safely

If Excel is the tool you have, set the workbook up so that the raw data is never touched and every change has a reason. Four sheets are enough:

The corrections sheet is the record. Keep it as an Excel table with these columns, and use data validation on decided_by so names are spelled the same way every time:

Name the interview by the platform's own submission key, the column that is different for every submission, since your household ID can itself be wrong. Then add check columns to the right of the clean table. With the table named Clean, these formulas flag the common problems:

Repeated ID (TRUE when another interview has the same hhid)
=AND([@hhid]<>"", COUNTIF([hhid], [@hhid])>1)

Interview length in minutes (once start and end are Excel date-times)
=([@endtime]-[@starttime])*1440

Too short: under 40% of the median length
=[@minutes] < 0.4*MEDIAN([minutes])

Outlier: beyond 3 interquartile ranges from the quartiles
(remove don't-know codes such as -99 first, or they count as values)
=AND([@income]<>"",
     OR([@income] < QUARTILE.INC([income],1) - 3*(QUARTILE.INC([income],3)-QUARTILE.INC([income],1)),
        [@income] > QUARTILE.INC([income],3) + 3*(QUARTILE.INC([income],3)-QUARTILE.INC([income],1))))

To see every change at a glance, select the data area of the clean sheet, starting at B2, and add a conditional formatting rule with the formula =B2<>raw!B2 and a fill colour. Every cell that differs from the raw data lights up, logged or not. Then count the changed cells and compare the count with the rows in the corrections sheet:

Cells in clean that differ from raw (adjust the range to your data)
=SUMPRODUCT(--(clean!B2:Z2000<>raw!B2:Z2000))

If the count is higher than the number of corrections, someone changed a cell without logging it. Find the highlighted cell and either log it or put the raw value back. Read the flags with a PivotTable of flags per enumerator per day, divided by interviews done, so a busy enumerator doesn't look worse than a slow one (enumerator monitoring explains why).

When the next export arrives, add only the new interviews, matched by key, to the bottom of both raw and clean. Never paste a full export over the clean sheet: that is the step where corrections are lost. An interview edited on the server since your last export needs a decision of its own.

This holds up for one person and a few hundred interviews. Beyond that, the same structure is easier to keep as code: a Stata do-file that starts from the raw export and changes one value per line, with the reason beside it (see Stata high-frequency checks), or a tool that keeps the log for you. The guide to the best data cleaning tools for survey data compares the options, and data cleaning software for field surveys sets out what such a tool has to do.

Excel and DataFlow compared

Keeping Excel for supervisors: the corrections sheet

Field supervisors often work in Excel, and many have no reason to learn another tool. DataFlow keeps a round trip for them. On a problem in the Today list, Send to a supervisor… saves that check's open flags as an Excel file, one row per flagged case. Each row has the key, the date, the enumerator's ID and name, the place, the question, the recorded value and why it was flagged, then two pale yellow columns for the supervisor, Corrected value and Reason, and a column named “DataFlow flag (don't change)”. ID columns are ticked to go in by default and others can be added. A protected column of personal data goes in only when ticked, and reading it is logged. A second sheet, “How to fill”, tells the supervisor what to write:

• The right value in Corrected value, or the recorded value again if it is right.
• (blank) to leave an answer blank, or to confirm that an answer recorded empty is right.
• A reason on every row they fill, a value confirmed as right included.
• Nothing in Corrected value if they couldn't check: the case stays open.
• Rows can be sorted or deleted, as long as the DataFlow flag column stays.

Bring back corrections… reads the filled sheet, as Excel or CSV, matches each row to its flag and shows a preview row by row before anything changes. Rows are skipped, with the reason shown, when the corrected value or the reason is empty, the value changed since the sheet was made, the flag is already closed or isn't in this study, the question is protected, or the same flag appears on an earlier row. Interview length, GPS and don't-know flags can only be confirmed through the sheet, not corrected. Applying the sheet makes one bulk change: each correction with its row's reason, and each value confirmed as right with its note. The activity log keeps the file's name and SHA-256 fingerprint, and if the file changed after the preview, DataFlow asks you to look again. Undoing the change takes back every correction it made and reopens the flags it confirmed.

How DataFlow handles survey data cleaning

DataFlow is a desktop app that connects to SurveyLoopr, SurveyCTO, ODK Central and KoboToolbox, and imports CSV or Excel exports from any other tool. On each sync it adds the new interviews, adds any checks the form and data now call for, and runs the checks. An interview it already has is matched by its submission key and updated, so a new export never adds it twice.

The raw submissions are never modified. The clean data is rebuilt from them plus the corrections still in effect, so every correction can be undone. A correction can't be saved without a reason, and it records the old and new value, who made it and when. Every action goes into an activity log where each entry carries a hash of the one before, so editing past entries breaks the chain.

For the Excel habit of fixing a whole column at once, a bulk fix (“divide income by 10 where it is over 200,000”) is previewed value by value before anything is written, needs a reason that says why (“ok” or “fixed” is refused), and covers at most 5,000 interviews. One you set up yourself that covers more than 50 interviews needs a spot check first: five of them, picked at random, are decided one by one, and if any isn't right for the change, it isn't made. Blank answers aren't filled with a value nobody recorded unless the person says so knowingly. The whole change is kept as one entry and undone as one.

A download gives the data as Stata .dta, CSV, Excel (with a codebook sheet) or R, de-identified unless personal data is unlocked for it, which is logged. Every download includes corrections.csv, with each correction's old and new value, reason, who and when, undone ones included, and, when there are any, a list of the bulk changes. With the do-file option, on by default, it adds the raw data as received and a Stata do-file that replays every correction from it.

Step by step

1. Keep the raw export untouched. Put each export on a raw sheet and never edit or sort it. New interviews go at the bottom, matched by the platform's submission key.
2. Start a corrections log. One row per change: interview key, variable, old value, new value, reason, who decided and the date.
3. Correct on a copy. Make a clean sheet as a copy of raw, filter it instead of sorting it, and make each correction there at the same time as you log it.
4. Highlight every change. Add a conditional formatting rule that colours any clean cell different from raw, and check that the count of changed cells matches the log.
5. Add check columns. Flag repeated IDs, interviews under 40% of the median length and outliers beyond 3 interquartile ranges with formulas, and summarise the flags by enumerator in a PivotTable.
6. Add new exports without pasting over. Append only new interviews to raw and clean, so corrections already made are not lost.

Questions