In Part 3 of this tip, we show how saving macros, linking worksheets and importing and viewing PDF files can make Microsoft Excel a powerful cost estimating tool. (The previous section of this tip, Expanding Excel for construction: Calculations, offered hints for performing common construction cost estimating calculations.)

Tip #8: Automating tasks and creating controls

(Note: In order for macros to work when they are not digitally signed, you have to enable them. You should only enable macros on documents from trusted sources. To enable macros to run in Excel that are not digitally signed SELECT>TOOLS>MACRO>SECURITY>MEDIUM, then CLOSE and REOPEN the file and SELECT ENABLE MACROS when prompted.)

Macros allow you to record your keystrokes and mouse selections and then play them back using a shortcut key. You can also create buttons and assign macros to those buttons.

Let’s assume you have a budget sheet that includes two columns labeled Budget Item and Difference. The Difference column shows you the difference between your budgeted amount and the amount spent to date. You want to be able to quickly pull out only those items that are over budget (shown in a negative number).

First, if macros are not already enabled you have to enable them.

1. SELECT>TOOLS>MACRO>SECURITY>MEDIUM
2. Close and then re-open the spreadsheet
3. When prompted SELECT ENABLE MACROS

Next, create a macro that sorts the Difference column.

1. SELECT>TOOLS>MACRO>RECORD NEW MACRO
2. ENTER MACRO NAME, SHORTCUT KEY and FILL-IN DESCRIPTION
3. HIGHLIGHT HEADER of column to be sorted
4. SELECT>DATA>AUTO FILTER
5. SELECT CUSTOM from the drop down menu over the column you want to sort
6. SELECT>IS LESS THAN from the left drop down menu
7. ENTER 0 in the right drop down menu space
8. SELECT>OK
9. CLICK STOP RECORDING button or SELECT>TOOLS>MACRO>STOP RECORDING

Suppose you assigned “s” as your shortcut key. (Note: The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.)

Now when you hold down CTRL and hit s the Difference column is sorted so the only items shown are the ones with negative numbers. As you probably noticed there are many more options for sorting, not only directly under the AUTO FILTER drop down menus at each column header, but also under the CUSTOM dialog box you access from the AUTO FILTER drop down menu.

Next, you’d like to be able to activate the macro from a button somewhere on the sheet.

1. CHOOSE>VIEW>TOOLBARS>FORMS
2. SELECT>BUTTON on Forms Toolbar
3. Using the Crosshair DRAW a button
4. RIGHT CLICK>SELECT EDIT TEXT> TYPE desired label
5. RIGHT CLICK Button and CHOOSE Assign Macro
6. From the list SELECT the macro you just recorded; SELECT OK

Now, whenever you hit the button the list is sorted to show just the budget items with negative numbers.

Tip #9: Linking cost estimating with bidding

Using the power of sheets with linking, you can get the results of your estimates to show on your bid form. That way, as you work, the bid form stays current with the customer costs you are calculating and is pretty much ready to send to the client once you have completed the estimating process for the project.

One way to make this all work is to design a bid form right on its own sheet within your construction cost estimating workbook. You can use the DRAWING toolbar included with Excel to make lines and even include artistic-looking text. You can also INSERT pictures and logos.

Once you have your bid form created, navigate to your estimate sheet:

1. SELECT the cells you want to link to and CLICK the COPY Symbol on the toolbar
2. SELECT the cells you want to link from (Sheet 2 in this example)
3. CLICK PASTE LINK from the drop down menu beside the PASTE symbol in the toolbar
4. (or) SELECT>EDIT>PASTE SPECIAL>PASTE LINK

You can also include cells that have formulas, but no calculated quantities yet. That way, as you work, the totals will fill in the bid form. Plus, if you change things the changes will also be updated in the bid form.

Tip #10: Easily incorporating forms in PDF format

Jared Hawk at Hawk Education recommends that, if you have a PDF proposal or bid form, complete with your company logo and contact information, you can easily paste it into a sheet without having to recreate the form manually. He says you have to have the full version of Acrobat to do this. Simply COPY the OUTLINE of the form and paste it into an Excel sheet. It sits on top of the cells -- but, because it is transparent, any entries in the cells behind it are visible. Of course you will need to move the form out of the way until you have the cells behind linked, and then move the form back into place and line up the cells so they appear in the right places on the form.

About the Author: Duane Craig learned journalism courtesy of the US Department of Defense and subsequently edited a weekly newspaper. He has owned and operated a landscaping company, built custom homes as a managing superintendent and managed a multi-million dollar apartment renovation project. He describes building as a fascinating activity and these days he especially likes writing about it. You can read construction news, views and commentary at Duane's blog, Construction Informer.