Graphing IEP Data in a Spreadsheet, Step by Step

A teacher has twelve weekly scores in a notebook. She wants a graph for the Friday meeting, and she has a spreadsheet she has never used for charts. The task takes about fifteen minutes the first time. After that, it takes two.

This guide walks through a graph in Excel or Google Sheets with a data table, an aimline column, a chart, and a trendline.

Why a graph with a goal line

NCII's goal-setting guide says teachers sometimes graph student scores with no goal or goal line, and that this makes it impossible to assess whether the rate of improvement is adequate. A graph with only scores shows a pattern. A graph with a goal line shows whether the pattern is enough. The logic behind the lines is in aimline and trendline explained.

Step 1: set up the table

Use one row per probe. Keep the layout the same for every student.

Week 0 holds the baseline. Put the baseline, the goal, and the number of weeks in three cells at the top of the sheet, for example B1, B2, and B3, so you can change them in one place.

Step 2: calculate the aimline

The aimline starts at the baseline and rises by the weekly growth needed. If the baseline is in B1, the goal in B2, and the weeks in B3, the weekly growth is (B2 minus B1) divided by B3. For the aimline column, with the week number in column A, the table headers in row 5, and the first row of data in row 6, use:

`=$B$1 + ($B$2 - $B$1) / $B$3 * A6`

Copy it down. With a baseline of 42, a goal of 70, and 12 weeks, the values rise by about 2.33 a week, as in the table.

Extend the aimline column to the last week of the goal, even for weeks with no score yet, so the line runs to the goal. Leave the Score cells empty for those weeks.

Step 3: build the chart

In Google Sheets, Google's help says to select the cells and choose Insert, then Chart. The chart editor has a Setup tab, where you change the chart type and data range, and a Customize tab for gridlines, chart style, and chart and axis titles. In Excel, select the cells and insert a scatter or line chart from the Insert tab.

Plot Score and Aimline as two series against Week. Make the scores points, with a line if you like, and the aimline a plain line in a different style.

Step 4: add a trendline

Microsoft's help describes adding a trendline from the Chart Elements button (the plus sign at the chart's top right), then Trendline, and picking options. The page lists the types as exponential, linear, logarithmic, polynomial, power, and moving average. For IEP data, choose linear. The format pane also lets you name the trendline, and it has Forward and Backward fields to project the line. Do not project it unless your team has agreed to; it is a summary of the past, not a forecast.

Google Sheets has a separate help article for trendlines, and menu labels change over time, so check the current wording there.

Step 5: get the slope as a number

A trendline on the chart is a picture. The SLOPE function gives the number. Microsoft documents it as returning the slope of the linear regression line through a set of data points, with the syntax `SLOPE(known_y's, known_x's)`: y values first, then x values. For the table above, with scores in C6:C12 and weeks in A6:A12:

`=SLOPE(C6:C12, A6:A12)`

This returns about 2.1 for the invented data, matching the calculator in the aimline guide. Compare it with the aimline's weekly growth, (goal minus baseline) divided by weeks, which is about 2.33. The trend is slightly flatter. Microsoft notes that empty cells are ignored, so a blank score does not break the formula.

If you have no spreadsheet, NCII's guide shows a hand estimate using the median of the first and last groups of points.

Build it once, copy it per student

Make one clean template tab with the table, the three setting cells, the aimline formula, and a chart already formatted. For each new student, copy the tab, rename it with a code, type in the baseline, goal, and weeks, and start entering scores. The chart updates itself. A caseload of fifteen students then needs fifteen copies and about a minute of setup each, and every graph looks the same, which helps when you compare them at a meeting.

Step 6: label it so someone else can read it

Google's help lists chart and axis titles in the Customize tab. Use them.

• Title: Student code, skill, and measure, for example "Student J.R., words correct per minute."
• Vertical axis: The unit.
• Horizontal axis: Weeks, or dates.
• Legend: Scores, aimline, trendline.
• Marks: A vertical line or a note on the date of any change in instruction.
• Gridlines: Light enough to read through, as Google's help describes.

A few cautions

• Keep the vertical scale fixed. A scale that auto-fits to the data can make small changes look dramatic. Set the minimum and maximum by hand, and use the same range from graph to graph for the same measure.
• Do not smooth the scores. The points are the data.
• Keep one file per student, or one tab per student. Not one giant table.
• Back it up. A graph that vanishes in March is a bad surprise.

Spreadsheet graph errors

• Aimline from the first score instead of the baseline. Use the baseline in the IEP.
• Plotting dates as text. The x axis may space them unevenly or sort them wrong.
• Forgetting blank weeks. In a line chart, they close up the gap. In a scatter chart, they show.
• Treating the trendline as a prediction.

For how to explain the graph to a family, see how to read a progress graph for parents. Terms are in the glossary.

If your district has a graphing system

The sources document the tools, not a required graph format. Your district may have a template or a data system that makes graphs for you. If so, check that it shows a goal line and the baseline, and use it.