When you run a study on Prolific, you will likely receive at least two spreadsheets: our demographic export, and your survey responses from your external software.

You may find it helpful for your analysis to combine these into one document. Please note that the exact way to do these steps may differ depending on the spreadsheet software you’re using (e.g. Microsoft Excel, Google Sheets, Apple Numbers).

Preparation

Have your two spreadsheets side by side. On both spreadsheets, find the column that contains the participant ID. On the Prolific demographic export, this is typically column B, though you will need to check the column in the download from your external software.

As the Prolific demographic export is typically the smaller of the two spreadsheets, we recommend moving the demographic data into the spreadsheet from your external software.

Option one: Use a formula

You can use an XLOOKUP or an INDEX-MATCH formula, but in this guide, we’re going to be looking at the VLOOKUP.

The VLOOKUP formula works by pulling the data from one spreadsheet into another, by matching based on a unique identifier in both spreadsheets. This unique identifier would be the Prolific participant ID.

Select the cell you want to paste the data into and input the VLOOKUP formula.

• The ‘Lookup value’ is the unique identifier, in this case, the participant ID.
• The ‘Table array’ is the data in the demographic export you want to copy over to your other spreadsheet. The participant ID must be in the leftmost column of this selection.
• The ‘Column index number’ is the number of columns that the data you want to copy over is, from the column containing the participant ID. For example, if the participant ID is in column A and the data you want is in column D, you would input the number 4 here.
• The ‘Range lookup’ is to specify whether you want exact participant ID matches or similar matches. Type ‘FALSE’ to ensure you only get exact ID matches.

We recommend you lock your arrays by putting $ $ on either side of the reference you want to stay the same. You can also activate this by clicking F4 on your keyboard.

Once you’ve successfully done one cell, you can drag the formula down to the bottom of the spreadsheet so that all remaining rows are inputted with the correct data.

You can then repeat this if you have other columns to move over.

Once you have your formula results in the spreadsheet, we recommend copying these data, then pasting them ‘as values’ back into the same place. This will ensure that your data isn’t changed if something happens to the first spreadsheet or the formula.

Option two: Sort both spreadsheets in participant ID order

We recommend using a formula, but if this won’t work for you, you can manually sort your spreadsheets to be matched.

Select the column containing the participant IDs. and sort this into Ascending order. How you have to do this may differ depending on what spreadsheet software you’re using. Make sure that the whole sheet changes to match this sort, not just the one column. Do this on both spreadsheets. Spot-check some of the rows on both spreadsheets to make sure they contain the same ID. For example, check that row 26 on both spreadsheets contains ID 123456.

You can then copy and paste the data from one spreadsheet to the other. We’d recommend copying the data from the smaller spreadsheet and pasting it into the larger spreadsheet. This typically means you’ll be copying the data from the Prolific demographic export. Paste these data into the last column of the spreadsheet so that none of your other data is overwritten.

Need further help?
Click here to contact us