Using Microsoft Office in Qualitative Analysis
When you first see this list of instructions you are going to wonder if it is worth it. If you are coding a relatively small body of text (10 to 20 pages, say) it probably isn't. Rarely, though, is the corpus in qualitative research that small. It can easily grow to hundreds of pages. When that happens using electronic tools to organize your work can save hours in the long run.
Safety First
Start by developing rigid protocols for backing up everything you do and stick to them. Also save a pristine version of all of your data and don't touch that unless it is to make a copy on which you will work.
Your Computer and Microsoft Office
Some of the operations listed here are computing intensive. Depending on the age of your computer, the amount of memory you have, and the version of Office you are using, there may considerable delay times between executing a command and seeing the result appear on the screen. Rarely are these more than a few seconds but learn to be patient.
What is Here
Preparing Data in Word
Text Chunks to be Coded
Opening and Positioning a Second Window
Preparing a Drop Down Code List
Analysis (Second Level Coding)
This link is to a sample document that shows what the instructions look like in use.
Preparing Data in Word
1. Transcribe everything into Word. You can do this as one big file or multiple files (individual interviews perhaps) depending on how you like to work.
2. Start by reading all of your data. While you are doing that, look for errors—mostly things that Word has identified as spelling or grammar problems. Doing this kind of work is much easier in Word than it is in Excel. Saldanã and others promote that you read all of your data before coding anyway.
3. When you get ready to transfer data into Excel combine copies of all of your text files into one large Word file.
Text Chunks to be Coded
You are about to convert all of your data into codeable text chunks that will be transferred to Excel. You must make a decision about the unit of text you want to use. The easiest thing to do is convert everything into individual sentences but your coding strategies might dictate something else.
Paragraphs
1. Copy the entire document (select all/copy) and paste into a new Excel spreadsheet.
2. Excel will assume that you want each paragraph in its own cell.
Sentences
1. To convert each sentence into its own paragraph, open the Advanced Find and Replace dialog box (Edit/Find/Advanced Find and Replace).
2. Then click on the Replace tab.
3. Make sure No Formatting in the lower left is grayed out. If it is not, click on it.
4. In the Find what box type a period and a space. That is telling Word to look for the end of every sentence.
5. In the Replace with box type a period followed by ^p (that is the code for a return character).
6. Then click on Replace All. Word will then add a return character to the end of every sentence.
7. Repeat this process with a question mark and a space in case some sentences end with a question mark.
8. If you transcribed using exclamation points then you need to do that as well.
9. Once each sentence has been made its own paragraph select all/copy and then paste into a new Excel spreadsheet.
Text Chunks: Some researchers go through the data and identify text that stands out as something that might be coded later. This is a form of first level coding. Remember that Wolcott and others say that when you are beginning the only way to learn what you don't need to code is to code everything. So, you may choose not to select text chunks.
1. To extract these chunks and move them into Excel, first go through the data, highlight a chunk you want to move and change the font to some font that is relatively easy to read and is bold (i.e., Bauhaus).
2. Use this same font for every text block that you want to extract. It does not matter how long the block is.
3. Once you have gone through all of the data, save the document. Since these text blocks could be some distance apart in the document you want to be able to easily find them later. That is why you need to choose an easily identifiable font.
4. Go to Advanced Find and Replace. Be sure No Formatting is grayed out.
5. Leave the Find what box empty.
6. At the bottom select Format/Font.
7. When the font dialog box opens select the font you used to highlight the text chunks.
8. Close the font dialog box. In the Find dialog, toward the top, click on Highlight all items found in … and make sure it says Main Document in the drop down.
9. Then click on Find All.
10. Close the Find dialog box and every occurrence of your special font will be highlighted.
11. BEFORE YOU DO ANYTHING ELSE, select copy.
12. Then open a new Word page and paste. What will happen is that Word will assume that each copied text chunk is its own paragraph.
13. Save the new document and make all the text some more normal font.
14. Now, select all/copy and paste into a new Excel document.
[top]
Attaching Line Numbers
As part of analysis, text groups (paragraphs, sentences, or text chunks) will be sorted together and it may be difficult to trace back to the original sequence of the text. So, it is important to number the lines when they are first imported into Excel.
1. If the data were pasted into column A in Excel, Click on the A at the top of the column and select Insert Column from the Insert menu or from the ribbon.
2. Highlight cell A1 and insert the number 1.
3. Use the Fill/Series command to add sequential numbers all the way to the bottom of the text. You now have each paragraph numbered in sequence.
4. To permanently affix the number to the sentence, in cell C1 insert the formula
[ =concatenate(A1,". ",B1) ] (that is a period-space between the quote marks [remember, no spaces in an Excel formula unless it is inside of quote marks]).
5. Hit Return and you will see the first sentence preceded by the line number and a period space.
6. Highlight cell C1.
7. Shift click on the last cell in the C column. This should highlight the entire column.
8. Select Edit/Fill/Down.
9. Copy the selection and Paste Special/Values.
10. Save the document.
11. If you want you can delete the B column. Highlight the A column and hit delete. That will clear all the cells in the A column.
Note: If you have blank lines in the document, they will all get numbered with this process. Since the purpose of attaching line numbers is to be able to go back and easily find where a sentence has appeared in the original text, the numbered blank lines do not impede this process. For me, I like that the blank lines show where paragraph separations appear. If they really bother you, take them out in Word before you transfer the data to Excel. Search for ^p^p (two return characters in a row) and replace with ^p, and all the blank lines will be removed.
Spreadsheet Appearance
Some appearance adjustments will make reading and coding faster and easier.
Text Alignment: Select the column that has the data in it and go to format/cells/alignment and click on wrap text. Then at the top of the spreadsheet roll the cursor over the vertical line between the column with the data and the column to the right until the cursor turns into a vertical line with arrows pointing left and right. Click and drag to adjust the column width to your preference. When you resize, large empty spaces may appear in the cells. To make the text fit neatly in the cell first select all, then select Format/Rows/AutoFit.
Text Size: Use the zoom adjustment to make the text larger or smaller (view/zoom or the zoom slider in the bottom right corner of the window). I find this more useful than changing the font size because you can easily adjust to fit your screen as more windows are opened.
Font: Select the entire spreadsheet (select all or click in the box above the row numbers) and select a font which you are comfortable reading on the screen.
Coding Position: When using Excel to code data, codes are placed in cells adjacent to the text. You can do this either to the left of the text or to the right. If you like to code left, make sure that there is a blank column to the left of the text column.
Column Labels: Make sure the top row (Row 1) has the name for each column. At first it will seem obvious that there is a column for coding and a column for text but this will get more complicated later. I would suggest using the coding strategy as the column name (i.e., Versus or In vivo). Usually you will do more than one set of first level codes, so each pass through the data will have an accompanying column label with the coding strategy you used.
Grid Lines: Since you will be reading a lot of text on the screen, it will make you less tired to remove any unnecessary marks on the screen. To get rid of the grid lines go to either the Page Layout or the View tab and uncheck Gridlines.
[top]
Code Sheets
Open a second tab in the spreadsheet. You could put the codes in a separate document but I like that everything I am doing during coding is in the same document. There are two purposes for code sheets. First, you will list every code that you use. Do this in column A. In row one at the top of column A write something that is short and graphically obvious like xxCODESxx.
(Don’t use --Codes--. A double hyphen is a reserved code in Excel and strange things will happen if you use it.) Whatever you write will show up in the coding column on the Data tab.
Second, code sheets serve as a place to record memos about the codes. The most obvious memo is a description of what you mean by the code. Often this description changes as you work so it is nice to have the description readily available. Place memos adjacent to the codes in the next column. You can write whatever you want in this sheet to keep track of your thoughts as you code but do not write anything in column A except the codes.
[top]
Opening and Positioning a Second Window
You will need to have the Data tab and the Code Sheet tab open at the same time. With your spreadsheet already open, select Window/New Window. A copy of the spreadsheet appears. Open the Data tab in one copy and the Code Sheet tab in the other. You will want quick access to the code sheet so I usually position it on the left of the screen with only column A showing (the column that will have the codes in it). Then the window with data is to the right.
On your screen you need to be able to simultaneously see the Code Sheet tab code column, the Data tab coding column, and the data. Reposition windows and adjust the zoom to make everything comfortably viewable.
[top]
Preparing a Drop Down Code List
Once you have decided to use a specific code, you will need to insert this code verbatim every time you want to use it. This will make sorting easier when you are doing second level coding. In order to do this, all of the cells in the coding column (probably column A on the Data tab) need to be converted so that by clicking on a cell it presents a list of all available codes from which you can select the code you want.
1. Start by being sure that something is in the first row of the codes column of the Code Sheet tab.
2. Next click on the cell into which you would put a code on the Data tab.
3. From the Data tab on the ribbon, select Data Validation from the Data Validation drop down menu.
4. From the Allow drop down menu select List.
5. The window changes and now includes a Source input box. Click in the box so that the cursor is there and then bring the Coding Sheet tab window to the front.
6. Click once on cell that has your pre-inserted text at the top of column A.
7. Imagine how many codes you will potentially use. If you are using more than a couple of hundred that is probably too many. Scroll down to the row that represents how many codes you think you will use (row 200?) and Shift-click on the A column cell in that row. You have now highlight cells A1 through A200. Those cell references should now be in the source box of the validation window (=Coding Sheet!$A$1:$A$200).
8. In the validation window be sure that both Ignore Blank and In-Cell Drop down are checked.
9. Click on OK.
Now, in that cell that you have applied validation, every time you click on the cell it will show a drop down list of everything that is in the A column of the code sheet tab.
Click on the cell and select the column heading text that you typed in previously (xxxCodesxxx or whatever you chose). That text is now inserted in the cell on the Data tab.
Select the codes column and select Edit/Fill/Down. Every cell in that column should now have the same text showing and every cell will display a drop down menu if you click on it.
[top]
Coding
Start reading your data.
1. When you decide you want to enter a code, go to the Code Sheet tab and enter the code in column A.
2. Add notes about the code if you want in column B.
3. Go back to the Data tab and select your new code from the drop down list in the coding column.
4. Keep reading. The next time you want to enter a code, either click on the drop down list and select an existing code or go back to the Code Sheet tab and enter the new code in column A.
5. Then return to the Data tab and click on the code cell and select the new code.
Note: When you start, this will seem a bit laborious. After you begin to accumulate a number of codes in the list, coding will proceed refreshingly quickly.
[top]
Using the Code List
When you get lots of codes, the order of the codes makes coding faster. You might wish to order them alphabetically in the drop down list or you might want to organize them by type (a form of pre-second level coding). Go to the Code Sheet tab and re-sort the codes however you want. Each code cell drop down list will automatically be updated.
Sometimes when you are coding you will change your mind about a code after you have coded more. At any time you can go back to a code cell and change the code. You will have to go back and reselect the code in the cells where you have adjusted the code.
The most noticeable irritation with this process is that the validation drop down menu only shows 12 items in the window. So, you have to scroll to see the rest. There is no scroll bar until you roll the cursor over the drop down list and start to scroll. Click and drag on the list to move up or down. There are ways to increase the number of items showing on the list but they are complicated and did not seem worth the time to me.
Adding a Second Set of Codes: Saldanã tells us that it is usually better to go through the entire corpus using a single coding strategy and then go back and do it again if you want to use a second strategy. To use second coding, go through the same process. On the Code Sheet tab identify the cells in column A into which you will put the new codes—cell A201 to cell A400 maybe. I prefer to put the second set of codes below the first but you could put them in a new column to the right of the memos you have written about the first set of codes. On the Data tab add a second coding column. Validate the second coding column cells on the Data tab to the cells on the Code Sheet tab that will have the second set of codes following the same procedure as above. You will have to readjust the windows on the screen to see everything you need.
[top]
Analysis (Second Level Coding)
1. On the Data tab highlight row one.
2. Find the filter tool—the funnel (it appears on a number of ribbons) and click on it. On the right of each cell in row one a drop down arrow appears.
3. Go to the coding column and click on the down arrow. A list of each term in the column appears.
4. Click on Select All (which is already selected) to deselect all.
5. To view the text related a specific code, select the specific code. In the spreadsheet everything disappears except the rows that have the selected code.
6. To extract those rows, highlight all the rows, copy, and then paste them in a new tab or workbook. Save.
The power of this strategy is that the filter tool allows selecting multiple codes simultaneously. If you are thinking of compressing multiple codes into a single category, select all of the codes from the filter list and then copy all of the results into a new workbook.
Note that you can filter two or more columns simultaneously. Select the filters from each column and the results represent the mix of codes from each of the coding sessions.
Note also that when filter results are copied into a new workbook or tab, the line numbers from the original text are also copied so it is easy to check the context from which the text was extracted.
[top]