In my past job as an estimator for a lumberyard, I did
hundreds of material takeoffs every year, and I still do them
in my current job as an architectural draftsman. Some
estimators use specialized software, but I estimate the same
way many contractors do — with a spreadsheet. Almost any
spreadsheet will do; I happen to use Excel.
You can create a spreadsheet estimating template without
knowing more than a few basic functions — how to add,
subtract, multiply, and divide. But it's worth taking the time
to learn a few more advanced functions: Not only will you be
able to use them in your estimating spreadsheet, but you'll be
able to create spreadsheets for many other construction
calculations as well.
Estimating materials is all about calculating areas and volumes
— something you can do faster and more reliably with a
spreadsheet than with a calculator. When you use a template,
the formulas are already in place, so all you have to do is key
in dimensions from the prints, which greatly reduces the chance
for keystroke errors.
My estimating template has many worksheets, or tabs. Since the
worksheets for the roof contain many advanced functions, I'll
use them to show how the template works. Most of the buildings
I estimate use trusses, so the template isn't set up to
estimate rafters. It does, however, contain worksheets for
other roof components, like sheathing, finish roofing material,
and wood and metal trim.
Roof Area Calculations
The first thing I do when estimating a roof is figure out its
area. For the purpose of this article, let's say we're looking
at a simple L-shaped house with a pair of 6/12 gables. The
plans indicate that, including overhangs, the roof measures
1,820 square feet on the flat. But since the sloping area is
greater than the flat area, you'll need more material than
You could scale the slope distance from an elevation and use it
to calculate the area of the roof, but as an estimator I've
learned not to scale from prints. It's too easy to make a
mistake, especially when they're drawn to 1/4-inch or 1/8-inch
scale. Plus, if the prints were drawn by hand or subsequently
copied, they may not actually be to scale.
Pitch factor. I prefer to put a
formula in the template and let the spreadsheet calculate the
slope. It's faster than using a calculator and there's less
chance of a mistake. All I have to do is enter the area on the
flat in one cell and the pitch in another, and the template
creates a pitch factor, which it uses to calculate the sloped
area of the roof (see Figure 1).
Figure 1. Plans typically show horizontal
and vertical dimensions and the slope of the roof. This roof is
1,820 square feet on the flat, but to estimate sheathing and
roofing materials it's necessary to determine the area on the
slope. First, using the Pythagorean theorem, calculate the
slope distance — 13.4164 inches per foot of run for a
6/12 roof. The ratio between this slope distance and a unit of
run is called the pitch factor. Multiplying the run of the roof
by the pitch factor gives the slope, or rake, distance of that
roof. Multiplying the area of the roof on the flat by the pitch
factor gives the sloped area of the roof.
The pitch factor is the ratio between the sloping distance and
the run. Multiplying the area on the flat by this number gives
you the area on the slope. To calculate a pitch factor, start
with the Pythagorean theorem:
A2 + B2 = C2
A is the rise, B is the run, and C is the sloping distance. For
a 6/12 roof, A equals 6, B equals 12, and C equals the square
root of 180 (62 + 122). The square root of 180 is 13.4164. The
formula for the pitch factor is:
sloping distance / unit run = pitch factor
13.4164 / 12 = 1.118034
Think of this number as a percentage: Rounded up, it tells us
that the distance up the slope of a 6/12 roof is 112 percent of
the horizontal run.
With the pitch factor, we can easily determine the sloped area
of the roof:
pitch factor x area on the flat = sloped area of the roof
1.118034 x 1,820 sq. ft. = 2,035 sq. ft.
This formula will work for any roof where all the areas are the
same pitch. If the roof has more than one pitch, then you'll
have to use separate pitch factors for each area. The
estimating template has been simplified for this story; my
regular estimating template contains additional rows that can
be used when the roof has more than one pitch.
Putting the Formula Into Excel
While a carpenter may understand these formulas, Excel can't
use them unless they're written in a certain format. Squaring a
number, as in 62, is the same as raising the number to the
In Excel, you can square the number 6 by entering this formula
into a cell:
That tells the program to raise the number 6 to the power of 2.
To square the number 4 you'd enter =POWER(4,2). If you wanted
to cube the number 6 (raise it to the third power), you would
We need to convert A2 + B2 = C2 into language that Excel can
understand. Since we're talking about a 6/12 roof, the
calculation would be:
(6)2 + (12)2 = C2
As you've guessed, the convention for entering a formula in
Excel is to put an equal sign in front of it. So for a 6/12
roof the cell entry to solve for C2 would be:
Since we usually work in pitches that show a rise over 12, we
can simplify the formula by using the number 144 (12 squared)
in place of the second POWER function. The simplified entry
Some plans show rise over 10. In that case you would substitute
the number 100 (102) for 144.
Finding the Square Root
To find the sloping distance of a 6/12 roof, we must solve for
C in the equation A2 + B2 = C2. So far, we've only entered A2 +
B2. C is the square root of A2 + B2. To find the square root,
we add =SQRT in front of the formula:
Note the outer set of parentheses, which tells Excel to treat
(Power(6,2)+144) as a single number. (There should always be
the same number of parentheses facing left as there are facing
The result is 13.41641. To convert this number to a pitch
factor, you divide it by 12. The formula will now read:
The result is 1.118034 — the pitch factor. To find the
sloped roof area of the roof described above, we can now
multiply by the area on the flat (1,820 square feet). The
formula now reads:
The result is 2,035, the square footage of sheathing needed for
This formula is not quite ready to be put into the estimating
template. The way it reads now, you would have to change some
of the numbers every time you did an estimate. For example, if
the roof had a 7/12 pitch, you'd have to change the 6 to a 7.
And if the roof was 2,500 sq. ft. on the flat, you'd have to
change 1,820 sq. ft. to 2,500 sq. ft.
It would be much better to put the formula into the template
once and then leave it alone. The way to do that is to use cell
references for the values that change.
A cell reference tells Excel where to find a particular piece
of data. This saves you from having to enter the same number
multiple times and places — an incredibly useful function
that's worth mastering.
So instead of changing the roof area formula for every
estimate, I substitute cell references for the values that
change (Figure 2). On the miscellaneous roof framing tab, the
area on the flat (1,820) is in cell E8 and the pitch (6) is in
E9. With these cell references, the formula reads:
Figure 2. When doing a roof takeoff, the
author enters quantities and dimensions in the
yellow-highlighted cells of this worksheet. Other cells contain
formulas that convert the takeoff quantities into a materials
list. For example, cell I3 calculates the sloped area of the
roof. The formula is already in the template; all the estimator
has to do is enter the area of the roof on the flat and the
With this formula in place, we can enter any area on the flat
we want in E8 and any pitch we want in E9 and the spreadsheet
will automatically calculate the sloped area of that
Creating references between tabs. In the formula above, the
cell references apply to cells on the same worksheet. But what
if you want to refer to cells on a different worksheet?
For example, the area of roof that has to be sheathed was
calculated on the miscellaneous roof framing tab, but I need to
use the same number to estimate shingles on the tab for roof
covering (Figure 3). I could manually transfer this number to
the roof covering tab, but it would be safer and faster to do
it by creating a reference to a cell on a different tab (Figure
4). That way, if a design change alters the amount of roof
sheathing, the shingle quantity automatically updates to the
Figure 3. The same takeoff data is often
used in many parts of an estimate. The area of roof to be
shingled roughly matches the area to be sheathed, so the
worksheet for roof covering uses cell references to transfer
this area as well as other data from the previous
Figure 4. Cell references transfer data
from one cell to another, even a cell on a different worksheet.
To create a cell reference between worksheets, first type = in
the cell that is to contain the reference. In this case, it's
cell E3 of the roof coverage tab (A). Next, click the tab of
the worksheet that contains the cell you want to refer to (B).
Then click the cell that contains the data you want (C) and hit
the enter key. This will take you back to the original
worksheet, where the cell reference is now embedded in the
original cell (D). The reference shown here tells Excel that E3
equals whatever number happens to be in cell I3 of 26-MRF. If
the quantity of roof sheathing in I3 changes, Excel will
automatically update the value for "SQF of Roof to be
Rounding Up to Full Pieces
If you wanted to sheathe an 8-foot-by-10-foot area, you'd need
80 square feet of plywood or OSB. That's two-and-a-half sheets,
but since you can't buy half sheets you'll have to round up to
three sheets to sheathe that area. You can tell the spreadsheet
to do this by using the ROUNDUP function.
Be aware that this is different from formatting a cell to
simply round to the nearest full number, because the cell could
just as easily round down.
The ROUNDUP function can be found in many places within my
estimating template (Figure 5). For example, on tab 29-CSF the
spreadsheet calculates the number of 16-foot pieces of 1x8
cedar it will take to trim the rakes and eaves. The formula, in
cell 11G, reads:
Figure 5. The ROUNDUP function tells Excel
to round quantities up to the next full unit — the next
full piece, sheet, box, or pound. Don't format a cell to simply
round to the nearest full number; there's always the chance
that the program will round down and you'll come up short on
Note that the formula is telling the spreadsheet to take the
value from I2 (210 lineal feet of fascia) and multiply it by 1
plus the value in cell I11 (a 10 percent waste factor).
210 x (1 + .1)
210 x 1.1 = 231
We want to buy 16-foot pieces of stock, so we divide the 231
lineal feet of trim by 16. The formula now reads:
231/16 = 14.44
We can't buy 14.44 pieces of trim, which is why I've used the
ROUNDUP function. Note the comma followed by 0 after the 16,
which tells Excel to go to the next full number, which is 15.
If you were to replace that 0 with a 1, it would round up to
the nearest tenth, and the result would be 14.5.
Using the IF Function
Occasionally I'll have to make a choice between two or more
types of materials. For example, does the job call for 15-pound
or 30-pound felt? In a case like this, you can use the IF
function to quickly specify quantities.
Because you're working from a template, you'll have a line for
every type of material you might use on a job, even though you
won't necessarily use every material on every job. There will
also be a separate formula to calculate the quantity of each
material. So you need a way to tell the spreadsheet which
calculations to perform.
For instance, if you're estimating 15-pound felt, you don't
want the spreadsheet to also calculate the quantity of 30-pound
felt — you want one or the other.
I use the IF function in this situation (Figure 6). Cell E9 of
sheet 27-RC allows me to specify which weight of felt to
estimate. Entering a 1 means 30-pound felt; leaving a zero
indicates 15-pound. Below, in G15, the formula reads:
Figure 6. Whenever there's a choice to be
made between materials, the IF function allows you to specify
which to estimate.
This tells Excel, if E9 is a zero, to calculate the rolls of
15-pound felt by dividing the area of the roof to be shingled
(2,035 square feet, in cell E3) by the number of square feet in
a roll (432), multiplying by a waste factor (here, 0), then
rounding up. If I had typed a 1 in E9, the worksheet would have
calculated rolls of 30-pound felt, using the formula in
To avoid accidentally changing a formula in the template, I
"lock" the cells that remain the same for every estimate
(Figure 7). That way, an errant keystroke won't mess up the
template and affect calculations.
Figure 7. To prevent the formulas from
being accidentally changed, you can lock cells. First, select
the ones that need to be protected, pull down the Format menu,
and click on Cells (A). This will cause a dialog box to pop up.
Click the Protection tab, check the box next to "Locked" by
clicking on it , and click OK (B). Then select the cells that
need to remain unlocked and repeat steps A and B, only this
time make sure the box next to "Locked" is empty. Next, pull
down the Tools menu to Protection and click on Protect Sheet
(C). When the dialog box pops up (D), make sure the box at the
top is checked and then click OK.
The cells in the worksheet are color-coded. I use the yellow
cells to enter the lengths and areas I take off the prints;
these are the only cells that are intended to change. The
unshaded cells in the column labeled "Qty" contain formulas
that refer to the numbers in yellow. The numerical values in
the white cells may change but the underlying formulas do not.
Using the template is simply a matter of entering numbers in
the yellow cells and letting the white cells perform
Bill Lacey is a former lumberyard
estimator and the co-owner of Visual Dynamics, LLC, an
architectural drafting and design company in Racine,
Download for Easier Reading
As you read this article, you can open the author's estimating
spreadsheet and follow along. To download the spreadsheet, go