How to Use Excel or Google Sheets for Research Data

TL;DR: This post answers a specific question: how do you use Excel or Google Sheets to organise, analyse, and present research data as a high school student? It is written for students in grades 9 to 12 who are conducting original research and want their data to be credible and publication-ready. After reading, you will know how to structure a dataset, run basic statistical analyses, and produce clean figures. If your research is ready for peer review, the Princeton Journal of Pre-Collegiate Research publishes original student work across all disciplines.

Why spreadsheet skills decide whether your data is usable

Most peer reviewers reject student papers at the data stage, not the writing stage. The most common reason is not fabrication or error. It is disorganisation: raw data stored without variable labels, calculations buried inside unlabelled cells, and figures copied from a spreadsheet with no axis titles. A well-structured spreadsheet is not a formatting preference. It is the foundation of reproducible research. If a reviewer cannot follow your data from raw input to reported result, your findings cannot be verified. Unverifiable findings do not get published.

Learning how to use Excel or Google Sheets for research data is therefore not a technical side task. It is a core research competency. This guide covers exactly what you need to know, from initial data entry to producing figures that meet publication standards.

How do you use Excel or Google Sheets for research data?

You use Excel or Google Sheets for research data by creating a clean, consistently structured dataset where each row is one observation and each column is one variable. From that foundation, you apply built-in functions for descriptive statistics, run basic inferential tests using the Data Analysis Toolpak (Excel) or add-ons (Sheets), and generate clearly labelled figures. The entire process, from raw data to publication-ready output, can be completed in either tool without additional software.

Here is what that process looks like in practice.

Step 1: Structure your dataset correctly before entering a single value

Every dataset must follow the same rule: one row per observation, one column per variable. If you surveyed 40 students, you have 40 rows of data. If you recorded temperature, time, and pH for each measurement, you have three columns. This structure is called tidy data, a term formalised by statistician Hadley Wickham in a 2014 paper published in the Journal of Statistical Software. It is the standard expected by any statistical software, including Excel and Sheets.

Label every column in row 1 with a short, unambiguous name. Use underscores instead of spaces if you plan to import the data into R or Python later (e.g., response_time not Response Time (ms)). Never merge cells. Never use colour coding as your only data indicator. A reviewer reading your spreadsheet should be able to understand every column without asking you a single question.

Step 2: Separate raw data from your analysis

Keep your raw data on one sheet and your calculations on a separate sheet. This is not optional. Once you overwrite raw data with a formula, the original values are gone. Label the raw data sheet clearly (e.g., Raw_Data) and protect it in Excel by right-clicking the tab and selecting Protect Sheet. In Google Sheets, use the Protected Sheets and Ranges option under the Data menu. This prevents accidental edits and demonstrates methodological rigour if you ever share your file with a reviewer or mentor.

Step 3: Use functions for descriptive statistics

Both Excel and Google Sheets include identical functions for the statistics you will use most often in student research. The core set is: =AVERAGE() for mean, =MEDIAN() for median, =STDEV() for standard deviation, =MIN() and =MAX() for range, and =COUNT() for sample size. Build a summary statistics table on your analysis sheet using these functions, referencing the raw data sheet directly. Do not type numbers manually into your summary table. Any manually entered value is a transcription error waiting to happen.

For Excel users running inferential statistics (t-tests, ANOVA, correlation), enable the Data Analysis Toolpak under File > Options > Add-ins. For Google Sheets, the XLMiner Analysis ToolPak add-on provides equivalent functionality. Both are free.

Step 4: Build figures that communicate your findings precisely

A figure in a published paper must communicate one finding clearly without requiring the reader to interpret it. Every figure needs: a descriptive title, labelled axes with units, a legend if more than one data series is shown, and error bars if you are displaying means. In Excel, add error bars by clicking the chart, selecting the data series, and choosing Format Error Bars. Use standard deviation bars for descriptive purposes and standard error bars when making inferential claims about a population.

Avoid 3D charts, gradient fills, and decorative elements. Publication-standard figures are plain. They use black, white, and one or two solid colours. If your figure looks like a business presentation slide, it is not ready for a journal submission. For a detailed guide on how data presentation connects to reviewer decisions, see what reviewers look for in student research data.

What separates a publication-ready dataset from a class project spreadsheet?

The difference is reproducibility. A class project spreadsheet needs to make sense to you and your teacher. A publication-ready dataset needs to make sense to a reviewer who has never met you, has no access to your lab notebook, and will spend approximately 20 minutes evaluating your entire paper.

Reproducibility means three things in practice. First, every calculation in your spreadsheet must be traceable. A reviewer should be able to click any cell in your analysis sheet and see the formula that produced that number. If a cell contains a typed value instead of a formula, it is not reproducible. Second, your variable names must match your methods section exactly. If your methods section refers to reaction time in milliseconds, your column header should reflect that. Discrepancies between your spreadsheet and your written methods are a common reason for revision requests. Third, your data must be complete. Missing values must be coded consistently (use NA as a standard, not blank cells, which Excel and Sheets treat differently in calculations).

Students often assume that a strong written argument can compensate for a weak dataset. It cannot. Reviewers evaluate the data independently of the prose. A paper with elegant writing and a disorganised dataset will be sent back for major revisions, or desk rejected. Understanding what makes a research paper get rejected before you submit is one of the most efficient things you can do.

What are the most common spreadsheet mistakes high school researchers make?

Four mistakes appear repeatedly in student submissions, and each one is avoidable.

The first is mixing data types in a single column. Entering both numbers and text (e.g., 45 and N/A and not recorded) in the same column breaks every formula that references it. Excel will silently ignore text values in numerical calculations, which means your mean is calculated on fewer observations than you think. Fix: standardise all missing values to NA before running any analysis.

The second is using the wrong chart type. Bar charts are for categorical comparisons. Line charts are for continuous data over time or a sequential variable. Scatter plots are for relationships between two continuous variables. Using a bar chart to show a correlation, or a line chart for unordered categories, misrepresents your findings. This is not a stylistic issue. It is a methodological one.

The third is reporting means without measures of spread. A mean with no standard deviation or standard error tells a reviewer almost nothing. According to the American Psychological Association's Publication Manual (7th edition), all descriptive statistics reported in a paper should include a measure of variability alongside the central tendency. Excel's =STDEV() function takes 10 seconds to add to any summary table.

The fourth is submitting the spreadsheet file itself as a supplementary document without cleaning it first. Delete unused sheets, remove personal information from file properties, and ensure every sheet is labelled. A messy supplementary file signals to reviewers that the research process was equally disorganised.

How to prepare your spreadsheet for a journal submission, step by step

1. Create a new workbook with two sheets: Raw_Data and Analysis.

2. Enter all raw data into Raw_Data with consistent column headers in row 1. One row per observation. No merged cells.

3. Protect the Raw_Data sheet to prevent accidental edits.

4. On the Analysis sheet, build a summary statistics table using formulas that reference Raw_Data directly.

5. Run any inferential tests (t-test, ANOVA, correlation) using the Data Analysis Toolpak (Excel) or XLMiner (Sheets). Copy the output to the Analysis sheet.

6. Build all figures on the Analysis sheet. Add axis labels, units, a title, and error bars where applicable. Remove all decorative formatting.

7. Cross-check every variable name in the spreadsheet against your methods section. They must match exactly.

8. Review your methods section to ensure it aligns with your data structure. A strong research methods section describes your data collection procedure in enough detail that another researcher could replicate it.

9. Save the file with a clear name (e.g., Smith_J_ResearchData_2025) and submit it as a supplementary file alongside your manuscript.

PJPCR publishes original research across all academic disciplines, including quantitative studies that rely on spreadsheet-based analysis. If your data is organised and your paper is ready for peer review, review the submission guidelines at how to submit a research paper as a high school student.

Frequently asked questions about using Excel or Google Sheets for research data

What is tidy data and why does it matter for student research?

Tidy data is a dataset structure where each variable occupies one column and each observation occupies one row, as defined by Hadley Wickham in the Journal of Statistical Software (2014). It matters because every statistical function in Excel and Google Sheets, and every statistical software package, assumes this structure. Datasets that do not follow it produce incorrect calculations without generating an error message, which means your results can be wrong without you knowing.

How long does it take to prepare a dataset for journal submission?

For a well-collected dataset of 50 to 200 observations, expect 3 to 6 hours of cleaning, analysis, and figure preparation. Poorly structured raw data can take significantly longer. Starting with a clean data entry protocol before data collection saves more time than any post-collection fix. PJPCR's standard review timeline is 2 to 3 months from submission. A fast-track option is available for students who need a quicker turnaround.

Do I need to know statistics to use Excel or Google Sheets for research?

No advanced statistics background is required for most high school research projects. Descriptive statistics (mean, median, standard deviation, range) and basic inferential tests (independent samples t-test, Pearson correlation) are sufficient for the majority of student papers. Both Excel and Google Sheets run these tests through built-in tools. Understanding what each test measures is required. Knowing the underlying mathematics is not.

What makes a spreadsheet-based analysis publishable in a peer-reviewed journal?

A publishable analysis is reproducible, complete, and correctly interpreted. Reproducible means every number in your paper can be traced to a formula in your spreadsheet. Complete means no missing values without explanation and no calculations performed on a subset of your data without disclosure. Correctly interpreted means you have chosen the right statistical test for your data type and research question, and you have not overstated what your results show. For guidance on how your analysis connects to your paper's argument, see how to write a discussion section in a research paper.

What kinds of research does PJPCR publish, and does it accept quantitative data studies?

The Princeton Journal of Pre-Collegiate Research publishes original, peer-reviewed research by high school students across STEM, humanities, social sciences, and interdisciplinary fields. Quantitative studies using spreadsheet-based analysis are accepted across all disciplines, provided the methodology is sound and the data is presented transparently. Submission and peer review are free. A publication fee applies for accepted papers. Review the full research paper formatting guide before preparing your manuscript.

What you should do next

Spreadsheet competence is not a bonus skill for student researchers. It is the infrastructure your findings depend on. Structure your raw data correctly from the start. Separate it from your analysis. Use formulas, not manual entries. Build figures that communicate one finding at a time, with labelled axes and appropriate error bars. Cross-check every variable name against your methods section before submission.

These are not stylistic choices. They are the difference between a paper a reviewer can evaluate and one they cannot. If your data is clean, your analysis is traceable, and your paper is ready for peer review, submit your research to PJPCR at princeton-jpcr.org/submit.