In the last issue (VMRD Newsletter, April 2004) we saw how to use Microsoft Excel to calculate positive / negative values from optical density (OD) results of ELISA assays. This can be helpful in reducing faulty results caused by human error and human bias. However, it bears mentioning that blindly trusting a computer is a good way to produce nonsensical data. It is always a good idea to double-check the computer’s work. One thing that facilitates such an evaluation—as well as being useful for reporting purposes—is color coding of the data sheet. In this article, I will show you how to persuade Excel to automatically highlight cells or rows based on the data contained in them. You might use this functionality to highlight all positive samples, for instance.
We will use the same data set used in the April article. It might be helpful to create a similar spread sheet in Excel so that you can follow along. To begin, we will select a row of cells and choose Conditional Formatting from the Format menu (Figure 1). In the ensuing Conditional Formatting dialog (Figure 2), choose “Cell Value Is” from the first pull-down menu, “equal to” from the second pull-down menu, and enter “Pos.” in the text box. This tells Excel what cells we want to format—in this case cells containing the text, “Pos.” Next, push the “Format . . .” button. In the ensuing dialog, we can specify the font, style, size and color of text; the style, color, and width of border; and the background color and pattern of cells that match the criteria that we previously chose. Right now we just want to highlight cells containing “Pos.” in yellow, so we go to the Patterns tab, click on the swatch that is our desired color of yellow, click OK, and OK again to close out of the Conditional Formatting dialog.
If you’re following along with this tutorial, you may be surprised to find that, in spite of my earlier promises, only one cell on your worksheet is yellow. This is because we have yet to copy the formatting from that one yellow cell to the others. This process is facilitated by the Format Painter, a handy little tool that copies the format of whatever area is highlighted when the tool is selected and then “pastes” that formatting on to whatever area is highlighted immediately after it is selected. Make sure that your cursor is on the cell to which you applied conditional formatting and double click the Format Painter icon in the toolbar. Use the resulting Format Painter cursor to click on each cell to which you wish to apply conditional formatting. You can also click and drag over contiguous cells in order to apply formatting to them. When you are finished applying your conditional formatting to all the cells you desire, click on the Format Painter icon in the toolbar once again in or to put it away. Incidentally, it is possible to single click the Format Painter when getting it out of the tool box. In this case it behaves somewhat differently in that it puts itself away immediately after you have finished highlighting one cell or range of cells with it. Using it this way will work just fine, but I find it more convenient to use it as many times as I want and then put it away. At this point you will find that your spreadsheet looks like Figure 4 with only cells that actually contain “Pos.” highlighted in yellow. My preference is to have not only the result, but also the OD and the sample identification highlighted. To do this, highlight cells A3:C3, choose Conditional Formatting from the Format menu, change the first pull-down menu to “Formula Is”, type “=$C3=“Pos.”” in the text box to its right, and use the Format button to specify a yellow background (Figure 5). Then use the format painter as previously described to copy the format from cells A3:C3 to cells A4:C8. The resulting formatted spread sheet is shown in Figure 6. The “$” preceding the cell address is important in this formula; without it only the cells in column C would be highlighted in yellow because Excel will change the “C3” to “B3” when it copies the formatting formula to column B. Of course, the value in B3 does not equal “Pos.” so the cell will not be highlighted.
Having highlighted all of our positives, we may want a count of how many positives or negatives exist in our data set. This is easily accomplished using Excel’s COUNTIF worksheet function. The syntax of this function is as follows: COUNTIF(range_of_cells_to_be_examined, criteria_for_counting). If we add the formula, “=COUNTIF(C3:C8, “Pos.”)” to Cell C9 on the tutorial spreadsheet, Excel will count the total number of positives and place the number in well C9. Likewise, placing the formula, “=COUNTIF(C3:C8, “Neg.”)” in cell C10 of the tutorial spreadsheet will cause Excel to count the total number of negatives and place the number in cell C10. The complete spreadsheet, along with the formula for counting positives, is shown in Figure 7.
In this article I have hopefully elucidated several techniques that can significantly reduce human error associated with interpreting OD data, but I must reiterate that it is always a good practice to check and double check. Excel almost never makes a mistake, but it is fairly easy to make mistakes with Excel, either through error or misunderstanding.
