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


Insights from UX research

A column by Michael A. Morgan
June 21, 2021

In this first installment of my series “Rows and Columns,” I’ll describe how to use some very powerful tools of spreadsheets that can make analyzing your UX research data much easier. For those who have been reluctant to use spreadsheets during analysis, this series is for you, and you’ll hopefully find this information useful. For those of you who have expertise in using spreadsheets, some of this information might be review.

The central part of any UX research project is the analysis of data. This task can be both satisfying and cumbersome at the same time. As you go through your data, you might become excited as you recognize emerging patterns or see great variations across participants. However, getting to the point at which you can easily see such trends can be quite difficult. Your data must be in a format that affords easy filtering, so you can decipher the various rows and columns across participants.

Part 1 of this series covers the following features of spreadsheets, which can facilitate your understanding of the data you’ve gathered:

  • filters
  • form elements such as checkboxes and drop-down lists
Champion Advertisement
Continue Reading…

In my examples throughout this column, I’ll use a fictional Web site called Vinyl Mart, which lets people buy and sell their vinyl record albums. Here’s the scenario: Vinyl Mart hired you, the UX researcher, to do user interviews with their customers to better understand how they use the site. You’ve conducted a dozen interviews and organized your data in a spreadsheet. All figures show how to complete the steps I’ve described using Google Sheets.

The Magic of Filters: How to Find the Data You Need

One of the most powerful features of spreadsheets is their filtering capability. The magic of filters lies in their ability to let you focus on a subset of your data. Before you can filter your data, it must reside in a table comprising rows and columns. Otherwise, you could experience unexpected results.

Whether you’ve created a data table using Microsoft Excel or Google Sheets, you’ll find a Data menu somewhere. To set up a filter, you must first select a cell in the table that contains some data. (If you haven’t selected any actual data—for example, if you’ve selected a blank cell outside the table—the filter feature won’t work.) In Google Sheets, choose Data > Create a filter. Spreadsheet programs are smart enough to figure out the boundaries of the table and enable filters within its columns.

Now, you can start going through the data from each interview to begin identifying some patterns. Let’s look at an example of how to use filtering to analyze subsets of the data in a spreadsheet.

Using Filters to View Subsets of Vinyl Mart’s Data

One of the first things you might look at are the customer profiles. In your spreadsheet’s Category column, there is a category called Profile. Rather than scanning the Category column to find the Profile category, you can use your spreadsheet application’s filter capability to show only the rows for which the category is Profile. Select a cell in the table, then choose Data > Create a filter, as shown in Figure 1.

Figure 1—Creating a filter using Google Sheets
Creating a filter using Google Sheets

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.

Figure 2—Filtering is active
Filtering is active

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!

Figure 3—The Clear link
The Clear link

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.

Figure 4—A filtered list of all participants’ occupations
A filtered list of all participants' occupations

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.

Figure 5—A table with blank rows
A table with blank rows

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.

Figure 6—Selecting (Blanks) prior to deleting all blank rows
Selecting (Blanks) prior to deleting all blank rows

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.

Figure 7—Inserting checkboxes into Google Sheets
Inserting checkboxes into Google Sheets

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.

Figure 8—Using checkboxes to tally the frequency of behaviors
Using checkboxes to tally the frequency of behaviors or 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.

Figure 9—Workarounds for the binary nature of checkboxes
Workarounds for the binary nature of checkboxes

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.
Figure 10—A worksheet containing all of your drop-down lists
A worksheet containing all of your drop-down lists
  1. Provide a descriptive name for the new worksheet by double-clicking its label. I usually use a title that relates to lists.
  2. 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.
Figure 11—A list of formats on a drop-down list
A list of formats on a drop-down list
  1. Select the cell in which you want the drop-down list to appear, as Figure 12 shows.
Figure 12—Adding a drop-down list to a cell of the spreadsheet
Adding a drop-down list to a cell of the spreadsheet
  1. To add data validation, choose Data > Data validation, as shown in Figure 13. The Data validation dialog box appears, as shown in Figure 14.
Figure 13—Adding data validation
Adding data validation
  1. 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.
Figure 14—Setting up data validation in the Data validation dialog box
Setting up data validation in the Data validation dialog box
  1. 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.
Figure 15—The Select a data range dialog box
The Select a data range dialog box
  1. 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!
Figure 16—Completing your selection of the data range
Completing your selection of the data range
  1. A drop-down list comprising the items from your worksheet should now appear in the cell you originally selected, as shown in Figure 17.
Figure 17—Your Formats drop-down list
Your Formats drop-down list

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.

Figure 18—Adding items to a drop-down list
Adding items to a drop-down list

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.

Figure 19—The range of cells in red shows the items you added
The range of cells in red shows the items you added

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.

Figure 20—An invalid-input error resulting from renaming a list option
An invalid-input error resulting from renaming a list option

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.


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. 

Senior UX Researcher at Bloomberg L.P.

New York, New York, USA

Michael A. MorganMichael has worked in the field of IT (Information Technology) for more than 20 years—as an engineer, business analyst, and, for the last ten years, as a UX researcher. He has written on UX topics such as research methodology, UX strategy, and innovation for industry publications that include UXmatters, UX Mastery, Boxes and Arrows, UX Planet, and UX Collective. In Discovery, his quarterly column on UXmatters, Michael writes about the insights that derive from formative UX-research studies. He has a B.A. in Creative Writing from Binghamton University, an M.B.A. in Finance and Strategy from NYU Stern, and an M.S. in Human-Computer Interaction from Iowa State University.  Read More

Other Columns by Michael Morgan

Other Articles on UX Skills

New on UXmatters