Excel for Research Assistants: Clean and Summarize Data

Excel for research assistants is mostly about four jobs: entering study data in a tidy layout, cleaning it, checking it for mistakes, and summarizing it by group. You don't need macros or advanced statistics for this. A handful of functions, TRIM, COUNTIFS and AVERAGEIFS, plus a pivot table, cover most of the day-to-day work in a lab, survey project or university department.

What Excel work do research assistants do?#

A research assistant's Excel tasks usually follow the life of a dataset:

These are the same foundations data analysts use. The guide to Excel skills for data analysts goes further into lookups and dashboards.

Set up the data: one row per participant#

Good analysis starts with a tidy layout. Follow three rules:

1. One row per participant (or per response, or per measurement).
2. One column per variable, with a short, clear header such as Age or Score.
3. A unique ID in the first column, such as P01, so you can always trace a row back to its source.

Here is a small example dataset in A1:D8:

Avoid merged cells, totals inside the data, and empty rows between blocks. Keep a separate sheet as a codebook: a list of every variable, what it means, and its allowed values (for example, Score 0 to 20).

Clean text codes with TRIM#

Text codes typed by hand or exported from a form often have hidden problems. "Treatment " with a trailing space, " treatment" with a leading one, and "Treatment" all look alike, but Excel treats them as three different groups. Your counts will be wrong without any warning.

TRIM removes spaces at the start and end of text, and turns double spaces in the middle into single ones. In a new column next to the raw codes, type:

=TRIM(B2)

To fix capitals at the same time, wrap it in PROPER:

=PROPER(TRIM(B2))

This turns " treatment " into Treatment. Fill it down, check the results, then paste them as values over a copy of the column. The TRIM function guide covers the details, and the guide to cleaning messy data in Excel shows the full cleaning routine, including non-breaking spaces that TRIM can't remove. If the same participant appears twice, see how to remove duplicates.

Check for missing and impossible values#

Before you summarize, check every variable. Three quick formulas catch most problems:

A result above 0 in the first two checks means you need to look at the source. Filter the column to find the rows. An age of 420 is almost certainly a typing slip for 42, but don't guess: note it and check it against the original record.

Also check that each ID appears only once: =COUNTIF(A2:A8,A2) filled down should show 1 on every row.

Summarize groups with COUNTIFS and AVERAGEIFS#

COUNTIFS counts rows that meet every condition, and AVERAGEIFS averages the rows that meet every condition. Together they give the descriptive tables most studies start with.

How many participants are in each group?

=COUNTIFS(B2:B8,"Treatment")

This returns 4. The Control group has 3. The COUNTIFS function guide explains the criteria rules.

What is the mean score in each group?

=AVERAGEIFS(D2:D8,B2:B8,"Treatment")

This returns 17.25 ((18 + 15 + 20 + 16) ÷ 4). For Control it's about 12.33.

You can add more conditions. The mean score of Treatment participants aged 30 or over:

=AVERAGEIFS(D2:D8,B2:B8,"Treatment",C2:C8,">=30")

This returns 18: P02, P05 and P07 (18 + 20 + 16 = 54, divided by 3). P03 is 27, so it's left out. In AVERAGEIFS, the range to average always comes first. The AVERAGEIFS function guide and the article on AVERAGEIFS with multiple criteria cover dates, wildcards and blank cells.

Use a pivot table for quick crosstabs#

A pivot table builds the same group summaries in a few clicks, which is handy when you're exploring. Click inside the data, choose Insert > PivotTable, then drag Group to Rows and Score to Values. Change the summary from Sum to Average with Value Field Settings, and drag Score in again set to Count to see the group sizes beside the means.

Pivot tables are great for exploring, and formulas are better for a results table that must update by itself and sit in a fixed layout. For survey-style data with rating scales, the guide to analysing survey results in Excel shows both approaches.

Keep your work reproducible#

Research data must be traceable. A few habits protect it:

• Never edit the raw data sheet. Copy it, and do all cleaning in new columns or on a new sheet.
• Sort the whole table, never one column. Sorting a single column breaks the link between participants and their values.
• Write down every cleaning step in a notes sheet: what you changed, why, and on which rows.
• Use formulas, not typed results, so anyone can see how a number was calculated.
• Save versions with dates in the file name before big changes.

Practise it#

Clean codes and correct group means are the skills a research supervisor checks first. Practise cleaning messy text in a live spreadsheet: you fix stray spaces and capitals with TRIM and PROPER, and each step is checked instantly. Then find group averages with AVERAGEIF, MAXIFS and MINIFS and build your first pivot table. Start with the AVERAGEIFS exercise on this page.