MS-Excel / Excel 2003

Printing the Formulas in the Report

Normally, whenever you print a worksheet, Excel prints the entries exactly as they appear in their cells of the worksheet. As a result, when you print a section of worksheet that contains formulas, the printout shows only the results of the calculations performed by the formulas and not the contents of the formulas themselves. In addition to a printout showing the results, you may also want to print a hard copy of the worksheet showing all the formulas by which these results were derived. You can then use this printout of the formulas when doublechecking the formulas in the worksheet to make sure that they're designed correctly.

To print a copy of the worksheet with the formulas displayed in the cells, follow these steps:

  1. Choose Tools → Options to open the Options dialog box.
  2. Select the View tab, select the Formulas check box, and click OK.
    When the Formulas check box is selected, Excel displays the entry in each cell in the worksheet as it appears on the formula (as well as the Formula Auditing toolbar which you can close by clicking its Close button). In this state, not only do formulas appear as entered in the worksheet, but also all values (text and numeric) appear without their formatting.
  3. Click the Print button on the Standard toolbar to send the print job to the printer.
    If you need to print only a particular area of the worksheet, select that range and then designate that section as the Print Area (File → Print Area → Print Area) before you click the Print button on the Standard toolbar.

Note that Excel automatically adjusts the column widths to display the contents of their formulas and displays the Formula Auditing toolbar. Note too, that the program automatically replaces all formatted numbers with their raw values, including the dates in row 2 that now display their serial numbers. (Excel restores all number formatting as soon as you clear the Formulas check box.)

After printing the formulas in the worksheet, you can return the worksheet to its Normal view without the formulas displayed in the cells by clearing the Formulas check box on the View tab of the Options dialog box. Excel returns the worksheet display to normal so that only the results of formulas are displayed in the cells and all entries are displayed with their formatting.

You can instantly switch between displaying and hiding formulas in a worksheet by pressing the Ctrl+' keyboard shortcut - Ctrl plus the accent key with the tilde (~) symbol above it. This shortcut acts as a toggle; the first time you press it, all formulas are displayed in the cells of your worksheet, and the second time you press it, they are once again hidden.

To help you identify the cell reference of each formula in your printout, print the version of the worksheet that displays the formulas in the cells with the column letters and row numbers on the top row and leftmost column of each page. To do this, you need to open Sheet tab of the Page Setup dialog box (File → Page Setup) and select the Row and Column Headings check box. Then you can print the worksheet by clicking the Print button in the Page Setup dialog box or, if you need to return to the worksheet to display the formulas in the worksheet before sending the job to printer, click the dialog box's Close button instead.

[Previous] [Contents] [Next]