Software

Quickly document Excel formulas

You can document an Excel worksheet easily by printing formulas. Store the hard copy with the application's documentation.

Formulas are your main tool in Excel, so you'd think that it would come with some kind of documenting tool. But it doesn't. However, it's easy to print formulas. You'll want to print and save formulas for two reasons:

  • It can be easier to debug problems when viewing the bigger picture rather than one screen at a time.
  • You'll want to maintain a formula history. When you make changes, print new formulas and store them with your application's documentation.

First, save the worksheet, because you're going to make some format changes you'll want to dump rather than save. Next, display the formulas. There are a number of ways to do this, but the quickest is to press Ctrl + `. (That second character, `, is just to the left of the digit 1; it isn't the ordinary single quote that's just to the left of the Enter key.) This combination toggles the worksheet between formulas and the results of those formulas.

Unfortunately, column widths won't automatically adjust to display complete formulas, so, click the intersecting header cell (the one to the left of the column header row and at the top of the row header column) to select the entire worksheet. Then, double-click any right borderline in the column header row to apply an AutoFit property to all of the columns in the worksheet.

july2008blog2fig1.jpg

Next, choose Page Setup from the File menu. Click the Sheet tab and in the Print section, check Gridlines and Row And Column Headings.

july208blog2fig2.jpg

Adding these two attributes will make it easier to match each formula to its cell. A simple worksheet of formulas isn't very helpful; you need to know where each formula belongs.

july2008blog2fig3.jpgAt this point, you can print the worksheet as you normally would. If you're documenting changes, you might want to print just the cells you've updated, added, or deleted, instead of printing the entire worksheet. That'll make changes easier to track. If you must print the entire worksheet, find some other way to identify the changed formulas -- highlighting them will work nicely.When you're done, close the worksheet immediately, without saving. Otherwise, you might accidentally save the formula-accommodating column widths. If that does happen, simply select the entire worksheet as I described above and reset them.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
jean.w.boyd
jean.w.boyd

I go to Tools, Options, and at the View Tab under Window Options I check formulas to see my formulas on a worksheet.

tngotu
tngotu

It's verygood.it help me very much

dogknees
dogknees

This is a useful tip, but the instructions contain a potential gotcha. "Otherwise, you might accidentally save the formula ? accommodating column widths. If that does happen, simply select the entire worksheet as I describe above and reset them. " This will not reset column widths to their original size. It will auto-fit all columns instead which is not what you want if you've carefully sized all your columns to specific widths. A common case is sizing columns to fit the largest value that will be encountered. This is generally done when you're locking a sheet down so the page will be a consistent width when printed. There is no easy fix for this if you accidently save your file. My best suggestion would be to create a copy of your file solely for the purposes of documentation.

Gonzalo@Sisap
Gonzalo@Sisap

Nice tip. And good comments. I couldn't make formulas appear using Ctrl+` It just didn?t work for Excel 2007, even though help says so. Go to the Office button, clic on Excel options, advanced and scrroll down to Show options for this sheet. Very useful tip, and use it on a copy of your book

Editor's Picks