Modifying the Template The individual worksheet tabs on the estimate templates are protected so that the only that can be changed are the data input areas described above. The rest of the cells are write protected to prevent the formulas from being accidently changed. Some minor changes to the template are allowed without having to unlock the worksheet protection. Other changes will require unlocking the worksheet first. Making minor changes without unlocking the worksheets. The template users are allowed to increase or decrease the row heights to expand the size of the input areas. To do this, simply use the and click on the input cell or cells you want to change, and then right click on the numbered left margin of the spreadsheet; a pop-up menu will appear. Select the Row Height option and an box will appear where you can change numeric value of the row height. An alternative method of changing row heights is to simply click-and-drag on the line between the numbered rows in the left margin of the spreadsheet. This method is faster, but less precise than changing the numeric value for the row height. Template users are also allowed to format the input cells without unlocking the protection features. This can be used to change fonts, font size, font type, color, alignment, wrap text etc., which is sometimes helpful to get descriptions to fit into the input areas, or to highlight a specific line item. It can also be used to increase or decrease the number of digits after the decimal point. Making other changes that require unlocking the worksheets. UNLOCKING THE TEMPLATES: When the templates are first downloaded the password for all of the individual worksheets is the same. The initial password is the word open with no capital letters. To unlock a worksheet, right click on the tab and select the Unprotect Sheet option from the pop-up menu that appears. In the dialogue box that appears type the word open and hit the carriage return key. The worksheet should now be unlocked and you can make any changes that are necessary. CAUTION: Once the worksheets are unlocked it can be very easy to inadvertently make changes to formulas or accidently delete something that could have an
Change the “Total Bid Items 1-XX” title of the worksheet bottom line to reflect the correct number of Bid Items. Modify the formulas in the Total Bid Items 1-XX line, for each column on the worksheet bottom line to include the newly added bid item cost totals. You may want to adjust the page breaks on the Project Cost Summary worksheet. The worksheet is currently formatted to five of the Bid Items per page in the landscape format. Try to keep all of the lines for an individual Bid Item on the same page. The best way to change page breaks is to use the page break preview viewing mode in Excel and physically click and drag the hard page breaks to the desired location. CREATING ADDITIONAL PROJECT COST SUMMARY WORKSHEETS: This is a very easy process. Right-click on the Project Cost Summary tab and select the Move or Copy option from the pop-up menu that appears. Click on the Create a copy box in lower left corner of the dialogue box that appears Select the location in the workbook you want the copy inserted by choosing one of the tabs in the Before sheet: window. This location would normally be the tab immediately after the current Project Cost Summary tab. Click the OK button and the new worksheet be created. Rename both Project Cost Summary tab names to identify the bid items on each tab. This can be done by appending the item numbers to the current tab description (i.e. 1-10, 10-20, etc.) You should probably change the current bottom line title on each worksheet from “Total” to “Subtotal” and add a new bottom line to the last worksheet that totals the entire project. REMOVING LINES FROM THE INDIVIDUAL ELEMENT MINI-SPREADSHEETS: This is probably one of the most common modifications made to the estimating templates and is very easy to do. It is best to not actually remove the lines, but to hide them instead. This way you run less risk of damaging the template and they will be available later if you need to increase the level of detail in the estimate. First, unlock the individual worksheet as described above. Select the line or lines you want to hide by mouse clicking on the numbered line in the left margin of the worksheet. This will select the entire line. To select more than one line at a time click on adjoining lines while holding the shift key on the keyboard.