Coding Guide and Data Cleaning in Excel for Action Research

Most errors in an action research report start before the analysis: a score typed as 77 instead of 7, a “G” where the code should be 1, a learner entered twice. The Department of Education’s (DepEd) Action Research Toolkit covers this step in section 5.1, Preparing Data for Analysis: levels of measurement, sorting and cleaning, and the coding guide. Below are the toolkit’s own example, how to set up and check the data in Excel, four planted errors and what each does to the results, and lessons from cleaning DepEd’s public data.

It is written for teachers encoding test, survey and observation data for action research, and for school heads and research committees who check the results.

Key points

• Write the coding guide before you encode. It lists each variable’s column, short name, full label, codes, and level of measurement.
• One row per participant, one column per variable, one value per cell, codes instead of words. Keep an untouched copy of the raw file.
• Prevent errors at entry. Excel’s Data Validation can refuse a score above 10 or a gender code other than 1 or 2.
• Then check every column: range (MIN, MAX), blanks (COUNTBLANK), invalid codes (COUNTIF) and duplicate IDs.
• One typing error can wreck a mean. In the toolkit’s data, a 77 typed for a 7 and one duplicated row raise the experimental group’s mean from 7.00 to 10.48; the median stays at 7.
• Correct errors; do not delete inconvenient values. Go back to the paper. A real extreme score stays, and is reported.
• Keep a cleaning log: what changed, why, and in how many rows.

Levels of measurement

The toolkit starts with the four levels of measurement, because the level decides which statistics are allowed:

A small correction to the toolkit: its interval example, IQ, describes the average of 100 as the “arbitrary zero” with negative values below it. IQ scores are never negative; the point of an interval scale is that zero does not mean “none” (0 °C is not the absence of temperature), so ratios make no sense (“20 °C is not twice as warm as 10 °C”). For action research, test scores are usually analyzed as interval or ratio data, and rating-scale items as ordinal, or as interval when several items are averaged.

Sorting and cleaning, as the toolkit defines them

• Data sorting is the “physical weeding out” of tests and questionnaires that are not valid, such as incomplete ones. The toolkit’s advice is to avoid losing data at all: check each paper when it is handed in and ask the participant to answer the missed items.
• Data cleaning is the “electronic correction of seemingly doubtful data”. Look for extreme values in each variable, retrieve the original paper, and correct the entry if it was a typing error.

The coding guide

The coding guide, or codebook, tells the encoder where and how each piece of information goes. The toolkit’s example (Table 5.1.2) is for posttest scores on a 10-item language test after a six-month intensive English program:

Every participant gets an ID number, written on the upper right corner of the paper. It links the spreadsheet row to the paper and keeps names out of the data file.

Add three columns the toolkit leaves out:

The valid range tells the encoder and the checker what is impossible. Leave missing answers blank: a code such as 99 for “no answer” gets averaged in by Excel unless you remove it first, while AVERAGE simply skips blanks.

The toolkit says variable names can have “only eight (8) characters” in many statistical programs. That limit is long gone in current software, but short names without spaces (score, q5, gender) are still easier to work with.

Setting up the sheet

The toolkit’s encoded data (Table 5.1.3) follow these rules:

• One header row with the variable names, and nothing above it.
• One row per participant and one column per variable; no merged cells, no subtotals inside the data.
• Codes, not words, in coded columns; colors are not data.
• Keep the raw encoded file untouched, and clean a copy.

Stop errors at the door with Data Validation. Select the score column, then Data > Data Validation > Allow: Whole number, between 0 and 10. For gender, Allow: List, Source: 1,2. Excel then refuses a 77 or a “G” as it is typed.

Double entry for small, important data sets: two people encode the same papers in separate sheets, and =IF(Sheet1!D2=Sheet2!D2, "", "CHECK") flags every cell where they differ.

Four planted errors, and how to find them

To show the checks, we took the toolkit’s 40 rows and introduced four common errors: learner 8’s score typed as 77 instead of 7; learner 23’s gender typed as “G” instead of 1; learner 34’s score left blank; and learner 15’s row entered twice, giving 41 rows. With the IDs in A2:A42, group in B, gender in C and scores in D:

Conditional formatting (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) shows duplicates at a glance. Data > Remove Duplicates deletes rows that are identical in the columns you choose; use it only after checking that the rows really are the same learner.

What the errors do to the results:

One wrong keystroke turns a 10-item test’s group mean into 10.48, an impossible score, and its standard deviation from 1.38 into 15.32. Any t-test or ANOVA on these data would be meaningless. The median barely moves, which is why the median is a useful check: if the mean and median of a group are far apart, look for an error.

Screening for extreme values when there is no hard maximum:

• z-scores: =STANDARDIZE(D2, AVERAGE(D$2:D$42), STDEV.S(D$2:D$42)). Values beyond ±3 deserve a look. The 77 has z = 6.1.
• The 1.5 × IQR rule: values above Q3 + 1.5 × (Q3 − Q1), using =QUARTILE.INC(D$2:D$42,3) and =QUARTILE.INC(D$2:D$42,1), or below Q1 − 1.5 × (Q3 − Q1). Here the upper fence is 10.375, and the 77 is far above it.

Then go back to the paper. If the paper says 7, correct the cell. If an extreme value is real, such as a learner who truly scored far above the rest, keep it. The toolkit notes that “some statisticians advice the deletion of extreme values”; do that only for errors. Deleting real scores because they spoil the result is not cleaning. If one real value strongly affects the result, report the analysis with and without it.

Cleaning text and imported data

Data from Google Forms, the Learner Information System or other files bring their own problems:

What cleaning DepEd’s data taught us

NASH Reviewer has cleaned several of DepEd’s public data files. The same principles apply at that scale:

• Log every change. Cleaning the EDCOM II classroom data produced a log of 31,415 entries: 13,139 address placeholders emptied, 4,589 region labels standardized, 1,353 Sulu records moved to Region IX, and 882 rows mapped to a school’s current ID (How the EDCOM Classroom Data Was Cleaned).
• Flag, do not silently delete. 103 school building lines were exact copies of an earlier line. They were kept and flagged, so that anyone can check whether dropping them changes a result.
• Impossible values are rare but real. In DepEd’s SY 2023-24 school files, 139 public schools report learners but no teachers, 267 report no classroom, and one school reports 15,699 tablets (DepEd Open Data Quality: Errors, Blanks and Coverage Limits). Range checks find them in seconds.

A cleaning log for an action research can be a second sheet:

Encoding qualitative data

Answers to open-ended questions, interview transcripts and observation notes go into a word processor, not a spreadsheet: a two-column table with the ID and the response, typed word for word, as in the toolkit’s Table 5.1.4. Thematic Analysis for Action Research: Steps and Sample shows the next steps.

Common mistakes

• Encoding without a coding guide, so that “1” means girl in one sheet and boy in another.
• Typing words in coded columns, or mixing words and codes.
• Using 0 or 99 for missing answers and then averaging them.
• Cleaning the only copy of the raw data.
• Deleting real extreme values to improve a result.
• No record of what was changed.

Frequently asked questions

What is a coding guide in research?

A table that tells the encoder which column each variable goes in, its short name and full label, the numeric codes for each category (such as 1 = girl, 2 = boy), and its level of measurement. DepEd’s Action Research Toolkit calls it a coding guide or codebook.

How do you clean data in Excel?

Check each column for impossible values (MIN and MAX, COUNTIF for out-of-range values), blanks (COUNTBLANK), invalid codes, and duplicate IDs (COUNTIF or conditional formatting). Correct errors against the original papers, and keep a log of every change.

Should I remove outliers from my action research data?

Correct values that are errors. Keep real extreme values, and if one strongly affects the result, report the analysis with and without it. Do not remove values only because they weaken the result.

How do I code missing data in Excel?

Leave the cell blank. Excel’s AVERAGE and similar functions skip blanks, while a code such as 99 would be averaged in by mistake.

More on DepEd research

• Start here: DepEd Action Research Guide: From Topic to Report
• One-Way ANOVA in Excel for Action Research
• Paired and Independent t-Test in Excel for Action Research
• Weighted Mean and Likert Scale Interpretation in Excel
• Survey Research With Google Forms for Action Research
• Document Analysis in Action Research Using DepEd Records
• Related: How the EDCOM Classroom Data Was Cleaned, DepEd Open Data Quality: Errors, Blanks and Coverage Limits

Sources

• Department of Education and the Basic Education Sector Transformation (BEST) Program. (2019). Action Research Toolkit: Teacher’s Guide, section 5.1 (preparing data for analysis; Tables 5.1.1 to 5.1.4), with support from the Australian Government.
• Microsoft. STANDARDIZE function, QUARTILE.INC function and TRIM function.

Written by Mark Anthony Llego for NASH Reviewer. NASH Reviewer is not affiliated with DepEd.