Excel - Graphing

Graphing is enormously important to the world of science and math (not to mention standardized testing like the ACT). Before using Excel as a tool to help us analyze data and model our natural world, it is useful to practice with the tool on its own first. This lesson is a tutorial on using excel for graphical analysis of data and creation of mathematical models.

​Note: In this tutorial, I am focusing on using these files on Excel's iPad App but the materials and structure of the lesson will work just as well on a computer :)

---

Structure of the Lesson

To maximize our time practicing graphing skills, I put together a file where the structure, formatting and data is already done so the students are only required to use the graphing tools to visualize and create models. Each worksheet is in its own tab along the bottom of the file and is intended to walk students through the entire process of graphing with Excel. In my classroom, I typically use the first few tabs as a full class tutorial and allow them to complete the rest on their own while I circulate the room.

Below is a list of the tasks and skills introduced for each of the worksheets:

Worksheet Title	Task	Skills Introduced
#1 ​Heating of Compound X	​Add a scatterplot with title and axis labels	Inserting scatterplot and editing title and labels
#2 ​Car Value by Year	​Add a scatterplot with title and axis labels	More practice with inserting scatterplot and editing title and labels
#3 ​Roth IRA	​Insert a scatter plot for each set of data	Insert a scatterplot with more than one series of data
#4 ​Growth Rate of a Pine Tree	​Insert a scatter plot and add line of best fit w/ equation	Adding best fit line and using mathematical model to answer questions
#5 Jump Height vs Bike Weight	Copy table, insert a scatter plot and add line of best fit w/ equation	More practice with best fit line and modeling
#6 Dist vs Time for a Rolling Tire	Copy table, insert a scatter plot and add line of best fit w/ equation	Completing data table before inserting graph
#7 ​Lab Data	​Use formulas to calculate the average and plot the distance vs average time	Rearranging data to graph non-adjacent columns

Here are the excel files with all the exercises to provide to students to download. For more info about providing editable files to students with iPads, see the post on teaching Excel with iPads.

Excel - Graphing.xlsx
File Size:	57 kb
File Type:	xlsx

Download File

---

Excel - Graphing - KEY.xlsx
File Size:	93 kb
File Type:	xlsx

Download File

---

---

Graphing Worksheet - Printout

To help reinforce that Excel can be used as a tool to discover information about a scenario, the excel file is supplemented with a printed worksheet. This worksheet asks further questions for students to answer about tabs 4-7.

It is expected that students can pull the following from their Excel data:

• Slope
• y-intercept
• Line Equation
• Prediction from model

Excel - Graphing Printout (pdf)
File Size:	432 kb
File Type:	pdf

Download File

---

Excel - Graphing Printout (editable)
File Size:	20 kb
File Type:	docx

Download File

---

Excel - Graphing Printout - KEY (pdf)
File Size:	435 kb
File Type:	pdf

Download File

---

Excel - Graphing Printout - KEY (editable)
File Size:	20 kb
File Type:	docx

Download File

---

---

Warm Up

Before starting on the graphing portion of the lesson, the first tab is meant as a warm up to review and practice the basic excel formula work that was presented in the previous lesson. This warm up covers the following topics:

• Using built in formulas such as average(), sum(), max() and min()
• Writing simple custom formulas (like percentage) with cell references
• Number Formatting
• Formula Propagation
• Pattern Fill

This can be presented as a full-class guided practice, group work, or individual warm up depending on the students' comfort level.  For more information on these topics, see the lesson plan write up for Solving with Formulas

Excel - Solving with Formulas

---

#1 - Heating of Compound X

Add a scatterplot with title and axis labels

1. Select the data in the table (this will work with or without the titles)
2. In the Insert menu, click on Charts and select X Y (Scatter)
3. For this set of data, it would ok to connect the points with a curve but typically we won't so it is best to stick with scatterplot that shows simply the unconnected points
4. Change the chart title to "Heating of Compound X" by clicking on the chart title box
5. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Time (min)"
6. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Temperature (C)"

---

#2 - Car Value by Year

​Add a scatterplot with title and axis labels

1. Select the data in the table (this will work with or without the titles)
2. In the Insert menu, click on Charts and select X Y (Scatter)
3. For this set of data, it would ok to connect the points with a curve but typically we won't so it is best to stick with scatterplot that shows simply the unconnected points
4. Change the chart title to "Car Value by Year" by clicking on the chart title box
5. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Year"
6. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Car Value"

---

#3 - Roth IRA

Insert a scatterplot for each set of data

1. Select the data in the table. Since there are two sets of data here, make sure to select the entire table including the column titles (so the legend is correct)
2. In the Insert menu, click on Charts and select X Y (Scatter)
3. For this set of data, it would ok to connect the points with a curve but typically we won't so it is best to stick with scatterplot that shows simply the unconnected points
4. Change the chart title to "Roth IRA" by clicking on the chart title box
5. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Year"
6. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Value"

---

#4 - Growth Rate of a Pine Tree

​The data provided tracks the height of a specific pine tree over time after it was purchased at a nursery. Plot the data and add a best fit line to answer the following questions:

1. What is the slope of the best fit line (with units)?
   
2. How tall was the tree on the day it was purchased from the nursery? 
   
3. Write the equation of the line. (use t for time and h for height)
   
4. Use your equation to predict the height of the tree when it reaches an age of 47.

1. Select the data in the table (this will work with or without the titles)
2. In the Insert menu, click on Charts and select X Y (Scatter)
3. Make sure you select the scatterplot that shows simply the unconnected points
4. With the graph selected, go to the Chart menu and click Layouts
5. To add a trendline with the equation, you will need to scroll down these layout options and select the one that includes an equation and line of best fit
6. Change the chart title to "Growth Rate of a Pine Tree" by clicking on the chart title box
7. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Time (years)"
8. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Height (m)"

---

#5 - Jump Height vs Bike Weight

In BMX dirt-bike racing, jumping high or "getting air" depends on many factors: the rider's skill, the angle of the jump, and the weight of the bike. Here are data about the maximum jumping heights for various bike weights. Plot the data and add a best fit line to answer the following questions:

1. What is the slope of the best fit line (with units)?
   
2. Write the equation of the line. (use w for weight and h for height) 
   
3. Determine the maximum height that an 18.4 lb bike could jump.

1. Select the data in the table (this will work with or without the titles)
2. In the Insert menu, click on Charts and select X Y (Scatter)
3. Make sure you select the scatterplot that shows simply the unconnected points
4. With the graph selected, go to the Chart menu and click Layouts
5. To add a trendline with the equation, you will need to scroll down these layout options and select the one that includes an equation and line of best fit
6. Change the chart title to "Jump Height vs. Bike Weight" by clicking on the chart title box
7. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Weight (lbs)"
8. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Height (in)"

---

#6 - Distance vs Time for a Rolling Tire

A tire is rolled down a flat road. Data for the tires distance from the starting point was collected and placed into the following data table. Plot the data and add a best fit line to answer the following questions:

1. What is the slope of the best fit line (with units)?
2. Write the equation of the line. (use t for time and d for distance) 
   
3. After 25.4 seconds, how far is the tire from the starting point?

1. Complete the table based on the data provided
2.  Select the data in the table (this will work with or without the titles)
3. In the Insert menu, click on Charts and select X Y (Scatter)
4. Make sure you select the scatterplot that shows simply the unconnected points
5. With the graph selected, go to the Chart menu and click Layouts
6. To add a trendline with the equation, you will need to scroll down these layout options and select the one that includes an equation and line of best fit
7. Change the chart title to "Rolling Tire" by clicking on the chart title box
8. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Time (s)"
9. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Distance (m)"

---

#7 - Lab Data

Your lab group collects the time it takes a battery powered car to move in 0.5 meter increments. For each distance, you collect 3 trials worth of time information. Average these times and plot the distance vs average time and add a best fit line to answer the following questions. You will need to rearrange the columns on your table in order to create a graph with time on the x-axis and distance on the y-axis.

1. What is the slope of the best fit line (with units)?
   
2. Write the equation of the line. (use t for time and d for distance) 
   
3. How long would it take for this battery powered car to go 12.4 meters?

1. Select the first two values listed in the Distance column and long press to open up the tooltip menu. Select Fill to make the selection corners turn from circles to squares. Pull down on the bottom right square to fill the rest of the column in ascending increments of 0.5 m.
2. In the first row for the Average Time column, add a formula to average the three trials together. This can be done easily by going to the Formulas menu and selecting Autosum ⇨ Average. The resulting formula should be =AVERAGE(C5:E5)
3. In order to make the data selection for the graph, the distance and time columns need to be adjacent. One easy way to do this on the iPad is to select the entire column of data and drag it somewhere else. Once you have made your Distance / Average Time table select this new set of data. (*note: this is more easily done on a computer by holding ctrl and making multiple selections)
4. In the Insert menu, click on Charts and select X Y (Scatter)
5. Make sure you select the scatterplot that shows simply the unconnected points
6. With the graph selected, go to the Chart menu and click Layouts
7. To add a trendline with the equation, you will need to scroll down these layout options and select the one that includes an equation and line of best fit
8. Change the chart title to "Battery Powered Car - Distance vs Time" by clicking on the chart title box
9. Add an x-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Horizontal and editing the label that appears to show "Time (s)"
10. Add an y-axis label by going to the Chart menu and selecting Elements ⇨ Axis Titles ⇨ Primary Vertical and editing the label that appears to show "Distance (m)"

---

Files

Excel - Graphing Printout (pdf)
File Size:	432 kb
File Type:	pdf

Download File

---

Excel - Graphing Printout (editable)
File Size:	20 kb
File Type:	docx

Download File

---

Excel - Graphing Printout - KEY (pdf)
File Size:	435 kb
File Type:	pdf

Download File

---

Excel - Graphing Printout - KEY (editable)
File Size:	20 kb
File Type:	docx

Download File

---

Excel - Graphing.xlsx
File Size:	57 kb
File Type:	xlsx

Download File

---

Excel - Graphing - KEY.xlsx
File Size:	93 kb
File Type:	xlsx

Download File

---

---

Follow the links below to get details and materials for the rest of this 4-day unit on Excel

​Incorporating Excel on the iPad

​Day 1: Solving with Formulas

​Day 2: Graphing from Data

​Day 3: Graphing Data Collected from a Lab

​Day 4: Excel Skills Assessment​