Bill of quantities template for Excel
A free workbook and CSV with the nine columns a priced BOQ needs. Amounts calculate as you enter rates, and a Sections sheet adds up each part so you can compare it with the tender's collection figure.
No account and no email address. The workbook is built in your browser; the spreadsheet library loads from cdnjs only when you click the Excel button.
What you get
The Items sheet, with six example rows
| section | item_no | description | unit | quantity | rate | amount | tags | page |
|---|---|---|---|---|---|---|---|---|
| 1.0 EARTHWORKS | A | Strip topsoil average 150 mm deep and stockpile on site for reuse | m² | 2,450 | 2.85 | 6,982.50 | 1 | |
| 1.0 EARTHWORKS | B | Bulk excavation to reduce levels, not exceeding 1.50 m deep, deposit in spoil heaps on site | m³ | 3,120 | 6.40 | 19,968.00 | 1 | |
| 1.0 EARTHWORKS | C | Extra over item B for excavating in rock, using breakers | m³ | 180 | extra-over | 2 | ||
| 2.0 CONCRETE | A | Plain concrete grade C15, blinding to bottom of excavations, 75 mm thick | m² | 640 | 3 | |||
| 2.0 CONCRETE | B | Provisional sum for testing of concrete cubes | sum | 4,500.00 | provisional-sum | 3 | ||
| 2.0 CONCRETE | C | Allowance for pumping concrete (rate only) | m³ | rate-only | 4 |
Blank rate and amount cells are the point: in the workbook they are ready for pricing. In the CSV they are empty.
In plain terms
What a BOQ template is for
- Bill of quantities template
- A spreadsheet layout for holding the items of a Bill of Quantities, one row per item with its section, reference, description, unit, quantity, rate and amount, so the bill can be priced and totalled.
A bill is only useful in Excel if every line is in the same shape and the totals can be compared with the document. This layout keeps the shape fixed and puts the comparison on a second sheet.
Where NRM 2, CESMM4 and SMM7 fit
NRM 2 (RICS New Rules of Measurement), SMM7 (the older standard method of measurement for building work) and CESMM4 (the civil engineering method, published by the Institution of Civil Engineers) are common formats for the bills a quantity surveyor prepares. They are rules for how work is measured and described. This template does not apply them and makes no claim of compliance with any of them. It is a working layout for pricing, and it reads the same whichever method the bill was measured to.
Step by step
How to use the template
-
Download the workbook
Take the Excel workbook if you will price in it: it has the Quantity x Rate formulas, a Sections sheet and a column guide. Take the CSV if you are importing the layout somewhere else.
-
Replace the example rows
One row per item, in the order of the bill. Put the bill or section heading in the Section column on every row, spelled the same way each time, and keep the item reference exactly as the tender prints it.
-
Enter units and quantities
Use the unit the bill uses (m, m2, m3, nr, kg, t, item, sum, lm, %). Quantities go in as plain numbers without thousands separators. Leave the quantity blank on rate-only lines.
-
Price the rates
Type your unit rate in the Rate column. The Amount cell on an unpriced row is a formula, so it fills in as soon as quantity and rate are both there. Lines the tender prices with a fixed sum, such as a provisional sum, keep that amount as typed.
-
Fill the Sections sheet
Each section already counts its items and adds up its amounts from the Items sheet. Type the collection figure the tender prints for that section into the stated total column and the variance column shows the difference.
-
Clear the variances before you submit
A variance that is not zero means a line is missing, repeated or priced wrongly. Resolve each before the sheet goes anywhere. See how to price a BOQ for the checks that go with it.
Column guide
What each column holds
Items sheet
| Column | What it holds | Example |
|---|---|---|
section | The bill, part or heading the line sits under. Carried onto every item below the heading. | 2.0 CONCRETE |
item_no | The reference printed in the tender: A, B, 1.1, 2.3.4, A.1. Kept as text so 1.10 stays 1.10. | C |
description | The full item text. Lines that wrap over several rows in the PDF are joined into one cell. | Reinforced concrete grade C32/40 in ground beams, including construction joints |
unit | Unit of measurement, normalised: m, m2, m3, nr, kg, t, item, sum, ls, %, hr, day, lm. | m3 |
quantity | The quantity measured by the quantity surveyor. Plain number, no thousands separator. | 64 |
rate | Your unit rate. Blank in an unpriced tender copy; fill it in when you price. | 128.5 |
amount | Quantity x rate. Printed amounts are kept as printed; blank amounts become a formula in the XLSX. | 8224 |
tags | Flags for lines that need different handling: provisional-sum, prime-cost, rate-only, included, extra-over. | extra-over |
page | Source page, filled when the converted text carries page breaks. Blank otherwise. | 3 |
Sections sheet
| Column | What it holds |
|---|---|
section | Section name, identical to the section column on the Items sheet. |
item_count | Number of item lines in the section. Compare with your own count of the tender. |
quantity_by_unit | Quantities added up per unit, never across units: m2: 7170; m3: 6591. |
amount_sum | Sum of the amount column for the section. |
stated_collection_total | The figure the document prints for the section ("Carried to Collection"), when there is one. |
variance | amount_sum minus the stated total. Anything other than zero means a line is missing, repeated or misread. |
Compared
A blank sheet, a generic template or this one
| What a BOQ sheet needs | Blank sheet | Generic template | This template |
|---|---|---|---|
| Getting the tender's items in | Type every line from the PDF. | Convert with a general tool, then clean wrapped lines and repeated headers. | Run the PDF through the converter: it fills these same columns. |
| Amount calculation | Write the Quantity x Rate formulas yourself. | A bare grid; formulas are yours to add. | Blank amounts are formulas that fill in as rates are entered. |
| Section subtotals | Subtotals added by hand or in a second sheet. | None. | A Sections sheet counts and adds each part, with a variance column against the stated total. |
| Provisional sums and rate only | Marked in the description text, if at all. | No place for them. | A Tags column built for them, so they can be filtered. |
| Explaining the columns | In your head. | Usually not explained. | A Column guide sheet in the workbook and the table below. |
FAQ
Questions estimators ask
What should a bill of quantities template contain?
At minimum: an item reference, a description, a unit, a quantity, a rate and an amount, plus the bill or section each item belongs to. This template adds two working columns, tags (provisional sum, prime cost, rate only, included, extra over) and the source page, and a second sheet that adds up each section so you can compare it with the collection figure in the tender.
Does the template follow NRM 2, CESMM4 or SMM7?
No, and it does not claim to. NRM 2, CESMM4 and SMM7 are methods of measurement: rules for how work is measured and described. This file is a working layout for holding and pricing items. If you are preparing a bill, follow the method of measurement the contract names. If you are pricing one, the layout simply mirrors the columns those bills use.
Is the template free?
Yes. The CSV and the Excel workbook download straight from this page with no account and no email address.
How does the Amount column work?
In the Excel workbook the Amount cell of an unpriced row is a formula, Quantity x Rate, and it stays blank until both are filled in. Type a rate and the amount appears. Rows that already carry a fixed amount, such as a provisional sum, keep it as a typed value.
How do I get section subtotals?
Use the Sections sheet. Each row counts the items and adds up the Amount column for one section name from the Items sheet, then subtracts the stated collection total you type in. Keep the section names identical on both sheets and the numbers follow as you price.
What is the Tags column for?
To mark lines that behave differently from measured work: provisional-sum, prime-cost, rate-only, included and extra-over. Filter on it when you need to see every provisional sum, or to keep rate-only items out of a total.
Can I use the same layout for a schedule of rates?
Yes. Leave the Quantity column blank, tag the lines rate-only if the document asks for rates without quantities, and fill in the Rate column. The Sections sheet then counts items per section and the amount total stays at zero, which is correct for a schedule that has nothing to multiply.
I already have the tender as a PDF. Do I have to type it into the template?
No. The converter on the home page reads the PDF and returns the items in these same columns, with each section's subtotal checked against the figure printed in the document. The template is for when you are building a bill or an estimating sheet from scratch.
CSV or Excel: which download should I use?
Use the Excel workbook if you are going to type rates into it, because the formulas and the Sections sheet only exist there. Use the CSV if you are importing the layout into another system. The CSV opens in Excel too, but it carries values only.