While our suggested best practice is to keep your data in the STACK platform and create detailed estimates and proposals directly from your takeoff information, we recognize that not every estimator works the same way. Microsoft Excel has historically been a component in the estimating process for many folks, and we recognize it is still commonly used. Excel is a great tool that not only organizes and analyzes data but also learns your patterns.
If you’re going to continue using the spreadsheet software, we want to provide you with the top dos and don’ts of presenting and analyzing your data. You could have minor errors that add up in the long term, or major ones, like in 2005 when a Kodak employee added too many zeros resulting in an $11 million overstatement. Excel with Excel with our recommended practices:
CTRL + Z These Mistakes, FAST!
It’s easy to make mistakes in complex spreadsheets containing multiple formulas, data sets, and multiple sheets. Undo these common mistakes in your Excel practices:
Don’t leave cells containing formulas unlocked
Your spreadsheets are full of precious data. With one click, your complex formulas or special formatting can disappear. Protect your work from unwanted changes by locking a cell or cell ranges. Go to: Format – Cells – Protection. Hot tip – if you share your spreadsheets with multiple users, you can protect the sheet so other users can’t unlock cells (Tools – Protection – Protect Sheet).
Don’t put multiple values in one field
Give data its own cell. Putting multiple values in one cell makes it nearly impossible to analyze, enter a formula, categorize, or chart. For example, separate quantity and material description into different rows.
Don’t forget to save versions for an audit trail
Excel can’t show you the history of document versions. Remember to save versions of your worksheets as project changes occur so you cover yourself from potential risk or litigation. Hot tip – if using the Office 365 or online version, there is a log feature so changes can be easily identified and tracked to the team member that made them. Cloud spreadsheets for the win!
Top 6 Tips and Tricks for Estimating in Excel
1. Absolute and Relative Values
There are two types of cell references in Excel: absolute and relative. When data is copied into another cell, absolute cell formulas will remain constant. For example, if you want to add 20% markup on materials and labor, you can add that fixed value into your cell formula.
Relative cells are the default reference, and those formulas change based on the relative position of rows and columns. You would use this to calculate price by quantity. Hot tip – F4 on your keyboard is a shortcut to switch between absolute, relative, and mixed cell references. Formulas: Absolute =$A$1, Relative =A1.
2. Choose from a List
You can create dropdown lists in cells using the Data Validation function. This restricts entries so only values you have preselected are applied to the sheet. This is a great feature when multiple users have access to a workbook. For example, if you’re inputting roofing material names, you can predefine metal, shingles, solar, slate, etc. Go to: Data – Validation – Settings – List.
3. Pop-Up Messages for Specific Data
You can choose to show an Input Message when a user highlights a specific cell, giving them guidance about the type of data you want entered. For example, if you’re inputting rates for labor, you can select a range from $20/hr-$80hr, and so on. Hot tip – you can also show an Error Alert when a user enters invalid data, further protecting your estimate from mistakes. Go to: Data – Validation – Input Message.
Bid Faster. Win More. Build Smarter.
Get your FREE account today to:
- Increase Team Efficiency
- Complete Estimates Faster
- Generate More Revenue
4. Use Predefined Selection Options
Excel defined names make it easy to refer to data sets, instead of writing out the cell name each time. It’s time-consuming referencing and typing the exact formulas in cells, especially if you have complex data sets. Giving meaningful names to ranges and individual cells will turn this:
=SUM($A$2:$A$10, $D$2:$D$10)
into this:
=SUM(PRICE, MARKUP)
Go to: Formulas – Define Name.
5. The AutoSum Formula Will Be Your Best Friend
This feature in Excel is probably the most used by estimators, as they need breakdown subtotals and totals of takeoffs, non-measured costs, and labor. In addition to SUM, the small dropdown arrow reveals additional formulas, like AVERAGE, MAX, and MIN. Go to: Formulas – AutoSum.