Information You Need to Set up a Graph

Cheat Sheet for using Excel to Make Graphs Updated 12/8/18

Barb Scierka SCRED 320 358-3616 or 651 674-2436 or

A. Information you need to set up a graph:

Time period for graph – school year, quarter, semester, 2-3 months, 1 month
How often will you collect data – 2-3 times per week, weekly
What is the possible range of scores – 0 to what number? : Look at student’s present level of performance, goal for end of time period, and peers’ performance level.
What subject area will be measured
What is the measurement unit that will be used (number of correct words read correctly in one minute, number of correct vocabulary matches in five minutes, etc.)

B. Steps to creating a new graph:

1. Open Microsoft Excel

If a new spreadsheet does not appear, go under File and select New.

2. In column A enter three dates into cells to show the pattern you want for this graph. (The program will recognize that you are doing dates if you enter them in in this format: Sep 1 – first three letters of the month, NO period after the abbreviation, space, then the number for the date)

Click in the last box of the three dates you entered, then this “cell” should be highlighted. Place the cursor over the small square in the bottom right corner of that box (your cursor should now be in the shape of a square), click on it and hold. Drag the cursor down to fill in additional dates. Stop holding the mouse button when you reach the last date you want.

Move back up to the first date in this list. Click and hold cursor in that first cell. Now press and hold the Control button. As you are holding down the control button (and you still are holding down the click on the mouse), drag the cursor down your list of dates until all of the dates are highlighted. Release mouse and control button.

Now with this list of dates highlighted, go under Edit to Fill and select Series. See picture below.

A pop-up menu will appear that looks like this:

The only change you need to make on this pop-up menu is to change the Date Unit (3rd box) from Day to Weekday. This will give you only Monday through Friday. Click OK.

Troubleshooting: If this did not work there are two commons reasons, try it again paying close attention to these: 1) be sure that you are holding down the control button when you select the dates. 2) The dates must be highlighted when you go to the Edit menu to select Fill and Series.

3. Highlight all of the dates and columns B and C for those dates. (To do this, click and hold as you drag down your list of dates and move over to the right to get columns B and C highlighted.

4. Click on chart wizard button located in the menu above the spreadsheet.

This is the menu bar to look for at the top of the page.

5. On the pop-up screen in the standard types section, click on line

6. Choose the style of line graph that shows the data points, like this:

Press Next button.

7. On the next pop-up screen look at the miniature graph and be sure that the dates are running along the bottom of the graph (horizontal axis). If it isn’t change the button that is selected (Rows or Columns button)

Press Next button.

8. The next pop-up screen has many things for you to add or change.

Notice the tabs that stick up and look like recipe cards – we will go to most of those sections and fill in things.

First, the Title section:

10. Click on the word Titles if it is not the top card.

Fill in Chart title – most of the time this will simply be the academic subject area like Reading, Writing, Math.

11. Category or x- axis is where the dates will be so just type in the word Dates.

12. Value or y-axis is where you will write the measure you are recording, for example: number of words read correctly in one minute, or number of digits correct in two minutes, etc.

13. Click on Axes. This should come up automatically set for you. Check to see if category (x) axis and value (y) axis are checked. Under Category (x) axis automatic should be checked.

14. Click on Gridlines. Checks should be for major gridlines for x and y axes. I would recommend also selecting minor gridlines for x axis.

15. Click on Legend. If you are graphing only one student, deselect the legend by clicking on the check mark to turn it off. If you have more than one student on the graph you will need the legend to identify which line goes with which student.

16. You don’t need to make any changes on the other two, so you can click Next. Data Labels allows you to have the number printed on the graph next to the data point. Data Table allows you to print out the data sheet along with the graph.

Press Next button.

17. The next screen asks you about placement of the chart. Click the button for as new sheet and fill in a name for the graph, such as: “John’s graph”.

Press Finish button.

18. The graph is basically done at this point. One other change I would recommend making is to place the cursor on the gray section of the graph (the background color) and double clicking. A Format Plot Area screen will come up in the Area section click on the white square to select a white background then click Ok.

19. To make further changes on the graph, go to Chart on the top menu. Select Chart type to change from a line graph to some other kind, or Chart Options to change title, axes, gridlines, legend, etc.

20. Changing the scale or the numbers along the side of the graph:

To change numbers along the y or vertical axis: place the cursor along the numbers on the y axis and Value axis should appear in a box, double click. The pop-up screen will allow you to change several things. Click on the card that says Scale, then you can set the minimum and maximum numbers, most common is probably 0 to 100. You can also set what the major and minor units will be. Most common would probably be major units of 5 with minor units of 1. To get a large enough graph so you can do this you need to go under File and select Page Setup. Then select margins and change the margins to .25 for top, bottom, and sides.
You can also change the way the dates appear across the bottom of the graph. I think the best way is to have just the Mondays dates showing and then lines for the rest of the days of the week. This is how most of our graphs look.

To Do this:

Double click along the x or horizontal axis. You will get a pop up screen that looks like this:

You want the Major unit to say 7 – 7 days in a week

And the Minor unit should say 1 – each day showing. Click OK.

21. SAVE graph! I would suggest saving the graph as John-Reading.

22. Notice at the bottom of the screen for the data sheet and graph you have tabs that look like this:

23. Double click on Sheet 1, the words “sheet 1” will become highlighted. Type in “John’s data”. Then when you need to add data you can go straight to this sheet.

24. Double click on chart 1, this will become highlighted. Type in “John’s graph”.

25. Then SAVE again.

C. Adding data to an existing graph (without having to create a whole new graph by repeating the steps above)

Open the file for that student, for example John-Reading as said above.
Look along the bottom of the screen for the data sheet for that student, for example John’s data as said above. Click on that tab.

Add data to the spreadsheet for the corresponding dates.

Do a SAVE

Click on the tab for John’s graph. This will take you to John’s graph. Duh! You should see the changes you added to the data sheet appear on this graph.

D. Adding Goal lines and intervention lines

intervention and goal lines must be added using the drawing tools. That is why it is very important to make your graph for the whole time period right from the start. If you add dates, they will be added to your graph but the goal line and intervention lines will stay the same and that will mess up your graph. You can change the goal line but it means adjusting it every time you add a day.

This is the drawing tool palette that you will use:

To get this palette go under View, Toolbars, select Drawing:

Select the line drawing tool from this drawing tool palette.
draw the line from the median baseline score to the goal at the end of the time period
While this line is still highlighted (or click once on it to highlight it again – when the line is highlighted you will see a square at each end)

Select the color and thickness you want for the line:

line thickness

line color

Do the same thing for intervention lines, drawing vertical lines on the date that the intervention began. I would recommend different colors of lines for intervention and goal lines.
To add words to describe your interventions, go to the text writing tool also on the drawing palette. . Place the words next to your intervention line.

How to add trend line: Under Chart select add trend line. A pop up screen will appear.

Select a linear trend line. Select OK. The trend line will be done for all of the data on the graph.

Making a graph from a template.

You can getsome templates of graphs from the SCRED website under special education (

Download the template to your computer.
Open the template.
Right away do a Save As and name it something else, this way you will still have the original graph template on your computer.
To make changes to this graph, such as the Title, the labels for the axes, the style of graph, the legend you will need to go under Chart which is in the top menu bar.

To change the type of graph – go to columns, pie chart or some other style: go to Chart, then Chart Type.
These are the different charts you can choose from.

To make other changes, go to Chart, then Chart Options

8. This pop-up screen has many things for you to add or change.

Notice the tabs that stick up and look like recipe cards – we will go to most of those sections and fill in things.

First, the Title section:

10. Click on the word Titles if it is not the top card.

Fill in Chart title – most of the time this will simply be the academic subject area like Reading, Writing, Math.

11. Category or x- axis is where the dates will be so just type in the word Dates.

12. Value or y-axis is where you will write the measure you are recording, for example: number of words read correctly in one minute, or number of digits correct in two minutes, etc.

13. Click on Axes. This should come up automatically set for you. Check to see if category (x) axis and value (y) axis are checked. Under Category (x) axis automatic should be checked.

14. Click on Gridlines. Checks should be for major gridlines for x and y axes. I would recommend also selecting minor gridlines for x axis.

Press Next button.

17. The next screen asks you about placement of the chart. Click the button for as new sheet and fill in a name for the graph, such as: “John’s graph”.

Press Finish button.

20. Changing the scale or the numbers along the side of the graph:

To change numbers along the y or vertical axis: place the cursor along the numbers on the y axis and Value axis should appear in a box, double click. The pop-up screen will allow you to change several things. Click on the card that says Scale, then you can set the minimum and maximum numbers, most common is probably 0 to 100. You can also set what the major and minor units will be. Most common would probably be major units of 5 with minor units of 1. To get a large enough graph so you can do this you need to go under File and select Page Setup. Then select margins and change the margins to .25 for top, bottom, and sides.
You can also change the way the dates appear across the bottom of the graph. I think the best way is to have just the Mondays dates showing and then lines for the rest of the days of the week. This is how most of our graphs look.

To Do this:

Double click along the x or horizontal axis. You will get a pop up screen that looks like this:

You want the Major unit to say 7 – 7 days in a week

And the Minor unit should say 1 – each day showing. Click OK.

21. SAVE graph! I would suggest saving the graph as John-Reading.

Making a graph for steps for a procedure or speech.

Open 3consecutivetrials.xls
Immediately do a save as and change the name of the file.
Open the worksheet with the data (the spreadsheet).

You can click in the boxes to change the labels for the times you give the measurements. In this example the student is measured 3 times a month.

When you click in the box the information that is already in this box will show up in this area:

To change something that is already written there, put your cursor in the box above (officially it’s called the formula bar)

You can then change the number of trials the student will get by clicking in those boxes and writing over it in the formula bar.
When you go back over to the graph sheet, you should see the changes you made.

How to get the lines to show up between your data points.

If you are in OS 9:

Have a graph opened when you do these steps.

Under Edit select Preferences.

A box like the one below will appear.

Click on the card that says Chart to make that the top card.

Select the button that says ‘interpolated’. This will give you the lines connecting dots that are spread out. If the buttons cannot be pressed, it is because you did not have a graph open when you went to Edit and Preferences. Cancel out of this, open a graph, then go under Edit.

If you are in OS 10:

Have a graph opened when you do these steps.

Under Excel select Preferences.

In the left hand list – select Chart. In the right hand list select ‘interpolated’. Click Okay

SCRED Office:BJES:Data:Graphing:2004-05 graphs:Cheat Sheet for Excel Graphs