A better approach is to generate a list of all the formulas in a worksheet by using the ListFormulas VBA macro. This downloadable macro, which works in all versions of Excel, inserts a new worksheet and generates a list of all of the formulas in the active worksheet. You can then print that new worksheet to help you debug formulas in the active worksheet.

The rf-099.xls sample worksheet shows some output that is suitable for printing. The output occupies four columns: the cell address, the current value of the formula, the formula in standard Excel notation, and the formula in R1C1 notation. The R1C1 notation shows the location of a cell ù the number after the R is the row number, and the number after the C is the column number. You can use this notation to compute the row and column positions in macros or to show relative cell references.

Caption: Output from the ListFormulas macro can help you spot likely formula errors, like the one in Cell D11

There's another reason for including the R1C1 notation. If you sort the output using Column 4 as the sort key, identical formulas will appear together. This makes it easy to spot an oddball formula that's slightly different from the others ù and quite likely a mistake ù such as the formula in cell D11.

To use the ListFormulas macro, download it from our cover CD, or from www.j-walk.com/ss/pcworld/index.htm. Then open the worksheet that contains the formulas you want to list, and execute the macro.