AI Skill Report Card
Analyzing Construction Quantities in Excel
Markdown--- name: analyzing-construction-quantities-in-excel description: Analyzes construction quantity survey (QS) data in Excel and generates structured cost/measurement formats. Use when a quantity surveyor needs to process BOQ (Bill of Quantities) data, build measurement sheets, calculate material/cost quantities, or generate standardized reporting formats for a construction project in Excel. ---
Quick Start14 / 15
Standard QS workflow in Excel: Analyze raw data → Create structured format → Generate final output (BOQ/summary)
Step 1 (Analyze): Import site measurements/drawings data into a raw data sheet
Step 2 (Create): Build a Bill of Quantities (BOQ) template with formulas
Step 3 (Generate): Produce summary report (cost abstract, material schedule)
Minimal BOQ formula pattern:
EXCEL=Length*Width*Height ' Volume (Concrete, Excavation) =Length*Width ' Area (Flooring, Plaster) =Quantity*Rate ' Amount =SUMIF(Category, "Concrete", Amount) ' Category-wise total
Recommendation▾
Add an example showing a bad/error-prone output (e.g., unit mismatch causing wrong SUM) to contrast with correct approach, per best practices on showing good vs bad outcomes.
Workflow14 / 15
Progress:
- Step 1: Collect raw data (drawings, site measurements, client BOQ)
- Step 2: Build "Raw Data" sheet — enter measurements exactly as recorded
- Step 3: Build "Calculation" sheet — apply formulas (Nos × L × B × H)
- Step 4: Build "Abstract/BOQ" sheet — link category totals from Calculation sheet
- Step 5: Apply formatting (headers, borders, number formats, currency)
- Step 6: Validate totals (cross-check with manual/independent calc)
- Step 7: Generate final report (print-ready BOQ / cost summary)
Step Details
1. Analyze (Raw Data Sheet)
- Columns:
Item No. | Description | Location | Nos | Length | Width | Height/Depth | Unit - Never calculate directly on raw data — keep it as source of truth.
2. Create (Calculation Sheet)
- Reference raw data via formulas, don't retype:
EXCEL
=Volume: =RawData!D2*RawData!E2*RawData!F2*RawData!G2 - Add a "Unit" column (Cum, Sqm, Rmt, Nos, Kg) to avoid unit mismatch errors.
- Use
SUMPRODUCTorSUMIFSfor category subtotals:EXCEL=SUMIFS(Calculation!Qty, Calculation!Category, "Excavation")
3. Generate (BOQ / Abstract Sheet)
- Structure:
Item Code | Description | Unit | Quantity | Rate | Amount - Pull quantities via
VLOOKUP/INDEX-MATCHfrom Calculation sheet:EXCEL=INDEX(Calculation!Qty, MATCH(A2, Calculation!ItemCode, 0)) - Grand total:
=SUM(Amount)withSUBTOTALif filters are used.
Recommendation▾
Include a brief note on handling multi-tab workbook navigation or larger BOQ templates (100+ items) for scalability edge cases.
Examples16 / 20
Example 1: Concrete Quantity Calculation Input:
Item: Column footing, Nos=12, L=1.5m, W=1.5m, H=0.6m
Output:
EXCELVolume = Nos*L*W*H = 12*1.5*1.5*0.6 = 16.2 Cum Formula: =B2*C2*D2*E2
Example 2: Category-wise Abstract Input: Raw data sheet has 50 rows mixed with Excavation, PCC, RCC, Brickwork items. Output:
EXCELExcavation Total (Cum) = =SUMIFS(Qty,Category,"Excavation") PCC Total (Cum) = =SUMIFS(Qty,Category,"PCC") RCC Total (Cum) = =SUMIFS(Qty,Category,"RCC")
Displayed in an "Abstract of Quantities" sheet with borders, bold headers, and totals row highlighted.
Example 3: Rate Analysis / Amount Input: RCC Qty = 16.2 Cum, Rate = ₹6500/Cum Output:
EXCELAmount = =Qty*Rate = 16.2*6500 = 105,300 Format: ₹ #,##0.00 (Indian currency style: [$₹-en-IN] #,##0.00)
Recommendation▾
Consider adding a short troubleshooting section for common Excel formula errors (#REF!, #N/A) specific to VLOOKUP/INDEX-MATCH in this QS context.
Best Practices
- Separate sheets by purpose: Raw Data → Calculation → Abstract/BOQ → Summary. Never mix formulas with source data.
- Never hardcode numbers in formulas — always reference cells so revisions auto-update.
- Use Named Ranges for constants (e.g.,
TaxRate,WastageFactor) used across sheets. - Standard units column on every calc row — prevents Cum/Sqm mixing errors.
- Color-code cells: yellow = input, white = formula/calculated, green = final totals.
- Use
SUMIFS/SUMPRODUCTover manual addition for category totals — auto-updates when rows are added. - Freeze header rows (View > Freeze Panes) for long BOQ lists.
- Round only at display level using
ROUND()in a display column — keep raw calc precision intact. - Cross-check with a dummy total column using an independent formula (e.g.,
SUMPRODUCT) to catch errors. - Protect calculation/formula cells (Review > Protect Sheet) after finalizing, leave raw data editable.
Common Pitfalls
- Don't type numbers directly into the Abstract sheet — always link/formula from Calculation sheet.
- Don't skip the "Unit" column — mixing Sqm and Cum values in one SUM causes wrong totals.
- Don't use merged cells in data ranges — breaks SUMIFS/VLOOKUP/sorting.
- Don't leave raw data and calculations on the same sheet — makes audits and revisions error-prone.
- Don't forget wastage/breakage factors for material quantities (e.g., brick, tile) — add a
WastageFactorcolumn. - Don't use manual
+chains for totals — a missed cell won't show in SUM ranges; useSUM()/SUMIFS()with full ranges. - Don't ignore rounding differences between BOQ quantity and site measurement — always add a remarks/variance column.