Share via

Reorganising survey data in excel?

Anonymous
2023-02-05T15:15:19+00:00

Hi, I collected a large amount of survey data recently (1000+ respondents, across 42 questions and info on responded demographics) and I was wondering If anybody could give me some tips on how to re-organise my data into the format I need for my analysis.

The survey collected data on a number of covariates and then collected opinion scores both pre and post treatment for 5 treatments (Group A- Group E)

Any tips, tricks or hints would be welcome before I attempt to reformat it by hand and inevitably make a number of mistakes

Thanks

My Data is currently formatted like this:

ID covariate1 covariate2 ... covariate8 opinion1 (pre) opinion2<br><br>(pre) ... opinion21<br><br>(pre) opinion1<br><br>(post) opinion2<br><br>(post) ... opinion21 (post)
1 0 1 1 4 1 5 6 2 3
2 0 1 0 2 3 7 1 2 3
...
n 0 1 0 3 1 3 3 3 1

But I'm looking to reformat it like this:

ID group time opinion1 opinion2 ... opinion21 covariate1 covariate2 ... covariate8
1 GroupA pre 5.0 4.0 ... 3.0 0 1 ... 0
1 GroupA post 6.0 3.0 ... 4.0 1 0 ... 1
2 GroupB pre 7.0 8.0 ... 9.0 1 0 ... 0
2 GroupB post 6.0 7.0 ... 8.0 0 1 ... 0
... ... ... ... ... ... ... ... ... ... ...
n GroupE pre 2.0 3.0 ... 4.0 0 0 ... 1
n GroupE post 3.0 4.0 ... 5.0 1 1 ... 0
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-02-05T23:29:41+00:00

    Hi,

    You may download my solution workbook from here.

    Hope this helps.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-02-05T16:09:13+00:00

    Hi,

    I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

    One possible way to reformat your data in MS Excel is to use the "Pivot Table" and "Pivot Chart" features.

    1. Select the range of cells that contains your data, including the header row.
    2. Click the "Insert" tab, and then click "Pivot Table" in the Tables group.
    3. In the Create PivotTable dialog box, select the location where you want to place the pivot table, and then click "OK."
    4. In the PivotTable Fields list, drag the "ID" field to the "Rows" area, the "group" field to the "Columns" area, and the "time" field to the "Filters" area.
    5. Drag the "opinion" fields to the "Values" area and choose the type of calculation (e.g., average, sum, etc.) that you want to perform on the opinion data.
    6. Drag the "covariate" fields to the "Values" area and choose the type of calculation you want to perform on the covariate data.
    7. To create a pivot chart, select any cell within the pivot table and then click the "Insert" tab, and then click "Pivot Chart" in the Charts group.
    8. In the PivotChart Fields list, drag the fields you want to display in the chart to the "Axis Fields (Categories)" and "Legend Fields (Series)" areas.
    9. You can then use the pivot chart and pivot table to perform your data analysis.

    These steps should give you a good starting point for reformatting your survey data in MS Excel.

    If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

    Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

    Best Regards, Sneha

    Was this answer helpful?

    0 comments