Www.betterevaluation.org



This is Google's cache of?. It is a snapshot of the page as it appeared on 29 Mar 2015 12:59:51 GMT. The?current page?could have changed in the meantime.?Learn moreTip: To quickly find your search term on this page, press?Ctrl+F?or??-F?(Mac) and use the find bar.Text-only version?The Listening ResourceHomeServicesProgram EvaluationPolicy DevelopmentNeeds AssessmentCase StudiesBlogSusan EliotBlog HYPERLINK "" \o "Using Excel for Qualitative Data Analysis" Using Excel for Qualitative Data Analysisposted by?Susan Eliot?on?July 19,2011?in?Coding qualitative data,?Qualitative data analysis,?Qualitative researchI’ve been using an Excel spreadsheet to organize focus group and interview data for analysis for several years. It’s a nice compromise between the manual “cut-and-paste” method and commercial software like Atlas or NVivo.To be clear, no system—Atlas, NVivo, or Excel—can analyze the data for you, no matter how expensive or sophisticated it is. It takes a human brain to do that. But, unless you’re conducting a large, multiple investigator research study that produces a ton of data, Excel does a fine job of organizing textual data for analysis. In this article I describe a step-by-step process for doing that.AssumptionsFirst, the following assumptions are made about any qualitative study for which you plan to use this methodology:You used a rigorous data collection process.The question guide was structured and intentional.Data collection resulted in high quality plete, accurate transcripts were produced.Worksheet TemplateBefore you start entering data into Excel, your spreadsheet must be formatted in a way that eases data manipulation. Setting up the template properly will reduce frustrations often associated with trying to manage word data with Excel. I start by creating a single worksheet template for the first question, which I then duplicate for each additional interview question. This saves me from having to recreate the spreadsheet each time.My guidelines for developing the template are as follows:Set margins to “0” and page orientation to “landscape” to maximize space for data entry.Enter the title of the study at the top of the page and leave a blank line for the study question (remember, each question has its own worksheet).Create columns with headings for each of the following:PID (Participant identification number)CodeResponses (as wide a column as possible without flowing over to the next sheet)You can also create separate columns for face codes such as gender, age, occupation, location, etc. Attaching face codes to each response allows you to sort the data by those parameters. For example, you may want to contrast responses made by women versus those made by men. Or you might want to look at how responses vary by age. Of course, this presupposes that you have collected face code data on each participant (through a short pre-interview survey for example) and labeled it with the corresponding PID.Once you’ve created the template, make a copy of it (within the same workbook) for each question in the study. Don’t forget to insert one question at the top of each worksheet and label each tab at the bottom accordingly.Data EntryThe goal is to transfer all of the study data you’ve collected into the spreadsheets. Since my transcripts are in Word, I convert the document to table format (Table > Convert > Convert Text to Table) before I transfer the data to the Excel spreadsheet. Assuming there is a carriage return after each response on the transcript, each response will transfer over to a separate cell on the spreadsheet when you copy and paste.? Alternatively, I’ve also used voice recognition software to “speak” the responses into cells.As I transfer responses to the Excel spreadsheet, I simultaneously add the unique PID and face codes that go with each entry. Generally each response occupies one cell, but if the response is multivalent (has more than one meaning unit per response) I split it into separate cells and copy the PID and face codes for each split response.Because I like to see entire response entries on one screen, I use “Wrap Text” to keep long entries from running across the next several sheets of the spreadsheet. Wrapping responses keeps them neat and tidy for printing too. When I’ve completed the data entry I like to print out the data set and sit in a comfy spot with a nice cup of tea to begin reviewing it.Coding and CategorizingI start the categorization by jotting down category labels that come to mind as I read and re-read responses one question at a time. These category labels can be a word or, more often, a short phrase (often verbatim from the transcript). I use a large (4” X 6”) sticky pad that I attach to the corresponding printed spreadsheets to denote the? category labels as they come to mind.When I think I have a fairly comprehensive list of mutually exclusive category titles, I assign each a letter code (A, B, C, D, etc.) to make the categories easier to work with. This first pass through the data is only my first “best guess” of categories emerging from the data. I then use a pencil to assign one of these codes to each entry on the spreadsheets. On this first swipe at the data I always find entries that:Don’t fit in any categoryFit in more than one categoryAre a major subcategory of one of my categoriesGenerate a whole new categoryAre the only one of their kindI make adjustments in coding categories to accommodate my new insights–collapsing categories with others that may only have one response, teasing out? sub-categories or smaller categories from larger ones. It still might not be perfect the second or even third time through, but each time I move through the data, categories becomes less vague and a more precise fit with the data.Once I am satisfied that I have found an appropriate code for each response, I enter the codes into the “CODE” column on my electronic spreadsheet. Then I highlight the entire data set (make sure you include ALL columns and rows), click “Sort” (under “Data” in the top toolbar), choose the “CODE” column, and, voila, all of the data appears in separate little groups according to the codes just assigned.Now I can look at each group of responses and add, delete, change, subdivide, or collapse categories if necessary. I sort again and repeat the process as many times as necessary until I am satisfied that my categorization reflects the true essence of the data. I ask myself if this is what respondents were actually trying to collectively convey. Do their voices come through or have I imposed my own?An ExampleBelow is a simple analysis spreadsheet for a focus group study I conducted for the University of Colorado School of Dentistry regarding dental hygiene. I interviewed flossers and non-flossers in separate groups. Participants were asked who influenced their dental habits,? about flossing follow through, and how they felt about themselves when they flossed. This example includes a partial list of responses to question 4 (Who influenced you?). Notice the coding key that corresponds to the letter codes in the Code column. In this example I have not yet sorted the data by code.Making ComparisonsIf you faithfully entered face sheet and identification codes with each response, then Excel’s “Sort” function will enable you to make comparisons among subsets of your data. For example, if you entered the gender of each participant, then you will be able to compare responses of men to those of women for each question if you sort all of your data by the “gender” column. You will be able to do this for each of the face sheet codes entered. Like a kaleidoscope, the picture changes each time you re-sort.Below is a sample spreadsheet from a workplace inclusivity study I conducted for the city of Portland several years ago. During analysis I sorted data by the eight separate face codes (role, location, work group, etc.) you see in the example. (Remember, you must grab all columns and rows before hitting the Sort button).?Step-by-Step GuideYou can find a more detailed step-by-step guide on using Excel to organize and code data for analysis by?clicking here.I developed these guidelines to help others learn how to use Excel to make sense of the piles of qualitative data they generate in research projects, evaluation studies, and open-ended survey questions. I think you’ll find it useful for your project too.Happy coding!Click here?to view this post in Serbo-Croatian as translated by Vera DjuraskovicThank you for reading my blog. To subscribe, it’s easy. Just enter your email address below. As a subscriber you’ll receive a brief email letting you know every time I’ve posted something new.2Top of FormReceive new articles free in your inbox!Bottom of FormCategories:?Coding qualitative data,?Qualitative data analysis,?Qualitative research?Tags:? HYPERLINK "" analyzing qualitative data,?codingPopular PostsRelated PostsUsing Excel for Qualitative Data AnalysisCross-Cultural Qualitative InterviewingParaphrasing in Qualitative InterviewsSurvey or Focus Group: Which to Use WhenQualitative Data Visualization: An Interview With Dr. Stuart HendersonStories As An Evaluation ToolRecording Focus GroupsCoder Attributes13 CommentsMaggie MillerJuly 23,11Susan, I love this posting! You taught me to do this process about …8 (?) years ago and I still use it. I taught a modified version to my students at Regis University.I find it useful to actually write keywords in the code column. Sometimes I’ll add a 2nd column for sub-codes. For example, I interviewed 10 people on a team about their experience during the first year of a project. One of my codes was “1st Year” with the sub-code “We made it!” Another code was “1st Year” with the sub-code “Really hard!”When my numbers are small enough (e.g. I asked 10 people 7 questions) I’ll sometimes make just one spreadsheet and simply add a column to indicate Question #. That way, if people bring up a particular issue across several questions, I can structure the report by themes (i.e. codes) rather than by questions. For example, for one report, “technology was a challenge” showed up as a theme, and different people brought it up in their answers to different questions.One thing I like about this method is that it’s really easy to spot whether 7 people commented on a certain topic or whether one person commented 7 times in different ways.?As you said, getting Word text into Excel can be interesting. Sometimes simply hitting “enter” at the end of each meaning unit (I like that language by the way) in Word, then “converting text to table,” then pasting it into Excel makes things easier.I wish I could have seen your presentation in person. I would have wanted to hear more about Organizing vs. Analyzing, and also about the 5 kinds of codes. Maybe someday!OK, I think that’s all for now. Thank you so much for your blog!-MaggieSusan EliotJuly 25,11Great to hear from you Maggie! I’m so glad that the method is still useful to you in your continued evaluation work with nonprofits. Thanks for adding the additional suggestions. I especially like the one about using key words in the CODE column. I’m going to try it on my next analysis (coming up next month).Jane OsmondDecember 29,11Thanks for the clear directions on using Excel to analyze qualitative data. I have also used colors to categorize and code data in Excel. You can sort and filter data by font and cell color. Makes working with lots of text fun!Susan EliotDecember 29,11Thanks for sharing Jane. I didn’t know about the color and font sorting capabilities of Excel. That adds even more possibilities (and fun as you suggest)! Can’t wait to try it out.Kathleen LynchMarch 27,12I have recently been struggling with how to organize a series of textual reports to make data analysis easier. Your guidelines will be a tremendous help. Thank you.reggyJune 23,12I am struggling with qualitative data analysis and i would love to use this excel for qualitative data analysis method. Can please explain it further for me cause am strugling with understanding this method.Susan EliotJune 27,12Dear Reggy,I would be happy to work you with one-on-one. My rates for Qualitative Methods Coaching (QMC) are as follows:1 – 3 hours: $150/hr.4 – 19 hours: $125/hr.20+ hours: $100/hr.Nonprofit, student and previous client discounts are available. I also offer a free 15 minute upfront consultation.You can contact me at?susan@qualitative-I’d love to hear about your project and see if I can be of help.SusanMartinJuly 23,12Hello Susan,Do you have a publication with your recommendations?I’d like to quote part of your work in a paper in which I am working on.Many thanks,Regards,MartinSusan EliotJuly 24,12Hi Martin,I don’t have a publication in a peer-reviewed journal (though that’s my intention ultimately). Many people quote my blog posts though. Good luck with your paper.SusanZia ul HaqueFebruary 8,13Hi, I am from Bangladesh. in-depth interview, FGD, research protocol and data analysis are part of my job. Hope to learn a lot from your postingsZiaHajarFebruary 21,13Hi, I am Hajar from Morocco, I really would like to thank you, you really saved my life on this. wish you the best of luck with what you’re doing.ismailApril 17,13Hi,l also use excel for analysing my qualitative data. But I use msword to type the interview transcript or field note. Then I use Comment function to code any meaningful phrase. Then I transfer all phrases which have been commented or coded into excel spreadsheet. Both phrase and comment will be pasted into excel in one step only. Then, I move the comment or code to the second column.In excel, l used sort and filter function to manage code and category. With those functions, I can easily group data into its category.DaleDecember 7,14Hi Susan,I used excel to analyze data for my dissertation. However, when I received comments, it was suggested that I utilize a data analysis software such as Atlas or Nvivo. I liked using excel, but for now I have to give my committee what they want.Leave a ReplyTop of FormName?*Email?*WebsiteBottom of FormPost CategoriesActionable data?(2)Coding qualitative data?(2)cross-cultural listening?(2)Culturally sensitive focus groups?(5)Data interpretation?(1)Focus group guidelines?(6)Focus group methods?(3)Focus group participation?(3)Focus group questions?(2)Focus group recruitment?(1)Focus groups?(22)Fotonovela?(1)Group wisdom?(2)intercultural listening?(1)Interview guidelines?(1)Interview questions?(1)Interviews?(16)Lean Six Sigma?(1)Listening in qualitative research work?(17)Listening reverence?(7)Note-taking?(1)Paraphrasing?(1)Qualitative data analysis?(4)Qualitative data visualization?(1)Qualitative methods?(6)Qualitative research?(13)Qualitative sampling?(2)Qualitative vs. quantitative?(4)Recording interviews?(1)Recrding interviews?(1)Richard Krueger?(1)Stories?(5)Two-person interview?(1)Tagsactionable data?active listening? HYPERLINK "" \o "5 topics" analyzing qualitative data?coding?cross-cultural listening?engaging focus group participants?focus groups?hearing data?interviewing?lean six sigma?listening during interviews?Listening in qualitative research workmaking sense of focus group data?need to know about focus groups?Note-taking during interviews?note-taking in focus groups?oral history?paraphrasing?probing and prompting?Qualitative data analysis?qualitative interviewing?qualitative research?qualitative vs. quantitative?question development guidelines?recording focus groups?recruitment?reverent listening?Richard Krueger?social marketing?Stories?survey or focus group?using focus group data?using qualitative data644 SubscribersEmail marketing powered by MailChimp? 2005 - 2012 All Rights Reserved. Produced by?Leave a Message ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download