BOQ Format in Excel Free Download with Formula: Complete Guide

Buy Construction BOQ Template at 99 INR only 

What Is a BOQ Format in Excel?

• Sl. No. (Serial Number): To keep track of the sheer volume of line items.
• Description of Item: A detailed explanation of the work. You shouldn’t just write “Concrete.” You must specify “Providing and laying M25 grade RCC for columns including compaction and curing.”
• Unit: The unit of measurement as per Indian Standards (IS 1200). Common units include cubic meters (cum), square meters (sqm), running meters (rm), kilograms (kg), or numbers (nos.).
• Quantity: The accurately measured quantity extracted from the architectural and structural drawings.
• Rate (₹): The unit rate, which generally includes the cost of material, labor, machinery, and sometimes overheads (if not calculated separately at the end).
• Amount (₹): This column is auto-calculated using a formula (Quantity × Rate).
• Remarks: A crucial space for referencing drawing numbers, specific brand requirements, or tender clauses.

• Section-wise Subtotals: Grouping costs by trades like Civil, Electrical, Plumbing, and HVAC.
• Overhead, Profit, and Contingency: Automatic percentage-based calculations applied to the base cost.
• GST Calculations: Compliance is mandatory. Most construction works in India attract 18% GST, which needs to be calculated accurately on the final taxable amount.
• Grand Total: A neatly formatted final figure that represents the total estimated project cost.

Step-by-Step Guide to Create BOQ Format in Excel with Formula

Step 1: Open Excel and Set Up Your Workbook

1. Open Microsoft Excel (or Google Sheets, though Excel is preferred for heavy tender files).
2. Create a brand new file and immediately save it with a clear naming convention, such as ProjectName_BOQ_v1.xlsx.
3. Set up your sheets. A professional QS never uses just one sheet. Create three distinct tabs:
   • BOQ Main: This is where the exhaustive item-wise breakdown will live.
   • Summary: A high-level overview for clients or management to see trade-wise totals.
   • Rates: (Optional but recommended) A backend sheet for rate analysis to feed your main BOQ.

Step 2: Design the Core Columns

• A1: Item No.
• B1: Description of Work
• C1: Unit
• D1: Quantity
• E1: Rate (₹)
• F1: Amount (₹)
• G1: Remarks

Step 3: Add Formulas for Amounts

1. Click on cell F2 (the first row where data will be entered under “Amount”).
2. Enter the multiplication formula: =D2*E2
3. Press Enter. It will currently show 0 (or a dash) since D2 and E2 are empty.
4. Click on the bottom-right corner of cell F2 (the fill handle) and physically drag this formula down to row 100 (or however many rows you anticipate needing).
5. Select column F entirely. Right-click, go to Format Cells, choose Currency, set the symbol to ₹ (Rupee), and restrict decimal places to two.

Step 4: Add Section-Wise Subtotals

1. After listing all your Earthwork items (say, from row 2 to row 10), leave row 11 for the subtotal.
2. In cell B11, write “Total for Earthwork”.
3. In cell F11, use the SUM formula: =SUM(F2:F10)
4. Bold this entire row and add a top and bottom border to make it visually pop out as a subtotal. Repeat this process for every subsequent section.

Step 5: Include Overhead, Profit, and GST

1. Total Base Cost: In cell F155, sum up all your section subtotals. Example: =F11+F30+F55...
2. Contractor’s Profit & Overhead (e.g., 15%): In cell B156, write “Add: Contractor Profit & Overhead @ 15%”. In cell F156, calculate it: =F155*0.15
3. Subtotal before Tax: In cell F157, write =F155+F156
4. GST (18%): In cell B158, write “Add: GST @ 18%”. In cell F158, calculate: =F157*0.18
5. Grand Total: In cell B159, write “GRAND TOTAL PROJECT COST“. In cell F159, write =F157+F158. Bold this heavily.

Step 6: Add a Summary Sheet

1. Go to the Summary tab.
2. Create a simple table with “Description” and “Amount”.
3. Instead of typing the amounts manually, link them dynamically. Click the cell where you want the Civil Total, type =, then navigate to the BOQ Main sheet and click on the cell containing the Civil Subtotal, and hit Enter.
4. Do the same for the Grand Total. If you change a single quantity in the main sheet, your summary will automatically update.

Step 7: Format and Protect Your Template

1. Freeze Panes: Go to View > Freeze Panes > Freeze Top Row so your headers stay visible when scrolling down massive item lists.
2. Protect Formulas:
   • Select the entire worksheet (Ctrl+A). Right-click > Format Cells > Protection tab. Uncheck “Locked”. Click OK.
   • Now, selectively highlight only the cells that contain formulas (like the Amount column and all Subtotal rows). Right-click > Format Cells > Protection tab. Check “Locked”.
   • Finally, go to Review > Protect Sheet. You can add a password if you wish. Now, users can only enter data into the Quantity and Rate columns without destroying your automation.

Key Excel Formulas Used in BOQ Templates

1. Amount Calculation

2. Subtotal for Sections

3. Overhead and Profit Margins

4. GST Calculation

5. Conditional Formatting (Advanced)

Free vs Paid BOQ Excel Templates – What Should You Choose?

Free BOQ Templates

Paid Professional Templates

Where to Download BOQ Format in Excel with Formula

1. Construction Estimator India (Highly Recommended)

• Features: Flawless auto-calculations for amounts, complex section-wise subtotals, overhead, profit margins, and dynamic GST. Multiple integrated sheets for civil, electrical, plumbing, and a master executive summary.
• Compliance: Structured similarly to the CPWD standard specifications and IS 1200 norms.
• Access: Available at nominal rates (₹99–299) with instant digital download and lifetime access. Browse our store to find the exact fit for your next tender.

2. SiteSetu Free Templates

3. Government and Professional Resources

4. Educational Blogs and LinkedIn

Common Mistakes in BOQ Excel Templates (and How to Avoid Them)

1. Missing or Incorrect Formulas: The most dangerous mistake. Users accidentally type a hard number (e.g., typing “5000” in the Amount column) instead of ensuring the cell reads =Quantity*Rate. If the quantity later changes to reflect revised drawings, the amount remains stubbornly at 5000, causing massive budget shortfalls.
2. No Section-Wise Subtotals: Dumping 400 items into one continuous list makes the BOQ impossible to audit. It makes it incredibly hard to track costs by trade. You won’t know if your civil works are over budget or if the plumbing is too expensive. Always group and subtotal.
3. Ignoring Overhead, Profit, and GST: Many free templates simply sum up the material and labor cost and call it the “Grand Total.” If you submit this for a tender, you will have to pay the 18% GST out of your own pocket, completely wiping out any profit and causing severe cash flow issues.
4. Unprotected Formula Cells: Sending an unprotected BOQ to a junior engineer to fill out quantities is a recipe for disaster. They will accidentally hit backspace on a crucial summary formula. Always lock your formula cells.
5. Poor Formatting: A BOQ is a financial document. If there is no currency formatting, no bolded subtotals, and frozen header rows, the sheet becomes an unreadable wall of numbers. A poorly formatted BOQ screams “amateur” to clients and architects.
6. Using Generic Free Templates for Strict Tenders: Government tenders often have very specific formatting requirements. Trying to wedge your data into a rigid, badly designed free template can lead to technical rejection of your bid.
7. Not Validating Quantities and Rates: An Excel formula only calculates the numbers you give it. If you accidentally input 1000 cubic meters instead of 10, the template will dutifully multiply it, leading to a wildly unrealistic total. Always do a “sanity check” on your final numbers.

FAQs

Conclusion