How do I use over-indexing analysis to visualise differences between segments?

Over-indexing refers to investigating the difference between a specific subset of a dataset and the dataset overall. This can be a valuable tool when performing survey experiments, as it allows you to easily see the differences between a particular segment of responses and the overall average.

Over-index analysis is performed automatically in Conjointly reports when you use crosstabs or monadics. But in this guide, we are going to show how over-indexing can be performed manually to visualise the results of Conjointly’s segmentation feature. We will be using the Conjointly Excel Plugin to help us perform this quickly and efficiently.

To use over-indexing analysis in your report:

Crosstab by segments tab in the Excel survey report

To format this tab for easier interpretation:

  • Expand column widths, and hiding unnecessary columns, such as “Question order” and “Option order”.
  • Calculate the difference between each of the three segmented columns and the overall “All participants” column. This difference can be seen in the far right column of the following screenshot:
Calculating difference between columns in the Excel survey report

We will use cell highlighting to easily visualise the magnitude of the differences between segments:

  • Highlight the new difference columns on the right hand side.
  • Select “Conditional Formatting → Colour Scales → More Rules” under the “Home” tab.
  • Set the “Format Style” as “3-Colour Scale within the following dialogue box.
  • Set the minimum, middle, and maximum values/colours and select “OK”.
Adding conditional formatting in the Excel survey report

The difference values will now have their cell backgrounds dynamically formatted to reflect the cell value:

Highlighting the difference between cells in the Excel survey report

As the formatting is currently saved as conditional formatting, the cell colours will dynamically update if the underlying value changes. As we want to paste this formatting onto the segmentation values, we need to convert the cell colours to static formatting. This can be easily performed using the Conjointly Excel Plugin:

  • Highlight the right hand columns and navigate to the “Conjointly” tab.
  • Select the “Lock Conditional Formatting” option.
Locking conditional formatting in the Excel survey report

Once the formatting has been locked in place:

  • Copy the difference columns.
  • Use the “Format paste” function to move the difference columns onto the segmentation columns.
Copying difference calculation columns in the Excel survey report

Now that we have set the cell background to easily display the cell’s difference from the mean, we will also use data bars to easily display the cell value for easy comparison:

  • Highlight the first column.
  • Navigate to “Conditional Formatting” and select “Data Bars” → “More Options” to open the following dialogue box:
Setting cell values in the Excel survey report

To clearly displaying percentages:

  • Set the minimum value to 0 and the maximum value to 1.
  • Choose an appropriate colour and select “OK”. We can go through and repeat this for each of our columns.
Dispaying cell value percentages in the Excel survey report

Finally, we can use background colouring and borders to tidy up our table for use in presentations:

Formatting a table for presentation in the Excel survey report