August 2024 Update

Whilst you are still able to conduct your own data manipulation, CWC has now developed a Conversion Service where, if an Illuminate-compliant template was used, CWC can provide you with a file of your data, ready-formatted for Illuminate. This means that there is minimal manipulation required.

For information on how to use the Conversion Service, please see the relevant resource.

To proceed with conducting your own data manipulation, please continue reading this guide.

 

If you have collected your Illuminate data in Culture Counts, then you will need to transfer the relevant responses from Culture Counts into the Illuminate platform.

This process involves manipulation of the data to make it work for Illuminate because the two platforms (Culture Counts and Illuminate) use different systems, formatting, and technologies.

We have supported you in streamlining your data collection processes through the inclusion of Illuminate-compliant templates, meaning that you don’t need to send two surveys to the public. However, you will need to do some work in Microsoft Excel to enable successful upload to Illuminate.

Guidance on how to import your data to Illuminate is available through the Training Module within the Illuminate platform.

 

 

Downloading your data from Culture Counts

 

You will need to download a CSV file of your data by:

• Opening the relevant evaluation in Culture Counts
• Selecting the appropriate survey in Culture Counts (most likely one used with members of the public)
• Clicking the ‘Download survey results’ button on the right of the screen

 

Once your survey results are ‘in’ Excel, you should:

 

• Make a copy of the downloaded data so that you have the original set to hand, just in case there are any problems that unexpectedly arise!
• Use ‘save as’ to save the file as an Excel workbook; this will enable you to make edits
• ‘Hide’ any columns that do not contain Illuminate responses (e.g., the responses to dimension questions); this will make the workbook neater and easier to navigate

 

You can see how to ‘hide’ columns via this resource on Excel Easy.

 

Ensure that you have the file of acceptable responses to each question from Illuminate, so that you are ready to commence the data manipulation.

 

Manipulating the downloaded data

 

Now, on to some tips for the data manipulation itself…

Use Find and Replace in Excel to make changes to the whole document, a specific column, or en masse.

 

To make changes for all instances of an item:

 

1. Click the Find & Select button on the Home tab.
2. Select Replace.
3. Type (or paste if you have copied what you need to replace) the item you want to replace in the Find what 
4. Type the replacement text/value in the Replace with box (or leave it blank).
5. Click Replace All

 

To make changes in just one column:

 

1. Highlight the column
2. Press ctrl/command + F
3. Select Replace
4. Enter the item you wish to change in the Find what box
5. Enter the replacement in the Replace with box
6. Click Replace All

 

More detailed guidance on the Find and Replace features in Excel is available on the Microsoft Office support pages here.

 

 

Some specific examples of required changes

 

Socio-economic question, labelled B-Q7

• Culture Counts includes the description for each option and square brackets, but Illuminate only requires the opening phrase. Use Find and Replace to delete the descriptions and square brackets.

 

Ethnicity question, labelled B-Q4

• Culture Counts split the question into different parts as opposed to one long list to make the options easier to digest for the respondent. Answers are therefore spread across different columns. Collate these into one column to copy into Illuminate.

 

Sex and gender identity questions, labelled B-Q16, B-Q17 and B-Q18 [1]

• To limit the number of questions in a survey, Culture Counts features question logic. This improves the flow of the survey and allows you to specify which questions to show/hide based on how preceding questions are answered.
• For example, B-Q18 (‘How would you describe your gender identity?’) would only appear as a question if someone answered ‘No’ to B-Q17 (‘Is your gender identity the same as the sex you were assigned at birth?’). This is because, when someone answers B-Q16 (‘What is your sex?’) with ‘Female’, and their answer to B-Q17 is ‘Yes’, we can reasonably infer that their answer to B-Q18 would be ‘Woman’.
• Respondents who answer ‘Yes’ to B-Q17 will not be asked B-Q18, so the data field for this question would be blank. Remember to add in any similar missing data in Excel completely manually, or by using multiple IF formulae.
• Recommended function, replacing cell identifier with the appropriate one for your spreadsheet:  =IF(A1=”Male”,(IF(B1=”Yes”,”Man”)),(IF(A1=”Female”,(IF(B1=”Yes”,”Woman”)))))

Ensure that you have the original data to hand so that you can manually change the cells where someone’s gender identity is not the same as the sex they were assigned at birth.

 

Changes to group attendance question, labelled B-Q13-13.4

• Remember to check whether logic has been used in the Culture Counts version of your survey. If it has, remove any follow-up logic questions that are not needed for Illuminate, such as the ‘Group size and age’ question: ‘Which age groups were the people who attended with you (excluding yourself)?’
• For questions B-Q13.1-13.4, ‘With how many people of each age group did you attend (excluding yourself)?’, Illuminate requires you to put a value of 0 in the blank cells to indicate that no one of that age attended.
• To replace blank cells with zeros in Excel:

1. Select the blank cells you want to fill with ‘0’.
2. Click the Home tab, then Find & Select, then Go To Special.
3. Select Blanks and click OK. Excel will then select all blank cells within the specified range.
4. Type ‘0’, then press Ctrl/Command + Enter. The blank cells will be populated with 0.

 

Other changes that are required

 

Multiple choice questions

• Will be surrounded by square brackets and single quotation marks. Use Find and Replace to delete these items.

 

Yes / No questions

• Will appear as a value of 0 or 1. Use Find and Replace to alter these (remember that 0 will be whichever button was displayed on the left and 1 will be whichever was displayed on the right e.g. Yes / No).

 

 

Closing Words

 

These are just a few tips on how to start manipulating the data required to successfully transfer data from Culture Counts to Illuminate.

You can learn more about manipulating data in Microsoft Excel by accessing their online guidance.

If you have questions about Illuminate or are experiencing difficulties uploading the data, please do contact their support desk.

 

 

 

[1] Please note – We will be amending this when we have confirmation from Arts Council with any further updates to required questions.