Part 1 - Using Excel for Open-ended Question Data Analysis

so I want to show you how to enter responses to open-ended questions for data analysis this is not something that we covered in class going back to the campus life survey case study that we used on Monday you remember that question number six was an open-ended question what suggestions would you make for improving campus life I did not include responses on the handout you received but let's pretend that we had responses on this question people answering the survey would have put their responses into this space and responses could have ranged from anything such as more food options to transit to anything that people thought would improve campus life what we want to achieve with the data analysis on the open-ended responses is to list all the responses that we received on our 15 questionnaires or in your case 40 identify common themes in the responses and then count the number of responses that apply to each of these themes so let's go to our data analysis worksheet and see how that's done as you can see I have my codebook on my first tab my data in my second tab and then I've got a tab for each of my questions so the first thing I'm going to do is insert a new tab or new worksheet for question number six so I'm going to rename it q6 and then I don't copy and paste data because it's not an original data tab but I do want to list all the responses that I've received for each of the questions so I can go to this page and copy and paste the question numbers instead of rewriting them all pasted here and then what I would do is I would go to each of the individual questionnaires and capture the responses for each one of those and type them in to the data analysis spreadsheet so again going back to the questionnaire looking at people had responded in this area and then entering each response based on which as applies to each questionnaire so some of the things that people may have said is they may have said more live music would improve campus life better food options in the cafeteria they may have said organized carpooling they may have said free ice cream someone may have seats decided they would like to see McDonald's on campus someone else might have felt student fees oops student fees on transit and let me just actually improve or increase this size here so you can see better ah biggest student lounge maybe one of the responses that we might have received printers that work in the computer labs organic food options in the cafeteria more computer labs maybe someone might have thought we required more parking might have been something that someone asked for better computers better transit service free concerts maybe something that we got for off of one of the question is and maybe cheaper parking rates so these are the 15 responses that we've captured in the question by the questionnaires then the next step we want to identify common themes so that we can group these responses so for me what I'm seeing is people are interested in entertainment options they're interested in food options there's a transportation component let's just fix this and there's this seems to be computers so I would just list the themes in this in this column so entertainment food computers no computer facilities and transportation so these are four themes that are coming up so the statistics that I'm going to want to apply is the first thing I'm going to want to do is do a frequency count how many times do each of these responses show up so I can use the labels that already exist to do my count drag down and I've got my four right here and then what I could do is I could say okay well I'm going to give this sort of a small letter code and I'm going to label each of these responses based on each one of these themes so let's say e for entertainment F for food C for computers and T for transportation so so then what we would do is next to each one of these responses is just label it using one of these codes so we can highlight this and say codes I can move this over one to make a little bit more room okay let me make this a little bit wider so I'm going to assign one of these codes to each of these responses so live music would be an e that a food would be an F carpooling would be t3 ice cream would be F McDonald's F transport for tea we could say big a student lounge is entertainment you may feel that there's another theme that you want to use for that but you want to make sure that you're getting more than one response in each theme or each grouping organic food options or sort printers at work see organic food is F more computer labs that's going to be C again parking is transportation C is computers t again for transportation entertainment and then transportation then what we want to do in order to count these items connect up as small as you want to use account if so we can enter the formula for count if open brackets highlight the range we want the count to be completed in comma open inverted commas and we've said efore entertainment closed inverted commas and closed bracket and so Excel returns the count number of times we see in an entertainment option show up we can do the same thing with a copy and paste down once we've added our dollar signs to keep the range the same then I've also got to remember to change the letter that I've assigned for this code so f of food C for computers and transportation is a T and I just want to do a check to make sure that I've gotten the right number and I've gotten my my my count has worked so I'm going to put or the sum in ask it to sum all the number of responses and this number should match the number of surveys that I have and in this case it does so I know my count if has worked so I like to just highlight what I've done so that the person who comes after me can easily see what we've worked on and I can see what I've worked on the other thing we talked about was using proportion so rather than just using the count proportion is often a more useful calculation or more useful data analysis so again I can use the theme or the title that's already in place copy and paste down and then I want to enter a formula to calculate the percentage so 3 out of 15 respondents indicated that entertainment is something they would like to see more of and I want to show that as a percentage so I go to the percentage icon on my tool are you convert it into percentage I can drag my formula down once I put in my dollar signs oops and then I just want to do a check again they should tally to 100% just to make sure that it's working correctly you can go back to my sum icon and I get a hundred percent I'm going to do a little color coding so that it's clear this is what I'm doing and now we've got some data analysis on the responses that we've received from our open-ended questions by creating these themes or categories we're now able to group these responses and identify some key areas of key buckets that people are interested in when we talk about then presenting the information we can use a table but we've talked about the fact that a chart is often more useful so I would like to do a chart for my proportion information so I highlight the data that I want to use in my chart I go to my chart tab and I ask Excel to insert a chart for me I click on column and it gives me a number of options I'm keeping it simple and it creates my chart I can move it around but it's in a location that's going to be a little bit out of the way scroll my spreadsheet up and then I want to format my chart to make sure that it has all the requirements I don't need a legend so I can go to my chart Layout tab to manage and format my chart I have the option here to click on the legend icon and indicate that I don't wish to have a legend if at some point I changed my mind and I would like to have a legend I would come back to this icon and indicates Excel where I would like the legend to show up so I want to put it back at the right I click on this and it shows back up here on the right or once I know I don't want it I can easily delete it just by clicking on it and clicking clicking on my delete button one of the three the three key things that we want to have in our chart is we want to have a chart title and we want to have access titles so our chart title in this case we click on the chart icon and I'll give us the title above the chart or we can get at the bottom of the chart above the chart is preferable so we give it a title then we want to give it access titles our vertical axis the rotated title is the best one to use it's the easiest to read what we want to do is identify what this axis is showing so its percentage of respondents we cannot say resent egde of students because we haven't spoken to all students this is the percentage of respondents we add the second axis title below the axis and we can call this one recommendations and then what we've done is we've done at least some basic formatting for our graph we have other options we can add data labels where we can add the value or the series name or the category name to the chart so if we wanted to be able to highlight the value very clearly we could click on value and the values would show up at the top of each column we could change the gridlines we can add or take away gridlines we could also add a data table to the graph but you want to think about what is the best way to show my data or to help someone interpret my graph and those are the changes that you would want to make so at this point now we're ready to copy and paste our chart into our PowerPoint presentation so I simply click on the chart edit and copy or you

Channel: Jacqueline C

Views: 245,165

Rating: 4.9032679 out of 5

Keywords: Data analysis, excel, open ended question, how to, questionnaire

Id: yWBXV651yd4

Length: 14min 2sec (842 seconds)

Published: Thu Mar 21 2013