If I had to choose just one piece of software with which to
run my business, it would be the spreadsheet. This is as true
today, when I work on a stateoftheart Pentium machine, as it
was when I purchased my first IBM XT clone more than 15 years
ago. Over the years, I’ve used spreadsheets for the usual
purposes — estimating, invoicing, and jobcosting —
and the unusual — figuring cuts for rake walls or hip
roofs. I have even heard of a contractor who used a spreadsheet
to lay out a tile pattern for a shower.
Most new computers come with software, such as Microsoft
Works or Claris Works, that includes a spreadsheet. If you
spend a few hours learning how to use a spreadsheet,
you’ll begin to find ways to put it to work in your
business, without having to spend another cent on software.
In this article, I’ll use a couple of simple
spreadsheets I set up when I first started using a computer to
explain how spreadsheets work. But those are just the tip of
the iceberg. Once you feel comfortable with these simple
examples, you’ll find that’s it’s easy to set
up more complex spreadsheets to fit your business.
Rows and Columns
If you
have ever used a columnar pad to estimate or do your
bookkeeping, then perhaps without realizing it, you’ve
used a manual version of a spreadsheet. Spreadsheet software
builds on the rowandcolumn structure of a paper columnar pad,
adding automated shortcuts and other features that make the job
of entering and manipulating data easier.
The biggest advantage a spreadsheet has over a paper
columnar pad is that a spreadsheet will do all the math for
you. Even if you use a calculator to estimate, it’s
tedious to add up all your labor, material, and subcontractor
costs across each row, then total the columns. If you’re
interrupted midstream, it’s easy to forget where you left
off. And changes — like a call from the architect telling
you to use $6 per square foot for tile instead of $5 —
mean you have to start all over again. After all that, your
client may still get this strange smile on his face during your
presentation, causing you to wonder if your price is $20,000
too low because you made some math error.
A spreadsheet takes the guesswork and the tedium out of all
of these situations. A spreadsheet can link individual entries,
or "cells," in the rows and columns so that when the value of
one cell changes, the values in all linked cells change, too.
The process is accurate and it all happens in the blink of an
eye.
Estimating
When I first
started using a computer in my business, I did almost
everything on a spreadsheet. Early on, the most pressing issue
was to be able to estimate and feel confident that I had not
made any math errors. At the time, I was using my best judgment
as to how long each component would take to build, since almost
everything we did was custom and I had not yet done enough
jobcosting to come up with squarefoot or linearfoot costs
for labor. So I set up a "template" with a list of labor items
I used regularly to estimate labor for the job (see Figure
1).

=SUM(F3:J3) 
=(F3*Foreman)+(G3*Carpenter1)+(H3*Carpenter2)+(I3*Carpenter3)+(J3*Laborer1) 
=SUM(D3:D13) 
=IF(E3=0,"0",D3/E3 
=AVERAGE(A3:A13) 
Figure
1. The rows in this laborestimating template correspond
to specific tasks, which are coded and listed in columns B and
C. Hours for each employee are entered in columns F through J.
The formulas that fill columns A, D, and E (colored cells)
calculate totals.
I used the template as a worksheet to do my estimating and
to track my labor costs once the job was underway.
The layout of this template is simple. Columns B and C hold
task codes and descriptions, and match those on my
company’s timesheets. Each of the columns F through J
correspond to one of my employees, and the headings of these
columns (cells F1 through J1) hold job descriptions, such as
"Foreman," "Laborer," or one of several skill levels of
"Carpenter." Immediately below (cells F2 through J2) is the
hourly wage for each job description. The cells in the other
three columns (A, D, & E) hold formulas that perform math
calculations on the numbers I enter in each row; the cells in
row 14 hold formulas that total the columns.
Adding numbers in rows.
When I do a labor estimate, I follow the list of tasks, row by
row, entering the number of hours I think each employee will
spend on each one into the cells under each job description
(columns F through J). To find the total hours estimated for a
given task, column E (labeled "Total Hours") holds a formula
that adds the hours, row by row, from the job description
columns. In cell E3, for example, the formula looks like
this:
=SUM(F3:J3)
If you key this formula in directly, you have to be careful
to include the correct symbols in the proper position. You can
save time and reduce the chances of making a typo by using one
of two other ways to enter the formula. One method is to click
on the function button, which calls up a list of available
functions (Figure 2).
Figure
2. The easiest way to enter a formula is to pick from a
list, such as this one in Excel (top). Clicking on the SUM
function, for example, opens a dialogue box (bottom), which
walks you through the formula.
After choosing SUM from the list, a dialogue box opens and
walks you through the formula. During this process, you can use
the keyboard or mouse to select the cells you want to add.
Because the SUM function is used so often in most
spreadsheets, there’s a second, easier way to enter the
formula: Just click on the SUM button on the toolbar at the top
of the screen. This button takes you directly to the dialogue
box for the SUM function.
Adding numbers in
columns.
The SUM function works with columns of
numbers, too. In my labor estimating template, for example, the
formula in cell D14 looks like this:
=SUM(D3:D13)
Like the other formulas in row 14, this formula adds
together all of the values in the column above it.
Absolute references.
So
far, we’ve looked at formulas that operate on cells based
on their position relative to each other. For example, the SUM
formula in cell E3 operates on the five cells immediately to
the right (F3:J3). If we were to copy this formula into the
cell just below (E4), the spreadsheet would automatically
change the references from F3:J3 to F4:J4, because these cells
are immediately to the right of E4.
Sometimes, however, I need to use an "absolute reference"
— a formula that operates on the same cell no matter
where on the spreadsheet the formula is located. The formulas
in column D of my template use absolute references to calculate
the labor costs for each employee and add them together to get
the total cost of each task. In cell D3, for instance, the
formula first multiplies the number of hours in cell F3 times
the hourly rate in cell F2; it does the same for each of the
other four cells in the row and adds them all together. One
cell down in D4, however, the formula again refers to cell F2,
even though cell F2 is not located in the row directly
above.
If this formula used relative references, it would look like
this:
=(F3*F2)+(G3*G2)+(H3*H2)+(I3*I2)+(J3*J2)
Using absolute references, it looks like this:
=(F3*$F$2)+(G3*$G$2)+(H3*$H$2)+(I3*$I$2)+(J3*$J$2)
The dollar signs indicate that the formula should reference
this cell no matter where the formula is located on the
spreadsheet.
Naming cells.
You have
to be careful when copying formulas that use absolute
references from one cell to another. Most spreadsheets assume
that all references are relative, so you have to manually go
into each copied formula and add the dollar signs. An easy way
to work around this problem is to give the absolute reference
cells a name. In my template, for example, the cells that hold
wage amounts are named "Foreman," "Carpenter 1," Carpenter 2,"
and so on. Using these names, the formula in cell D3 looks like
this:
=(F3*Foreman)+(G3*Carpenter1)+
(H3*Carpenter2)+(I3*Carpenter3)+(J3*Laborer)
When I copy it down one cell to D4, the names of the
absolute references stay the same:
=(F4*Foreman)+(G4*Carpenter1)+
(H4*Carpenter2)+(I4*Carpenter3)+(J4*Laborer1)