Examine a step-by-step approach to creating two sheets, one being a front-page total sheet and the other a backup detail sheet, for managing and formulating project costs more effectively. Learn how to filter, copy, and paste specific data between sheets, delete unnecessary columns, and add crucial new ones to pull in cost information for every specification section.
Key Insights
- The tutorial explains how to create a front-page total sheet and a backup detail sheet in a workbook for efficient cost management. This process involves renaming sheets, copying and pasting data, and deleting or adding columns as needed.
- Filtering is a crucial step in this process. It allows for the selection of specific data, like high-level totals of specification sections, which can be copied and pasted onto the total sheet. It is also used to clean up the data by hiding unnecessary items.
- The addition of new columns, such as 'cost per unit' and 'total cost', is essential for pulling in cost information for each specification section. This helps to calculate the total cost of each item on the job, like all the concrete, masonry, wood trim, etc., thereby formulating the total price for the estimate.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Now that we have exported to CSV, our CSV summary, we're going to go ahead and create two sheets in this workbook. One's going to be our front page total sheet and the next is going to be our backup detail sheet.
So let's go ahead and add a new sheet here and we'll put this sheet in front of the existing one and we'll make the existing one called the detail sheet and we'll make the first one called the cover sheet. We're just changing the name down at the bottom, clicking on the tab to rename and to create a new one. So with the lines on the detail sheet here, what we're going to do now is we're going to select in bulk the top level summary of the specification sections and we're just going to copy them over and paste them onto our total sheet.
Go ahead and find all of the line items that have a number in parentheses next to them as these are our high level totals of specification sections and we're going to go down here to grab the standard pattern wood trim, fiber siding cement and I'm just holding in the control button as I select each of these and that is what is allowing me to multi select but not select every single one in order. And we're going to get wood windows and cement stucco and gypsum board, going all the way down to ceramic tiling and hardwood flooring, sheet carpeting, plumbing fixtures and electrical. Okay, so let's go ahead and right-click on that grouping.
Oh no, I just messed it up. Sorry about that. I'm going to do it one more time.
Sorry about that. You know what, here's an easier way to do this. Let's go ahead and put a filter on all of these and let's just filter this subject column by, we're going to clear them all and we're going to filter by blank.
And filtering by blank allows us to just get all of the high level specification sections without subject lines and then we can just do shift, select all, right-click, copy and we can just go ahead and paste this back into our front sheet here and let's go ahead and paste the top section as well. We have our headings the same on both sheets. All right, so we'll expand this out.
We've got specification section, we've got subject, we've got measurement units. We can delete the subject column on the first sheet as we're not really going to need that. And we can actually delete this unit column as well.
I accidentally included that in the export because I thought we would need it, but we did have measurement unit that came along with our report so that is going to serve our purpose here. We've got our specification sections, we've got our total measurement per, we've got all of our units. Now let's go ahead and add a cost per unit column here and a total cost column.
So adding in these columns is going to allow us to pull in cost information for each of these specification sections and really come up with a total cost for all the concrete on the job, all the masonry, all the wood trim and allow us to get a total price for what this estimate is going to come out to. We're going to input those columns on the cover sheet and then we're going to go back into our detail sheet here and we're going to input those columns as well. So let's take the filter off, go ahead and clear it and then hit OK.
Oops, sorry. We're going to select all instead of clear it. We now have our detail sheet with all of our totals and let's go ahead and create those same two columns here.
We're going to do a cost per unit and we're going to do a total cost. I'm going to add those two columns in there. We're also going to go ahead and delete this unit column on this sheet as well because we did not need it.
And we'll go ahead and just re-choose all the items in the first row here, take the filter off and then put the filter back on so we have them all filtered and we can manipulate this sheet as needed. What we're going to do is go back to subject here and we are going to filter out the blanks. We're going to choose blanks and we're going to unselect it so they're not going to show.
And what this gives us is just all of my line-by-line takeoff items. And now what we can do is we can apply cost to this sheet and we can really start to formulate what this project costs for us to build.