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.
6. Save Save SAVE
And save again. Seems obvious, but you must make it muscle memory. The risks involved with rework and lost data using on-premise software is high, hence why we recommend doing your estimates within the STACK platform. The good news? If you’re a Microsoft 365 subscriber, Excel will AutoSave your file every few seconds. Hot tip – CTRL + S is the shortcut to save. Go to: File – Save.
5 Quick Tip Functions for Estimating in Excel
1. Freeze panes
Lock specific rows and columns in place so they are always visible. Go to: View – Freeze Panes.
2. Filter
This function helps with checks and balances, quickly scanning through lists of data, and narrowing information when presenting to others. Go to: Data – Filter.
3. Conditional formatting for color coding data
Conditional formatting allows you to change a cell’s color based on data within the cell. For example, you can flag totals that don’t meet your price per square foot, ensuring you aren’t underbidding. Go to: Conditional Formatting – Highlight Cell Rules.
4. Add multiple rows or columns
If there is a document revision and structures are added to your job, you need to revise your estimate. To add multiple rows or columns, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”
5. Wrap text
Typing long copy into spreadsheets by default allows the text to continue forever. Make your notes (or novel) legible in the cell. Go to: Format – Cells – Alignment – Wrap Text.
Did You Know...
You can estimate directly in STACK? Our platform offers two types of estimates to cater to your business needs – Unit Cost Estimates and Material and Labor Estimates. With our regional cost data integration, you’re able to automatically pull pricing into your estimate. When you have calculated your selling price and profit, you’ll be able to create a custom, professional proposal within STACK!
If you’re still most comfortable using Excel, you can integrate the software to estimate with STACK. STACK’s Report Sync plug-in for Excel can be added through the Microsoft app store directly into your Excel software. Once installed, the plug-in brings up a list of cost-centered reports that are in your STACK account. With that data, you can map information out of your STACK project directly into your spreadsheet, never leaving Excel. Try it now for free!
