Excel for Science Students: Lab Data, Graphs and Stats

Excel for science students comes down to a short list of skills: record lab data in a clean table, average your repeated trials, measure how much they spread, work out percent error, round the results and draw a proper scatter graph. You don't need macros or advanced statistics for a typical lab report. This guide walks through each skill on one small physics data set, and the same steps work for chemistry and biology practicals.

How to set up lab data in Excel#

A clean layout makes every later step easier. Follow three rules:

1. One row per trial or reading, one column per measurement. Don't put two experiments side by side in the same columns.
2. Put the unit in the header, like "g (m/s²)" or "Mass (g)". Type only the number in each cell. A cell that says "9.62 m/s²" is text, and AVERAGE will ignore it.
3. Keep raw data and calculations apart. Leave your readings untouched and do the maths in separate cells, so you can always check it.

Here is the data set used below. A falling-ball experiment measured g, the acceleration due to gravity, six times:

Average, median and range of repeated trials#

Summarise repeated trials with AVERAGE first, then check MEDIAN, MIN and MAX to see whether one reading pulls the average off.

The mean and median are close, so no single trial is distorting the result. If they were far apart, look for an anomalous reading, and say in your report whether you excluded it and why. MODE, the most common value, is useful for counted data such as the number of seeds that germinate, but rarely for measurements with decimals.

The AVERAGE function guide covers how blanks and text are treated. Our article on AVERAGE, MIN and MAX has more examples, and the average, min and max lesson and median and mode lesson let you practise each one.

Measure spread and percent error#

Spread tells you how consistent your trials were, and percent error tells you how close you got to the accepted value.

• STDEV.S is the sample standard deviation. Use it for repeated trials; STDEV.P is for a complete population. Our guide to standard deviation in Excel explains the difference.
• Standard error shows how precisely you know the mean. It shrinks as you add trials.
• Percent error uses ABS so the answer is positive whether you measured too high or too low. Here it is about 0.37%.

STDEV.S needs Excel 2010 or later, which covers any current version. In very old files you may see STDEV, which gives the same result.

How to round results for a lab report#

Use ROUND for the numbers you report, and keep full precision in the cells that other formulas use:

=ROUND(AVERAGE(B2:B7),2)

This returns 9.77. The second argument is the number of decimal places, and a negative number rounds to the left of the decimal point: =ROUND(1234,-2) gives 1200.

ROUND changes the stored value. The Decrease Decimal button only changes what you see. That matters in a lab report, because a table that shows 9.77 but calculates with 9.7733 can give totals that look slightly wrong.

Many lab courses ask for results to a number of significant figures rather than decimal places. Our guide to rounding to significant figures has the formula. The ROUND function guide, the article on ROUND, ROUNDUP and ROUNDDOWN and the rounding lesson cover the rest.

How to graph lab data with a trendline#

Use a scatter chart for x-y data, such as force against extension, because it spaces the points by their real x values. A line chart treats the x values as labels and spaces them evenly, which can distort the shape.

Take a spring experiment with force in A2:A7 (0 to 5 N) and extension in B2:B7 (0, 2.1, 3.9, 6.2, 8.0 and 9.9 cm):

1. Select A1:B7 and choose Insert > Scatter.
2. Add axis titles with units: click the chart, then the Chart Elements button (Windows) or Chart Design > Add Chart Element (Mac).
3. Click a data point, choose Add Trendline, pick Linear, and tick Display Equation on chart and Display R-squared value on chart.
4. For error bars, use Chart Elements > Error Bars > More Options and choose Custom to point at your own standard deviation or uncertainty cells.

To get the line's numbers in cells for your calculations, use the same functions the trendline uses:

Note the order: y values first, then x values. Our step-by-step scatter plot guide covers formatting the chart.

Common mistakes in lab spreadsheets#

These cause most wrong answers in practicals:

• Units typed in cells. "2.1 cm" is text, so formulas skip it. Keep units in headers.
• Mixed units in one column. Convert first, for example grams to kilograms with =B2/1000, in a new column.
• Averaging rounded values. Average the raw readings, then round the result once.
• The wrong chart type. Line charts space x values evenly; use a scatter chart for measured x values.
• Hard-typed results. If you type 9.77 instead of using a formula, it won't update when you fix a reading.

The same habits carry over to engineering and lab jobs. If you are heading that way, see Excel skills for engineers, and take the Excel level test to see which skills to work on next.

Practise it#

Try the percent error exercise on this page. Then practise AVERAGE, MIN and MAX in a live spreadsheet, practise MEDIAN and MODE on repeated readings, and practise ROUND in the rounding lesson. Each lesson checks your formula as soon as you press Enter.