Using Microsoft Excel® to Build a Customized Partial-Interval Data Collection System

Abstract

Using data to inform treatment decisions is a hallmark of behavior analysis. However, collecting the type of data that behavior analysts often require can be a labor-intensive and time-consuming task. Electronic data collection systems have been identified as a tool to alleviate some of the issues related to data collection, but many obstacles still exist. Current limitations of electronic data collection systems include cost, adaptability, ease of use, and compliance with privacy and security guidelines. The purpose of this article is to offer practitioners an alternative to buying an electronic data collection system by providing a task analysis on how to build customized electronic data collection systems using Microsoft Excel®. This task analysis is written for individuals with limited or no experience working with Excel® but may also be of utility to individuals fluent in Excel®. This task analysis is organized into three sections: (a) creating a basic electronic data collection table with dropdown menus and autofill features, (b) creating a timestamp for all data entered, and (c) creating automatically graphing displays of data.

Electronic supplementary material

The online version of this article (10.1007/s40617-018-0259-3) contains supplementary material, which is available to authorized users.

It is vital to have data collection procedures that are accurate, consistent, and reliable because practitioners of applied behavior analysis (ABA) rely on data to make informed clinical decision (Jackson & Dixon, 2007; Najdowski, Chilingaryan, Bergstrom, Granpeesheh, Balasanyan, Aguilar, & Tarbox, 2009; Sleeper, LeBlanc, Mueller, Valentino, Fazzio, & Raetz, 2017). While data are essential to provide effective behavioral treatments, collecting it can be labor-intensive and time-consuming (Dixon, 2003; Madsen, Peck, & Valdovinos, 2015; Sleeper et al., 2017). Even after traditional paper-and-pencil data have been collected, a great deal of time is required for data transcription and graphing (Sleeper et al., 2017). Research has shown that computer-based programs can be more accurate and efficient than the traditional paper-and-pencil methods for data entry (Dixon, 2003; Kahng & Iwata, 1998; Sleeper et al., 2017; Tapp, Ticha, Kryzer, Gustafson, Gunnar, & Symons, 2006).

Currently, there are many computer and mobile device apps that may help alleviate some of the time-consuming inconveniences of data collection (Sleeper et al., 2017). However, current computer and mobile device apps may be limited in their utility because of cost, lack of adaptability, difficulties in learning new technology, and concerns about compliance with privacy and security guidelines. Dixon (2003), Jackson and Dixon (2007), and Whiting and Dixon (2012) provided task analyses of how to write and create one’s own programs for data collection, which is an alternative to purchasing specialized computer software or mobile device apps. One limitation of these task analyses is that the authors used programming languages (i.e., visual basic and Xcode) which may be unfamiliar to many behavior analysts. Unfortunately, this may limit the adoption of these task analyses for many practitioners who are not inclined to learn new programming languages.

This paper extends the work of Dixon (2003), Jackson and Dixon (2007), and Whiting and Dixon (2012) by providing a task analysis for creating a customized partial-interval electronic data collection system using Microsoft Excel®. This platform was selected because it is the most widely used graphing software (Cole & Witts, 2015), is highly customizable, has a free app that can be downloaded to most tablet and mobile devices, and has features that allow time-stamped data and automatic graphing. The task analysis provides instructions for creating one type of discontinuous data collection system (i.e., partial-interval with suggestions for variations). Cooper, Heron, and Heward (2007) describe partial-interval systems as a common applied behavior analytic data collection method. Partial-interval recording is a method of data collection in which an observation is divided into equal intervals and an observer records whether a behavior occurs during the established interval or not. The observer only has to record data when the behavior occurs a single time during an interval (Cooper et al., 2007). The data from the recorded intervals is then summarized by calculating the percentage of intervals that contained the target behavior (LeBlanc, Raetz, Sellers, & Carr, 2016) which provides an estimate of the actual amount of behavior that occurred during the observation. This type of discontinuous data collection is prone to overestimating the true amount of behavior that occurred during the observation, but error decreases with smaller intervals (e.g., 5-s, 10-s). The discontinuous nature of the data collection system is well-suited to the electronic data collection as interval signals can be easily added to the system. See Cooper et al. (2007) for more details on how to collect partial-interval data.

The task analysis is written for individuals with limited experience working with Excel®. It is organized into three sections: (a) creating a basic electronic data collection table with dropdown menus and autofill features, (b) creating a timestamp for entered data, and (c) creating automatically graphing displays of data. Although some components of this task analysis are optional, the steps are arranged sequentially so that each new component builds on previous components. The task analysis includes basic instructions, elaboration of the instructions for both Windows Excel® 2013 and Mac OS Excel®2016 (in italics), and suggested alterations for other types of data collection systems. In addition to the task analysis, suggestions are provided on how to use the system.

The usability of the task analysis was assessed by five graduate students who were not involved with creating the task analysis. Graduate students, who reported their experience with Excel® to range from “beginner” to “adequately experienced,” were asked to complete the task analysis independently and provide feedback. The graduate students successfully completed the task analysis in the time range of 37–68 min. Although individuals who reported more experience with Excel® completed the task analysis more quickly than those with less experience, every participant successfully built the electronic data collection system. Following the completion of the task analysis, the graduate students provided feedback on the clarity of the instructions and the feedback was used to refine the task analysis.

Creating an Electronic Data Collection Table with Dropdown Menus and Autofill Features

This section describes the necessary first steps for creating an electronic data collection system using Microsoft Excel®. Table 1 provides a task analysis for creation of an electronic data collection table utilizing a 60-min partial-interval system across four target behaviors as an example. The methods described can be customized to create many other types of data collection systems. Table 2 provides a task analysis for creation of dropdown menus. Using dropdown menus for entering data rather than having data collectors fill in cells manually may reduce the amount of time practitioners spend entering data because of the autofill feature. Table 3 provides a task analysis for creation of a formula to produce an autofill feature for specific data collection cells. This formula will automatically fill the cells for a specific interval with “No” when an individual enters their initials under the “Staff Initials” section for that interval. This formula is used is to save staff time when no target behaviors occur during that interval by only requiring them to enter their initials instead of having to select No for each behavior. Finally, Table 4 provides a task analysis for calculating text responses (i.e., “Yes” and No) as values that will be used for data analysis. See supplemental appendices A, B, and C for screenshots of critical steps of the task analysis in Excel® to guide table creation.

Table 1.

Instructions for creating a basic data collection table

Instruction	Description	Explanation	Alterations
1. Open a new Excel® workbook.	(a) Click on Excel® Icon
2. Label the interval column.	(a) Type “Interval” into cell A1	Do not include quotation marks unless they are used in a formula found in brackets.	“Interval” can be substituted with an appropriate label for another type of system such as “time” or “trial”
3. Fill in the interval times.	(a) Type “9:00 AM–10:00 AM” into cell A2. (b) Continue labeling the 60-min intervals (e.g., type “10:00 AM–11:00 AM” into cell A3) until you have completed “2:00 PM-3:00 PM” in cell A7. i. To stop the text from overlapping into the B cells, highlight cells A2:A7 on the HOME tab and select WRAP TEXT.	“A2:A7” stands for A2 through A7. Colons often mean “through” in Excel®	The interval times can be substituted with other time intervals or any other appropriate labels like a specific time (i.e., 12 p.m.) for a momentary time sampling system, or trial numbers for any trial-based system. Any number of rows can be added for a custom system.
4. Label the interval completion column.	(a) Type “Staff Initials” into cell B1.	The text or values entered into this column will be the source cell for the autofill feature described in Table 3.	Staff Initials can be substituted with something like “was the trial completed” with a dropdown menu of yes/no.
5. Label the target behaviors.	(a) Type “Physical Aggression”, “Self-Injurious Behavior”, “Elopement”, and “Property Destruction” into cells C1:F1, respectively. i. To stop the text from overlapping into the next cell, utilize the wrap text procedure described in step 3b(i).		The target behavior labels can be substituted with any behavior of interest, including behaviors with the goal of acquisition or reduction.

Table 2.

Instructions for adding dropdown menus to the data collection table

Instruction	Description	Explanation	Alterations
1. Create a dropdown list with labels to indicate if the target behavior occurred.	(a) Select cell C2 and open the DATA tab (at the top of the screen). (b) Select the DATA VALIDATION Icon (it has a green check mark and red circle with a line on two rectangles). (c) In the DATA VALIDATION menu, open the dropdown menu under ALLOW and select LIST. (d) Click onto the SOURCE Cell that appears under the menu and type “Yes, No”. i. Select OKAY at the bottom of the menu.	Any dropdown list can be made by entering labels in the format of “option1, option2, option3…”. A second option for creating a dropdown list would be to enter the options in a column of cells in a spreadsheet, selecting the SOURCE cell in the DATA VALIDATION menu, and highlighting the range of cells that contain the desired options.	“Yes” and “No” can be substituted with any other appropriate data labels including specific numbers for frequency data collection or other labels such as partially prompted and independent. For a frequency system, you would enter the numbers to indicate the amount the behavior occurred (i.e., “1, 2, 3, 4, 5, 6, 7, 8”).
2. Copy the dropdown menu into other cells.	(a) Drag cell C2 by clicking the bottom-right corner of cell C2 (the little green square). Your mouse should turn into a (+) sign. i. Drag down to cell C7 (this should highlight cells C2:C7). 1. While C2:C7 is still highlighted, drag to cell F7 (this should highlight C2:F7).	Dragging is the most efficient way to “copy and paste” formulas and formatting, but traditional copy and paste methods could be used instead.	This can be done for any number of rows and columns.

Table 3.

Instructions for creating an autofill feature for data entry cells

Instruction	Description	Explanation	Alterations
1. Create an autofill source for the first interval	(a) Select cell B2 and enter “TEST.”	This is done because the formula used in the next step requires cell B2 to contain a value when it is entered.	“Test” can be substituted with any value or text.
2. Enter the autofill formula.	(b) Select cell C2 and enter the formula that is shown within the brackets [=IF($B2=“”,“”, “No”)], then press ENTER (RETURN) on the keyboard. (A “No” should appear in the cell).	All formulas will be shown in brackets but should not be entered in Excel® with brackets. The formula used is a conditional formula that will generate a value contingent on a logical test. So, for this cell, the formula reads that if cell B2 is blank, cell C2 should be blank, but if cell B2 is not blank, C2 should display No.	In the formula, No can be substituted with any appropriate label, such as “0” for a frequency system. If “No” is substituted with a 0 the formula should read [=IF(B2=“”,“”, 0)]. Any time text is included in a formula it should be in quotations. Values do not need quotations.
3. Copy the autofill formula for each target behavior in this interval.	(a) Drag cell C2 by clicking the bottom-right corner of cell C2 (the little green square). Your mouse should turn into a (+) sign. i. Drag over to cell F2 (this should highlight cells C2:F2).	Entering this formula for each target behavior in an interval will result in all cells for that interval producing No when staff initials are entered.	If more target behaviors were added, this formula should be entered for each one.
4. Copy the formula for each interval.	(a) While C2:F2 are still highlighted, drag down to cell F7 (this should highlight C2:F7).	Dragging the formula down is the most efficient way to copy the formula and have it formatted for each row (i.e., when the formula is dragged from C2:F2 to C3:F3 the formula for the new row will automatically format to [=IF($B3=“”,“”,No)].	If more rows were added, this formula should be dragged down to cover all of them.
5. Remove the autofill source.	(a) Click cell B2 and press BACKSPACE (DELETE) on the keyboard to clear the cell. (The No in cells C2:F2 should disappear).	Test was only placed in cell B2 so that the formula would format correctly. Now that the formula is set, Test is not necessary.

Table 4.

Instructions for converting text to values for calculations and graphs

Instruction	Description	Explanation	Alterations
1. Copy the interval times.	(a) Highlight cells A1:A7, then press CONTROL+C (COMMAND + C) on the keyboard.
2. Create a new sheet.	(a) Select the (+) at the bottom-left of the screen next to SHEET1.	The new sheet created will be used to format and calculate all the data collected. Using a separate sheet prevents the data sheet from cluttered or distracting.	You can change the name of the sheets to something like “data” and “calculations,” but only do so after the table is complete because the following formulas included in subsequent steps are built for “SHEET1” and “SHEET2.”
3. Paste the interval times onto SHEET2.	(a) Within SHEET2, select cell A1 and press CONTROL + V (COMMAND + V) on the keyboard.	The interval times were copy and pasted into the second sheet so that when data are entered in the data sheet, the second sheet will mirror the data entered.
4. Copy the target behavior labels.	(a) Open SHEET1. (b) Highlight cells C1:F1, then press CONTROL + C (COMMAND + C) on the keyboard.
5. Paste the target behavior labels onto SHEET2.	(a) Open SHEET2. (b) Select cell B1 and press CONTROL + V (COMMAND + V) on the keyboard.
6. Enter the formula to calculate “Yes” or “No” as a value.	(a) Select cell B2 and enter the formula, [=IF(Sheet1!C2 = “Yes”,1,IF(Sheet1!C2 = “No”,0,“”))], then press ENTER (RETURN) on the keyboard.	To calculate or graph the Yes/No data, “Yes” and “No” need to be computed as a value. This is another conditional formula that basically reads that if “Yes” is entered in the selected cell, “1” should be produced. If “No” is entered in the selected cell, “0” should be produced. Otherwise, the cell should appear blank, which is indicated by “”.	1 or 0 can be replaced in the formula for any desired value. The “” can also be replaced by any text or value, such as “no data entered.” If a frequency system is being used and the dropdown selections are already values and not text, this formula should be replaced with [=Sheet1!C2].
7. Copy the formula for the rest of the Cells.	(a) Drag the formula down to B7. i. While B2:B7 are still highlighted, drag to cell E7 (this should highlight B2:E7).		Any added rows or columns should be included.

Creating a Timestamp for All Data Entered

Table 5 describes the process of creating a timestamp for each piece of data entered per cell, and Table 6 describes the process of creating a timestamp of data entry per interval. Although tracking the time that data are entered is not a mandatory aspect of all data collection systems, it may be beneficial to have this information to determine if staff entered data at the appropriate times. A study by Taber-Doughty and Jasper (2012) indicated that longer latencies in data recording resulted in less accurate data. Having records of when data were entered may give supervisors insights into the validity of data collected. Creating timestamps for each individual cell will give supervisors detailed information about how staff are collecting data while timestamps per interval provide summary data that can be graphed for analysis and feedback. See supplemental appendices D, E, F, and G for screenshots of critical steps of the task analysis in Excel® to guide timestamp creation.

Table 5.

Instructions for creating an automatic timestamp for data entry per cell

Instruction	Description	Explanation	Alterations
1. Enable iterative calculations.	(a) Select FILE then open then OPTIONS menu. i. Open the FORMULAS menu and click the check box to ENABLE ITERATIVE CALCULATIONS, then select OKAY. (b) Mac OS instructions are as follows. Select the EXCEL menu and open PREFERENCES. i. Open the CALCULATION menu and select the box next to USE ITERATIVE CALCULATION, then close the menu by selecting the EXIT Icon.	Enabling Excel® to make iterative calculations is necessary for the timestamp formula to work appropriately.	Differences may be found in different versions of Excel®. PC instructions are based on Excel® 2013, and MAC OS instructions are based on Excel® 2016. Each version will have a way to enable iterative calculations within the calculations menu.
2. Label the timestamp interval times.	(a) On SHEET2, type “Timestamp Intervals” into cell F1.
3. Copy and paste the interval times.	(a) Highlight cells A2:A7. i. Copy the highlighted cells by pressing CONTROL + C (COMMAND + C) on the keyboard. (b) Select Cell F2, then paste the copied cells by pressing CONTROL + V (COMMAND + V) on the keyboard.	This is not a requirement but is done for organization and ease of viewing the raw data when it is entered.	Any added rows should be included.
4. Copy the target behavior labels.	(a) Highlight cells B1:E1, then press CONTROL + C (COMMAND + C) on the keyboard.
5. Paste the target behavior labels onto SHEET2.	(a) Select cell G1 and press CONTROL + V (COMMAND + V) on the keyboard.
6. Enter the timestamp formula.	(a) Select cell G2 and then enter the formula [=IF(NOT(B2 = “”),IF(G2 = “”,NOW(),G2),“”)].	The timestamp formula is a complicated conditional formula that basically reads that if cell B2 is not blank then G2 should display the time that a value was added to B2.	This formula can be edited and used anytime a timestamp is desired in Excel®. Simply replace “B2” with any cell you want to timestamp and replace both “G2” inputs with where you want the data to output to.
7. Copy the formula to each cell.	(a) Drag down to cell G7. (b) While G2:G7 is still highlighted, drag to cell J7.
8. Format the cells.	(a) While G2:J7 is still highlighted, on the HOME Tab in the NUMBER section, open NUMBER FORMAT dropdown menu. (b) Select TIME on the dropdown menu.	All the cells must be formatted to register the input for the timestamp as time or it will not calculate correctly.

Table 6.

Instructions for creating an automatic timestamp for data entry per interval

Instruction	Description	Explanation	Alterations
1. Label the column that will count the cells that need data entered	(a) Type “Count Intervals” into cell K1.
2. Enter the formula that will count how many cells need to have data entered.	(a) Select cell K2 and enter the formula [=COUNTIF(B2:E2,“”)], then press ENTER (RETURN) on the keyboard.	This formula will count the number of cells that are blank indicated by “” per cell. For this example, a 4 will be produced. As data are entered in cells, the 4 will automatically reduce.	If more target behaviors were added to the data sheet, “B2:E2” in the formula should be extended to include each cell a target behavior is tracked in.
3. Copy the formula for each interval.	(a) Drag down to cell K7.		Any added rows should be included.
4. Format the cells.	(a) While K2:K7 is still highlighted, on the HOME Tab, in the NUMBER section, open NUMBER FORMAT dropdown menu. i. Select GENERAL on the dropdown menu. (“4” should appear in each cell).	All the cells must be formatted to register the input or it will not calculate correctly.
5. Label the column that timestamps the interval.	(a) Type “Interval Timestamp” into cell L1.
6. Enter timestamp formula.	(a) Select cell L2 and enter the formula [=IF((K2 = 0),IF(L2 = “”,NOW(),L2),“”)], then press ENTER (RETURN) on the keyboard.	This produces a single timestamp output for the entire interval so that each individual target behavior timestamp does not need to be calculated and graphed.
7. Copy the formula for each interval.	(a) Drag down to cell L7.		Any added rows or columns should be included.
8. Format the cells.	(a) While L2:L7 is still highlighted, on the HOME Tab, in the NUMBER section, open NUMBER FORMAT dropdown menu. (b) Select TIME on the dropdown menu.	All the cells must be formatted to register the input for the timestamp as time or it will not calculate correctly.

Once timestamps are being automatically produced during data entry, simple formulas can calculate whether data are entered within a criterion timeframe. Table 7 provides a task analysis for calculating the timeliness of data entry based off of an established criterion that can be set by the system creator. This calculation allows practitioners to evaluate data entry timeliness of staff if desired and provide feedback when appropriate. The criterion timeframe used for this example will be that data need to be entered within 5 min of the completion of the interval to be considered on time. For instance, for the 9:00 a.m. to 10:00 a.m. interval, data would need to be entered sometime between 9:00 a.m. and 10:05 a.m. to be considered on time. The 5-min timeframe is arbitrary and can be set to any timeframe the reader desires. See supplemental appendices H and I for screenshots of critical steps of the task analysis in Excel® to guide timestamp calculation.

Table 7.

Instructions for calculating on-time or early/late data

Instruction	Description	Explanation	Alterations
1. Label a column for the earliest time data should be considered on time.	(a) Type “Earliest Time” into cell M1.
2. Enter earliest possible times that data can be considered on time.	(a) Select cell M2 and enter “10:00 a.m.” (b) Drag down M2 to cell M7 (this should highlight Cells M2:M7 and fill in the appropriate times).	For partial-interval data collection systems, data should not be entered until the interval elapses. Anything entered before the interval ends would not be representative of the entire interval.	“10:00 a.m.” can be changed for any appropriate criterion.
3. Label a column that will change the timestamp format.	(a) Type “Time Difference” into cell N1.
4. Enter a formula that will change the timestamp format to something that can be compared in other formulas.	(b) Select cell N2 and enter the formula [=TEXT(L2-M2,“h:mm:ss”)], then press ENTER (RETURN) on the keyboard. (“#VALUE!” will appear in the cell). i. Drag the formula down to cell N7.	This formula calculates the difference between the timestamp (L2) and the time set and step one (M2), then formats it to read as hour: minutes: seconds.
5. Label a column for the on-time criteria.	(a) Type “On-Time Criteria” into cell O1.
6. Enter the on-time criteria.	(a) Select cell O2 and type “0:05:00” into it. (b) Drag down to Cell O7. i. While O2:O7 is still highlighted, select the AUTOFILL OPTIONS which is a small box with an orange (+) that appears to the bottom right of the highlighted cells. 1. In the AUTOFILL OPTIONS dropdown menu, select COPY CELLS.	Using the autofill options when dragging the cell is the most efficient way to copy the cell without creating changes to the format or value.	The 5-min criteria is completely arbitrary and can be substituted with any time criteria entered in the appropriate format (h:mm:ss), such as 0:15:00 for 15 min.
7. Label a column that will change the on-time criteria format.	(a) Type “Criteria Format” into cell P1.
8. Convert the on-time criteria to the necessary format for calculations.	(a) Select cell P2 and enter the formula [=TEXT(O2, “h:mm:ss”)] (“0:05:00” should appear in the cell).	This formula is used to format the contents of cell O2 to match the format of cell N2 so that calculations can be made.
9. Copy the format for each interval.	(a) Drag down to cell P7.
10. Label a column that will calculate if data were entered on-time.	(a) Type “On-Time Calculation” into cell Q1.
11. Enter a formula that will calculate if data were entered on-time or not.	(a) Select cell Q2 and enter the formula [=IF((N2 < P2),1,-1)] (”#VALUE!” should appear in the cell).	This is conditional formula that reads if N2 (the difference between the timestamp and the time it was supposed to be entered) is less than P2 (the time criteria set in step 3) “1” will be produced, if N2 > P2, then “− 1” will be produced.	The outputs of “1” for on-time data inputs and “− 1” for data that were not entered on-time are used for ease of graphing described in Table 10 but can be substituted for other values.
12. Copy the formula for each interval.	(a) Drag down to cell Q7.
13. Label a column that will provide a textual indicator for on-time or early/late data.	(a) Type “On-Time?” into cell R1.
14. Enter a formula that will give you a textual indicator if data were entered on-time or early/late.	(a) Select cell R2 and enter the formula [=IF(Q2 > 0,“On-Time”, “Early/Late”)] (”#VALUE!” should appear in the cell).	This formula produces a textual indicator for whether the data were entered on-time or early/late. The formula reads that if cell Q2 (described in Step 11) is larger than 0, then it will produce the text “On-Time”. If Q2 is not bigger than 0 (i.e., − 1), “Early/Late” will be produced.	“On-Time” and “Early/Late” can be replaced with other desired labels. “Early/Late” data entries are grouped together in this example for ease of calculations and graphing but can be separated by adding another time criterion and calculating them separately using the methods described in this table.
15. Copy the formula for each interval.	(a) Drag down to Cell R7.

Creating Automatically Graphing Displays of Data

This section describes the process of creating an automatically generated graphic display of data. A review by Alvero, Bucklin, and Austin (2001) indicated that graphic feedback was effective as a behavior change agent. The automatic graphic display of data can be used for faster analysis of the client data (Sleeper et al., 2017) or for feedback for the individual entering data (i.e., staff data entry performance). As an example, we will describe how to graph the percentage of intervals containing target behavior and on-time data entries. This section only focused on basic graph creation, but see Deochand, Costello, and Fuqua (2015) and Dixon et al. (2009) for further instructions on graph creation.

Before graphing any data, a few formulas must be added to compute the collected data into summary data to be graphed. Table 8 provides a task analysis for deriving the percentage of intervals that contain a target behavior by calculating the number of intervals that contain target behaviors and the total number of intervals recorded. Once the percentage of intervals containing the target behavior is calculated, the instructions in Table 9 can be used to create a graph to display these data for analysis. Additional customizations to the graph may be made based off individual preference, as this only covers the basic components. See supplemental appendices J, K, L, and M for screenshots of critical steps of the task analysis in Excel® to guide percentage of interval graph creation.

Table 8.

Instructions for calculating percentage of intervals containing target behavior

Instruction	Description	Explanation	Alterations
1. Create a label for the formula.	(a) On SHEET2, select cell A8 and type “Total TBs.”	Labels are used for organization for the table and the graphs.	“Total TBs” can be substituted with anything like “Total Instances of Target Behaviors”
2. Enter a formula that will calculate how many intervals contained target behaviors.	(a) Select cell B8 and enter the formula [=COUNTIF(B2:B7,“ > 0”)] (0 should appear in the cell).	This formula counts the number of intervals that contained target behavior as indicated by the value in the selected cells (B2:B7) being greater than 0.	Any added rows should be included.
3. Copy the formula for each target behavior.	(a) Drag over to cell E8.		Any added column should be included.
4. Label the formula.	(a) Select cell A9 and type “Total No TB.”		“Total No TBs” can be substituted with anything like “Intervals W/O TB.”
5. Enter a formula that will calculate how many intervals did not contain target behavior.	(a) Select cell B9 and enter the formula [=COUNTIF(B2:B7,“0”)] (0 should appear in the cell).	This formula counts the number of intervals that did not contain target behavior as indicated by “0” from the selected cells (B2:B7).	Any added rows should be included.
6. Copy the formula for each target behavior.	(a) Drag over to cell E9.		Any added column should be included.
7. Label the formula.	(a) Select cell A10 and type “Total Intervals” (this row will indicate how many intervals data has been entered for each individual target behavior).		“Total Intervals” can be substituted with anything like “Number of Intervals Data was Entered”.
8. Enter a formula that will calculate how many intervals data has been collected for.	(a) Select cell B10 and enter the formula [=SUM(B8:B9)] (0 should appear in the Cell).	This formula sums the number of intervals that data was entered in based off the formulas from step 2 and step 5.
9. Copy the formula for each target behavior.	(a) Drag over to cell E10.		Any added column should be included.
10. Label the formula.	(a) Select cell A11 and type “% with TB.”		“% with TB” can be substituted with any desired label.
11. Enter a formula that will calculate what percentage of interval contained target behavior.	(a) Select cell B11 and enter the formula [=B8/B10] (“#DIV/!” should appear in the Cell).	This is a simple formula that divides B8 (the number of interval containing target behavior) by B10 (the total number of interval that data has been collected) to produce the percentage of intervals that contained target behavior out of all the intervals collected. The reason “#DIV/!” appears is there are currently no inputs for the formula. When data are entered it will automatically change.	For other data collection systems, frequency counts can be added below by using the [=SUM(B1:B6)].
12. Copy the formula for each target behavior.	(a) Drag over to cell E11.		Any added column should be included.
13. Format the cells.	(a) While B11:E11 is still highlighted, on the HOME Tab, in the NUMBER section, open NUMBER FORMAT dropdown menu. i. Select PERCENTAGE on the dropdown menu.	All the cells must be formatted to register the input or it will not calculate correctly.

Table 9.

Instructions for graphing percentage of intervals data

Instruction	Description	Explanation	Alterations
1. Select the range of cells that will be included on the graph.	(a) Highlight cells A11:E11 on SHEET2.
2. Create a bar graph.	(a) Open the INSERT tab, then select the COLUMN Icon in the graphing section. (b) When the COLUMN dropdown menu appears, select CLUSTERED COLUMN (A graph should appear).
3. Add X-axis label.	(a) Right (two finger) click on the graph, then choose SELECT DATA in the menu. i. Click onto the HORIZONTAL (CATEGORY) AXIS LABELS: Cell. ii. While cursor is still in the HORIZONTAL (CATEGORY) AXIS LABELS: cell, select SHEET1 at the bottom of the screen. iii. Highlight cells C1:F1. iv. iv. Select OKAY on the SELECT DATA menu.
4. Format the Y-axis.	(a) Click onto the graph, click onto the Y-axis, then left (two finger) click on the Y-axis. i. Select FORMAT AXIS ii. In the FORMAT AXIS menu, scroll to the bottom and select NUMBER. iii. In the NUMBER dropdown menu, select the CATEGORY dropdown, then select PERCENTAGE. iv. Exit the FORMAT AXIS menu.
5. Move the graph to SHEET1.	(a) Click on the graph, then press CONTROL + C (COMMAND + C) on the keyboard. (b) Select SHEET1 at the bottom of the screen. (c) Select cell H1, then press CONTROL + V (COMMAND + V) on the keyboard.	This is done so that individuals entering the data receive immediate graph feedback on how the client is doing.

Finally, Table 10 provides a task analysis for creating a graph for on-time and early/late data entries using the data created in the timestamp section. Although graphing on-time and early/late data entries may not be required in a data collection system, having immediate graphic feedback on timeliness of data entries may be valuable to supervisors and staff. Graphic displays of data collection timeliness provide immediate feedback on data entry performance, which may affect the future accuracy of the data collection. This calculation and graph is meant only to separate on-time data entries from all other data entries. If separating the early from late data entries is desired, further formulas can be added. See supplemental appendices N, O, P, and Q for screenshots of critical steps of the task analysis in Excel® to on-time and early/late graph creation.

Table 10.

Instructions for graphing timeliness data

Instruction	Description	Explanation	Alterations
1. Select the range of cells that will be included on the graph.	(a) On SHEET2, highlight cells Q2:Q7.
2. Create a graph.	(a) Open the INSERT tab, then select the COLUMN Icon in the graphing section. i. When the COLUMN dropdown menu appears, select CLUSTERED COLUMN (a graph should appear).
3. Add X-axis labels.	(a) Right (two finger) click on the chart area, then choose SELECT DATA. i. Click onto the HORIZONTAL (CATEGORY) AXIS LABELS: cell. ii. While cursor is still in the HORIZONTAL (CATEGORY) AXIS LABELS: cell, highlight cells A2:A7. iii. While still in the SELECT DATA menu, select the NAME Cell and type “On-Time VS Early/Late Data Entries”. iv. Select OKAY on the SELECT DATA menu.
4. Format the Y-Axis.	(a) Click onto the graph, click onto the Y-axis, then right (two finger) click on the Y-axis. i. Select FORMAT AXIS ii. Under the HORIZONTAL AXIS CROSSES section, click the AXIS VALUE checkbox. iii. Under the BOUNDS section, select the MINIMUM Cell and enter “− 1.” iv. Under the BOUNDS section, select the MAXIMUM Cell and enter “1.” v. Under the UNITS section, select the MINOR Cell and enter “1,” then press ENTER (RETURN) on the keyboard. vi. Enter a formula to alter the number values in the axis. 1. In the NUMBER dropdown menu, select the CATEGORY dropdown, then select CUSTOM. 2. 2. Select the FORMAT CODE Cell and enter [[=1] “On-Time”;[= − 1] “Early/Late”; “”] 3. Select ADD. 4. Exit the FORMAT AXIS menu.	Do not enter the outermost brackets, but do include the brackets found around = 1 and = − 1. These directions describe how to create a bar graph that displays negative values so that on-time data entries can be easily compared to early/late data entries. The formula used in step vi is the most efficient way to change values on a graph to text while keeping data entry as numbers to save time. This formula is described in detail by Deochand et al. (2015).
5. Format the X-axis.	(a) Select the X AXIS in the middle of the graph. i. Left (two finger) click on the chart area, then select FORMAT AXIS. ii. Scroll to the bottom of the FORMAT AXIS menu and select the LABELS dropdown menu. iii. Open the LABEL POSITION dropdown menu, then select LOW. iv. Exit the FORMAT AXIS menu.
6. Move the graph to SHEET1.	(a) Click on the graph, then press CONTROL + C (COMMAND + C) on the keyboard. i. Select SHEET1 at the bottom of the screen. ii. Select cell H8, the press CONTROL + V (COMMAND + V) on the keyboard.	This is done so that individuals entering the data receive immediate graph feedback on how the client is doing.

Using the Electronic Data Collection System

Now that the electronic data collection system is complete, it is ready to be used and/or customized. The instructions provided are meant to assist in creating a basic table and graphs. Further esthetic changes are encouraged for the table and the graphs but are beyond the scope of the current article. While there is no “right” way to use the current system, five suggestions are offered. First, save copies of the datasheet for each use with specific labels. For instance, if using the datasheet daily, the practitioner will want to create copies of the datasheet for each day. The easiest way to do this is by creating a folder for each week with datasheets titled for each day. Electronic data often require extra precautions to be compliant with the Health Insurance Portability and Accountability Act (HIPAA). See Rios, Kazemi, and Peterson (2018) and Cavalari, Gillis, Kruser, and Romanczyk (2015) for more information about HIPAA compliance with technology. Second, choose a device on which to enter data. The dropdown menus embedded in the datasheet allow for easy use with a tablet or other mobile devices that have access to the Microsoft Excel® app. Third, once the datasheet is loaded on the device, enter data using the dropdown menus and keypad. Most mobile devices require a single tap to access a dropdown menu and a double tap to access the keypad for entering text (e.g., initials) into a cell. Saving the data will vary for each device. Some devices have auto save functions while others require you to select the save button at the top of the screen. Fourth, provide an outline for how to enter data and provide training for staff who use the datasheet. Saving the instructions as the background of the tablet or computer will insure that staff can access the instructions whenever necessary. This can be done by creating an outline of the instructions in a word processing document, saving the document as an image, and saving the image as the background on the device. Fifth, monitor the data collected. Supplemental appendix R shows an example of data entered in the electronic data collection system with the corresponding graphic feedback. At the upper-right side of the figure, you can see the target behavior graph and lower-right shows the data entry timeliness graph. The data entry timeliness graph shows that all intervals except 1:00 p.m.–2:00 p.m. were entered on time. If you want to check the exact time that data was entered, you can see each interval’s time on SHEET2 in column N. Use the target behavior data to inform clinical decisions and use the data entry timeliness data to provide feedback to staff members collecting the data.

Discussion

The purpose of this paper was to outline a method that practitioners can use to build a customized partial-interval electronic data collection system using Microsoft Excel®. Although our example used a 60-min partial-interval system, it important that intervals be set as short as possible to curtail measurement error common in partial-interval systems (LeBlanc et al., 2016; Fiske & Delmolino, 2012; Wirth, Slaven, & Taylor, 2014).

The method described in this paper can be used to create shorter interval systems as well as many other types of data collection systems by making minor alterations as suggested in the tables. However, the generalizability of this method to other data collection systems is restricted by limitations of Excel®. For example, using a time-reliant system like duration or latency may not be feasible without creating a macro which is not currently supported on mobile versions of Excel®. Despite technological boundaries limiting some applications of this system, there are endless possibilities for how this system can be customized and built upon. Future task analyses should build upon the method outlined in this task analysis and create new methods for building electronic data collection systems in Excel® and other user-friendly interfaces. For example, a task analysis describing a method for building an interobserver agreement (IOA) measure into an electronic data collection system like the one described in this paper would be very valuable.

Because data collection adherence, accuracy, and integrity are important in the field of ABA, resources to improve staff performance in these areas should be created and evaluated through research. In addition to creating high-tech methods for collecting data, we should investigate the dissemination and adoptability of our techniques. Electronic data collection may be a convenient solution to many data collection issues, but the paucity of research in this area must be noted (Sleeper et al., 2017). Researchers should consider assessing the effects of systems like the one described in this article or current apps focused on data collection integrity and adherence to further research in this domain.

Compliance with Ethical Standards

Conflict of Interest

The authors declare that they have no conflict of interest.

Ethical Approval

This article does not contain any studies with human participants or animals performed by any of the authors.