MegLearner
New Member
- Joined
- May 8, 2016
- Messages
- 1
I issued a questionnaire a few years ago and downloaded responses from the questionnaire provider into an Excel spreadsheet. I did simple analysis, no problem. I have now been asked to provide some pivot tables (crosstabs), eg gender by age. The problem is the data came down in separate columns that the pivot table doesn't recognise. (I also downloaded it into SPSS but I am getting the same problem there. I use Excel at a simple level pretty often, so reckoned I would have a better chance of doing it in Excel.)
1. Each respondent has an ID number and each row on the spreadsheet refers to one ID number (ie person) but not all people answered all questions, so some cells are blank anyway because they weren't answered.
2. The data for gender is in two separate columns, one headed "male", one headed "female". All the females are identified as such in the female column by the word "female" (Duh), ditto all the males in the male column. The corresponding cells in the male column are thus blank for "females" and ditto in the female column for "males".
3. The age groups are in separate columns headed with the specific age group, eg, 17, 18, 19, 20 - 24, 25 - 30, 31 - 34 and 35 and over. The age or age band is entered into the appropriate cell in the appropriate column for that person and all the other cells under age band for that person are thus empty. I am going to try and post a screenshot of the part of the sheet:
From reading the information on pivot tables, it seems Excel is looking for 2 columns with all cells filled, one column with male / female in the appropriate cell and the other column with the age or age band in the appropriate cell?
Is there any way I can get the data into a format that Excel understands without having to copy and paste all this across and move it by hand please? I have been searching for information on line for the last 24 hours but can't find anything similar.
Thanks in advance for any help you can rrovide
1. Each respondent has an ID number and each row on the spreadsheet refers to one ID number (ie person) but not all people answered all questions, so some cells are blank anyway because they weren't answered.
2. The data for gender is in two separate columns, one headed "male", one headed "female". All the females are identified as such in the female column by the word "female" (Duh), ditto all the males in the male column. The corresponding cells in the male column are thus blank for "females" and ditto in the female column for "males".
3. The age groups are in separate columns headed with the specific age group, eg, 17, 18, 19, 20 - 24, 25 - 30, 31 - 34 and 35 and over. The age or age band is entered into the appropriate cell in the appropriate column for that person and all the other cells under age band for that person are thus empty. I am going to try and post a screenshot of the part of the sheet:
From reading the information on pivot tables, it seems Excel is looking for 2 columns with all cells filled, one column with male / female in the appropriate cell and the other column with the age or age band in the appropriate cell?
Is there any way I can get the data into a format that Excel understands without having to copy and paste all this across and move it by hand please? I have been searching for information on line for the last 24 hours but can't find anything similar.
Thanks in advance for any help you can rrovide
