All
After seeing Basswood start the door hanging tutorial, I got to thinking about a tutorial on using Excel.
So here it goes.
If you already use Excel this thread isn’t for you.
I am going to write this for people who don’t have a clue about spreadsheets.
I am just an average wood butcher with just enough knowledge of spreadsheets to get by. But I remember the first time I opened a spreadsheet (works) I was lost. All these lines on the page and what was I supposed to do.
So if you want to know how to use a preadsheet to do estimates read on and I will try to guide you thru designing your own spreadsheet.
I will post examples with every post.
Rich
Replies
All
Open your own copy of Excel. If you have Microsoft office then you should have it in the MS Office Suite.
Go to All programs, click on MS Office and then choices will appear. Click on Excel and it will open a sheet.
Excel is a series of cells arranged in columns (vertical) and rows (horizontal). You can add, multiply, average or manage any kind of information.
Start in the top left hand column and name your columns. Materials, Price, Quanity, and Total. Open attachment to see example.
All
Next I am going to change column width.
Highlight (from now on I will use HL) any cell in column A.
Click on format, click on column, change with to 20. Press enter or click on OK.
Leave B and C alone. Change D to 14.
Change E to 5
Column F change to 20. Columns G and H leave alone.
Change column I to 14.
Rich
All
In this post I am going to write a list of materials on the left and labor functions on the right. For every material (more or less) there should be a labor action.
And then I put prices in the column B.
Rich
All
I have to leave for a few minutes to let my son's dog out to do his business.
I will try to get back to posting.
I have 23 steps I want to teach in Phase 1.
Rich
All
I wrote out materials to start a house or addition. Plate, joists, subfloor, Ext. walls and sheathing.
I am using i-joists but I forgot, rim board.
This is a good chance to show inserting a row.
HL plywood A4, click on insert, click on insert row. and now you have a new row for rim board.
DW says it's time for bed, I will try to continue in the morning.
I will also show you what the end product is going to look like in #23. But stay with me and learn the steps to get there.
Rich
Thanks for the thread.
I'll tune in tomorrow.
dont stop on my account. even though I do use excell, knowledge is power. keep it coming
All
Here is where we start using formulas to have Excel work for us.
I am going to have excel multiply price x quanity.
HL D2, press =then click on B2, type*(which means x) then click on C2. Hit enter.
In the dialog bar just above this worksheet you will see the formula you are creating.
You can also manually create a formula in the dialog bar. Or you can change the type in any cell, say in the materials column.
Now do the same thing for labor. Click on I2 type= then click on G2*H2 enter. a 0 should appear.
Rich
All
Using fill. This is a term Excel uses to fill the cells below with the information in the cell above.
Click on D2 move cursor to bottom RH corner until a + appears. Click and drag down until you get to D10. Release.
You have just filled all the cells with the formula in a cascading fashion.
Row 3 is now B3*C3. Row 4 is now B4*C4.
Now do the same thing with the labor total column. Start in I2.
I used the rate of $40/Hr enter that in G2. Us fill again and put $40 in that column.
You can HL any cell and see what the formula is in the above dialog bar.
Rich
All
Now I am going to total or columns.
HL D11, click on Sum in the tool bar above. It looks like a roman numeral E.
When you do that Excel will put a flashing border around the range of cells that are to be totaled. And the formula will appear in the cell D11. Press enter and it's done.
I am going to wait for a couple a steps before I total Labor and # of hours.
You can also HL D11 and manually write the formula in the dialog box if you want but is is faster to use sum. Formula would be SUM(D2:10)
When you hit SUM you can change the range of cells by clicking and dragging. Say we wanted the sum to be D4: D10
Rich
All
Now I am going to add tax and markup. HL D12 press = type *.27 enter.
Tax is 7% and markup is 20%
You can do this in sperate cells if you wish.
Rich
All
Now I am going to total Materials, Labor and # of hours.
In HL D13, type =, HL D11 type + then HL D12. Press enter
Now go to I 13and use sum to total the column
Do the same with # hours in H12
Rich
going a little fast, you need to explain thet the term "=" tells the program that a forumla is in that cell and all formulas start with "="
Brownbag
Good point.
( ) brackets mean that Excell treats this as 1 number say (B2*B3) is treated as 1 number.
I am not doingmuch proff reading. This is alittle time consuming as it is. In about 10 minutes I am going to get Tim up and give him a bath and get ready for church. Tim is home for the weekend.
Rich
All
Add currency $.
I want the numbers in the columns B D G and I to be shown in $.
HL the range of cells, start at the top click and drag, then hit the $ sign above.
To undo $ from a cell go to Edit, clear then formats and that will eliminate the $ format.
In the exmaple I have column D changed and column C HL and ready to hit $.
Rich
All
Now I am going to insert 10 rows to teach you how to insert rows.
Obviously you are going to need more rows to add materials and labor operation.
HL row 11 thru row 21 with click and drag.
Go above and click on insert. go to Rows and click.
Now you have extra rows to work with and all the fomula have moved down.
But the total Materials in D22 is still only showing D2:D10. So use fill to out the formulas in D and do Sum again in D22.
Yes I could have had the extra rows earlier but then you wouldn't have learned to insert rows and check for formulas and praticed fill.
You may start with worksheets that have 15 rows and expand into worksheet that have hundreds of items.
Correct the formulas in G,H and I using fill. And check your sum formulas.
Rich
Rich
All
I have to take a break for a while and get Tim and myself ready for church.
This my the last post until this evening. We'll see how the day works out.
Tell me if this is helping anyone. I will do this until i get to #23 worksheet.
After that i can show other things Excel can do or if no one is interested I'll drop it.
Thanks
Rich
All
Now I am going to change the font and make the totals appear in bold type.
HL 3 rows 22, 23 & 24 columns A-I
Go to the tool bar where is says 10 with an arrow pointing down. Click on the arrow and font size choices will appear.
Click on 12 and click on bold.
Rich
All
Now I am going to add color
HL D2 - D21, click on the arrow next to the paint bucket, a selection of colors appear. I use gray to indicate the cell has formulas in it.
So if the cell is gray do not enter data or #s.
Now the paint bucket shows gray below. That is the color you will get if you HL a cell and click on the the paint bucket.
You will notice that I did the same with labor totals and total # of hours.
Rich
All
I like to color my labor and materials totals for easy identification.
HL the cells you want colored, make a color choice and click.
Rich
All
Now I am going to use copy and paste.
HL Total Materials A24-D24
click on copy, just to the right of the scissors on the tool bar.
Click on F25 just the total for Materials and click on paste ( it looks like a clipboard.)
You will notice that the right formula for our purposes did not transfer over. we sill deal with that in the next post.
Rich
All
So we need to change the formula in cell I 25.
HL I25 hit = HL D24 and hit enter.
Rich
All
Tha's all for now.
Rich
Thanks for your effort Cargin. So far, you haven't gotten into anything that I didn't learn myself during that grueling learning process I went through last fall/winter LOL! Keep up the good work and don't feel like you need to get it all done fast. If you spread it out over a month, it will still be a great thread and it won't overload you. If you start going slow enough, we might be able to chip in a bit and help with pictures and answer questions. Sometimes a picture is worth a thousand words. Bob's next test date: 12/10/07
Jim
during that grueling learning process I went through last fall/winter LOL!
Just trying to make BT a place to learn and share.
When I first opened a spreadsheet, I said now what? I didn't have a clue what to do.
If I had your pdf button i would be able to show some screen shots that might help explain a procedure.
Thanks for the advice.
Rich
You can always capture your screen using cntl/prt scrn. That copies the screen to the clipboard. Open paint and hit paste. Then, edit your picture anyway you want and save as a jpg. Here's a picture of one of your last spreadsheets that you posted. I clicked on one of the boxes to show how the formula opens up. Bob's next test date: 12/10/07
I always liked the color in your sheets, but was too lazy to look up how to do it. Now I know!
Be aware that on large spreadsheets the background color will balloon the size of them. The large size slows down a lot of the processing power when working within the program. When my spreadsheet got too slow, I stripped out the non essential colors until I was ready to finalize it. Colors helped me immensely on the large spreadsheet. Bob's next test date: 12/10/07
That's good to know. I've got an older computer and it has trouble keeping up some times.I'm always making a spreadsheet, not using it for awhile and then getting lost and screwing up my formulas. I'm hoping some color might help.
Most spreadsheets have a method that you can protect specific parts of so that the data or forumula's can't be changed..
.
A-holes. Hey every group has to have one. And I have been elected to be the one. I should make that my tagline.
Thanks Bill, that's good to know. I've just learned just enough to get by. I need to brush up on some of the other features.
Craig
To protect a sheet go to tools, got to protection.
Then it will give you a bunch of options. You have to turn it off to make changes.
I don't use it much.
So I can't teach you much about it.
Rich
Cool. Maybe I won't mess up my sheets and have to redo them as much.
Craig
Good to hear from you again.
Keep watching. I'm going to spill out what limited knowledge I have on the topic.
I learned from your sheets last year on how to use multiple sheets.
Thanks
Rich
All
Now I am going to total the job.
Write Total in F26.
HL I26, HL I24 type + HL I25 hit enter
Now You have a total for the job.
Rich
All
Time to correct a mistake.
F26 says Total Materials.
Excel assumed when I typed Total that I was typing Total Materials. I didn't catch it.
Now I can just type over it. Or I can go up to the dialog bar at the top and backspace out materials.
Rich
All
Using color and font again.
HL F26- I26 go to font size change to 14, hit bold, go to color and choose a color. I chose dark blue.
Rich
All
Sorry I forgot to post attachment.
Now you will notice my total cell says #####.
That means I chose to big of font for my column width.
No problem just HL the cell go to font size and change to 16.
Rich
All
Now I am going to dress it up alittle.
HL two rows, 1 and 2.
Go to insert, click on insert rows.
add information, job name, company name and date
Change font size to 14 and add color.
Rich
All
Now you have a basic worksheet.
Save as house, or additions.
Add the materialsyou would use and the labor functions you would go thru to install those materials. Giving each labor job a # of hours.
As you can see I can do everything in 2 hours, big or small. LOL
Now open up the sheet again. And HL all the materials, quanity, price,
Press delete.
Do the same with # of Hours and labor functions.
Now you have a blank worksheet template. Fill it in for deck building. Or building a garage, or basement remodeling.
If you are doing basement remodeling you don't need shingles for fascia in your worksheet.
Rich
All
Now take your blank worksheet. Delete everything on the labor side.
Delete the totals and tax for materials.
In order to get rid of the colors you have to HL the colored area and go to the paint bucket. Select No fill at the top.
Not save as a material list template.
Rich
All
Now open both House template (#23) and the Materials List template.
HL the Materials (column A) and the prices (B)
Click on copy, open the materials list template and click on paste.
Now e-mail this to your lumberyard salesman and as him to update your prices and e-mail it back to you.
Save the e-mail. Cut and paste the new materials and prices into your House worksheet and you have quickly and easily updated your prices. Even you have the figured already. With cut and paste you have updated everything.
Rich
Here's another one I was working on in trying to figure out how much to bid for my company's services for a gov't contract. I didn't get the contract because I wanted a reasonable profit for quality work. The contractors who got the contract were willing to run a different type of operation, and I could not compete by my own standards.
The second one shows some pics embedded, if it works.
More samples.
Sasquatch
Thanks for sharing the worksheets.
I like your use of color to Highlight different areas of work.
I always like to see how other people think.
Why the pictures in that one worksheet?
Rich
The pictures were just a test. The spreadsheets I posted were basic starting points. The final versions were a bit more complicated and detailed. I just wanted to see how pics worked out in a spreadsheet. I figured they might be good to project some interest to a client. Before I closed my business, I was doing extensive spreadsheets with options, AutoCAD LT files of the projects, pictures of work in progress, and then putting it all on a CD and giving it to the customer.
Graphics are always helpful if they are well-chosen.
Sasquatch
The pictures came thru alright.
I never thought of using pictures in a spreadsheet.
I rarely show my spreadsheets to customers.
I always use it as a worksheet and take the numbers off it to a Word document for an estimate or Bid.
Rich
I recommend you think about using it as a presentation tool. A picture is worth...
You don't have to give the customer all of the information. You can have spreadsheets for you and spreadsheets for the customers. I chose to have full disclosure.
Too many times, this resulted in no work for me. On the other hand, I think I was better off without that work. I know that sometimes the client will take your info and use it as a tool or instrument of negotiation to get a better deal from another contractor. Basically, you give them a little information and they can use it for a decision or use it to pursue other options.
Bottom line: This is like being nice to people. Some will in turn be nice to you and others will see it as an opportunity to take advantage. I like to be nice and let the consequences play out. Not everybody can afford to do that.
Sasquatch
I find that people just don't understand them.
I have used them as an estimate several times when people were trying to make a decision between 2 or 3 different choices.
And i thought i did a logical spreadsheet.
I usually have to spend some time explaining.
And some of my spreadsheets are too long and too wide to print out on a single sheet of paper.
I like my estimates or bids to fit on one page if possible.
In billing, I used to give customers alot of information. Had some problems.
My old lumberyard manager gave me advice to not give them so much information.
That was years ago. And I have had fewer problems with billing. That a a few other practices like billing weekly on larger jobs.
Rich
All<!----><!----><!---->
Okay, I'm going to show a couple more things you can do with Excel<!----><!---->
I am going to show you how to use multiple sheets within the same worksheet.<!----><!---->
Open your basic worksheet #30. I'll post that below.<!----><!---->
If you want you can cut and paste it for yourself.<!----><!---->
To Cut and Paste, open a blank Excel sheet. On BT open #30. HL the whole sheet (click and drag from the bottom Rh corner to the upper LH corner), hit copy then open your blank worksheet and hit paste.<!----><!---->
HL the whole page, go to Format, go to columns, click on Auto Fit Selection, that will change all the column widths for you. (I learned that one from you while doing this thread. I told you I am not very computer smart.)
Now open the roofing worksheet #32 I will repost that one for you too.
HL the whole roofing worksheet. Bottom RH to top LH. Click on copy.
Open #30, at the bottom click on sheet #2.
In the top right corner HL and click paste.
Format, columns and and click on Auto Fit. Then it should look like #33.
Rich
<!----><!---->
All
Now with #33 open.
We want to insert the subtotals from the roofing worksheet into our main worksheet.
In cell D21, type = then open sheet #2, HL the subtotal for materials and press enter.
Do the same with labor.
I usually use a different color to HL the different sheet totals being moved into the main sheet.
I do this with bathroom worksheets, vinyl siding worksheets and so forth. I keeps your main worksheet from getting cluttered up.
Right click on sheet #2 and you can rename it. Or move it before or after other worksheets.
When I did this I noticed that the materials total did not change. My sum formula did not go down to D21 so I manually changed that.
Rich
All
I am going show you how to freeze panes.
This is handy when you have a large spread sheet but you want to your headings in view.
Highlight the row below the row you want to keep in place. Go to window and click on freeze panes.
#35 is a screen shot of the HL and window operation.
#36 is the freeze panes in action. You can work with the worksheet on BT. You can scroll down the sheet and leave the headings in place.
Rich
All
In this post I am going to show you how to do side by side comparisons.
Open two excel sheets of the same format.
For this I will use #30 (30' x 40' house) and #31 (30' x 60' house).
Go to Window as click on Compare Side by Side. See screen shot.
Screen shot #2 shows you what it looks like. Then you can scroll down together and compare item by item the different costs of the project.
I will attach #30 and #31 if you want to copy and paste and then try it on your own.
Rich
Two more, should be self-explanatory. These sheets are just starting points, and are sometimes just lists and sometimes use numbers too.
Now you will notice my total cell says #####.That means I chose to big of font for my column width.No problem just HL the cell go to font size and change to 16.OR
go up to format, cell, alignment and then click the box shrink to fit
brownbagg
Always 2 ways to skin a cat.
I'll give that a try.
Rich
All
Okay this is were using Excel gets exciting to me.
I took our basic worksheet and I reconfigured it alittle. Not going to go thru the steps with you.
I added what I call Data cells above, where I can enter the LF and SF of a job.
I am assuming for the purposes of demonstration that we are estimating a 30' x 40' house with the gable end being 30'.
I also added wall height even though for this demonstartion i didn't need to. But I will need it when I start figuring siding.
Yellow cells are where I add data, gray cells have formulas. By entering 3 numbers I have generated Ext. Wall LF and SF, and floor SF.
Off those 3 number I can figure all the materials below. I actually only need 2.
Rich
All
Now I am going to figure Sill Seal.
In the Sill Seal quant. column I HL hit = then click on Ext wall LF and I come up with 2.8 rolls.
Rich
All
But you say to me that I have to buy Sill Seal in full rolls.
Then I will change the formula to use roundup.
Which looks like this =roundup(D6/50,0)
Rich
All
For sill plate I used Exterior Wall LF.
If I am concerned about waste the I use a formula like D6*1.1 to figur 10% waste.
In this example I as going to use 16" centers and take the eave length (B3*.75)*C3
Or the (eave length x .75 ) x the gable length. Remember ( ) means Excel treats it a one #.
Rich
All
In this example I just figure the rest of the column.
You can HL each cell and see the formulas.
So when the HO's DW says I changed my mind i want a house that is 60 x 30 instead.
Just change 2 numbers and give them and at least you have the materials figured.
I know it's not always that simple in the real world, I am just trying to show you how to harness the power of a spreadsheet.
Rich
All
Now take the basic worksheet with the data area above and do a roofing worksheet.
The roof areas use Excel to just be a calculator to figure SF. On my regular roof worksheet I have 10 roof areas.
If you HL any range of numbers Excel will give you that amount in the lower RH corner of the worksheet. So if 2 of my roofs were 4/12 and the others were 8/12 if could quickly figure that just by HL a range of cells.
If you put the cursor on sum in the lower RH corner and right click it will give you other options such as Average.
I total the eave and rake just for my convience. I enter those manually because they are so variable.
The eave LF and the valley LF are both used to figure the Ice and water shield.
The tear off portion uses the # of squares to figure the tonnage and the amount that I need to charge for the landfill. All I have to do is enter the # of layers.
Rich
Edited 9/20/2008 11:05 am ET by cargin
For those without MS Office, Google provides a spreadsheet program for a price you can not refuse -- FREE
Faulted
And there is Open Office
http://www.openoffice.org/
I've got all three and Open Office is my default. I got tired of the Google version. I dumped google mail too. I hate Microsoft, so Open Office won by default. It's a great free program though. Im very happy and thankful that Imerc turned me onto it. He also helped me find AVG which solved my antivirus program. Bob's next test date: 12/10/07
I use Excel for many things and especially like the fact that I can create a project sheet and then reuse it for years and years. I just cut and paste the parts I need and put them in a new sheet.
For example, I had a spreadsheet set up for building decks. There was a row for each type of material in the deck along with price, tax, overhead, markup etc.
As time went by and I worked on a different deck, I only had to change quantities to match the new requirements and prices to keep up with fluctuations in the market.
Another nice trick is to present two or three options to a customer. For instance, you can do an estimate for a cedar deck, then copy and paste it into two other locations and change the numbers for Trex or treated.
I have also done comparisons of this type where I would add options into a base estimate. This way it is easy to explain where the money is going. Often, when a customer sees how changes affect a project, it makes decisions easier.
One example of this is to show labor remains essentially the same when changing a few features, such as the quality of a window. If the customer sees that the labor cost is a significant part of an estimate, he may not be so concerned to pay an extra hundred bucks for a significantly higher quality window.
Sasquatch
You hit on a couple of key advantages of using Excel (or any spreadsheet).
As time went by and I worked on a different deck, I only had to change quantities to match the new requirements
Another nice trick is to present two or three options to a customer. For instance, you can do an estimate for a cedar deck, then copy and paste it into two other locations and change the numbers for Trex or treated.
Would you be comfortable sharing some of your worksheets with us?
I would love to see how someone does it differently.
Rich
Edited 5/4/2008 2:57 pm ET by cargin
I'm pretty much retired, at least for the time being, but I think I may still have some old sheets around.
I'm going to put this one in just to see how it works. It is pretty basic.
One page is one method of figuring completion of various tasks for bank payments.
The next one is a starting point for me in considering the building a new house in the county and the city. I just update the numbers to match the situation once I find a lot and get some subcontracting bids.
"If you already use Excel this thread isn't for you."
Unless you probably use it 'wrong' and/or are stupid like me!
Go nuts! Love to learn something here.
"Research is what I'm doing when I don't know what I'm doing..."
Waters
I would love some one to chime in and teach me a thing or two.
I know how to use about 1% of Excel.
Rich