10 top spreadsheet tips. (Compute's Getting Started with Spreadsheets)
by Mark Minasi
1 Create a quick security screen.
Want to quickly hide a spreadsheet from prying eyes? I hate for people to look over my shoulder when I'm working on financial material.
First, if you're using a Windows-based spreadsheet, just minimize it. A related method is to move your mouse to the place on the screen that activates the Windows screen blanker (it's the upper right corner for IdleWild and adjustable for Intermission).
Second, if you run a spreadsheet as a DOS application under Windows, you can hide your screen by pressing Ctrl-Esc to bring up the Windows Task List.
For non-Windows users, 1-2-3's "show graph" key comes in handy. If you're not working with a graph, F10 effectively blanks your screen.
2 Get out of 1-2-3 menus quickly.
Sometimes you'll find yourself five levels deep in a 1-2-3 menu looking for a quick exit. The typical action is to lean on the Esc key, but that's not necessary. Just press Ctrl-Break, and the nested menus will disappear quickly.
3 Create a retirement-planning spreadsheet.
Just how much will you need to put away in order to tell the boss to take this job and--$? Well, to start off, you need to know the following: CURAGE = your current age. RETAGE = desired retirement age. FINALAGE = estimated age at death. RROR = the rate of return on your investments after inflation. If your IRA yields 6 percent and inflation is 5 percent, RROR is 1 percent. Be honest about this: Optimistic guesses will lead you to retire a few years too early and spend your golden years in penury. CURWLTH = the current dollar value of your savings and investments. Leave the house's value out; you'll need somewhere to live even when retired. FUTINC = desired annual retirement income in today's dollars.
Put those data in the spreadsheet as named ranges; then follow them with the following formulas.
YRSRET = FINALAGE - RETAGE + 1 YRSLFT = RETAGE - CURAGE LUMPSUM = lump retirement sum needed = [FUTINC.sup.*](((1+RROR[curet]YRSRET-1)/((1+RROR)[curet][YRSRET.sup.*]RROR)) FUTWLTH = value at retirement of current wealth = [CURWLTH.sup.*](1+RROR)[curet]YRSLFT AMTSAV = amount to be saved = LUMPSUM - FUTWLTH ANNSAV = amount to be saved each year = AMTSAV/(((1+RROR)[curet](YRSLFT+1)-1)/RROR)
Plug this into your spreadsheet and do some what-iffing. Don't forget your pension savings when calculating wealth, and figure that you'll need 70 to 80 percent of what you're making now during your retirement years.
4 Get spreadsheet documentation tools.
Programs like CellMate allow you to annotate spreadsheets for Lotus 2.1. Later versions of Lotus make it a bit easier--3 x has the three dimensional quality that allows you to use one two dimensional plane as a documentation plane. Spreadsheet Auditor is another help here, but the bottom line is that a big spreadsheet is a data processing professional's nightmare: basically a hugh undocumented program. Enforce strict documentation standards on yourself and your company, or one day you'll wonder just what these spreadsheets do.
5 Avoid manual calculation blues.
All of us learn quickly to set the calculation method to manual when a spreadsheet grows beyond a few K, or every keystroke causes an agonizing recalculation delay. But we forget that the numbers in the spreadsheet can't be trusted until we force a recalculation. Several times I've had employees bring me baffling spreadsheets. After scratching my head for a while, I'd look at their printouts and say, "Did you recalc this before you printed it?" I always get a sheepish grin.
6 Solve FAT error when installing Excel.
PageMaker, Excel, and a number of other Windows applications get apoplexy when they are installed on a drive larger than 32MB under DOS 4 or 5 when the SHARE program has been run. The installation routines for those programs can't handle drives after SHARE has been run. The solution is simple: Rename SHARE.EXE to SHARON.EXE or something else, and reboot.
7 Get 1-2-3- to print correctly.
1-2-3- used to drive me crazy by printing over the page perforations on my dot-matrix printer. It seemed totally unaware of the dimensions of the printed page. The solution is n the manual.
Before printing, align the paper so the printhead is sitting just below the paper perforation. Then type /PPA (Printer Print Align); 1-2-3 now knows where the paper begins.
If you use a laser, be aware that 1-2-3 is largely unaware that lasers figure
63 lines per page, not 66. You can set that information with /PPO (Printer
Print Options). Alternatively, you can convince your printer to print 66
lines per page by sending it the following: E 8 Landscape-Print with Laser Jet III, Excel.
If you've Excel under Windows 3.0 with the LaserJet III driver, you'll find
that landscape printing doesn't work. Don't fiddle with the options or
Control Panel; the problem isn't fixable. Mircosoft claims that there's a bug
in the HP III driver for Windows. There is an answer, however: The April 1991
HP series III driver for Windows fixes the problem.
9 Print tiny letters with a LaserJet III.
Not only does the new LaserJet III driver work well with Excel, but it also
helps produce large and readable spreadsheets. I use the Universe built-in
typeface at six-point size. When printing in landscape mode--which works fine
with the latest HP driver--you'll get lots of columns on a normal 8 x 11 inch
page. The latest driver also is 30 percent faster. Call HP or visit
CompuServe's HP forum to get version 3,778 HP series III driver for Windows.
10 Use the plus key as a placeholder.
I often jump around a spreadsheet looking for some bit of information. Once
I've found it, I have to remember where I was in the first place. Then I
realized that spreadsheets let you move anywhere where building a formula.
Once you've pressed the + key, you can move anywhere. To jump back to your
original location, press Esc. (Tip courtesy of Mike Sola, my spreadsheet
whiz.)