Printing: Showing formulas

Sometimes you not only want to make the printout look like a spreadsheet, you also want to see the formulas underlying the results. There are a number of different reasons you might want to “show your work” this way.

The most obvious ones to me are:

  • Proving you did it. Sometimes the process is as or even more important than the result, and in these cases there needs to be a way to make sure that the process was followed. This might be the case in a classroom where a teacher’s checking on students, or in a business office where an auditor is checking a company’s books.
  • Remembering how you did it. Hard drives crash every day. Sometimes backups crash simultaneously. Sometimes a file gets corrupted but not destroyed, and the backup program merrily overwrites the good file with the corrupt one. There are any number of ways that the most carefully tended computer file can become toast, and while the data might be retrievable from other sources (reports and the like), the formulas which processed/generated that data only existed in the spreadsheet. If you have a hardcopy of the formulas it may be tedious to re-enter them, but it’ll be less agonizing than trying to re-create them.
  • Figuring out how you did (or failed to do) it. Mistakes happen. The more complex you get, the more likely you are the make a mistake, and the harder it will be to find. Programmers call this stage debugging.

    When I was studying computer science and one of my programs Just Wouldn’t Work I often ended up printing it out so that I could see more of it at once, trace through, flip back and forth between sections, mark things up, and make notes about changes so that if they became complex I wouldn’t lose track of them. Seeing it on paper was simply a different experience than seeing it on a screen.

    The formulas in a spreadsheet may be simpler than what I was writing back then (or they might not, depending on what you’re doing), and the screen resolution is most certainly better, but the basic principle still holds: paper is different from screen. If nothing else, if you print out the formulas from a spreadsheet then you can stick your finger (or a tape flag, if you want to get all fancy) down on one cell, and keep that place while you go looking for the cell it’s referring to. You can also tape multiple sheets to your wall or table, and see a whole worksheet at once even if it’s to big to fit on a screen.

    It can be a particularly good technique for tracking through circular references. If you’ve managed to make a bad snarl that you have to work through, it doesn’t help that every time you leave a formula to see what the formula it’s referring to is doing, the program is likely to helpfully inform you that you have a circular reference. Again. By printing it out you can still follow the steps to solving a circular reference, without getting your thought train derailed on a regular basis.

Now that I’ve sold you on how useful this ability can be, the good news is that most programs make it easy for you to do.

In Excel 97:

  1. Click on the header for the Tools menu or type alt-t.
  2. In the menu that opens, click on “Options…” or type o.
  3. In the pop-up window, click on the “View” tab.
  4. Mark the box next to “Formulas”. It’s on the left, near the bottom, in the “Window options” section.
  5. Click “OK”
  6. Print as you usually would.

In Excel 2007:

See Excel 2010.

In Excel 2010:

  1. Click on the “Formulas” tab or type alt-p.
  2. Locate the “Formula Auditing” group. For me it’s 2nd from the right
  3. At the top of the “Formula Auditing” group, click on “Show Formulas”. It’s at the top, kind of in the middle of the group.
  4. Print as you usually would.

In Google Spreadsheet:

Google Spreadsheet does not seem to have a way to allow you to print formulas. Although you can dispay them by clicking on “View” then “All formulas”, when you print it will only show the results.

In OpenOffice Calc:

  1. Click on the “Format” header or type alt-o.
  2. Click on “Page” or type p.
  3. In the middle section, labeled “Print”, mark the box next to “Formulas”.
  4. Click “OK”.

In Microsoft Works Spreadsheet:

  1. Click on the header for the View menu or type alt-v.
  2. In the menu that opens, click on “Formulas” or type f.
  3. Print as you usually would.