Rows and Columns, Part 1: Jump-starting Analysis Using Spreadsheets

Once you’ve chosen the Create a filter command, a menu icon appears to the right of each column header in the first row of the table, as shown in Figure 2, indicating that filtering is available. Clicking the menu icon displays a variety of sorting and filtering options.

To help you understand what customers do for a living—when they’re not buying or selling vinyl records—you can filter on Category. All of the categories are selected by default, so if there are only a few items, deselect any that you want to hide. However, after conducting sessions that are one to two hours long, there might be a dozen or more different items, so it would be quicker to click the Clear link shown in Figure 3, then select just the items you want to show. This saves a lot of time!

Select Profile, then click OK. Next, filter the Sub-category column, keeping only Occupation selected. You’ll end up with a table that you’ve filtered by Profile and Occupation, as shown in Figure 4.

Filtering makes it much easier to sift systematically through the various parts of your sessions to find the data you need for your analysis—a critical step in your research process.

Filtering Out Blank Rows

One issue that commonly occurs is that your filters might not show all of your data. For example, while you might have interviewed twelve participants, when you apply a filter, you might get only nine participants. This typically happens when there are one or more blank rows between rows that contain data, as shown in Figure 5, causing filtering not to work as expected.

The filtering feature is not smart enough to figure out that this is an error, so it returns only the data up to the first blank row. To address this issue, simply delete the blank rows. If you have an inordinate number of blank rows, the best way to eliminate all of them is to select the entire table, create a filter, then display the filtering options for any column. Deselect all filtered items except the one labeled (Blanks), as shown in Figure 6, then click OK. Once you’ve filtered out all but the blank rows, select all the blank rows, then choose Edit > Delete selected rows to delete them. Next, choose Data > Turn off filter to view your clean set of data, with no blank rows. The missing participants should magically appear the next time you run your filter.

Adding Form Elements to Your Spreadsheets

When thinking about spreadsheets, you might think of complex formulas and tables of data spilling over into more and more worksheets. But form elements—which are similar to those in Web forms—are also available in spreadsheet applications. You can easily start adding form elements to your spreadsheets to aid both data collection and the analysis of your research data. Let’s look at two critically important form elements: checkboxes and drop-down lists.

Using Checkboxes When Tallying Your Data

A great way to tally up your data from various participants is using checkboxes. In Google Sheets, to add a checkbox to your spreadsheet, choose Insert > Checkbox, as shown in Figure 7.

Using our Vinyl Mart example, let’s say you’re gathering the findings from the sessions and want to tally the frequency of specific behaviors or feedback. Using checkboxes is the perfect way of systematically capturing specific instances of data. When you’re entering your observations in the spreadsheet, you can quickly select which participant did or said what, as shown in Figure 8, then tally the frequency of specific behaviors or participants’ statements.

For example, in Figure 8, you can see how often sellers ask buyers to provide feedback after a sale. Participants 2, 5–8 and 10–11 have indicated they always do so. Participant 3 does this only sometimes.

Drawbacks of Using Checkboxes

One drawback of the checkbox is that, by its very nature, it is binary—either on or off. There is no other state. Having another state could be useful when a participant either didn’t answer the question or the moderator forgot to ask it, so ideally, there should be a third state for that checkbox. There are a few workarounds for this problem.

The first option is to format a cell containing a checkbox in a different color, to indicate that the moderator did not collect the data. In Figure 9, a gray checkbox indicates that there is no data available for a participant. Another option is to add a new row to the spreadsheet for the comment “Forgot to ask the question,” as shown in Figure 9, then select all of the participants whom you neglected ask the question. You can see examples of both of these workarounds in Figure 9.

Using Drop-down Lists

Now, let’s look at how to add drop-down lists to your spreadsheets to ease data entry and analysis. These form elements provide a fixed list of specific options. Creating drop-down lists within spreadsheet applications is a bit more work than creating checkboxes. Here’s how you can create drop-down lists in Google Sheets.

1. Create a separate worksheet in your spreadsheet application, in which you can consolidate all of the drop-down lists you’re creating, by clicking the + icon, as shown in Figure 10.

2. Provide a descriptive name for the new worksheet by double-clicking its label. I usually use a title that relates to lists.
3. On the worksheet you just created for lists, create a list of possible options. Figure 11 provides an example. For readability, add a column header above the list and format it in bold to make it visually distinctive from the items directly below it.

4. Select the cell in which you want the drop-down list to appear, as Figure 12 shows.

5. To add data validation, choose Data > Data validation, as shown in Figure 13. The Data validation dialog box appears, as shown in Figure 14.

6. To finish setting up data validation, under Criteria, with List from a range selected, click the grid icon shown in Figure 14 to choose the drop-down list you created in Step 1. The Select a data range dialog box appears, as shown in Figure 15.

7. When in the Select a data range dialog box, you can select the data range for the drop-down list by navigating to the worksheet tab on which your lists of items reside. Select the list of items you created, but not its header, and the data range you’ve selected appears in the text box, as shown in Figure 15. Click OK to return to the Data validation dialog box.

8. In the Data validation dialog box, you can now see the data range you’ve selected, as shown in Figure 16. Click Save. That’s it!

9. A drop-down list comprising the items from your worksheet should now appear in the cell you originally selected, as shown in Figure 17.

Limitations of Drop-down Lists

Drop-down lists are useful for research questions that have a finite number of options and only one answer rather than multiple answers. For example, the Vinyl Mart discussion guide might include a question such as What music formats do you primarily sell on Vinyl Mart? Since there is only one answer, a drop-down list is a good choice for data collection. But, if there is more than one possible answer, a drop-down list would not be an option because drop-down lists don’t support multiple selections. One way around this issue might be to add a Multiple formats option to your list.

Benefits of Using Drop-down Lists

There are some benefits to using drop-down lists for data collection—over using a text-entry approach. One is that the consistent formatting of list options makes the data easier to analyze. If you used text entry, the data you entered would more likely be in a variety of formats. So, before you could analyze the data, you would first need to wrangle the data into a consistent format, which can often be time consuming and painstaking. Because data you’ve collected using drop-down lists already has a consistent format, data aggregation and analysis is much easier.

Another benefit of using a drop-down list is that you can easily add items to the list. For example, let’s say you created a list of music formats for data collection, then after the first few sessions, you realized that you wanted to distinguish between 45 rpm and 33 rpm record formats. You could simply go to the worksheet tab containing your lists, insert two new rows into the list, add these options to the list, then save them. Figure 18 shows the revised drop-down list, which has expanded automatically to accommodate them.

If you’re adding items to a drop-down list when a study is already in progress, just be sure that they fall within the original range of options in the drop-down list, as Figure 19 shows. Otherwise, your changes won’t render.

Drawbacks of Using Drop-down Lists

Although you can add more items to expand your drop-down lists, renaming items is another matter and can create problems because the drop-down lists that are already in use in your spreadsheet won’t automatically recognize your changes. As Figure 20 shows, changing Metal to Heavy Metal would cause an invalid-input error because the spreadsheet would still refer to the original value.

The best way to avoid such issues is to plan from the beginning what items drop-down lists should comprise. However, if you’ve done enough research, you’ll know to expect the unexpected. One way to rectify such errors is to filter on the column in which the error occurs, then manually select the new values. If there are many instances of such changes, you can reselect the value in one, then copy and paste the data into the other rows that also require the new value.

Conclusion

Just as when you’re learning anything new, it takes a bit of time to figure out how to work with these powerful features of spreadsheets. Once you’ve created a worksheet with all the bells and whistles that I’ve described in this column, you can use it to create a template, making your future research projects much easier. You can create a template by either making a copy of a spreadsheet or duplicating only particular worksheets within a spreadsheet.

In Part 2 of this series, I’ll describe some more ways in which you can use spreadsheets to power your UX research–analysis efforts.