How to Draw a Phase Line in Excel

Behav Anal Pract. 2015 Oct; 8(2): 207–211.

Inserting Phase Change Lines into Microsoft Excel® Graphs

Erick M. Dubuque

grid.412724.6000000012107308XApplied Behavior Analysis Program, Spalding University, Mansion East, Office #214, 845 South 3rd St., Louisville, KY 40203 USA

This article has been cited by other articles in PMC.

Abstract

Microsoft Excel® is a popular graphing tool used by behavior analysts to visually display data. However, this program is not always friendly to the graphing conventions used by behavior analysts. For example, adding phase change lines has typically been a cumbersome process involving the insertion of line objects that do not move when new data is added to a graph. The purpose of this article is to describe a novel way to add phase change lines that move when new data is added and when graphs are resized.

Keywords: Microsoft Excel®, Graphing, Phase change lines

Many behavior analysts rely on Microsoft Excel® to graph and visually analyze data. Generally, Microsoft Excel® allows behavior analysts to accurately meet the graphing conventions adopted by the field and utilized in practice. However, there are a few issues inherent to the software that prevent it from displaying behavioral data in an accurate and convenient way.

Adding phase change lines to Microsoft Excel® graphs is easily one of the most troublesome aspects of this software. Phase change lines are not intuitive to the software, which means that other workarounds need to be used to add these elements to graphs. The most common workaround described in the literature involves inserting line objects on top of graphs (Dixon et al 2009; Pritchard 2009). Unfortunately, line objects can be cumbersome to align within a graph and, more importantly, do not move when new data is added or when the graph is resized. This means that behavior analysts are forced to move their phase change lines whenever they add new data or resize their graphs. Given how frequently behavior analysts update their graphs and the likelihood that they are simultaneously maintaining several graphs for multiple clients, this minor annoyance can quickly escalate into a significant waste of clinical time. Vanselow and Bourrett (2012) recently advocated for an alternative method for adding phase change lines. The method that they described involves graphing data as scatterplots and plotting phase change lines as error bars. While this method appears to be a better alternative to inserting line objects, there are some notable limitations. First, when using this method, phase change lines can only be inserted between data points when the x-axis is an integer (e.g., sessions). In other words, this method does not work when the x-axis values are dates. Second, this method requires behavior analysts to graph their data as scatterplots as opposed to the "marked line graphs" as described in the other tutorials already mentioned. Third, this method requires behavior analysts to insert two separate values for the x-axis and y-axis for each phase change line, which can be cumbersome. Fortunately, there is another way to add phase change lines directly into Microsoft Excel® graphs using combined graphs, gradients, and transparency options. This method generates phase change lines between data points regardless of the x-axis values that move when new data is added and automatically resize when the height and width of a graph are changed.

The brief task analyses that follow have been applied to Microsoft Excel® 2007 and 2013 for PC and Microsoft Excel® 2011 for Mac. Although this method has not been directly tested, these steps (with slight modifications) may also work in other versions of Microsoft Excel® that support combined graphs, gradients, and transparency options. These task analyses should not be considered comprehensive graphing tutorials that review all graphing conventions adopted by behavior analysts. Instead, they should only be considered supplementary instructions that provide an alternative way to add phase change lines to graphs created in Microsoft Excel®. Two task analyses are provided below. The reader is encouraged to use the task analysis that most closely matches the version of Microsoft Excel® that they use for graphing.

Task Analysis for Adding Phase Change Lines in Microsoft Excel® 2007 and 2013 for PC

1. Add the data.

   1. OPEN Microsoft Excel® 2007 or 2013 for PC.

   2. ADD the following column titles to the spreadsheet: "Dates," "Rate," and "Phase Change."

   3. INSERT values under each column similar to Fig.1.

      

      Sample three-column data set

2. Create the graph.

   1. Use the mouse to HIGHLIGHT the "Date," "Rate," and "Phase Change" columns.

   2. CLICK on the "Insert" tab located in the top menu bar.

   3. CLICK the "Insert Line Chart" icon underneath the "Charts" section of the ribbon.

   4. CLICK the "Line with Markers" option to generate the graph.

3. Set the y-axis maximum value.

   1. RIGHT CLICK on the y-axis values.

   2. SELECT the "Format axis" option.

   3. Under "Axis Options," change the maximum value so that it equals the phase change line value.

4. Adjust the legend.

   1. CLICK once on the legend to highlight the entire legend.

   2. CLICK once on the "Phase Change" item in the legend to highlight only the "Phase Change" label in the legend.

   3. Press the DELETE key to remove the "Phase Change" label from the legend.

5. Create a combined graph.

   1. RIGHT CLICK on the phase change line data series.

   2. SELECT the "Change Series Chart Type" option.

   3. In Microsoft Excel® 2013, under "Combo," SELECT the "Clustered Column" option from the "Phase Change" dropdown box and CLICK the "OK" button. In Microsoft Excel® 2007, under the column heading, SELECT the "Clustered Column" option.

6. Format the phase change line data series.

   1. RIGHT CLICK on the phase change line data series.

   2. CLICK the "Format Data Series" option.

   3. Under "Series Options," CHANGE the "Gap Width" value to 0 %.

   4. Under "Fill," SELECT the "Gradient Fill" radio button option.

   5. Under the "Type" dropdown box, SELECT the "Linear" option (Fig.2).

      

      Formatting the phase change line data series in Microsoft Excel® 2013 for PC

   6. Under the "Direction" dropdown box, SELECT the "Linear Left" option and CHANGE the angle to 0 %.

   7. ADD or DELETE the gradient stops so that only three stops are shown.

   8. CHANGE the first gradient stop so that its color is black, its position is 0 % and its transparency is 0 %.

   9. CHANGE the second gradient stop so that its color is black, its position is 6 %, and its transparency is 0 %.

   10. CHANGE the third gradient stop so that its color is black, its position is 7 %, and its transparency is 100 %. The thickness of the phase change line can be adjusted by modifying the gradient stop position in this step and the previous step, for example, setting the second gradient stop to 8 % and third gradient stop to 9 %.

   11. If necessary, REMOVE any borders or shadows that may have been added to the phase change data series by default.

7. Remove intersecting data paths.

   1. CLICK once on the dependent variable data path to highlight the entire series.

   2. With the series highlighted, CLICK once on the first data point to the right of the phase change line to highlight only that data point.

   3. RIGHT CLICK on the data point and SELECT the "Format Data Point" option.

   4. Under the "Line" option, SELECT the "No line" option to remove the data path crossing the phase change line.

   5. REPEAT these steps to remove any additional data paths crossing phase change lines.

Task Analysis for Adding Phase Change Lines in Microsoft Excel® 2011 for Mac

1. Add the data.

   1. OPEN Microsoft Excel® 2011 for Mac.

   2. ADD the following column titles to the spreadsheet: "Dates," "Rate," and "Phase Change."

   3. INSERT values under each column similar to Fig.1.

2. Create the graph.

   1. Use the mouse to HIGHLIGHT the "Date," "Rate," and "Phase Change" columns.

   2. CLICK on the "Charts" tab located in the top menu bar.

   3. CLICK the "Line" icon underneath the "Insert Chart" section of the ribbon.

   4. CLICK the "Marked Line" option to generate the graph.

3. Set the y-axis maximum value.

   1. RIGHT CLICK on the y-axis values.

   2. SELECT the "Format axis" option.

   3. Under "Scale," change the maximum value so that it equals the phase change line value and CLICK the OK button.

4. Adjust the legend.

   1. CLICK once on the legend to highlight the entire legend.

   2. CLICK once on the "Phase Change" item in the legend to highlight only the "Phase Change" label in the legend.

   3. Press the DELETE key to remove the "Phase Change" label from the legend.

5. Create a combined graph.

   1. RIGHT CLICK on the phase change line data series.

   2. SELECT the "Change Series Chart Type" option.

   3. CLICK the "Column" icon and SELECT the "Clustered Column" option from the dropdown box.

6. Format the phase change line data series.

   1. RIGHT CLICK on the phase change line data series.

   2. CLICK the "Format Data Series" option.

   3. Under "Options," CHANGE the "Gap Width" value to 0 %.

   4. Under Fill, SELECT the "Gradient" button.

   5. Under the "Style" dropdown box, SELECT the "Linear" option (Fig.3).

      

      Formatting the phase change line data series in Microsoft Excel® 2011 for Mac

   6. CHANGE the "Angle" to 0 %.

   7. ADD or DELETE the gradient stops so that only three stops are shown.

   8. CHANGE the first gradient stop so that its color is black, its position is 0 %, and its transparency is 0 %.

   9. CHANGE the second gradient stop so that its color is black, its position is 6 %, and its transparency is 0 %.

   10. CHANGE the third gradient stop so that its color is black, its position is 7 %, and its transparency is 100 %. The thickness of the phase change line can be adjusted by modifying the gradient stop position in this step and the previous step, for example, setting the second gradient stop to 8 % and third gradient stop to 9 %.

   11. If necessary, REMOVE any borders or shadows that may have been added to the phase change data series by default.

7. Remove intersecting data paths.

   1. CLICK once on the dependent variable data path to highlight the entire series.

   2. With the series highlighted, CLICK once on the first data point to the right of the phase change line to highlight only that data point.

   3. RIGHT CLICK on the data point and SELECT the "Format Data Point" option.

   4. Under the "Line" option, SELECT the "No line" option to remove the data path crossing the phase change line.

   5. REPEAT the steps in this section to remove any additional data paths crossing phase change lines.

The purpose of these brief task analyses was to introduce a novel way of inserting phase change lines into Microsoft Excel® line graphs. Additional editing to match behavior analytic graphing conventions should also be applied (e.g., adding axis and phase change labels, formatting data series, resizing graphs, eliminating gridlines, etc.) where necessary. Readers are encouraged to review the multitude of excellent comprehensive graphing tutorials available if they are unfamiliar with how to carry out these steps (see Dixon et al 2009; Pritchard 2009; Vanselow and Bourrett 2012).

This solution for adding phase change lines is beneficial for several reasons. First, the phase change line is incorporated directly into the graph, meaning that the graph can be copied and pasted into other programs without needing to worry about carrying over external line objects. Second, phase change lines automatically move with the graph when it is resized. Third, the phase change line appears between two data points without any overlap. Fourth, new data and phase change lines can be easily added once the phase change line has been formatted. Fifth, this method works regardless of the values used in the x-axis (e.g., sessions or dates). Finally, these steps only need to be followed once. After a phase change line has been formatted using the steps described, it can be saved as a template and used for future graphs.

There are a couple of limitations to this solution that bear mentioning. First, this method does not allow for the option to create dashed or dotted phase change lines. Readers interested in this feature should consider the line object method or the error bar method mentioned earlier. Second, this option does not address the similar problem behavior analysts experience when adding phase change labels to their graphs. Currently, the only method described to address this issue involves pasting a textbox within the graph. Unfortunately, this method is subject to the same limitations as the line object solution, as the text boxes do not move when new data is added or when the graph is resized. However, even with this limitation, the method described herein will likely save a great deal of time in the long term, as behavior analysts will be required to only move phase change labels as opposed to also needing to adjust phase change lines whenever a graph is updated.

Footnotes

1. Microsoft Excel® is a popular software program used by behavior analysts to graph and visually analyze data.

2. Current solutions to adding phase change lines within Microsoft Excel® have notable limitations.

3. The brief task analyses provided address these limitations and provide a novel way to add phase change lines using combined graphs, gradients, and transparency options.

4. Using this method, behavior analysts can quickly add phase change lines between two data points directly into their graphs saving time and effort when updating client data.

References

• Dixon MR, Jackson JW, Small SL, Horner-King MJ, Mui Ker Lik N, Garcia Y, Rosales R. Creating single-subject design graphs in Microsoft Excel™ 2007. Journal of Applied Behavior Analysis. 2009;42(2):277–293. doi: 10.1901/jaba.2009.42-277. [PMC free article] [PubMed] [CrossRef] [Google Scholar]
• Pritchard J. A decade later: creating single-subject design graphs with Microsoft Excel 2007™ The Behavior Analyst Today. 2009;9:153–161. doi: 10.1037/h0100655. [CrossRef] [Google Scholar]
• Vanselow NR, Bourrett JC. Online interactive tutorials for creating graphs with Excel 2007 or 2010. Behavior Analysis in Practice. 2012;5(1):40–46. [PMC free article] [PubMed] [Google Scholar]

How to Draw a Phase Line in Excel

Source: https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5048278/#:~:text=Use%20the%20mouse%20to%20HIGHLIGHT,option%20to%20generate%20the%20graph.

Share this post