Like most contractors, I’m always looking for ways to be more efficient and accurate when it comes to the paperwork needed in this business. There are a great many programs available that claim to fully automate your construction business. Most of them are either too expensive and/or too complicated for my small business. What I need is something easier to use and more flexible.
The tool I use the most is the spreadsheet. A spreadsheet program comes free with almost every computer purchased today. One of the best known is Microsoft Excel. You have to pay for Excel, but it could be worth it. A good free option is OpenOffice.org, which works similarly to Excel. But since I use Excel, that’s what I’ll be talking about in this article. The picture below shows how to locate the program on my computer. Yours will probably be similar.
Excel is a very powerful spreadsheet program. Don’t let the powerful part fool you. It is also one of easiest programs to learn and use. Unlike some powerful CAD programs that require you to spend hours in training and tutorials just to start using its features, any spreadsheet program can be used, in some fashion, very quickly.
The following picture shows the opening screen of Microsoft Excel. Depending on which operating system you’re using (Windows, Mac, etc.), and the version if Excel you have, your screen may look slightly different.
Here is the opening screen with some key menu items and headers identified for easy location:
A spreadsheet is nothing more than a database program with some unique features. The way a spreadsheet works is by a series of cell references. The cell reference is made up of rows and columns. The rows are numbered from 1 to 65536 and the columns are labeled from A thru IV. Each cell in this large matrix has a unique address, such as A1, P550 or IV65536. For you folks who understood geometry in high school this can be thought of as an X,Y coordinate in space. Now don’t stop reading, I won’t mention geometry again.
The picture below shows what a cell reference is. Follow down a column and stop at any row. Where the column and row intersect is the cell reference. I have typed the cell reference in a few cells to illustrate the relationship.
Entering text or numbers in a spreadsheet is straightforward. Place the square cursor on any cell, by using your mouse or the arrow keys on the keyboard, and start typing. When done typing, press the enter key. The text or number will be entered into that cell and the cursor will drop down one row.
In the picture above I typed the sentence into cell B3. It looks as if the sentence covers columns B through H. In reality, the entire sentence is in cell B3, it just spreads out across the other columns because there is nothing in the cells to the right of it.
In the picture below there is text in cells B3 and also D3. You can see the sentence in cell B3 stops at cell D3 where the new sentence starts. The sentence in D3 keeps going to the right because there is nothing in cells E3 through G3.
In order to see both sentences in their entirety we need to change the width of the columns. In the picture below I have highlighted the B column by left-clicking on the “B” in the column header, then I selected Format from the dropdown menu, then Column, then AutoFit Selection.
Here is the result:
It is helpful to set the width of the columns to what you want prior to entering your data. You can always modify them later to the exact width.
The height of the rows can be changed in the same way as the width.
The result of the row height command is shown below. I changed the height to 30 from 12.75, which is the default.
Now that we have a little background in formatting rows and columns, let’s look at formatting numbers, text, and cells. The format part of the menu is usually found in the upper right of your screen. It looks much like the format menu in Microsoft Word. In fact, they work almost exactly the same.
Let’s start by formatting a range of cells with a different background color.
Select a range of cells using your mouse. Here’s how: Press and hold the left mouse button while holding the cursor over a cell; now move down and to the right until you highlight the range of cells you want. Release the left mouse button and the selected area will be highlighted in a light grey color with a black border. Next, find and select the small arrow to the right of the icon of a pouring paint can in the top right of the menu bar.
Another drop down menu will appear showing a pallet of colors. Select the color you want, and the background color of the selected range will be changed.
The picture below shows the sequence. You can format one cell or a range of cells, an entire row or column, or a range of columns and rows.
Next, let’s take a look at how to format text and numbers in an Excel spreadsheet. By formatting text and numbers you can change the appearance of your spreadsheet to make it easier to read and understand. Excel has pre-defined number formats. Some of the most popular are: Number, Currency, Percentage and Fraction. You can format a number before or after it is entered into a cell. Just select (click on) your cell, go to the Format menu, and choose Cells. Here’s an example of formatting a cell for fractions:
Using the same procedure, you can format numbers and text to display as you desire. Change the size, color, and font of any text or numerical entry. Add patterns and borders to cells or ranges of cells. Change the alignment of numbers and text within a cell. You can center, left justify, or right justify your entries. Here’s an example of justifying text:
Let’s talk about formulas in a spreadsheet. Here is where the real power of this program lies. Remember, from the beginning of this article, a cell reference is the unique position within the spreadsheet—references such as A1, B66, and AB5025. A formula is simply an action performed relating two or more cells together. In the picture below I have entered numbers into four cells, B3 through B6. I center-justified those numbers and formatted them as Currency.
In cell B8 I entered the following formula: =B3+B4+B5+B6. This formula tells the program to add together the numbers in cells B3, B4, B5, and B6, and put the result in the cell that the formula was typed into—in this example, cell B8. You can see that the result of that addition formula is $746.75. Excel does the math for you.
Now, this is important: The equals sign (=) in front of the formula is mandatory! The equals sign tells the spreadsheet that the alpha-numeric text that follows is a formula, and not just a bunch of letters and numbers.
Another important consideration with formulas is the order of execution of mathematical operators (the signs for “plus,” “minus,” “times,” etc.). To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. For example, the following formula: =5+2*3 produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
In contrast, if you use parentheses to change the syntax, such as =(5+2)*3, Excel will add 5 and 2 together first, and then multiply the result by 3 to produce 21.
If you want to edit the contents of a cell, simply double-click the cell. If you want to delete the contents of a cell, simply select (click) a cell, and press the Delete button on your keyboard. What if you want to delete more than one cell? Say, in the above example, you want to delete cells B5 and B6 from your formula. Simply click and hold (click and keep the mouse button pressed down) on cell B5, and drag your mouse down to include cell B6. Press Delete, and they’re gone!
Here’s a quick and easy way to perform simple formula tasks:
|The Autosum button:|
This can be a really handy way to add up a lot of cells quickly. I’ll show you. (Note: the following screen shots are from a Mac. Don’t let that throw you off.)
Say you have a long list of numbers you need to add up, and you don’t want to write the formula out by hand.
You want the Total to be in cell B16. So, select (click on) cell B16, then click the Autosum button (it should be in your menu, up top somewhere). Excel will automatically select all the cells above and get ready to add them together.
Hit Enter (or Return) on your keyboard, and boom!, there’s the result.
Here’s something neat you can do with the Autosum function. Say you have a list of numbers you know you’re going to keep adding to, but you want to keep track of the Total as you go along. All you need to do is the Autosum trick we just learned, but farther down the spreadsheet.
Note that the highlighted section shows you the cells that are included in your formula. You can change those numbers to suite your needs.
Now, if you put a number in cell B15, Excel will automatically add it to the total.
If you put another number into cell B16, it would add that to the total, too.
These are just a couple of examples showing what you can do with the Autosum button. It’s definitely worth playing around with.
Now I’m going to create a simple spreadsheet using the features we have discussed in this article, and also some that will be covered in future articles.
As contractors, we generate bids/estimates frequently. This can be a very time-consuming process, and if you do a lot of the same type of work, it can become very repetitive. In the spreadsheet example below, I have created a simple form that can speed up this process.
With a little time and effort, you can create a similar form that works perfectly for your unique business needs.
The top part of the form has all the customer information, a job description, and some of the costs associated with this project. I selectively formatted various rows with color to make the whole spreadsheet easier to read. Additionally, I created another column, “Costs by Trade,” that summarizes each trade’s total costs.
Look at the bottom of the sheet and you can see some tabs labeled “HVAC Parts,” “Cabinets and Trim Parts,” and so on. These are different sheets within the same Excel “Workbook.” Much like a file folder with multiple pages, this is a very powerful feature. Notice the orange color on cell C20. I highlighted that to show the power of multiple sheets. In the following figure you can see the sheet named “Framing Lumber.” This sheet contains all the rough lumber and their respective costs. I intentionally made the list small and manageable, but you get the idea. All you have to do is input the quantity of each item you need for this particular job and Excel summarizes the total cost.
Remember the orange cell C20? In that cell on the “Summary Detail” sheet I typed the formula =’Framing Lumber’!E19. This formula instructs Excel to place the value of cell E19 in the Framing Lumber sheet into cell C20 on the Summary Detail sheet.
Create as many sheets as you need for your respective trades and costs. Then link those summarized costs to the Summary Detail sheet.
The following figure shows the bottom of our Summary Detail sheet. It shows a continuation of our project costs and the Sub-totals and Grand Totals of the project.
You can see that I entered my percentages for Mark-up and Profit. These are in different cells, and can be changed to suit your specific requirements, even from job to job. The formulas have been created to automatically recalculate when the percentages are changed.
Ok, now we have a nice accurate estimate with all the information you need to present to the customer. Of course, you don’t want the customer to have of this information, just the summarized prices. You could take all the information and retype it in another program. But why not create another sheet in this workbook that you can print and give to the customer?
What you choose to put on this form is totally up to you. All I did was to link data from other sheets in the workbook to the appropriate cells in the Customer Print Out sheet. It literally took me less than 5 minutes to create this new sheet.
Now I’ll show you how to format this sheet for printing. First, we have to define the print area.
Using your mouse, highlight the area you want to print. Then select: File > Print Area > Set Print Area. Then select: File > Page Setup to align the print area onto the paper in your printer. Depending on the size of your print area you may need to select Landscape mode, or fit the print area to more than one page tall.
By now, I’m sure you have realized the potential power of a spreadsheet program. With just a few of the techniques shown in this article you can create a form that can really help you in the day-to-day operation of your business. Of course, we have just scratched the surface of the power of this program. In future articles we will explore more advanced formulas, formatting, sorting your data, and adding charts and graphs to help you understand what the data is telling you.
If you’d like to download the spreadsheet seen in this article, you can do so HERE.
Chuck Kiser got his start in the trades in high school in Arizona. During school and the four years that followed Chuck learned a lot about the carpet and tile trade from his brother-in-law, and some residential plumbing skills working for a new construction builder in Phoenix. An offer from Uncle Sam put an eight-year gap in the trades while he played around with submarines and other forms of military transportation. After the Navy, Chuck entered into the Aerospace and Defense field, working in R&D for Advanced Composite materials. During this time Chuck returned to school to get his Undergraduate and Graduate degrees in Business and Management.
Ten years went by very quickly and, not wanting to live out of a suitcase for the rest of his life, Chuck re-entered the trades in the mid ’90s. Starting your own business is a challenge, but it was well worth it. Long hours of research paid off, and even after more than a few mistakes, it actually makes money—sometimes.
These days Chuck focuses on frame-to-finish residential carpentry, with the occasional remodel thrown in to keep things interesting. When not working, Chuck surfs the web trying to keep up with the changing construction industry, and the tools and technology that support it. Chuck and his wife Joan live in Palos Park, IL.