MnSTEP Teaching Activity Collection > MnSTEP Activities > Using Microsoft Excel to Explore Gravity Forces and Accelerations

Using Microsoft Excel to Explore Gravity Forces and Accelerations

Scott Holland, Henry Sibley High School, Mendota Heights, MN
Author Profile


In this activity, students will learn about the force of gravity and the factors that affect it (mass of objects and distance between them). They will learn the universal gravitation equation and analyze it qualitatively. They will then learn how to use Microsoft Excel and use the program to create a "gravity calculator" which will automatically calculate force of gravity and acceleration of two different objects if the user inputs the masses of the objects and the distance between them. They will use this gravity calculator to analyze several actual and hypothetical gravitational interactions (on earth and in space) to get a feeling for gravity forces and accelerations.

Learning Goals

This activity will help students gain a qualitative and quantitative understanding of gravity forces and accelerations.
This activity will help students learn how to use Microsoft Excel to perform complex calculations.

Key Concepts:
Gravity force is directly proportional to masses of objects and involved and inversely proportional to the square of distance between those objects.
Force of gravity between two objects is equal and opposite for the two objects.
Acceleration of the objects toward each other depends on the force and the masses of the objects.
On earth, gravity forces are different for all objects, but acceleration values are always about 9.8 m/s/s.
On earth, gravity forces do pull the earth toward objects, but the resulting acceleration of the earth is almost always so small as to be imperceptible.

Gravity, Force, Acceleration, Gravitational Constant

Context for Use

This is best used with high school physics classes of any size that have access to computers or a computer lab. The lesson begins in the classroom, moves to the computer lab, and can then be wrapped up in the classroom. It is a lecture/discussion/computer activity lesson. Before beginning, students should be familiar with Newton's laws of physics. The amount of time required will vary from 2-4 normal (50 minute) class periods, depending on the mathematical sophistication of the students and their familiarity with Microsoft Excel and computers in general.

Subject: Physics:Classical Mechanics:Gravity
Resource Type: Activities:Classroom Activity
Grade Level: High School (9-12)

Description and Teaching Materials

This lesson comes after students have done a pre-test and a qualitative computer simulation activity (involving solar system orbits) involving gravity. The lesson begins with some background information about universal gravitation and the equation used to quantify forces of gravity. Microsoft Excel is then introduced and examples of how I use it (tracking student participation grades, keeping track of softball stats, randomizing student groups, etc) are shown on the projector. I then show students how to do some simple calculations and formatting using Excel before explaining the assignment, which is to construct a simple calculator, have it checked, and then construct a more complicated gravity calculator that will take masses of two objects and distance between them as inputs and calculate the force of gravity and acceleration of each object toward the other.

After explaining the activity, students move to the computer lab, where they follow written directions to build the calculators and have them checked off by the teacher. Ideally, each student should have their own computer, but this could be done in pairs if necessary. Once they have created functional calculators, students calculate forces and accelerations for various sets of objects, including ant & earth, elephant & earth, student & earth, student & Jupiter, student & moon, earth & moon, earth & sun, student & student, among others. They are asked to record their results and answer some analysis questions about their findings. Ideally, they will note that all objects accelerate at the same rate toward earth even though the forces of gravity between earth and different objects vary. They also note that forces and accelerations are minuscule for all but the most massive of objects, and that forces fall off rapidly as distances between objects increase.

Upon completing the activity, we go over some of the important points in class. A group quiz or individual quiz on the basics of gravity can also be given at this time. Worksheet to handout / turn in / assess (Microsoft Word 207kB Oct13 08)

Teaching Notes and Tips

Student familiarity with computers and MS Excel in particular will vary wildly. Consider allowing advanced students to complete extra credit spreadsheets that will help them with something of interest (tracking grades or sports results or wages, etc.)

You may consider letting your MS Excel experts help the beginners, but I find that they usually end up doing their work for them, so I try to avoid this.

It is very difficult to write good instructions for MS Excel formatting and calculations. Be sure to let students know that whatever instructions you give will need to be read closely to be useful, and that the "help" feature on Excel will probably be more useful.

Kick students out of the computer lab if they use email or youtube before completing the assignment.

If you have advanced students, consider simply giving them the problem and withholding any instructions.

If you plan to use Excel later in the course, this is a good introduction. If you have already used Excel, this is a good review/new application.


Students are visually assessed based on their participation during computer lab time. Their worksheets (with results and analysis) are collected and graded. Students are quizzed (in groups or individually) on gravity concepts and Excel usage after this activity.

Standards How push and pull forces make objects move. Use data to construct reasonable explanations. Recognize that parts of a system influence one another. Demonstrate that the greater the force applied, the greater the change in motion. Explain how gravity affects the motion of objects, including planets. Use appropriate tools (including computers) and techniques (including mathematical formulas) in gathering, analyzing, and interpreting data.
9P. Use Newton's law of gravitation to explain the motion of astronomical bodies.

References and Resources