Physics Experiments for High School: Spreadsheet Graphing

This free physics experiments for high school lesson teaches students how to collect motion data, calculate velocity and acceleration in a spreadsheet, and use graphs to interpret what happened in the lab.

Overview

Students run a short motion investigation using a rolling cart or ball. They collect distance-and-time data at several marked positions, build a spreadsheet that calculates average velocity and average acceleration, and create three graphs: distance vs. time, velocity vs. time, and acceleration vs. time. Students then use slope and graph shape to explain the motion and evaluate a simple prediction made at the start of the lab.

Subject Connections

This lab strengthens math skills related to slope, rate of change, and unit reasoning. It also builds technology fluency by having students use a spreadsheet to organize data, apply formulas, and generate graphs that support scientific claims.

Learning Goals

• Collect accurate distance and time data from a repeated motion trial
• Use a spreadsheet to compute average velocity and average acceleration with correct units
• Create and label graphs that include titles, axis labels, and units
• Use slope and graph features to explain motion in words
• Compare patterns across multiple groups’ data and draw conclusions

Materials

• Rolling cart and track (or a smooth ramp and a ball)
• Measuring tape or meterstick
• Tape for marking distances
• Stopwatches (several per class, shared)
• Computers for student groups with spreadsheet software (Excel, Google Sheets, or similar)
• Optional: phone slow-motion video for checking timing (teacher-controlled or per school policy)

Preparation

Set up a straight track or ramp and mark distances at 1 m, 2 m, 3 m, 4 m, and 5 m (adjust if space is limited). Test one trial yourself to confirm the cart/ball reliably reaches the final mark. Create a simple spreadsheet template header (columns only) for students to copy: Distance, Total Time, Change in Distance, Change in Time, Avg Velocity, Change in Velocity, Avg Acceleration.

Teaching Procedure

Each session fits a standard class period of 45–50 minutes.

Session 1 – Prediction and Data Collection

1. Teacher prompts a quick prediction about motion on two different paths or ramp setups (for example, a steeper start vs. a gentle slope). Students write a one-sentence hypothesis and a one-sentence reason.
2. Activity: The teacher tells students they will time a cart/ball reaching marked distances to build a dataset. Using a track/ramp, distance markers, and stopwatches, students assign roles (release, timekeepers at each marker, recorder), run at least three trials, and record the total time to each distance mark. Students demonstrate completion by producing a neat data table with units for every entry.
3. Teacher leads a brief accuracy check. Students identify one likely source of timing error and write one improvement they will try next session.

Session 2 – Build the Spreadsheet and Calculate Velocity

1. Teacher models how to enter data and use formulas in one example row, then demonstrates “fill down” to apply the formula to the full column. Students recreate the structure in their own file.
2. Activity: The teacher tells students to turn raw timing data into motion quantities. Using computers with a spreadsheet, students enter distance and time, compute change in distance and change in time between points, and calculate average velocity for each interval. Students demonstrate learning by showing their spreadsheet with formulas visible and at least one velocity value checked by hand for reasonableness.
3. Teacher circulates to confirm units are written in headers and students are not mixing meters and seconds with other units.

Session 3 – Calculate Acceleration and Create Three Graphs

1. Teacher reviews that slope on a distance vs. time graph represents velocity, and slope on a velocity vs. time graph represents acceleration. Students write these relationships in their lab notes with units.
2. Activity: The teacher tells students to generate evidence from graphs. Using their spreadsheet, students calculate change in velocity and average acceleration by interval, then create and label three graphs: distance vs. time, velocity vs. time, and acceleration vs. time. Students demonstrate learning by producing graphs with clear titles, axis labels, and units.
3. Teacher prompts analysis questions. Students answer in complete sentences using their graphs: what the distance-time slope means, what the velocity-time slope means, and what a negative value would indicate in context.

Session 4 – Combine Class Data and Compare Patterns

1. Teacher assigns each group a label and has groups share their distance-time table (copy/paste into a shared file or upload per school routine). Students add at least two other groups’ data as separate series.
2. Activity: The teacher tells students to look for consistency and differences. Using a shared spreadsheet or a copied dataset, students graph velocity vs. time and acceleration vs. time with multiple group series, then identify one similarity across groups and one meaningful difference. Students demonstrate learning by annotating one graph (digitally or on paper) with two short evidence notes.
3. Teacher leads a closing discussion. Students revisit the Session 1 hypothesis and write a short conclusion that cites at least one graph feature as evidence.

Assessment

• Data table completeness and units (distance marks and times recorded clearly)
• Spreadsheet correctness (formulas, fill-down use, and reasonable values)
• Graph quality (titles, axis labels, units, readable scales)
• Written responses that interpret slope and sign correctly in context
• Conclusion supported by graph evidence

Differentiation

For students who need support, provide a spreadsheet with column headers already entered and one sample formula shown. Allow those students to focus on one graph (distance vs. time) first, then add velocity and acceleration if time allows. For advanced students, ask them to estimate an instantaneous velocity at a chosen time by using a tangent concept on the distance-time curve (or by using a smaller time interval approach) and explain the difference between average and instantaneous values.

Grade Adaptation

Grade 11 students can focus on clean spreadsheet structure, correct units, and interpreting slopes with confidence. Grade 12 students can extend analysis by comparing multiple trials, discussing uncertainty, and explaining how changing the ramp angle or surface affects the graphs and motion model.

Extension Ideas

Have students redesign the experiment to reduce error (role changes, more trials, better timing protocol) and then compare “before and after” graphs. As an at-home option (only if appropriate), students can use a toy car and a measured floor distance with a phone timer, then build the same spreadsheet and graphs using their own dataset.