Using Microsoft Office in Qualitative Analysis

 

Adding Line Numbers in Word Documents

As you are extracting ideas from a document, line number references will help you get back to the original context of the idea quickly.

Mac—Format/Document/Layout/Line Numbers

PC—Page Layout tab, then look for the Line Numbering icon (left of center).

If you use this strategy your line numbers will change if you edit the document. This can be a problem if you have noted line number references and try to return to them later. A work around for this described below under Adding Numbers at the Beginning of Lines.

 

Structural Coding in Word

The idea is to be able to code text quickly and then to put items together that have the same codes.

1. Make a copy of the original document and store the original somewhere.
2. Start reading the document. If you fine something that you want to code put a number at the beginning of the paragraph. You can use any symbol you want (it doesn't have to be a number) as long as the symbols aren't any character with which any paragraph in your document would start.
3. On another sheet write down the number and a few words that describe what you think might be the theme you have identified.
4. Find the next item of interest. If it is another example of the same thing put the same number at the beginning of the paragraph. If it is different then code it with a new number.
5. On your other sheet note the number and a few words that describe the theme.
6. Keep doing this until the paragraphs that contain items of interest have numbers at the beginning.
7. Select the entire document. Then go to the Table menu or tab and select sort. This will resort all of the paragraphs in the document alpha/numerically, placing all the paragraphs that start with a given number together.

To do this in a more refined way, when you fine a item of interest inside of a paragraph but don’t want the rest of the paragraph to be selected, place the cursor in front of the item of interest, hit the return key and then type the appropriate number. Go to the end of the item and insert another return. The item will now be its own numbered paragraph selected out of the original paragraph.

 

Descriptive Coding in Word

This strategy works best for descriptive coding but it can be applied to other strategies as well.

1. Highlight the entire document.
2. Select Table/Convert/Convert Text to Table. This makes every paragraph into its own table cell.
3. Select the table column that has the text in it and select Table/Insert/Columns to the Right.
4. Adjust the column widths to your liking.
5. Codes can be inserted into the cells in the blank column. You may wish to insert multiple columns for additional codes or for memos.
6. You may wish to turn every sentence into a cell. In order to do this before selecting Table/Convert/Convert Text to Table, place the cursor at the beginning of the document and select Edit/Find/Replace. Type a period-space into the find box. Type a ^p into the replace box and hit Return. This will turn each sentence into its own paragraph. Now go back to step 2.

Hint: Change the page orientation to horizontal especially if you are using more than 2 columns.

Adding Fixed Numbers at the Beginning of Lines

In order to be able to move text around and still be able to know its original position in the document the easiest thing to do is to append a number to the beginning of every sentence.

1. As in Descriptive Coding in Word, start by turning every sentence into its own paragraph. Place the cursor at the beginning of the document and select Edit/Find/Replace. Type a period-space into the find box. Type a ^p into the replace box and hit Return. This will turn each sentence into its own paragraph.
2. Select All to highlight the entire document and Copy
3. Open a new spreadsheet in Excel.
4. Highlight cell B1 and Paste.
5. Highlight cell A1 and insert the number 1.
6. Use the Fill command to add sequential numbers all the way to the bottom of the text. You now have each paragraph numbered in sequence.
7. 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).
8. Hit Return
9. Highlight cell C1.
10. Shift click on the last cell in the C column. This should highlight the entire column.
11. Select Edit/Fill/Down.
12. Copy the selection and Paste Special/Values.
13. Save the document.
14. If you want you can delete the B column. Save the A column to add numbers to the codes if you want.

You may find the text easier to read using with the following strategies:

1. Select column B and make it as wide as you want, usually most of the width of the page. Select Wrap Text either from the Ribbon or from the Format/Cells window.
2. Change the color of the sentence numbers (column A) to a lighter different color from the rest of the text.
3. Remove the grid lines by using the Grid line command in the Layout ribbon.
4. Adjust the font, font size, and font style to make the text easier to read.

Color Coding in Excel

Sometimes the easiest way to code text is to highlight text of interest and change its color. I think you will find this works better if you change the Cell fill color instead of the font color. The drawback is that Excel makes you fill the whole cell. To color code individual words you have to use the Text color command. When you are doing this be sure that the ribbon with the color selection icon is open so you can easily click on it.

Once you have coded the text highlight row 1, scroll down and shift-click on the bottom text row number in the spreadsheet. Remember this is just like sorting numerical data—do not highlight cells, only whole rows. Go to Data/Sort. Select Column B to sort and change the Sort On criteria to either Cell Color or Font Color depending on how you coded the data.

 

Count Word Occurrences in an Excel Document

1. On a copy of the transcript remove any text you may not want in the word count.
2. Check the spelling in the transcript.
3. Using Edit/Find/Replace, remove all punctuation.
4. Using Format/Change Case make all text lower case.
5. Remove text formatting like underline, italic, and bold.
6. Using Edit/Find/Replace, change all spaces into paragraph marks. (^p)
7. Select All and Copy.
8. Open a new Excel spreadsheet
9. Select cell A2 and Paste.
10. While the new text is still highlighted (click on the A above the column if you have un-highlighted the text) use Data/Sort/Column A to alphabetize the list.
11. Save the document.
12. Cell A1 should be empty. If it is not, select row 1 and then Insert/Row.
13. In cell B2 type the formula [ =if(A2=A1,B1+1,1) ] (do not put in the square brackets). This formula counts the number of occurrences of each different word.
14. Select the range from B2 to the last word entry and Fill/Down.
15. Copy the selection and Paste Special/Values.
16. Save the document.
17. In cell C2 type the formula [ =if(B3=1,B2,””) ] (that is two double quote marks).
    This formula lists the word count for each different word.
18. Select the range from C2 to the last word entry and Fill/Down.
19. Copy the selection and Paste Special/Values.
20. Save the document.
21. Select Row 2 and then Shift Click on the last row in the data set.
22. Data/Sort by Column C.
23. Decide on a frequency range in which you are interested. This will depend on the size of the transcription. (i.e., words that appear at least 4 times and no more than 10 times).
24. Select the rows that represent the word counts in which you are interested and Copy.
25. Open a new workbook and Paste.
26. Sort by column A to alphabetize the list. It is important to do this to identify similar forms of words.
27. Save the document.
28. Scan the word list for any words that might be important. You may wish to turn these into In Vivo codes.

Coding in Excel

1. Follow the same procedures for Adding Fixed Numbers at the Beginning of Lines described above.
2. Code in column C. If you have multiple codes for a given section, add additional codes in separate columns.
3. After you have coded the entire transcript, Select all of the rows and then sort by column C.
4. There is some risk of separating the codes from the sentences to which they are attached. To avoid this problem number the codes using the concatenation function described in Adding Fixed Numbers at the Beginning of Lines.
5. I recommend that you attach the numbers at the end of the codes so that you can sort the codes alphabetically and the numbers will not interfere with the sort. Assuming the sequential numbers are still in the A column, an example formula might be:
   [ =concatenate(C1, “ “, A1) ]. This will put a space between the code and the number.

Word Search Coding in Excel (In Vivo)

Either because you have identified recurring words or phrases from reading the transcript or you have identified high frequency words from doing the Count Word Occurrences in an Excel Document described above, you might wish to automatically insert codes relative to the identified words.

A simple way to do this is to go back to the original transcript in Word and search for the identified terms and type in the codes however you are keeping track of them. What follows is a bit more elaborate procedure that automatically puts in the codes.

Briefly what you are about to do is to search text for the word or phrase you wish to find. The Excel function to do this is =search("text",location). The result will be one of two things. If the text is there, the result will show the character number of the point where the text begins in the cell. If the text is not there, an error will appear.

When the text is found in order to get the actual text in the cell instead of the character number, an embedded if statement needs to be use: =if(search("text",B1),"text","").

This will still generate error codes if the text is not found. It is time consuming to get rid of these manual so one more element needs to be added to the formula. =iferror(if(search("text",B1),"text",""),"")

What this formula does is to look for specific text, if it finds it then it puts the text in as a result, if it doesn't find it then it leaves the cell blank.

1. Follow the same procedures for Adding Fixed Numbers at the Beginning of Lines described above.
2. If you were searching the word technology, in cell C1 (assuming the C column is empty), type:
   =iferror(if(search("technology",B1),"technology",""),"")
3. Hit Return and then click on the cell with the formula.
4. Shift-click on the last cell in column C.
5. Edit/Fill/Down
6. Copy
7. Edit/Paste Special/Values
8. Save the document
9. If you want to search for another term repeat the above but in a new blank column.

Usually you will want to pick up multiple forms of a word like technology, technologies, or technological. Think of the fewest number of characters that will find the word without including different words. In this case techno or even tech might work.

Add the line numbers at this point.