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

[@Seftehandle]

Very useful, short, precise and gives the answers to qualitative analysis right away without a person having to go through all the literature. Amazing!

[@maheenzafar]

girl you saved my life istg... This was the most easiest way someone has explained bar charts. Keep going.You're doing amazing

[@jacquelineclarke]

Thank you! Will do!

[@manoizere5489]

Didn't know it was this easy to analyse open ended questions on excel. Now i can explain better in my analysis. Thanks for this excellent video

[@bgabbidon6647]

I cannot thank you enough. I pray that my teaching will improve to reflect the clarity, patience, care. I learnt the outcomes and much more. You are an amazing teacher. Best wishes.

[@jacquelineclarke]

You are so welcome

[@patriciapeters7071]

Part 1 and Part of your videos are absolutely fantastic. I have been searching over the last couple of days for SIMPLE easy ways to teach my students to analyse survey responses. I am so, so glad for your 2 videos.

[@jacquelineclarke]

Glad it was helpful!

[@bikatblk1112]

She made it so easy unlike other videos

[@shafaqkhalid4563]

OMGGG... Jacqueline; you just made my life easy... amazingggggggggggggggg girl... that's so easy and without any technical software or so.. thanks a bunch

[@jacquelineclarke]

You're welcome! Glad it was useful!

[@cleaneat_shealthyrecipes5701]

Thank you. I barely finished watching the video and I knew how to start my analysis. this really helped me.

[@sawanabhatta]

Very useful, easy and quick process to analyse open-ended questions, even for beginners in Excel. I am really thankful to this video because I am a beginner in Excel and doing calculation for the first time. This video helped me in analysing datas of the researh questions in short time. Please keep uploading other videos regarding data analysis. Thank you! :)

[@jacquelineclarke]

1. The tutorials are part of a 3rd-year course on research concepts in a 4-year degree program. 2. Since we are looking for themes related to how the students feel rather than what action will be required, my preference would be to focus on themes that capture student feelings rather than actions that could be taken to address shortcomings. 3. The main determining factor when deciding on whether to include an open-ended question is: will the responses help to address the purpose/goal of the survey? A secondary determining factor is: does the researcher have the resources to analyze the results? For a survey with thousands of responses, the surveyor could use qualitative research software for the data analysis. Several options are available.

[@chadiatie]

Thanks, very useful! But what if you have 1,000 rows to analyze? @Jacqueline C

[@jacquelineclarke]

I would use a qualitative research software application rather than Excel.

[@GusnawatyAnwar]

@@jacquelineclarke what is the name of the software? I hope you have time to make tutorial. Thanks in advance

[@norzedaangagao5728]

Hi.. can i ask what is the exact term for this technique?? Is it content analysis but quantitative.

[@jacquelineclarke]

I haven't had to use qualitative software for the course I teach. Here's a possible place to start to find one that would work for you blog.hubspot.com/service/qualitative-data-analysis-software

[@ekayustina]

Thanks Jacqueline this is giving so much help to my thesis. I was searching for here and there to find the easier way to count the open-ended questionnaire as the students gave various responses to my questionnaire. This video is easy to understand :)

[@jacquelineclarke]

I'm glad to hear that the video is useful. Good luck on your thesis!

[@penelopemaclellanhawkes7822]

Dear Jacqueline, Thank you so much for sharing your class course work on u-tube! I am writing up the results section of my dissertation, I analysed the questionnaire data through SPSS which was fine as we had been taught how to use this as part of our course. However, I had two open-ended questions which also needed analysed, I down loaded Nvivo on to my computer and spent a couple hours looking at the tutorials and lost the will to live! I decided to search on Google "how to analyse open-ended questions" and to my great relieve your tutorial came up 5th or 6th on the first page! Excellent tutorial, at just the right speed to take it in, no fuss, straight forward and a lovely accent/voice to go with it! Thank you so much! My tutor loves my graphes! :)

[@jacquelineclarke]

Penelope MacLellan Hawkes Great to hear that the video was helpful. Thank you. Good luck with your dissertation!

[@nathanmcmullen1527]

Been searching for a tutorial like this for days. Thank you.

[@jacquelineclarke]

Glad I could help!

[@ssyoutubechannel86]

It is very very useful for me. You saved my terrible life. Thank you so much

[@paulciddelprado6061]

Extremely helpful, easy to understand and precise. Thank you very much!

[@usmankiupk]

Great... easy to understand for a common human

[@aurasbhattarai77]

Very nicely presented, awesome! Thanks Jackqueline.

[@mriffatt5]

Extremely helpful, easy to understand. Thank you so much!

[@jacquelineclarke]

Glad it was helpful!

[@sylamseto]

This tutorial is so good. I am lucky to find this one!

[@jacquelineclarke]

Glad it was helpful!

[@ssgstatswithexcel4966]

Excellent Madam. I loved it..............

[@jacquelineclarke]

Thank you!

[@oj6906]

Thank you for this video! I've used it as a reference for grad school when doing my descriptive analysis and for my job. Excellent video!!

[@jacquelineclarke]

Glad it was helpful!

[@johng5295]

Thanks in a million. Great content. Awesome. Very well explained. I couldn't find this explanation--simply put anywhere else. Great teachers are hard to find. Grade: A++💥

[@jacquelineclarke]

Glad it was helpful!

[@jacobzornu]

Thanks for clarifying my thoughts. Because I work with large qualitative data from open-ended questions. Most of the time, the responses are more than the number of participants so I have been wondering if the results will be accepted if the frequency of responses is more than the reported number of participants. That does not matter after watching the video once the total percentages is 100!

[@jacobzornu]

My question then is, do you then say percentage of responses or percentage of respondents knowing the counts (or responses) are more than the participants? In part 1, I see it more of percentage of respondents and part two, percentage of responses. Please clarify

[@jacquelineclarke]

Jacob, if there is question for which you have multiple responses from participants, then I would refer to the "responses" when reporting the results of that question. For a question in which each participant has only one response, I would refer to "respondents" when reporting the results of that question.

[@jacobzornu]

@@jacquelineclarke Many thanks! If you use responses where there are multiple responses, is it okay if you have total responses more than the number of participants in the study? I get a bit uncomfortable seeing responses more than the participants even though the percentage is 100 anyway.

[@jacquelineclarke]

Yes, it's ok. But when you are reporting the findings in a situation in which there are more responses than respondents then you definitely want to refer to "responses" rather than participants. Also, when reporting my findings, I would usually avoid referring to the number of responses or participants since this is not as useful as reporting on percentage of responses or percentage of participants. If you use the number, e.g. 15 respondents said xxxx, it's harder for your reader to interpret your findings because they have to constantly remember what the total number of respondents to your survey was.

[@virginiatoupinkunze8521]

Excellent presentation and extremely useful - Many Thanks!

[@danielnarsoo6014]

Very useful, short and precise video

[@奥图图]

So detailed! Very useful!! Thank you so much!!

[@jacquelineclarke]

Glad it was helpful!

[@liliadali6734]

Thank you sooooo much Jacqueline for this video. It really helped me to correctly interprete open-ended questions for a questionnaire I gave to my students some time ago. God bless you ^_^

[@jacquelineclarke]

You're welcome. So glad you found it useful

[@elizabethmatemba9020]

Thank you for sharing this important video,its real helpfull

[@apoorvasingh2808]

Wow really helpful 👍 thanks a lot mam ☺️

[@jacquelineclarke]

Most welcome 😊

[@swemakamputa6523]

this is so helpful, thanks Jacqueline

[@kaseybutternut]

Thank you. It's my first time doing analysis of open-ended/codable fields. This is rough when you have hundreds of responses to go through as you have to assess yourself. You think there may be some way to use the find and replace function and the wildcards *?~ to speed up coding the data like you did?

[@jacquelineclarke]

+kaseybutternut Possibly. I haven't tried that approach. Here's a link to using wildcards with the IF function that may be helpful. answers.microsoft.com/en-us/office/forum/officeversion_other-excel/wild-card-characters-in-if-formula/8d882e94-7c44-436e-86d7-8748cd398ff8?auth=1

[@calistusmbachu6252]

That was comprehensible. Good job!

[@hatranthu5255]

Very helpful and easy to understand, thank you!

[@jacquelineclarke]

Glad it was helpful!

[@simwayidanny5108]

Thanks for the video.... now i can analyze my data

[@funnyclips2696]

Hmmm, so simplified and interesting!!! Thanks 👍🏾

[@jacquelineclarke]

Glad you liked it!

[@louieallen4567]

I love this video - thanks for posting. I just didn't understand where you got the 111 number and the 16 number from when you were working out the percentage of the Entertainment theme.

[@jacquelineclarke]

Those aren't numbers. They are the cell addresses - i16 and i10. Instead of typing in the numbers, I am using the cell address to create the formula. This means that if the content in the source cell changes (i16 or i10), the result (percentage in this case) is automatically updated. Here's some info from MS that may be helpful support.microsoft.com/en-us/office/use-cell-references-in-a-formula-fe137a0d-1c39-4d6e-a9e0-e5ca61fcba03

[@louieallen4567]

@@jacquelineclarke Thanks very much for the explanation. Much appreciated.

[@tanisha5964]

This was extremely helpful, thank you

[@TamyraNH]

This is great and your explanation is very clear and concise. Does this analysis tool approach have a name?

[@jacquelineclarke]

This is descriptive analysis

[@nmirriaml6247]

Thanks. This was very helpful

[@jacquelineclarke]

I'm glad it was helpful!

[@internationaleducationalre5476]

Thank you excellent presentation !!!

[@jacquelineclarke]

Glad you liked it!

[@fatimaamponsahfordjour4008]

You do all Jacqueline, you saved me

[@vovo_vw8218]

save the day. awesome presentation. tks a lot.

[@jacquelineclarke]

Glad it helped!

[@mercyukoko2415]

Very helpful. Thank you.

[@jacquelineclarke]

You're welcome!

[@eharp6537]

Thanks so much..easy to follow and precise!

[@businessleadershipandmanag522]

Hi Jacqueline, could you please share with me the other parts. Thank you very much for the lecture.

[@jacquelineclarke]

Here you go kzbin.info/www/bejne/gJDamat_i8-Bn7s&lc=Ugyow5pR9iG3m8h877B4AaABAg

[@delphiolee]

Thank you very much! This has helped me a lot.

[@hamdimohamed9023]

It's very helpful but please zoom little so we can see what you write in details thank you

[@jacquelineclarke]

Thank you for the feedback, Hamdi. The video zooms in from the 2:50 min mark and onwards. If you're able to see this view clearly, you may wish to change the video settings to 720p (if not already selected) - click on the gear at the bottom of the screen, choose Quality, select 720p.

[@marwaryan14saifan45]

You helped me a lot.Thanks

[@jacquelineclarke]

Happy to help

[@LaVida2]

this was sooooooooo helpful. thanks!

[@Enigmo1]

Question: are these tutorials based on Data Analysis as a course for a specific qualification? Or just the concept in general?

[@maretztjitemisa6062]

Awesome, preciate this much....finally done with my dissertation. Thanks tikhe

[@lisaderepentigny7147]

never mind i read the previous responses thank u

[@carmenyelumalai3965]

This has been extremely helpful thank you !!

[@Enigmo1]

Also, I feel as though "bigger student lounge" and "more computer labs" could have been in their own category to do with changes to building/construction

[@lyndaelladius2338]

Very helpful Great thanks🤗

[@jacquelineclarke]

My pleasure 😊

[@priyankakoch6451]

Thank you...can you please suggest about how to do analysis of an inventory in excel?

[@jacquelineclarke]

I haven't used excel for this purpose. Here's a website that might be helpful! chandoo.org/wp/abc-inventory-analysis-using-excel/

[@siviwendokweni7954]

Can you please upload a video on how did you handle question 3

[@soniairam]

Very helpful, thanks

[@jacquelineclarke]

Glad it was helpful!

[@AnthonyRamtulla]

Very good tutorial

[@amrayabaptiste2933]

Thanks a lot! This would greatly help me for my assignment.

[@mdmehedihasan3664]

very helpful. thanks

[@zhipingpeng6480]

Thank you! It's very useful.

[@zacharywickline8382]

Great video! How do you find frequency and proportion if there are multiple codes per response? For example if one response involved live music and transportation the codes would be "e" and "t". How do you deal with this?

[@jacquelineclarke]

Good question. The numbering system refers to the questionnaires rather than the responses and is used for back checking purposes, i.e. if for some reason you need to check that the responses have been entered correctly, you can go back and check the entered response against the appropriate questionnaire (each questionnaire should be numbered). But the numbers are not relevant for calculating frequency and proportion. In a case where there are two responses on a questionnaire, insert a row under the appropriate questionnaire number and enter the additional response (so you know which questionnaire the response is related to). Tally the total number of responses - in this case it would be 16 instead of 15. Report the proportion as a percentage of the total number of responses. To check that the totals are correct, I would add a tally of the number of responses in Column D.

[@jjones70998]

Thanks for this. What if you have multiple codes per response? e.g. student wants a lounge with food services - code would be both F and E. Thanks

[@jacquelineclarke]

Part 2 of the tutorial shows how to address multiple response. kzbin.info/www/bejne/gJDamat_i8-Bn7s

[@jcroulet]

This is amazing. Thank you!

[@NoniAgustinaMPd]

Jacqueline thanks a lot for your fabulous explanation. Can you help me how to analyze the data if one respondent gives more than one responses?

[@jacquelineclarke]

+Noni Agustina It depends on your research study. One option would be to "break" the respondent's answer and code each part of the answer to the theme that it applies to. You would then count each "break" as a separate response. This is the option I would choose - I think it is more important to capture the actual responses rather than the number of responses (how many people responded to the survey). For this option, insert a row under the appropriate questionnaire number and enter the additional response (so you know which questionnaire the response is related to). Tally the total number of responses - in this case it would be 16 instead of 15. Report the proportion as a percentage of the total number of responses. To check that the totals are correct, I would add a tally of the number of responses in Column D. If it is more important to your research study that you report your data based on the number of responses (questionnaires), then you have to choose which part of the answer you want to report. You could choose to use the first part of the response using the logic that this seems to be the most important aspect for the responder since this is the first thing he/she wrote.

[@NoniAgustinaMPd]

+Jacqueline Clarke Jacqueline would you give me your email address? I wanna show you my data related to analyze multiple answers. Maybe I gonna catch up your explanation easily based on my data. Thanks a lot Jacqueline

[@manoizere5489]

+Jacqueline Clarke Can you please make a video to describe the process? I'm sure it will be a hit as they are no videos that explains such process on excel. All we can see are that of SPSS. Please make a video and develop a chart. some of us are quicker learning the practical bits than reading and applying the bits. Thanks

[@jacquelineclarke]

+Mano Izere Thanks for the suggestion. I will make a new video to explain how to address multiple responses as soon as I can

[@JustForTheRecordd]

+Jacqueline Clarke Hi, thanks for this video it was very helpful! I wanted to ask if you will be making a tutorial on multiple responses soon at all? many thanks

[@gigiyo78]

Thank you for showing this, I have been looking for it! Unfortunately, I have a large number of responses so placing a code on each response would take a lot of time, do you have a tip for it?

[@jacquelineclarke]

Using Excel's verbatim feature may be helpful. Here's some tips on how to use this function measuringu.com/code-verbatim/

[@divineasem6197]

Great tutorials

[@jacquelineclarke]

Glad you like them!

[@raindropsofsky]

Can I also make it in Excel 2016? Every version should be serve as convenient for data analyssisss.

[@jacquelineclarke]

Yes, you can also do these steps in Excel 2016

[@rabiafazalelahi8721]

Thanks its helpful for me

[@jacquelineclarke]

Glad to hear that

[@rovan8320]

interesting presentation

[@jacquelineclarke]

Glad you liked it

[@h3ll022]

Hi Jacqueline, kindly advise what to do in the situation the open ended question has a long answer touching multiple themes? what is the optimal way to present that information?

[@jacquelineclarke]

+h3ll022 It depends on your research study. I would "break" the answer and apply each part of the answer to theme that it applies to. I would then count each "break" as a separate response. In this case, it is more important to me to capture the actual responses rather than the number of responses (how many people responded to the survey). If it is more important to your research study that you capture the number of responses, then you have to choose which theme to apply the answer to. You could only use the first part of the response using the logic that this seems to be the most important aspect for the responder since this is the first thing he/she wrote.

[@h3ll022]

+Jacqueline Clarke tq!!!

[@arpitaghosh730]

thanks a lot for uploading this video!!

[@nathanartificio9254]

Hello, what exactly do you call this technique or approach? Thank you!

[@jacquelineclarke]

Apologies for the delayed reply, Nathan! This is called descriptive analysis www.analyticssteps.com/blogs/overview-descriptive-analysis

[@nathanartificio9254]

@@jacquelineclarke Thank you so much!!

[@eleanorcarson4411]

I understand that this is a descriptive analysis of qualitative data. Is there a book we can refer to when describing the method that you show in this video? Many thanks for the video - it makes analysis easier to understand and do!

[@jacquelineclarke]

E Carson, most research textbooks will explain how to analyze qualitative research (look for themes, etc.). I am not aware of a textbook that walks you through the steps for using Excel to analyze qualitative research.

[@eleanorcarson4411]

Thank you for taking time to answer. I have 2 follow-up questions: (1) Who would you recommend as closest to your methodology: Glaser & Strauss (The Discovery of Grounded Theory, 1967), Strauss & Corbin (Basics of Qualitative Research, 1990), or Creswell, Qualitative Enquiry & Research (2nd ed), 2007, or someone else? Keeping in mind that they did not show how to do this in Excel! (2) Is it ok to cite your video? If so, what citation would be best? Thanks!

[@jacquelineclarke]

I'm not familiar with those sources so can't say which is the closest. Yes, you can cite the video. www.easybib.com/guides/citation-guides/apa-format/youtube-video/

[@Ready120100]

Just to add my two cents. It appears Glaser & Strauss is closest in terms of the methodology. This excel method allows the researcher to move from data to theory so new theories or themes as it were can emerge which primarily is the basis for their grounded theory argument. Themes are grounded in the theories they emerged from.

[@dumisanichitaukali929]

Very helpful.. but I have a question.. is there any statistical way of coming up with a threshold to say that this response is significant enough to improve campus life ?? I don't know if my question makes sense though

[@jacquelineclarke]

I'm not sure whether you're asking about statistical significance (can the results be trusted) or whether you asking how to determine at what level should action be taken at. If it's the former, yes there are ways to figure this out (an online search will give you lots of resources). If you're asking about the latter, I think that would be up to what you're trying to accomplish with your survey (purpose) which would make it more of a subjective decision (made by the researcher and/or the decision-maker that they survey was done for).

[@dani28MSc]

Hello there Jacqueline. Got a question for you: Is there to do the whole questions in one only sheet, instead of one question per sheet? You are a START! Thanks for posting this, as I think you are being super useful in my masters degree on data analytics, as I need to do a final results on opened questions and I am taking your full advise, as you are doing it on your tutorial. I have 28 questions to do, and doing one by one, I think it is a killer task, and I was wondering if there is anything that could make it all in one go... Let me know, and thanks a million for posting this !!! Dan xx (I have also subscribed)

[@jacquelineclarke]

Hello Dan, Sorry for the delay in getting back to you. You could do more than one question on a worksheet. I just find it easier to navigate/find if I do one question per worksheet. Once you start writing up your results, you want to be able to go back forth between questions as quickly as possible.

[@dani28MSc]

@@jacquelineclarke Thanks Jacqueline for your reply. Don't worry for the delay... Things happen.... I hope I can manage when I reach my 3r question on the master's. Many thanks for posting that!! Dan xx

[@regeilangeliquebaldebrin5385]

Hi. I have a question though. What if there were answers that belong to two (or more) themes? Example, one student answered "more parking and free food". Should I just choose to put it on one theme or should I make another theme that perhaps says "Food & Transportation". Thank you!!

[@jacquelineclarke]

Hi Regeil, see Part 2 for the answer to your question: kzbin.info/www/bejne/gJDamat_i8-Bn7s&lc=Ugyow5pR9iG3m8h877B4AaABAg

[@lyndseyshannon6038]

Amazing. Thank you

[@jacquelineclarke]

You're welcome, Lyndsey!

[@maramezina1094]

very useful thank you so much

[@jacquelineclarke]

You are most welcome

[@Enigmo1]

Sorry for three comments in a row, but also, what if the survey had brought in thousands of responses to an open-ended question and there isn't time to go through all of them and categorise them? Or is it that an open-ended question wouldn't be included in a survey intended to bring in a high number of responses?

[@folasaderichards6787]

Very informative

[@koladeigbasan4697]

Thanks a Million!

[@saquibniaz]

Hi Jacqueline Thank you for this video. Its really helpful while interpreting data having open-ended questionnaires. I've writing theses for my degree in sociology and want to use this method but I'm required to cite ' the method, I'm using with regards to data analysis'. Is this your own method of analysis data or you adopted from some else where. If this is yours, then please help me cite this in my research or if it isn't then mention the source so that I could cite. Await your response! Thank you

[@jacquelineclarke]

Hi Saquib, the statistical method is referred to as Descriptive Analysis. I think the closest to data analysis method may be grounded theory: www.sxf.uevora.pt/wp-content/uploads/2013/03/Glaser_1967.pdf

[@akshayas5617]

How to do coding if I get two different responses from same person ?

[@jacquelineclarke]

Please see Part 2 of the tutorial kzbin.info/www/bejne/gJDamat_i8-Bn7s

[@DuyPhan-oq9vp]

Hi Jacqueline! Would you mind giving your full name and date of publication of this video so that people can cite your video properly, namely APA format? Thanks a lot.

[@jacquelineclarke]

Apologies for the delayed reply, Duy. The video was published on Mar 21, 2013. I would prefer not to provide my full name

[@DuyPhan-oq9vp]

@@jacquelineclarke Thank you so much!

[@norzedaangagao5728]

Hi, can ask what is the exact term of this technique??

[@jacquelineclarke]

This is referred to as descriptive analysis

[@GeefeAlba]

thanks so much! great help

[@gail297]

This was really helpful. Thank you.

[@maramezina1094]

if I have many open ended questions,can I analyse them separately?

[@jacquelineclarke]

Yes. I would create a new tab in my spreadsheet for each question, enter the data and then do the analysis. But this can be very time-consuming. Another option would be to use software that's designed specifically for open-ended question analysis. Or, avoid using open-ended questions unless absolutely necessary. Here's some guidance on when to use open-ended questions www.questback.com/blog/7-reasons-to-use-open-ended-survey-questions/

[@mohamedyasin2924]

thank u very much. please teacher we need other tutorial if there is more than one responses .

[@jacquelineclarke]

Mohammed, here is the video for more than one response. kzbin.info/www/bejne/gJDamat_i8-Bn7s

[@mohamedyasin2924]

thanks teacher. It is really help full.

[@lowkey-23]

Awesome! ^^ Thank You for this Video

[@shaimeezkhan2570]

can we use this method for yes or no question analysis and call it a descriptive method???? Please reply i'm stuck w my report😷

[@jacquelineclarke]

Yes, you can.

[@yaseenkhalil7902]

What if the same respondent had more than one response (mention two or more points)?

[@jacquelineclarke]

Part 2 of the tutorial shows how to address multiple response. kzbin.info/www/bejne/gJDamat_i8-Bn7s

[@carolward1142]

brilliant tutorial thank you (maybe avoid wearing ?bracelet as noise picked up by microphone) :)

[@jacquelineclarke]

Thanks for the suggestion :)

[@krutikavispute509]

Well, grateful!

[@ellypedersen7962]

but what do you do if one comment includes more categories?

[@jacquelineclarke]

@elly, the Part 2 video addresses this kzbin.info/www/bejne/gJDamat_i8-Bn7s&lc=UgzZaWvkJ1aF3am2kPR4AaABAg

[@drnitinjain]

thanks a million,

[@jacquelineclarke]

You're welcome! Glad it was useful!

[@HeavenWarner]

Thank you so much!

[@lisaderepentigny7147]

what if the respondents have more than one answer?