Lesson .
.Create a Solar System Scale Model With Spreadsheets
Overview
In this activity, students use scale, proportion and/or ratios to develop a scale solar system calculator. Using spreadsheet software, students will determine the size of and/or distances between planets on a solar system model that fits on a playground.
Materials
- Example not-to-scale images of the solar system
- Computer or mobile device
- Spreadsheet software (e.g., Excel or Sheets)
- Distance markers (cones, ground stakes, etc.)
- Paper
- Pencil, pen and/or colored pencils
- Ruler with centimeter markings
- Measuring tape
- Solar System Sizes and Distances reference guide – download PDF
- Scale Distance spreadsheet – download XLSX OR download CSV
- Scale Size spreadsheet – download XLSX OR download CSV
- Scale Size and Distance spreadsheet – download XLSX or download CSV
Management
- Decide in advance if students will calculate scale distance from the Sun to the planets, scale size of planets or both.
- Depending on student abilities, consider using astronomical units (au) for distances instead of kilometers or miles. More information about astronomical units can be found in the Background section below.
- Figure out which system of units your students will use: metric (SI) or U.S. customary units. Planetary data is provided in SI.
- Data is provided in both Microsoft Excel spreadsheets (XLSX) and comma-separated-value (CSV) files that can be opened in spreadsheets. You may choose to have students look up the information on their own and input it into a blank spreadsheet.
- Students may realize they can copy and paste formulas from cell to cell in spreadsheets. However, most spreadsheet software will paste using relative cell values, meaning the values will change and the calculated results could be incorrect. Make sure students correct any automatic changes, or use absolute cell references (e.g., $B$4) instead of relative cell references (e.g., B4) when linking to the same cell in pasted formulas.
- Determine how much space students have to work with by measuring an open area at the school using an online mapping tool. Or use a space with known measurements (e.g., a football field or basketball court).
- Remember, the calculated scale distances indicate radial distances to the planets, and because the planets orbit, the actual size needed for a scale model that allows for the placement of a planet anywhere in the model will be twice the calculated radial distance of the most distant planet.
Background
How big are the planets and how far away are they compared to each other? See how the sizes of planets and the distances between them compare in this video.
The solar system is huge! And that’s an understatement. Even traveling at the speed of light, it would take about four hours to get from the Sun to Neptune – a distance of about 2.8 billion miles. Because of the great distances between planets and the planets’ relatively small sizes compared to those distances, it’s difficult and sometimes impossible to create a visual representation on a computer screen or the page of a book that accurately represents the size of the planets and the distances between them.
A scale model – a model with sizes and distances proportionally reduced or enlarged – is a great way to correctly display the size of and distance between planets, giving students a better visual representation of the solar system than they could otherwise get from an image in a book or on a computer.
Some scale models show just scale distances, some show just scale planet sizes, while some display both. An accurate size and distance scale model in which Mercury, the smallest planet, is 1 mm across would require about half a mile to properly display the distance from the Sun to Neptune. There are scale solar systems all over the world. Some just a few blocks long, but the largest, in Sweden, stretches more than 140 miles!
Because the distances between planets are so great, astronomers sometimes describe distances in terms of astronomical units (au). One au is equal to the average distance between the Sun and Earth, about 150 million kilometers. This allows scientists to describe distances using smaller relatable numbers rather than tens of millions, hundreds of millions, or even billions of kilometers. For example, Mars is 1.5 au from the Sun and Neptune is 30 au from the Sun.
Procedures
Give students a primer on the size of planets and distances in the solar system, providing examples from the Background section above. Show them not-to-scale images of the planets and the solar system to demonstrate how size and distance are often portrayed incorrectly. Lead a discussion about why images often show size and distances this way (referring to talking points in the Background section). Ask students to guess how far away Earth would be from the Sun in a scale model if Earth were 1 cm in diameter. Ask students to guess how large a scale Sun would be.
Choose one of the methods below to view procedures for creating the scale solar system model of your choice:
Scale Distance Model (astronomical units)
Have students open the Scale Distance spreadsheet, or guide them through creating a similar spreadsheet layout.
With students, point out the distances in astronomical units (au) from the Sun to each planet. If students will input the distance data themselves, have them do that now. See the Solar System Sizes and Distances reference guide.
Decide or allow the class to decide how many centimeters will represent one astronomical unit (e.g., 10 cm = 1 au). This is your model’s scale value. In other words, how far will Earth be from the Sun in this model?
- Note: When 1 au = 10 cm, the scale distance to Neptune will be about 10 feet. Keep this in mind when considering the area you have to work with.
Have students come up with a formula for their spreadsheet to calculate how many centimeters each planet will be from the Sun in the scale model.
- The formula should multiply the au value by the scale value determined in Step 3. This will give students the scale distance to each planet in centimeters.
- Enter the multiplication formula into a cell in the cm column.
- A spreadsheet multiplication formula follows this format: =B3*10, where B3 is the cell with a planet’s au distance and 10 is the scale value. B refers to the cell column and 3 refers to the cell row.
Students should enter formulas in the other cells to determine the scale distance to each planet.
- Remember, when students enter formulas into other cells in the column, they must change the formula to reference the correct cell. If students are getting the same result in every cell, they may be repeating the formula without the changing the reference cell. For example, =B3*10 is the correct formula for Mercury’s scale distance, while =B4*10 is the correct formula for the distance to Venus, and so on.
Identify a spot to represent the Sun and use rulers or measuring tape to measure how far planets would be from the Sun in the classroom or on the playground, depending on the length of your scale distances. Because planets are not aligned in a row stretching out from the solar system, have students place them at the proper distances at various points around the Sun.
Students should determine another scale value (e.g., 15 cm = 1 au) and create an additional column with which to calculate and compare the distance to each planet. Before they do their calculations, ask students to predict how the distances will change with their new scale value.
Scale Size Model (metric units)
Have students open the Scale Size spreadsheet, or guide them through creating a similar spreadsheet layout.
If students will input size data themselves, have them do that now. See the Solar System Sizes and Distances reference guide.
Decide or allow the class to decide on the diameter of Earth in the scale model.
To calculate the scale planet sizes, discuss proportions and ratios with students. The ratio of the scale diameter of a planet to the scale diameter of Earth is equal to the ratio of the actual diameter of the planet to the actual diameter of Earth.
Scale Planet Diameter / Scale Earth Diameter = Actual Planet Diameter / Actual Earth Diameter
By knowing the actual diameters of two planets and the pre-determined scale diameter of Earth, students can rearrange the equation to find the unknown scale diameter of the other planets.
Scale Planet Diameter = Actual Planet Diameter (Scale Earth Diameter) / Actual Earth Diameter
Have students create a spreadsheet function that calculates this value. In the example below, the spreadsheet function calculates the product of the scale diameter of Earth (B5) and the actual diameter of Mars (C6) divided by the actual diameter of Earth (C5) using =(B5*C6)/C5.
Have students repeat the process in Step 4, using Earth’s scale diameter, Earth’s actual diameter and each planet’s actual diameter to find the scale diameters of the remaining planets.
Have students use various tools (ruler, compass, string, protractor, etc.) to draw circles of appropriate sizes for each planet. Students can color the circles to resemble the planets’ appearances.
Scale Distance and Size Model (metric units)
Have students open the Scale Size and Distance spreadsheet, or guide them through creating a similar spreadsheet layout.
If students will input size and distance data themselves, have them do that now. See the Solar System Sizes and Distances reference guide.
Decide or allow the class to decide on the diameter of Earth in the scale model.
- Note: When Earth’s scale diameter equals 1 cm, the scale distance to Neptune will be about 2 miles.
To calculate the scale solar system, discuss proportions and ratios with students. The ratio of the scale diameter of a planet to its scale distance to the Sun is equal to the ratio of the actual diameter of the planet to the actual distance to the Sun. Additionally, the ratio of the scale diameter of a planet to its actual diameter is equal to the ratio of its scale distance from the Sun to its actual distance from the Sun.
Scale Diameter / Scale Distance = Actual Diameter / Actual Distance.By having the actual diameter and distance of a planet, along with the pre-determined scale diameter of Earth, students can rearrange the equations to find the unknown values.
Using the decided upon scale diameter of Earth, have students rearrange the equation to solve for the unknown scale distance to the Sun.
Scale Diameter (Actual Distance) / Actual Diameter = Scale Distance
Have students create a spreadsheet function that calculates this value. In the example below, the spreadsheet function divides the product of Earth’s scale diameter (B5) and actual distance from the Sun (E5) by Earth’s actual diameter (D5) using =(B5*E5)/D5.
In this example, the spreadsheet function divides the product of Earth’s scale diameter (B5) and actual distance from the Sun (E5) by Earth’s actual diameter (D5) using =(B5*E5)/D5 to find the scale distance from Earth to the sun.
To calculate the scale diameter of the other planets, students will need to rearrange proportional ratios and write functions similar to how they did in Step 6, using the scale Earth diameter in their formula. If students change the scale diameter of Earth in the spreadsheet, it will cause the functions to recalculate the scale values for the other planets. By rearranging the equation below, students can create a formula in the spreadsheet to calculate both scale diameter and distance for the remaining planets.
Scale Planet Diameter / Scale Earth Diameter = Actual Planet Diameter / Actual Earth Diameter
In the example below, the product of the scale diameter of Earth (B5) and the actual diameter of Mars (D6) is divided by the actual diameter of Earth (D5) using =(B5*D6)/D5 to find the scale diameter of Mars.
In this example, the product of the scale diameter of Earth (B5) and the actual diameter of Mars (D6) is divided by the actual diameter of Earth (D5) using =(B5*D6)/D5 to find the scale diameter of Mars.
Students will use the scale Earth diameter and proportional ratios to find the scale distances to the other planets by rearranging the equation below.
Scale Planet Distance / Scale Earth Diameter = Actual Planet Distance / Actual Earth Diameter
In the example below, the spreadsheet function calculates the product of the scale diameter of Earth (B5) and the actual distance to Mars (E6) divided by the actual diameter of Earth (D5) using =(B5*E6)/D5.
In this example, the spreadsheet function calculates the product of the scale diameter of Earth (B5) and the actual distance to Mars (E6) divided by the actual diameter of Earth (D5) using =(B5*E6)/D5.Repeat steps 7 and 8 for the remaining planets.
Have students use various tools (ruler, compass, string, protractor, etc.) to draw circles of appropriate sizes for each planet. Students can color the circles to resemble the planets’ appearances.
Using online mapping software, such as Google or Bing maps, right-click on the location that represents the Sun (e.g., middle of the playground) and click “measure distance” to identify where the scale planets should go. Students may have to zoom in or out on the map to more easily see the required distances. Depending on the calculated size of the scale model, some planets, especially the outermost gas and ice giants, may have a location that would put them off-campus. In that case, have students choose a point on the map that is an accurate distance from the Sun at a location that is well known to students (e.g., a park or a neighborhood store).
Discussion
- How does using both scale size and distance in a model differ from a model that uses only scale size or distance?
Answer: Using both size and distance requires much more space to display the solar system.
Assessment
- Student spreadsheets should accurately calculate the scale size of and/or distances to all the planets.
- Students should be able to predict what would happen to the size and distance values in the model if the distance to or diameter of a single planet changes.
Extensions
- Students can develop a permanent or semi-permanent display of their model on the school campus.
- Students can work with local government to create a scale solar system model with correct sizes and distances that spans some or all of their city, town or region.
Explore More
About the Author
Lyle Tavernier
Educational Technology Specialist, NASA-JPL Education Office
Lesson Last Updated: Nov. 4, 2024