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:

• Firstly, export the raw data from your survey.
• Navigate to the Crosstab by segments tab. Within this tab you will see the results to all questions within your survey, both overall and divided by the segments set up on to platform.

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:

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”.

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

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.

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.

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:

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.

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