Using Excel's Basic Formula Functions to Create an Project Estimate

In the Construction Industry estimating projects is extremely important. Money is the driving force in most industries and the construction industry is no different. It may be one of the most money driven industries in today's culture. For this reason estimating is crucial to determining scopes of projects. The owner has a set budget that they must stick to and by knowing how close they are to their budget drives their decision making process. If there is a large chunk of the budget left over they will know they can build bigger and better. To do an estimate you must first do a quantity takeoff of the certain material you are looking for. In this example I will use concrete. Then setting up a table is the best way to lay out the areas and data you need. It is possible to do this all by hand but with larger projects the process can get messy. This tutorial will walk through some of the basic equations that Excel can use to make this process much easier.

To start off you will need to create a table. For this example I will do a simple concrete takeoff. Create a table that looks like the one in the figure above. To create the heading (i.e. Concrete Estimate) highlight the cells you want the heading to be in. Then click the merge and center button to change the individual cells into one bigger cell. You can then input your heading/title into your table. Your table may look slightly different depending on the type of information you have. For example you may have more or less areas with different names but a table that looks similar is what you need. 

Next put a border around your table to make it look more professional and easier to read. To do this click on the button with a box around it in the picture above. It is the box to the right of the underline button. This will give you a drop down menu which will give you many options for your borders. Select the option that says all borders. This will give you a table that looks like the one above. There are many other options to format a table. This example is just a simple basic format. To see more formatting options you may go to the format as table option. Now we can actually put some data into the table.

Now lets actually put some measurements into our table. For this example I will use a few different numbers representing a certain project. Measure out each of the different areas. So I measured out different areas of concrete. Then find the depth for these certain areas and put all the information into the tables. Make sure to put all the information in the same units. For example I am using feet as my units. 

Now your at the point where using formulas in excel makes this a lot easier. You could manually calculate each different area but that would take more time and be more difficult. To use a formula first click the cell which you want to calculate in, as shown above. Then to create a formula in the cell hit the equal sign (=) key. Then you will create the formula. Start by clicking your length. Then to multiply use the asterisk (*) and click the width cell, then again the asterisk (*) and the depth. Then hit enter. Now rather than entering that formula into each cell to find the area you can just copy and paste the cell into each area cell. Excel uses references to the formula cell so the next row will reference the numbers in the same row and give you the correct number. 

In my example we are dealing with concrete. Concrete is normally calculated in cubic yards. However I did my measurements in feet. Therefore my area is in cubic feet. To convert this we must divide the cubic feet by 27 to get cubic yards. We can again use the formula function to get these numbers. Simply hit the equal sign again, followed by clicking on the area you just solved for, and divide (/) by 27. Then you can copy and paste this formula into the cells below.

Excel automatically inserts as many numbers into the cell as possible. For our calculations we do not need numbers with eight significant digits. We need numbers with no more than two decimal places. So we need to format these numbers. To do this select all the numbers in the column. Then under the number subheading above select the drop down menu where it says general. You can then select a choice that says number, as shown in the second picture above. After clicking on that the data should narrow down to two digits after the decimal place.

Now we want to find the unit cost of the concrete. To make this easier I created a cell that includes the cost per cubic yard of concrete. For this example I will say the company I'm getting the concrete from charges $90 per cubic yard. You will see in the next step why I'm using this separate cell for the cost. We will also want to format this cell to show currency. This is done the same as the step before but using a different format as shown.

We now calculate the area cost by multiplying the area by the unit cost. So once again we click in the cell we want the information posted in. Then the equal sign (=) followed by clicking the Area(yd3) cell and the Cost Per Cubic Yard cell. Now this will work for the top row of data, however if you copy and paste the data all the way down any cell below the first one will not have the correct data. This is because, again, Excel uses cell references to determine qualities. To fix this we must use an absolute reference. To use an absolute reference highlight the reference you want to remain the same (Cost Per Cubic Yard, cell I3 in the formula) and hit F4. This should bring up dollar signs ($) into the formula, as shown in the second picture above. Now you may copy and paste the formula down and it will work for every cell.

Now that all of our information is in the table there is only one thing left to calculate, the total cost of the concrete. Rather than adding each individual cell in the column we can use the sum function to add together many numbers. To do this, again hit equal (=) in the cell where you want your data. Then type "sum" and a drop down menu will appear. After typing sum use the left parenthesis to start the function. Then you can highlight the cells that you want added together. For this example my data is in cells G3-G8. You may then use the right parenthesis to complete the function. This use of the sum function is just a basic example of the things it can do. To see the more advanced ways to use the sum function, follow the link below.

Your table is now complete and should look similar to the one above. You can do this type of table for various different applications and use the same type of formulas. Now if you only need this one table you are complete. Let's say for example I not only want the concrete estimate of the project but the estimate of the total project. The next steps will show you how to do this.

For this example I have set up other similar tables using steps 1-10. Now I want to add them together to make one project cost. Using the sum function in this case is possible, just not as functional as before. Since you are collecting data from all over your spreadsheet you would have to click every cell anyway so simply adding the up is just as easy. To do this I have merged a few cells together and created a small table with the heading Total Project Cost. Now using similar functions as before simply hit the equal sign (=) in the cell you want to start the function. Now click the total cost for each subcategory (i.e. Concrete, Electrical, Ductwork, etc.) with an addition (+) sign in between each one, as shown above. Then hitting enter will reveal your total project cost (or whatever total your calculating). There are endless options of equations, formulas, charts and tables you can use through excel, this was just one of the basics I find myself using often.