Using Excel to Chart Pupil Data

C-ABA Excel Graphing Instructions

Demonstration Case

Using Excel to Chart Pupil Data

Mireya is an inquisitive young girl who has difficulty staying in her seat and on task. Her teacher taught the SNA to collect Duration data using a stop watch during reading sessions. These data were converted to percentages of time in seat and on-task. The SNA collected data for one week as baseline and then the teacher moved Mireya’s desk closer to the front of the classroom as an intervention. The SNA collected data for a further 9 days in the same manner. The data are presented in the following table.

Column A / Column B
15
7
10
11
9
25
30
55
67
87
95
100
97
100

These are the data we will chart first.
Using Excel to ChartAB Data

Task 1: Entering the Data on the Worksheet

Open Excel programme.

Double click on Sheet 1 at the bottom of the page. When it becomes Bold, write your own label here. (We will call our first worksheet – DEMO.)

Count up how many data points you will be plotting in total. Using Column A, number the cells downwards. Avoid dates unless you really want them. Numbers are simpler.

Remember the ‘conditions’ called Baseline and Intervention? If you have Baseline data, enter them vertically in Column B. As soon as you are done with entering your Baseline data and want to enter Intervention data, you must move over to Column C.

If you have 10 data points for baseline, and 14 data points for your first (or only) intervention, you start entering your first intervention data point in Column C, at line 11.

Once your data are entered in the correct columns, you are ready to chart.

Task 2: Charting the Data

Look at the menu tabs across the top of the page in Excel. These are Home, Insert, Page Layout etc. Select the Insert tab. This gives you access to the different types of charts and line graphs you can use.

Now highlight (select) your two data columns. Do NOT select Column A here, just Columns B and C. Once they are highlighted, go to the Line option among the charts, pull down the menu and select the first one in row two – when you select it, it will say, ‘Line with markers displayed...’.

Now you should have a chart on your screen with baseline and intervention data in different colours. We will now make the data points all the same and both lines (data paths) and data markers all black.

Task 3: Editing – Fixing the Chart and Data

Place the cursor on any data point in baseline, right mouse click, and select ‘Format data series’. This brings up a selection box.

Go to Marker Options, select, and on the right side, select ‘Built In’. Choose the square from the types. (You may choose any symbol. Just remember, this type of charting has conventions. Whatever you choose for baseline, you must choose the same for all other conditions – interventions, return to baseline etc.)

Now go back to the left side and choose Marker Fill. Go to the right and select Solid, then black from the colour options.

Go back to the left side and choose Line Colour. Go to the right and select Solid, then black from the colour options.

Go back to the left side and you may choose a line style if you wish. We typically use the default here so you may ignore this.

Go back to the left side and choose Marker Line Colour and select Solid, and then black.

Now, close this window and click anywhere on the chart and see if the baseline data are all black squares joined by plain black lines.

Now do the same with the intervention data. Go back to instruction number 10 and follow the directions again for Intervention data.

Now all your data should be the same colour and design.

Now go back to the Layout Tab. Find the Gridlines menu. Open it and select None. The gridlines disappear from your chart.

Stay with (or go to) the Layout tab. We will now label the axes.

4: Labelling Axes and Conditions

Go to Axis Titles. Select Horizontal Axis and choose the Below option. A text box will appear on your chart below the X-axis. Delete the text there (double click on each word and hit the space bar) and type: Sessions, Lessons, Days or some measure of time.

Go back to Axis Titles. Select Vertical Axis and choose Rotated Title. A vertical text box appears next to the Y-axis. Delete the text and type in: Percent of ...., Number of .....or some measure of behaviour.

Select the Legend (Series 1, Series 2) and right click on it. Delete it.

Now let’s put a dashed vertical line between Baseline and Intervention. Click on the chart area.

Go to the Insert tab. Select Shapes and choose Line. Place the line between the data points that end baseline and start intervention. Once you release the line you get options to change how the line looks. Make it black, dashed, and a little heavy. Then click off the line and you will see what it looks like.

Choose the Insert Tab and find the Text Box. Place the text box in the baseline side. Type ‘Baseline’ in it and use the top of the box to drag it up a bit if necessary.

Choose Insert and Text Box again and place one on the Intervention side and type a description of the intervention (2-3 words). Do not use the word ‘intervention’ here.

The last thing we will do is to be sure the Y-axis (vertical one) only goes to 100 if we are using percentages. You can skip this step if you are using ‘numbers’ only and not percentages.

Place the cursor on the 120 and right click. Choose Format Axis.

You have a set of options here. Look at the Max number. It will show 120. Click on the ‘Fixed’ dot. The default is ‘Auto’. Then you can delete the 120 and replace it with 100.

You are now done. Select the chart and save and print. If you do not select the chart, Excel will print off the data as well as the chart.

Demo Chart Completed