Daljit Dhadwal

When extracted, data collected using radio buttons sometimes comes out as a TRUE/FALSE column for each option.

For example, data from the following question:

may look like this in the extract:

Data that is spread over multiple columns like this can be hard to analyze (e.g., pivot tables don’t work well with this kind of data). If you have data that looks like this, you can easily determine which option was selected and collapse the three columns of data into one column. This is assuming that one value is TRUE (this will be the case if a response is required). Once the data is in one column, it’s easy to summarize the information using a pivot table.

We’ll create a new column called Q1_ eaten_cereal that will have a value of Q1_eaten_cereal_yes, Q1_eaten_cereal_no, or Q1_eaten_cereal_dont_remeb.

The following Excel formula in cell E2 will determine which option was selected:

=INDEX($B$1:$D$1,1,MATCH(TRUE,B2:D2,0))

The MATCH(TRUE,B2:D2,0) part of the formula looks for the TRUE value in cells B2 to D2. It returns a 1 because TRUE is in the first cell. The outer part of the formula becomes INDEX($B$1:$D$1,1,1) and this will return the item in row 1 and column 1 from cells B1 to D1 (the row containing the text for the radio button options).