Let’s say you are a contractor bidding for a road re-tarring project in Thrissur. Here’s how to use the new Excel data book:
Step 1 – Open the Master File
Enable macros (if the file contains analysis buttons). Many new versions have a “Dashboard” sheet.
Step 2 – Select District
Use the dropdown filter in the “Material” sheet to choose Thrissur. The sheet automatically updates sand, aggregate, and bitumen prices. kerala+pwd+data+book+in+excel+format+new
Step 3 – Build Your Bill of Quantities (BOQ)
Create a new worksheet named “My Estimate”. Use VLOOKUP to fetch rates from the data book:
=VLOOKUP(A2, 'Main_Items'!A:D, 4, FALSE)
Where A2 contains the item code (e.g., PWDP405 – Granular Sub-base). Let’s say you are a contractor bidding for
Step 4 – Apply Lead & Lift
If the quarry is 15km away, the lead sheet automatically adds ₹X per km. The new Excel format has a pre-programmed Lead Calculator – simply enter distance.
Step 5 – Generate Print-Ready Abstract
Use pivot tables to group items (Earthwork, Pavement, Drainage, etc.) and add 5% contingencies. Where A2 contains the item code (e
Traditionally, the data book was a 200+ page PDF or a printed hard copy. The Excel format offers transformative advantages:
| Feature | PDF/Hard Copy | Excel Format | | :--- | :--- | :--- | | Searchability | Basic text search | Multi-conditional search (VLOOKUP, FILTER) | | Cost Estimation | Manual calculator entry | Instant auto-sum, pivot tables, macros | | Data Updates | Print new edition | Dynamic linking to master file | | Tender Preparation | Copy-paste prone to errors | Formula-driven, error-free BOQ generation | | Analysis of Rates | Hidden calculations | Transparent formulas visible in cells |
The new Kerala PWD Data Book in Excel format is particularly valued because it allows junior engineers to generate approximate estimates in minutes instead of hours.
| Project Code | Project Name | District | Sanctioned Cost (₹ Cr) | Expenditure (₹ Cr) | Physical Progress (%) | Target Completion | |--------------|--------------|----------|------------------------|--------------------|----------------------|-------------------| | PWD/2024/123 | MCH Bypass widening | TVM | 85.0 | 42.5 | 50% | 03/2025 |