How to help your students make a graph in excel
I realized something a few years ago as my students were turning in their hand-drawn graphs; they will probably NEVER graph something in the “real-world” on paper. Most hobbies or jobs utilize the computer. THIS was a moment where my education courses from college came back to me: “You are teaching 21st Century learners. Make sure you prepare them for the 21st Century.”
So, now I walk my 7th graders through how to make a graph in Excel and I expect my upper level courses to also work in Excel. This requires students to graph data from labs, data that is given to them, and then to be able to analyze their data in a conclusion OR in a set of questions.
I do wish I had MORE time to integrate Excel into my classes. It is a vital tool that is found in many different jobs and skillsets.
I’m going to break this into two parts:
Method of Teaching
How to graph in Excel - 15 steps!
iPad
Computer
Method of Teaching
My students are lucky enough to have a 1:1 device environment. This means that all of my 7th grade students have their own iPad to use throughout the day to complete and submit assignments. My upper classmen have laptops. Gone are the days of trekking everyone to the computer lab, getting logged on, reminding students of the assignment, and spending the rest of class working on getting the computers to open Excel while two students completed the assignment - those were the days!
To teach my 7th grade students how to graph, I dedicate a whole class period at the beginning of the year to walking through it. In my case, this means about 48 minutes. The first thing I do is get OFF of my laptop. The desktop and iPad version of Excel are DIFFERENT - enough that it can cause chaos. I grab an extra iPad and Airplay the iPad screen to the projected screen. This can be done if you have an AppleTV device or something comparable. For my older students I don’t dedicate a whole hour, but do walk through the process with them so they feel more comfortable and confident in the process. The best thing for your students is to walk through the steps together.
I always use this data set for my example. It gives a story behind the data for students to follow. This helps illustrate that data isn’t random.
How to graph in Excel - iPad
Open a new ‘book’ in Excel.
In a cell near the top left corner, double click. This will bring up the keyboard on the bottom of the screen.
Type the title of the first column from the sample data (Year) in the box you’ve selected.
In the cell directly underneath the ‘Year’ title, place your first year (1905).
It is VERY important to place all the data in cells next to each other. Excel doesn’t know what to do with empty cells and it will prevent a graph from being generated.
In the cell directly underneath the previous cell continue with the next year (1910). Repeat until all the years from the data have been recorded.
In the cell directly to the right of the ‘Year’ title, type ‘Deer Population.’
This title is too long to fit in the space. It is still only taking up one box, so you can leave it as is, BUT if you don’t like the way it looks you can wrap the text.
Make sure the ‘Deer Population’ cell is selected (has a green box around it). You might have to click off and click back onto the cell.
On the top, select the icon on the Home tab that has three lines with an arrow going from the second to third line. This will wrap the text in the box selected.
ANOTHER option is to make the column bigger to fit the text. To do this, select the letter of the column the ‘Deer Population’ title is in.
Double click the double lines in the box with the column letter. It will auto adjust the column width.
Enter the deer population data directly underneath the title and next two their corresponding years.
There should ONLY be numbers in your number cells. If words are added to the cell, such as ‘4,000 deer’ a graph will not be generated.
Click the cell with the ‘Year’ title.
Click and drag the green dot in the lower right corner until all the data is selected.
Again, ONLY the data should be selected. No extra cells.
On the top of the screen, select ‘Insert.’
Select ‘Charts’ from the second row on the top of the screen.
Because this data is a measurement over time, it should be a line graph. Select ‘Line’ from the dropdown menu.
Top left chart option is a great option.
You’ll notice that two lines appear: one for the deer population and another for the year. In the iPad version of Excel, you cannot change the lines because the mobile version has limited capabilities compared to the desktop. What you CAN do is change the chart title by double clicking on the words ‘Chart Title.’
Work Around: You COULD just highlight the deer population column, ‘insert’ line graph to create a graph with only the deer population. This will create a single line on the chart.
To make it a great graph, make sure the graph is selected. A green ‘Chart’ tab will automatically open. In the second row menu select ‘Elements’ and ‘Axis Titles.’ You will now be able to add a label to the ‘Primary Vertical’ axis.
At this point, this is about as much as you need your 7th graders to accomplish. They can submit this document using their LMS site.
How to graph in Excel - Computer
Open a new ‘book’ in Excel
Click in a cell near the top left corner, click.
Type the title of the first column from the sample data (Year) in the box you’ve selected.
In the cell directly underneath the ‘Year’ title, place your first year (1905).
It is VERY important to place all the data in cells next to each other. Excel doesn’t know what to do with empty cells and it will prevent a graph from being generated.
In the cell directly underneath the previous cell continue with the next year (1910).
Repeat until all the years from the data have been recorded.
Pro Tip: Highlight the first two years. Hover over the bottom right corner until a black crosshair appears. Click and drag the crosshair down. You will notice that all the years autofill because the data is recorded every 5 years. You can use this method for any predictable pattern in Excel (months, any numbering pattern, etc).
In the cell directly to the right of the ‘Year’ title, type ‘Deer Population.’
This title is too long to fit in the space. It is still only taking up one box, so you can leave it as is, BUT if you don’t like the way it looks you can wrap the text.
Make sure the ‘Deer Population’ cell is selected (has a green box around it). You might have to click off and click back onto the cell.
On the top, select the ‘Wrap Text’ option in the Alignment section of the Home tab. This will wrap the text in the box selected.
ANOTHER option is to make the column bigger to fit the text. To do this, select the letter of the column the ‘Deer Population’ title is in.
Hover over the right border line of this box until a black line with arrows pointing in opposite directions appears. Double click. The column will automatically adjust to the cell with the longest text.
Enter the deer population data directly underneath the title and next two their corresponding years.
There should ONLY be numbers in your number cells. If words are added to the cell, such as ‘4,000 deer’ a graph will not be generated.
Click the cell with the ‘Year’ title.
Hover over the lower right corner until a WHITE crosshair appears. Click and drag to the lower right corner until all the data is selected.
Again, ONLY the data should be selected. No extra cells.
On the top of the screen, select ‘Insert.’
Select the line graph icon in the ‘Charts’ section of the taskbar. This data is a measurement over time, so it should be a line graph.
Top left chart option is a great option.
You’ll notice that two lines appear: one for the deer population and another for the year. You need to get rid of the year line to accurately represent your data set. Here’s how to do that:
Click on the ‘Year’ line on the graph.
With the line selected, right click. Choose ‘Select Data’ from the pop up menu.
Click the ‘Edit’ button under the Horizontal Axis Label.
Another pop up box will appear. This box will let you select the correct data range for the x axis. Click the small blue box with the red arrow.
The pop up box will change again. It will now allow you to click and drag over the NUMBERS in the ‘Years’ column.
When the data has been selected, click the blue icon with the red arrow in the pop up box. Then click ‘OK'.’
A large pop up box should reappear. On the left side, uncheck the legend entry for ‘Year'.’
Click ‘OK.’
To make it a great graph, the title on the top can be edited to a descriptive title. Just double click on the automated title to edit.
A graph is incomplete with axis labels! To add them, make sure the graph is selected. In the top left corner select the ‘Add Chart Element’ dropdown and select ‘Axis Title.’ By selecting both, a text box will be added on both axis. To edit, double click in the text on the graph.
Method of Teaching (continued)
Once I walk my students through how to navigate Excel on their device, I immediately assign a new data set for them to graph in Excel on their own. This way they can immediately practice the steps we went over in class, but without me leading it. I really step back at this point and let my students work through the process. If they get stuck I encourage them to ask a classmate. Some students LOVE being the tech guru in class and this is a great opportunity for them to be an expert!
