Creating Functional Analysis Graphs Using Microsoft Excel® 2016 for PCs

Abstract

Practitioners in the field of applied behavior analysis rely on graphing software to display the data they collect during assessments. However, the graphing process can be cumbersome and procedures change as new versions of software programs are released. The current tutorial provides step-by-step instructions for graphing functional analysis data using Microsoft Excel 2016 for PCs. Instructions for creating functional analysis data depicted in reversal, latency, mulitielment, and trial-based functional analysis are provided.

In the field of applied behavior analysis, the utilization of graphs to interpret data is paramount. However, the process of creating graphs can be cumbersome, a challenge which has been ameliorated in part by the publication of tutorials which list out the steps necessary to create graphs using current popular software programs. Carr and Burkholder (1998) outlined steps for creating graphs using Microsoft Excel®, and Dixon et al. (2009) provided updated instructions for the 2007 version of that program. Vanselow and Bourret (2012) included an online training component to their published article, along with some additional techniques, such as creating bar graphs and using phase lines that update as data are added to the graph.

The introduction of Microsoft Excel 2016 for PCs resulted in numerous software changes that require additional expertise by the user to create graphs. The purpose of this article is to provide step-by-step instructions for creating graphs in Excel® 2016 that are commonly used when conducting functional analyses: reversal, latency, and multielement design graphs, along with bar graphs which are typically used when conducting trial-based functional analyses. This updated task analysis also includes directions for additional formatting options, such as changing the size of symbols in the legend and re-positioning the legend in the chart area.

It is important to note that the methods described in this tutorial represent one way to create single-subject graphs, and there are other methods which users may prefer. In particular, Dubuque (2015) describes how to input condition lines using gradient and transparency options, and also provides instructions for creating condition labels (rather than using a legend, which is included in this tutorial). Deochand (2017) describes a method for inputting condition lines using error bars, whereas the current tutorial describes how to input condition lines by creating a data series.

This tutorial includes sample data sets which are embedded within the instruction sets for each type of graph. The reader can input these data as they try out the steps listed below, and then compare their graphs to the model graphs included in this article. Users may find it easier to print the tutorial when constructing graphs, as the tutorial sometimes references steps previously covered (e.g., when creating a latency FA graph, the reader is asked to repeat steps outlined in the reversal FA graph section).

Task Analysis for Creating Reversal FA Graphs

Opening a Workbook

1. Begin by opening Excel 2016.

2. You will notice a column for “Recent” documents on the left side of the screen. To the right of this is a section which allows you to select a template. Select “Blank Workbook” by moving your cursor over the icon and clicking the left mouse button.

3. The workbook will open, and now you can enter your data.

Entering Data

1. Enter a label for sessions (e.g., type “Session #” in cell A1).

2. Enter labels for each of your conditions in the first cell of adjacent columns.

   1. e.g., column B1 = attention.
   2. e.g., column C1 = escape.
   3. e.g., column D1 = play.

3. Enter the label “Condition Lines” in the next column (e.g., E1).

After you enter these labels, your database should appear as it does in Fig. 1.

Fig. 1.

Arrangement of labels in database

• 4.In column A, enter in numbers 1 through 20 to represent each of the 20 sessions for which data were collected (e.g., 1, 2, 3, 4…).
• 5.
  Enter in the corresponding data in the attention, escape, and play cells—leave cells in which there are no data blank.

  1. Attention values to enter: 0, 0, 0, 0.
  2. Escape values to enter: 8, 9, 10, 12.
  3. Play values to enter: 0, 0, 0, 0.
  4. Escape values to enter: 10, 12, 10, 11.
  5. Play values to enter: 0, 0, 0, 0.
• 6.
  As can be seen in Fig. 2, insert two condition line values in the session # column at 0.5 values between changes in conditions.

  1. e.g., our attention sessions stopped on session 4, and escape sessions began on session 5. In order to have a condition line populate between the attention and escape conditions, add in 4.5 and 4.5 under the session column between the 4 and 5 values. This will create an x-axis value that falls between sessions 4 and 5 on the x-axis, which will become the location of your condition line.
  2. To add rows to the spreadsheet, right-click on the row just to the left of the cell with session 5, as displayed in Fig. 2.
  3. An option menu will pop-up. Select “Insert” then repeat this step so you have two new blank rows added to your spreadsheet between sessions 4 and 5.
  4. In the condition lines column, enter the values of 0 for the first 4.5 session, and then round up from the highest data value to determine what number you will list for the second 4.5 session (in this case, the highest data value is 12, so we can round up to 15 so that your condition line will extend just beyond your highest data point).

Fig. 2.

Inserting rows for condition line data points

After you enter the data, your spreadsheet should look like Fig. 3.

Fig. 3.

Sample data set for a reversal FA

Creating the Graph

1. Highlight all of your data and column labels by left-clicking in cell A1, holding the button down, and then dragging the cursor down and to the right to cell E29.

2. Click on the Insert tab, and select the “Scatter” option in the Charts area (see Fig. 4), then select “Scatter with Straight Lines and Markers” option.

Fig. 4.

Selecting a scatter graph

Once you select this chart type, your graph should look like Fig. 5.

Fig. 5.

Initial scatter graph for a sample reversal FA data set

• 3.Left-click on the graph and you will notice a Design tab that appears in the menu. Select the “Move Chart” function available beneath this tab as displayed in Fig. 6.

Fig. 6.

Selecting the “Move Chart” function

• 4.Select “New sheet,” type in “FA Reversal Graph” and click the OK button as demonstrated in Fig. 7. This will move your chart to a separate sheet and make it easier to work with.

Fig. 7.

Creating and labeling a new sheet in the workbook for a graph

Editing the Graph

There are several edits to the graph which can be made to match graphing conventions typically observed in the field of behavior analysis.

1. Left-click on the vertical gray gridlines (blue circles should appear on the top and bottom of these lines if you selected them) as can be seen in Fig. 8, and then hit the “Delete” button on your keyboard.

2. Do the same for the horizontal gray gridlines.

Fig. 8.

Selecting vertical (top panel) and horizontal (bottom panel) gridlines to be deleted

• 3.Your graph should now look like the graph in Fig. 9.

Fig. 9.

Reversal FA graph following deletion of gridlines

• 4.You will notice your y-axis value is higher than the highest condition line value. As displayed in Fig. 10, go back to your data sheet in Excel and change the upper value of your condition lines to 16, so they are even with your upper y-axis value. This step has been included so you know how to make the adjustment if need be—on future graphs, rounding up to an even number may eliminate the need to perform this step.

Fig. 10.

Adjusting condition line values to modify the graph

• 5.
  Return to the tab with your graph on it and right-click on the x-axis, then select “Format Axis.”

  1. Change the maximum value to 20, so it matches the number of sessions you conducted.
  2. When you are done, your graph should look like the picture in Fig. 11.

Fig. 11.

Reversal FA graph following a change in maximum x-axis values

• 6.
  Next, remove references to “Condition Lines” as a data path. The condition lines are not an actual data series so delete them from the legend on the bottom of the screen and remove the data symbols attached to the condition lines.

  1. Deleting the “Condition Lines” label from the legend.

     • i.
       Left-click on the legend at the bottom of the screen. As can be seen in Fig. 12 (top panel), the whole legend will be selected. Left-click on the “Conditions Lines” label in the legend so that only that portion of the legend is selected (Fig. 12, bottom panel).
     • ii.
       Hit the delete key on your keyboard.
  2. Removing symbols for condition lines.

     • i.
       Left-click on one of the yellow circle markers attached to a condition line as displayed in Fig. 13.
     • ii.
       You will notice a few of the yellow markers will be selected because of the blue circles that appear on them (if this does not work, left-click outside of the graph area and try again).
     • iii.
       As displayed in Fig. 14, right-click on one of the yellow markers with a blue outline and select “Format Data Series.”

       1. Left-click on the “Fill and Line” symbol.
       2. Left-click on “Marker.”
       3. Select “Marker Options” then select “None.”
     • iv.
       Your graph should now look like Fig. 15.
• 7.
  We are now going to make two changes that will make the graph look more conventional when displaying FA data within a reversal design: (1) selecting a unique symbol for each FA condition; (2) removing color so your graph is black and white.

  1. Symbols.

     • i.
       We suggest using open (filled with the color white) symbols for control/play conditions and solid (filled with the color black) symbols for test conditions. If you consistently graph in this manner, it may help your audience more readily discriminate test conditions from control conditions.
     • ii.
       Right-click on the attention data path and select “Format Data Series.”

       1. Left-click on “Fill & Line.”

          1. Select “Solid line” then select the color black from the “Color” option below.
          2. You can now left-click on the Escape data path, and then hit the “CTRL” and “Y” buttons simultaneously. This is the “Redo” function and will make the Escape data path black.
          3. Left-click on the Play data path and hit “CTRL” + “Y.”
          4. All data paths should now be black.
          5. Also select the area just above a condition line and hit “CTRL” + “Y” to make those lines black as well.
          6. Your graph should now look like Fig. 16.
  2. Changing data symbols.

     • i.
       Select the Attention condition data series by right-clicking on one of the data points and selecting “Format Data Series.”
     • ii.
       Click on “Fill & Line” and “Marker,” then “Marker Options.”
     • iii.
       Select a unique symbol (e.g., a square), then change the “Size” to a 6 or 7.
     • iv.
       Under “Fill” select “Solid Fill,” then select black as the color.
     • v.
       Under the “Border” section, select “No line.”
     • vi.
       Repeat these steps for the Escape condition symbols (you can leave them as circles) and the Play condition symbols (just remember to select “Solid Fill” then the color white for these). For the Play condition symbols, you also need to select “Border–Solid Line” and change the border color of your white circles to black, so they can be differentiated from the white background of your graph.
  3. Clarifying legend data symbols.

     • i.
       It may be difficult to see the symbols in your legend.
     • ii.
       One way to make the symbols easier to see is to decrease the thickness of the line going through them.

       1. Click on the legend, then click on one of the legend entries (e.g., Play condition) so that only that entry is highlighted, as displayed in Fig. 17.
       2. As displayed in Fig. 18, in the “Border” section, decrease the “Width” value to 0.5.
       3. This will decrease the thickness of the line crossing through the symbol, so the symbol becomes more visible, as seen in Fig. 19.
       4. You can also make the symbols for the condition stand out more by increasing the font size

          1. Select a condition in the legend, and then right-click, and select “Font” and change the font to 14, as displayed in Fig. 20.
          2. Left-click on each of the other conditions represented in the legend and hit “CTRL” + “Y” to redo this effect.
          3. Your legend should now look like Fig. 21.
       5. Move the legend so there is room for the x-axis label

          1. Right-click on the legend
          2. Select “Format legend”
          3. Under “legend Options–legend Position” select “Right” and then uncheck the box “Show the legend without Overlapping the Box,” as displayed in Fig. 22.
          4. Change the “Chart Title” at the top of your graph by left-clicking in it once, which will select the text box as signified by the appearance of blue circles on each corner, and then left-clicking again in the box. This will allow you to change the text and type in the name of your FA (e.g., “Functional Analysis of Aggression”).
          5. Removing borders

             • i.
               Click near (but not on) some data points so you can remove the border in the plot area. Select “No Line” in the Border menu, as displayed in Fig. 23.
             • ii.
               Click outside of the plot area (somewhere above the top of your condition lines)—you should see a menu labeled “Format Chart Area” appear–select “No Line” in the Border area, as displayed in Fig. 24.
          6. Your graph should now look like Fig. 25.
• 8.
  Adding labels for the y- and x-axis.

  1. Left-click on your graph.
  2. Left-click on the “Design” tab in the menu.
  3. Left-click on the “Add Chart Element” button to access the drop down menu, as displayed in Fig. 26.
  4. Left-click “Axis Titles,” then “Primary Horizontal.”
  5. A title for the x-axis will appear, left-click in the box, erase the existing title, and type in “Sessions (5 min)” to indicate you are displaying consecutive sessions that were 5 min in length (which we are assuming for the purpose of this exercise).
  6. Highlight the text and increase the font size to 14.
  7. Select the “Primary Vertical” from the “Axis Titles” menu and repeat these steps, using “Frequency of Aggression” as the title (Fig. 27).

Fig. 12.

Selecting the “Condition Lines” label in the legend

Fig. 13.

Selecting the condition line markers

Fig. 14.

Formatting markers within a data series

Fig. 15.

Graph containing condition lines with no markers

Fig. 16.

Changing data path colors to black

Fig. 17.

Selecting a legend entry

Fig. 18.

Changing the line thickness in the legend

Fig. 19.

Legend entries with modified lines

Fig. 20.

Changing the font of legend symbols

Fig. 21.

Modified legend entries

Fig. 22.

Changing the legend position

Fig. 23.

Removing the plot area border

Fig. 24.

Removing the chart area border

Fig. 25.

Updated reversal FA graph with modified legend and removal of borders

Fig. 26.

Adding axes labels

Fig. 27.

Inserting an x-axis title

Your graph is now complete and should look like Fig. 28.

Fig. 28.

Reversal FA graph including x- and y-axis labels

At this point, you may also wish to change the look of your x- and y-axes by incorporating a “floating zero” on the y-axis. This will move your data points so that they do not get plotted on the x-axis. In addition, you may wish to remove the zero value on the x-axis because there is no session number zero.

Adding a “Floating Zero”

• 9.Go to your data sheet and change all of the zero values in the Condition Lines column to “− 0.5,” as displayed in Fig. 29.

Fig. 29.

Changing condition line values to allow for a “floating zero” on the y-axis

• 10.Right-click on the y-axis on your graph and select “Format Axis.”
• 11.In the “Axis Options” menu, in the “Bounds” section, change the “Minimum Value” to − 2.0. This will extend the y-axis below the number zero, so that you can have the x-axis cross just beneath the y-axis value of zero (as described in the next step).
• 12.In the “Horizontal Axis Crosses” section, change the value to − 0.5, as displayed in Fig. 30. This change will result in the zero value on the y-axis moving up a bit because the x-axis will now cross just underneath the zero on the y-axis, rather than at the zero on the y-axis.
• 13.Right-click on the x-axis, select “Format Axis” and in the “Axis Options” section, go to the “Units” section and change the “Major” number to 1.0, as displayed in Fig. 31. This will allow you to numerically represent your first session on the x-axis.

Fig. 30.

Changing the y-axis range to allow for a “floating zero” y-axis

Fig. 31.

Changing unit values on the x-axis

Your graph should now look like Fig. 32.

Fig. 32.

Reversal FA graph with updated x- and y-axis values to allow for a “floating zero” y-axis

The final step is to cover the axis numbers on your graph that your audience does not need to see.

• 14.Left-click on your graph, then select the “Insert” tab in the menu, then left-click on the “Shapes” function, then left-click on “Rectangle,” as displayed in Fig. 33.
• 15.Move your cursor down to the graph area (you will see a crosshair appear when you do), and left-click and hold just to the left of the − 2 value on the y-axis, dragging your cursor far enough to the right to cover the 0 on the x-axis, and then up high enough to cover the portion of the y-axis line that extends beneath the intersection of your y- and x-axes. Once you do, this area of your graph should look like Fig. 34.
• 16.As a final step, fill the shape in with white, and remove the border by right-clicking on the shape and selecting “Format Object.” On the right side of the screen, in the “Fill” section, select “Color” as white. In the “Line Section,” select “No Line.” These steps are represented in Fig. 35.

Fig. 33.

Inserting a shape to cover unnecessary x- and y-axis values

Fig. 34.

Drawing a shape to cover x- and y-axis values

Fig. 35.

Changing the shape’s color and removing its border

Your final graph should look like Fig. 36, should you choose to incorporate the “floating zero” feature into your graph.

Fig. 36.

Final reversal FA graph with a “floating zero” y-axis, and x-axis that begins with session 1

Task Analysis for Creating Line Graphs for Latency FAs

The data sheet and graph for a latency FA will look quite similar to the way you constructed the data sheet and graph for the reversal FA. The main difference is that your y-axis values will change to “Duration (seconds)” rather than frequency. In addition, when conducting a latency FA, if no behavior occurs, then you will enter a data point at the maximum duration of the FA session. Therefore, the maximum value on your y-axis should be equivalent to the maximum duration, in seconds, of your FA sessions. For example, if you are conducting 5-min sessions, then the maximum y-value will be 300 s. When conducting a latency FA, lower duration values indicate that the target problem behavior occurred quickly in that condition, whereas higher values indicate the target problem behavior was slower to occur, or did not occur at all.

1. Create a new data sheet by clicking the “+” sign (as seen in Fig. 37) at the bottom of the workbook.

Fig. 37.

Adding a new sheet to the workbook

• 2.Right-click on the new tab that appears, and select the option “Rename,” then type in “Latency FA Data.”
• 3.Enter column labels for “Session #,” “Attention,” “Escape,” “Play,” and “Condition Lines” in the same manner as you did for your reversal graph.
• 4.
  For the purposes of this example, we will be graphing data for 5-min FA sessions (300 s). Put the following data values in the cells.

  1. Attention Values to Enter: 300, 250, 14, 6.
  2. Escape Values to Enter: 280, 300, 300, 300.
  3. Play Values to Enter: 270, 280, 290, 290.
  4. Attention Values to Enter: 10, 12, 10, 11.
  5. Play Values to Enter: 300, 300, 300, 300.
  6. Condition Line Values to Enter: 0, 300 between each condition.
• 5.When you are finished, your data sheet should look like Fig. 38.

Fig. 38.

Sample data sheet for a latency FA with 5-min sessions

• 6.At this point, you can follow the steps listed above for the reversal FA Graph in the “Creating the Graph” and “Editing the Graph” sections. When you have completed all steps, your graph should look like Fig. 39.

Fig. 39.

Final latency FA graph following editing

Task Analysis for Creating Multielement Graphs

Labeling and creating new data sheets

1. Left-click the “+” button to create a new sheet.

2. Right-click on the tab that appears and select “Rename.”

3. Rename this new sheet “Multielement FA Data.”

Entering data

1. Enter a label for Sessions (in cell A1).

2. Enter labels for each of your conditions in the first cell of adjacent columns

   1. e.g., Column B1 = Attention.
   2. e.g., Column C1 = Escape.
   3. e.g., Column D1 = Play.

3. Enter in the numbers 1 through 12 to notate there were 12 sessions in which data were collected.

4. Enter in the following data for each condition.

   1. Attention.

      • i.
        Session 1 = 6.
      • ii.
        Session 4 = 10.
      • iii.
        Session 8 = 12.
      • iv.
        Session 11 = 12.
   2. Escape.

      • i.
        Session 3 = 9.
      • ii.
        Session 6 = 8.
      • iii.
        Session 9 = 11.
      • iv.
        Session 12 = 13.
   3. Play.

      • i.
        Session 2 = 2.
      • ii.
        Session 5 = 1.
      • iii.
        Session 7 = 0.
      • iv.
        Session 10 = 0.

Creating the Graph

1. Highlight all of your data, including column labels, click on the Insert tab, and select the “Scatter” option in the Charts area, then select “Scatter with Straight Lines and Markers” option, just as you did when creating a reversal FA graph.

Your data sheet should now look like Fig. 40.

Fig. 40.

Initial multielement FA graph

• 2.Click on the graph and select “Move Chart,” then select “New sheet,” type in “Multielement FA Graph” and click the OK button, in the same manner as you did for the reversal FA graph.

Editing the Graph

1. Delete the gridlines as you did in the reversal FA graph, by left-clicking on the lines and hitting the delete button.

2. Now connect the isolated data points with a data path.

   1. Left-click on one of the data paths, then right-click and choose “Select Data.” This will bring you back to the data sheet with a new menu, as pictured in Fig. 41.
   2. Click on the menu option, “Hidden and Empty Cells.”
   3. In the field “Show empty cells as:” select “Connect data points with line,” as displayed in Fig. 42.
   4. Your graph should now look like Fig. 43.
   5. Next, follow the steps as you did for the reversal graph to edit the graph further (e.g., move the legend, increase font of legend markers, make data paths and points black and white, eliminate borders, add axis labels, etc.).
   6. Once you are done, your graph should look like Fig. 44.

Fig. 41.

Selecting a data series to allow a data path to be input

Fig. 42.

Connecting multielement data points with a line to form a data path

Fig. 43.

Updated multielement FA graph with data paths

Fig. 44.

Final multielement FA graph following editing

Task Analysis for Creating Bar Graphs for Trial-Based FAs

Creating bar graphs is quite simple and requires much less editing.

Labeling and Creating New Data Sheets

1. Left-click the “+” button to create a new sheet.

2. Right-click on the tab that appears and select “Rename.”

3. Rename this new sheet “Trial-Based FA Data.”

Entering Data

Enter your data in the manner displayed in Fig. 45.

Fig. 45.

Arrangement of sample data in a worksheet for a trial-based FA

Creating the Graph

1. Highlight all of the cells (including labels).

2. Click on the “Insert” menu tab.

3. Select “Insert Column or Bar Chart” from the Charts section.

4. In the 2D Column area, select “Clustered Column,” as displayed in Fig. 46.

Fig. 46.

Inserting a trial-based FA graph

Editing the Graph

1. Move the graph to a new tab, delete the grid lines, remove the borders, and edit the graph title as you did before when creating the previous FA graphs in this tutorial.

2. You do not need to add an x-axis title for this type of graph; however, add a y-axis title that reads, “% of Trials with (Target Behavior”)

3. Your graph should now look like Fig. 47. Notice that your y-axis label overlaps with the numbers on the y-axis. This will need to be fixed.

Fig. 47.

Initial bar graph for a trial-based FA

• 4.Click in the plot area (near, but not on your bars) to select the graph, then left-click on a corner of the graph and drag it toward the center of the screen to shrink your graph and make room for the y-axis title. You can now click on the y-axis title and drag it to the left a bit to create some further separation from numbers on the y-axis.
• 5.Increase the font of your legend, and left-click and hold to position it beneath the x-axis in a preferred location. You can also re-locate the legend toward the right side border as you did in the other graphs if you like. When you are done, your graph should look like Fig. 48.

Fig. 48.

Trial-based FA graph with re-positioned y-axis label and legend

• 6.Lastly, edit the color of your bars so they are black and white. We prefer to use black fill for test conditions, white fill for control conditions. Also, keep in mind, if you had an Ignore condition, both bars would be white because there is no test condition.
• 7.
  You will notice the x-axis line is faint, and the y-axis does not have a solid line.

  1. Left-click on the y-axis.
  2. Right-click, select “Format Axis.”
  3. In the “Axis Options” field, choose the “Fill & Line” option.
  4. Under the “Line” category, choose “Solid Line” then pick the color black.
  5. Repeat these steps for the x-axis.
• 8.Your final graph should look like Fig. 49.
• 9.You may also wish to raise the y-axis to include a “floating zero,” by following the steps outlined earlier in this tutorial. This is particularly helpful to do for a bar chart because if you have zero values, you will not be able to see the bars representing zero values.

Fig. 49.

Final trial-based FA graph following editing

The current tutorial provides detailed instructions for creating the most commonly used graphs to depict functional analysis data. Because many of the steps for creating single-subject design graphs overlap (e.g., inserting phase lines, formatting axes, symbols, and the legend), it is anticipated that these steps will also be helpful to those creating graphs not covered in this article, such as multiple baseline design graphs, and changing criterion design graphs. In addition, previous tutorials have covered steps for creating multiple baseline design graphs, such as Deochand, Costello, and Fuqua (2015) and Dixon et al. (2009). Practitioners may also wish to add to the literature in this area of practice by creating similar task analyses for use with Excel® designed for Apple® computers, or other software programs used in the field to depict behavioral data (e.g., Sigma Plot®, GraphPad Prism®).

Compliance with Ethical Standards

Conflict of Interest

This author declares that he has no conflict of interest.

Ethical Approval

This article does not contain any studies with human or animal participants performed by the author.