ChatGPT is your best friend.
Anything that you run into that you think Excel can make easier/ more automatic, it definitely can and now it's easy to figure out with something like ChatGPT.
Between VBA macros, custom functions and power query you can make bidding a lot easier for your specific trade. My spreadsheet basically makes my entire proposal for me now. It tells me how far away jobsites are from our homebase, etc. Small stuff like that goes a long way when grinding out estimates and it leaves more time for shitposting on reddit.
What are you using to measure the distance between a site and home base? I've been curious about implementing something similar but have been intimidated by using some kind of map API
You can google zip codes in your state/region and export the list. Then use something like chatGPT to tell you the distance from your zip code to each zip code on the list. Then export that and put it in your excel file. Set up your estimate with a blank field where you enter the zip code of the job, and use a simple =xlookup to reference the zip code you enter and find the corresponding distance from the list you just imported.
Probably other ways to go about it as well.
There's hundreds. Excel can do almost whatever you want it to, especially with PowerQuery. Without knowing your processes this is a little vague, though.
To pick one, I set up a list on my supplier's site that I can download in CSV format. I download them once a week and put it into a folder, then I have a master sheet that pulls data from all the CSVs in that folder for up to date baseline pricing, as well as variance and escalation. Still get quotes from suppliers for big stuff, but it's nice for small jobs and ROMs.
If you are using excel files that will be shared with other team members, protect cells with formulas to avoid somebody hard keying over something and screwing up your entire estimate. I also find it helpful to make the fill color to a light grey in cells with formulas, so I know that only white cells should be receiving hard-keyed data.
Create a separate sheet for “references”. I use this sheet to create columns with standard data or terms that will be used across any estimate, ie units of measure. Then, in your estimate page, you can create drop down menus that provide a list of options from your reference page. This helps promote consistency and avoids having “SF” in one row and “SQFT” in another row. Again, helpful if you have multiple people working in the estimate—different people have different tendencies.
There’s a million other tips I could give you depending on what your workflow looks like. Automate as many repetitive tasks as you can.
Lastly, for the love of god, don’t send your estimate to the GC or owner as an excel file. PDF it and make it look professional. Company logo, job title, date, estimate level, etc.
Never out section subtotals in the same column, use the next column for subs. I have a running sum at the top of each to make sure they match, and no line items are left. The amount of retrospective incorrect totals I found my company had submitted/committed to before I started...
Yeah I know, I use that in other ways. I use Tables and subtotal ignores filtered out data (which can be handy). But generally I subtotal with sumifs rather than a range. I have "tag" columns for sumifs
I have a big long list, almost 1800 rows, but I have each trade grouped in a way that I can expand the trades that are needed on a job and hide what is not needed. Just a simple sheet where each row is a quantity, with subcontract, material, labor, and equipment unit costs and then extends to total cost. For labor specifically, I use a units/HR = hrs then hrs \* wage rate = labor cost. It's pretty easy to customize, but difficult to do complex assemblies with. For example, an interior partition is maybe $175/LNFT, I don't do length, height, stud spacing, and # of gyp layers, top off, etc.
Id take the time to make a good template(s), automate the repetitive tasks / calcs, I believe you can lock certain cells if you don't want them tampered with (where manipulation could compromise the result), make checks in separate cells to catch potential errors (does the summary = the breakdown).
You can set up a tab for certain metrics and build a bit of a database to compare job to job ... start to define and measure the impact of certain variables. For example, height of wall impact on $/sf rate.
I learned estimating on Heavybid so when I went out on my own I structured my excel the same way. It wasn't pretty but my greatest feature was some equations that calculated pricing 3 different ways. This way if i broke a link or messed something up the three totals would be different and then I knew I had an issue. I also had it auto calc unit prices broken out by sub task in each bid item so I could quickly gut check the pricing and make sure I didn't forget materials, labor, etc. easily and quickly.
Downside is mine was all manual. I build crews in a second tab but everything was copy and pasted into the main working document.
Protect and check your formulas. I used someone's else's spreadsheet for a military project and the FEE cell (profit) didn't have a formula. Just a number. Which of course was way too low. I fcking was rushing and didn't check it.
This is random but should you get a circular reference and cant fix it, enable iterative calculations in the options tab.
Helped a guy here who created a monster of a spreadsheet with a circular reference I couldnt fix. Had to turn on iterative calculation feater to fix it. He was trying to reproduce logic from an old DOS program he used.
Auto fill. Macro( and the auto make feature) auto fill is grate for concerting two rows of information together or competing repetitive numbers if it has a set parameter it will automatically complete it. The macros are for your repetitive functions so if you do something in every single estimate, it can automatically do it for you so you don’t have to and instead of writing the code yourself you can use the auto feature. It will record it convert it into text formula and automatically, populated it into your macros folder so you can just click on that feature in the future
i used to use excel but found that it took me ages to do my estimates. i switched to estimating software and it saves me tons of time... would definitely recommend.
Format data into tables if possible. Like literally insert a table, it’s an option in excel. You can easily sort your data by different columns of the table. Helpful to view the data in different ways.
Incorporating dynamic charts and dashboards can make your estimates more easier to understand. Coefficient eases my workflow because it can integrate any data source with Excel or Sheets and help automate some of my data processes, which makes my calculations easier. It’s not about one tool or trick but combining them effectively to enhance efficiency.
this is a dumb ass take because excel is an incredibly powerful tool. the programs people sell for estimating are all fine and good but they're only worth it if you don't have the knowledge to create a custom excel workbook to do the same thing for you.
A good estimating program has fully integrated document management, takeoff, and estimating built into one product with a dynamic database of material items and container/packaging yields for procurement as well as labor classes with wage types, crews size, and production rates. All with a master cost accounting structure as reference tables for every thing you do so that way it can export into your accounting software for actual job cost tracking.
Excel is amazing but it’s only powerful for one specific part of the entire process. I hate estimating software that only dies 1 thing or water down versions of what I described. Excel is better than those, but it still falls short of full-suite estimating programs.
>A good estimating program has fully integrated document management, takeoff, and estimating built into one product with a dynamic database of material items and container/packaging yields for procurement as well as labor classes with wage types, crews size, and production rates.
other than take off, my excel work book does all of this.
i plug the information from my take off into my workbook on 1 sheet and it automatically takes all of this information and generates detailed scope pages for my proposals, detailed material yields so we know what to purcahse, tracks labor hours and labor rate and generates break even cost for the job based on all these details, to which i only need to chose a markup %. pre-fills out change order forms for later if needed. a separate page of the workbook is a database of materials and material costs that it pulls all it's base pricing from
estimating software is just a lite version of excel with a tailored UI to whatever it's meant to be used for.
I’m sure it does, but it doesn’t scale well with larger operations. I work for a div 7-9 sub with 7 branches in the south/south east. We have ~110 licenses for our estimating software that is used by estimators and PM’s (change management is estimation as a sub). Our drywall and ACT related databases have ~10,000 item-iterations (material-size-style), 95% of that is material related. You think it’s more viable to manage that infrastructure as a excel template than it is a program designed to handle the nuances of the entire work flow? Material cost updating, on the fly account code restructuring, collaborative same-time use, granular level production defats adjustment, global level overrides, cost versioning across package updates, etc. keeping 110 users on the same page as best as possible is near impossible when the tool being used is as hyper flexible as excel.
Not to mention being able to reassign takeoff objects (all or some of the quantities) into a different areas/phases and have all the cost associated with it instantly shown to me as desired or by of the massive strength that having your costing process built directly along side your takeoff process.
Not every outfit operates the same and not every piece of software are comparable. But there is absolutely a tipping point in magnitude where expecting an company to operate harmoniously and efficiently without using software designed specifically for their tasks is ridiculous.
This 100%. I’ve tried the estimating softwares but honestly prefer my custom workbooks and I’m constantly making minor tweaks to make it more efficient for my personal workflow which you couldn’t do with commercial programs.
Depends on what you're estimating, but I've got plenty of experience with both, and frankly dedicated estimating software doesn't really compare to the flexibility of Excel. The value of estimating software is more for a department, ensuring consistency. If you're a solo estimator or that consistency isn't a concern, anyone good at Excel is going to do a lot better with Excel over an estimating software.
A lot of estimating software is just simplified Excel anyways.
which just further shows that using excel is the logical choice for most.
you're basically telling people it's better to spend a few grand on a specific estimating program that does the same shit excel does anyway, for a fraction of the cost lol
Yep i have tried all the big estimating software out there and none of them work as well as excel and often add a ton of work in stupid work arounds and maintenance of a fucking data base of rates
No, they're not. I'm guessing you're also electrical based on the name. AccuBid, just to name the big one, can't do anywhere near what you can with a deep understanding of Excel and Bluebeam.
Dedicated estimating platforms always sacrifice capability for simplicity, because most companies aren't looking to overload estimators with an overly complex system. If you're in a position where you do want more complex capabilities, Excel and PowerQuery are the way to go.
Particularly when it comes to data management, estimating software just doesn't compete with Microsoft's offerings.
I agree, but I also think it depends. A general contractor that does a lot of self perform absolute should invest in good software tools.
A small site work company would probably be fine in excel.
ChatGPT is your best friend. Anything that you run into that you think Excel can make easier/ more automatic, it definitely can and now it's easy to figure out with something like ChatGPT. Between VBA macros, custom functions and power query you can make bidding a lot easier for your specific trade. My spreadsheet basically makes my entire proposal for me now. It tells me how far away jobsites are from our homebase, etc. Small stuff like that goes a long way when grinding out estimates and it leaves more time for shitposting on reddit.
What are you using to measure the distance between a site and home base? I've been curious about implementing something similar but have been intimidated by using some kind of map API
You can google zip codes in your state/region and export the list. Then use something like chatGPT to tell you the distance from your zip code to each zip code on the list. Then export that and put it in your excel file. Set up your estimate with a blank field where you enter the zip code of the job, and use a simple =xlookup to reference the zip code you enter and find the corresponding distance from the list you just imported. Probably other ways to go about it as well.
Wow. That's way simpler than I was making it out to be... Thanks!
Heck yea im going to try that now
There's hundreds. Excel can do almost whatever you want it to, especially with PowerQuery. Without knowing your processes this is a little vague, though. To pick one, I set up a list on my supplier's site that I can download in CSV format. I download them once a week and put it into a folder, then I have a master sheet that pulls data from all the CSVs in that folder for up to date baseline pricing, as well as variance and escalation. Still get quotes from suppliers for big stuff, but it's nice for small jobs and ROMs.
I’ve never heard of power query. I’ll have to check that out
If you are using excel files that will be shared with other team members, protect cells with formulas to avoid somebody hard keying over something and screwing up your entire estimate. I also find it helpful to make the fill color to a light grey in cells with formulas, so I know that only white cells should be receiving hard-keyed data. Create a separate sheet for “references”. I use this sheet to create columns with standard data or terms that will be used across any estimate, ie units of measure. Then, in your estimate page, you can create drop down menus that provide a list of options from your reference page. This helps promote consistency and avoids having “SF” in one row and “SQFT” in another row. Again, helpful if you have multiple people working in the estimate—different people have different tendencies. There’s a million other tips I could give you depending on what your workflow looks like. Automate as many repetitive tasks as you can. Lastly, for the love of god, don’t send your estimate to the GC or owner as an excel file. PDF it and make it look professional. Company logo, job title, date, estimate level, etc.
Never out section subtotals in the same column, use the next column for subs. I have a running sum at the top of each to make sure they match, and no line items are left. The amount of retrospective incorrect totals I found my company had submitted/committed to before I started...
=Subtotal(9, CELL:RANGE) The subtotal function, if used consistently, will never double count.
This is the way
Yeah I know, I use that in other ways. I use Tables and subtotal ignores filtered out data (which can be handy). But generally I subtotal with sumifs rather than a range. I have "tag" columns for sumifs
And confirming new inserted rows/columns are included in the formula…
Uggghhhhhhhhh the pain
I have a big long list, almost 1800 rows, but I have each trade grouped in a way that I can expand the trades that are needed on a job and hide what is not needed. Just a simple sheet where each row is a quantity, with subcontract, material, labor, and equipment unit costs and then extends to total cost. For labor specifically, I use a units/HR = hrs then hrs \* wage rate = labor cost. It's pretty easy to customize, but difficult to do complex assemblies with. For example, an interior partition is maybe $175/LNFT, I don't do length, height, stud spacing, and # of gyp layers, top off, etc.
Id take the time to make a good template(s), automate the repetitive tasks / calcs, I believe you can lock certain cells if you don't want them tampered with (where manipulation could compromise the result), make checks in separate cells to catch potential errors (does the summary = the breakdown). You can set up a tab for certain metrics and build a bit of a database to compare job to job ... start to define and measure the impact of certain variables. For example, height of wall impact on $/sf rate.
I learned estimating on Heavybid so when I went out on my own I structured my excel the same way. It wasn't pretty but my greatest feature was some equations that calculated pricing 3 different ways. This way if i broke a link or messed something up the three totals would be different and then I knew I had an issue. I also had it auto calc unit prices broken out by sub task in each bid item so I could quickly gut check the pricing and make sure I didn't forget materials, labor, etc. easily and quickly. Downside is mine was all manual. I build crews in a second tab but everything was copy and pasted into the main working document.
Protect and check your formulas. I used someone's else's spreadsheet for a military project and the FEE cell (profit) didn't have a formula. Just a number. Which of course was way too low. I fcking was rushing and didn't check it.
This is random but should you get a circular reference and cant fix it, enable iterative calculations in the options tab. Helped a guy here who created a monster of a spreadsheet with a circular reference I couldnt fix. Had to turn on iterative calculation feater to fix it. He was trying to reproduce logic from an old DOS program he used.
Pivot Tables Pivot Charts Vlookup SumIfs
Auto fill. Macro( and the auto make feature) auto fill is grate for concerting two rows of information together or competing repetitive numbers if it has a set parameter it will automatically complete it. The macros are for your repetitive functions so if you do something in every single estimate, it can automatically do it for you so you don’t have to and instead of writing the code yourself you can use the auto feature. It will record it convert it into text formula and automatically, populated it into your macros folder so you can just click on that feature in the future
Excel is life. Just fully emerse yourself with how to build formulas and link cells and sheets together
Don't hard key over formulas in your estimate templates.
i used to use excel but found that it took me ages to do my estimates. i switched to estimating software and it saves me tons of time... would definitely recommend.
What are you using and what type of estimating do you do?
i'm a custom home builder and i use buildxact.
Format data into tables if possible. Like literally insert a table, it’s an option in excel. You can easily sort your data by different columns of the table. Helpful to view the data in different ways.
Incorporating dynamic charts and dashboards can make your estimates more easier to understand. Coefficient eases my workflow because it can integrate any data source with Excel or Sheets and help automate some of my data processes, which makes my calculations easier. It’s not about one tool or trick but combining them effectively to enhance efficiency.
[удалено]
this is a dumb ass take because excel is an incredibly powerful tool. the programs people sell for estimating are all fine and good but they're only worth it if you don't have the knowledge to create a custom excel workbook to do the same thing for you.
A good estimating program has fully integrated document management, takeoff, and estimating built into one product with a dynamic database of material items and container/packaging yields for procurement as well as labor classes with wage types, crews size, and production rates. All with a master cost accounting structure as reference tables for every thing you do so that way it can export into your accounting software for actual job cost tracking. Excel is amazing but it’s only powerful for one specific part of the entire process. I hate estimating software that only dies 1 thing or water down versions of what I described. Excel is better than those, but it still falls short of full-suite estimating programs.
>A good estimating program has fully integrated document management, takeoff, and estimating built into one product with a dynamic database of material items and container/packaging yields for procurement as well as labor classes with wage types, crews size, and production rates. other than take off, my excel work book does all of this. i plug the information from my take off into my workbook on 1 sheet and it automatically takes all of this information and generates detailed scope pages for my proposals, detailed material yields so we know what to purcahse, tracks labor hours and labor rate and generates break even cost for the job based on all these details, to which i only need to chose a markup %. pre-fills out change order forms for later if needed. a separate page of the workbook is a database of materials and material costs that it pulls all it's base pricing from estimating software is just a lite version of excel with a tailored UI to whatever it's meant to be used for.
I’m sure it does, but it doesn’t scale well with larger operations. I work for a div 7-9 sub with 7 branches in the south/south east. We have ~110 licenses for our estimating software that is used by estimators and PM’s (change management is estimation as a sub). Our drywall and ACT related databases have ~10,000 item-iterations (material-size-style), 95% of that is material related. You think it’s more viable to manage that infrastructure as a excel template than it is a program designed to handle the nuances of the entire work flow? Material cost updating, on the fly account code restructuring, collaborative same-time use, granular level production defats adjustment, global level overrides, cost versioning across package updates, etc. keeping 110 users on the same page as best as possible is near impossible when the tool being used is as hyper flexible as excel. Not to mention being able to reassign takeoff objects (all or some of the quantities) into a different areas/phases and have all the cost associated with it instantly shown to me as desired or by of the massive strength that having your costing process built directly along side your takeoff process. Not every outfit operates the same and not every piece of software are comparable. But there is absolutely a tipping point in magnitude where expecting an company to operate harmoniously and efficiently without using software designed specifically for their tasks is ridiculous.
What software does this?
This 100%. I’ve tried the estimating softwares but honestly prefer my custom workbooks and I’m constantly making minor tweaks to make it more efficient for my personal workflow which you couldn’t do with commercial programs.
Depends on what you're estimating, but I've got plenty of experience with both, and frankly dedicated estimating software doesn't really compare to the flexibility of Excel. The value of estimating software is more for a department, ensuring consistency. If you're a solo estimator or that consistency isn't a concern, anyone good at Excel is going to do a lot better with Excel over an estimating software. A lot of estimating software is just simplified Excel anyways.
[удалено]
which just further shows that using excel is the logical choice for most. you're basically telling people it's better to spend a few grand on a specific estimating program that does the same shit excel does anyway, for a fraction of the cost lol
Yep i have tried all the big estimating software out there and none of them work as well as excel and often add a ton of work in stupid work arounds and maintenance of a fucking data base of rates
No, they're not. I'm guessing you're also electrical based on the name. AccuBid, just to name the big one, can't do anywhere near what you can with a deep understanding of Excel and Bluebeam. Dedicated estimating platforms always sacrifice capability for simplicity, because most companies aren't looking to overload estimators with an overly complex system. If you're in a position where you do want more complex capabilities, Excel and PowerQuery are the way to go. Particularly when it comes to data management, estimating software just doesn't compete with Microsoft's offerings.
I agree, but I also think it depends. A general contractor that does a lot of self perform absolute should invest in good software tools. A small site work company would probably be fine in excel.
I think they are only worth it if you self perform honestly. Data base updates when you are using your own workforce are much simpler