Excel is an excellent software tool for managing the details of any job, large or small. Each Excel file — called a workbook — is like an electronic three-ring binder with many pages in it. Each page — called a worksheet — is set up with rows and columns, much like you'd find in a paper ledger book.
To use Excel instead of a paper system for something like tracking job costs, you essentially just type the information into the computer instead of writing it on a piece of paper. Then, you keep all your project worksheets in a single Excel workbook, just as you might keep all your project notes in a single three-ring binder.
If they're so similar, you might ask, why go through the trouble to convert your tried-and-true paper system to an electronic spreadsheet? The answer is that having the data in an electronic format will save you time and make it possible to more effectively track your finances.
This article assumes you know how to start Excel and how to save and print documents in a Windows or Macintosh program. Please note: The Excel screens used here depict the most recent version of the program, Excel 2007. If you already have Excel 2003, there's no reason to upgrade — all the techniques used in this article apply to any version of Excel. The main difference (in the context of this article) is that in Excel 2007, a "home ribbon" replaces the "toolbar" used in earlier versions.
A Simple Worksheet
A worksheet has numbered rows and lettered columns. The intersection of a row and column is called a cell; each cell has a name, such as A1, B1, C1, A2, A3, A4.
In the example (below), the workbook is named "Job Workbook" (at top, circled in red). It's the single file that will contain all your projects. The worksheet being viewed is named "Smith" (at bottom, circled in red). And the selected cell address is C12.
The "Smith" worksheet uses simple formulas to track the cost of materials, the amount charged to the customer, and the builder's profit. An addition formula adds up the invoices for materials as you accumulate them. A subtraction formula calculates your profit. You always know where you stand financially on a project, without having to pull out the calculator every time you add an invoice.
Once you've mastered the basics, you can make your worksheets as elaborate as you like. For instance, the items can be broken down into detailed categories such as decking, framing, and railing. In a single worksheet, there are several million cells, so it's nearly impossible to run out of space for the material lists you will be creating.
Creating a Template
Templates are big timesavers. When you set up a worksheet as a template, you can simply copy it to a new worksheet each time you start a new job. The template will have a generic name — like "Template" — so it can't be confused with the job worksheets, which will be identified by the applicable homeowner's name.
To begin, run Excel and open a new workbook; cell A1 should be selected automatically. Enter the text as follows:
The cursor will move down one cell (to A2), automatically.
Type Street or Lot #:
Again, the cursor will move automatically, down to A3 this time. Continue to enter text as shown in the worksheet below. To move to another cell, you may press Enter, Tab, or Arrow keys. You may also click the mouse to choose a cell.
Widen columns to fit more text. Cells that you use for describing items may need to be wider than those used for numbers.
Place your mouse pointer on the line between column header A and B. The mouse pointer will change its shape to a thick black double-headed arrow.
Drag slowly to the right to widen the column. Do the same for any column that needs more width.
You can also widen columns without dragging the mouse. Click any cell in the column, then click the Format tool on the Home ribbon, then click Column Width. Type a bigger number in the column-width box and click OK.
Check cell size. You'll want to be sure that the cells where you keep track of details such as material quantities are large enough for your purposes, so enter some sample text and numbers before creating the formulas (below)
Excel ignores zeros after the decimal point. To show the cents, simply format the cell to show two decimal places. Click the cell. Click the Increase Decimal tool on the Home ribbon. (Excel 2003: Click Increase Decimal on the Formatting toolbar.)
Add formulas. The final step, after entering the sample data above, is adding the formulas to keep track of the money spent on material and labor. Note that there are no spaces allowed in a formula. Also, Excel will change small letters into capital letters automatically for cell addresses.
Click cell F9 to start the first formula
To determine profit, you subtract cost (e9) from price (d9), so type =d9-e9
The result of the formula (16,395) will appear in cell F9.
Select cell F10 and type =d10-e10
Repeat these steps for every row that you're tracking.
The result of a formula appears on the worksheet. The formula itself may be seen in what's called the formula bar, near the top of the worksheet (below, circled in red).
The last formulas we'll enter will add up the price, cost, and profit figures in columns D, E, and F. It's a good idea to leave a few blank rows for future material and labor items, so we'll put the totals in row 20. We'll also use a shortcut: Rather than entering an addition formula three times — to total each of the three columns — we'll select all three cells (D20, E20, F20) and just enter the formula once.
Scroll down to view row 20
In cell A20, type Total
Click cell D20, and hold down the left mouse button as you drag to the right to also select cells E20 and F20
Click the AutoSum tool (Σ, below, circled in red) on the Home ribbon
Save the template. Once the formulas are entered, the sample data can be removed to create the final clean template. To remove the sample data:
Select cells A9 through E10, then press Delete
Take care not to delete the formulas in column F and in row 20. The completed template worksheet should look like the example below.
Note: Sheet2 and Sheet3 may be removed at any time, as they are not needed. Right-click the Sheet2 tab and choose Delete. Press the Delete button in the confirmation box. Do the same for Sheet3.
To name the worksheet "Template":
Double-click the Sheet1 tab (lower left corner of the worksheet)
Copy the template. When you want to start a new project for a homeowner, you'll need to copy the template worksheet to a new worksheet:
Right-click the Template tab (above, circled in red)
Choose Move or Copy…
Click (move to end) and click the check box to Create a Copy
The new worksheet will be named "Template (1)." To rename it with the homeowner's name:
Double-click the Template(1) tab
Type the homeowner's name
A Few Tips
There are lots of tricks and shortcuts for creating and using worksheets, such as copying formulas, which may be discussed in a future article. But here are a few other things that will be helpful for you to know now.
Add rows to an existing worksheet. Many jobs will exceed the 10 or 11 rows we have allowed for listing materials. There are several ways to add rows. Perhaps the easiest is to click anywhere in a row, and from the Home ribbon select Insert Sheet Rows (top right corner of spreadsheet below, circled in red. (In Excel 2003, choose the Insert menu from the toolbar and select Rows.)
Hide columns to create a record for the homeowner. At any time during the job, you may give the homeowner an updated record of the project costs by hiding the "My Cost" and the "Profit" columns.
It's easy to do. Click any cell in the column you wish to hide. On the Home ribbon, click the Format tool; choose Hide & Unhide, then Hide Columns. Excel will print only what appears on the screen. You may also hide the extra, empty rows using the same method. After printing, use the same technique to unhide and continue using the worksheet.
Print a record. You can print this record for the homeowner, with extra rows and columns hidden (below), and it will look like the printout (also below).
Multiple jobs. If you have more than one job for a single homeowner, you might want to start a new worksheet with a different name, such as "Smith-SidePorch." You can also combine the jobs on one worksheet, with an extra column to identify the job (below, circled in red).
Back up your files. I recommend that you back up your files to a thumb drive at least weekly, if not daily. Thumb drives (also called flash drives) are no bigger than a highlighter pen, and prices start at less than $20.
Kathy Slivka is a computer trainer from New Milford, Conn.