BOQ format in Excel: a bill of quantities template with columns, units, and a worked sample

Plenty of bills of quantities in India still begin as an Excel sheet, and a sheet built in a hurry breaks in familiar ways: amounts typed in by hand, units that change halfway down a column, a grand total that adds the sub-totals twice. The maths is easy. The hard part is building the sheet so it stays right as the job changes.

This is a practical BOQ format in Excel for interior fit-out and civil work: the columns, the sections, the units, how to price a line, a worked sample, and the mistakes that break a total. For the background (what a BOQ is, and why it’s the document the client signs), start with what a BOQ is in interior fit-out.

The seven columns of a BOQ in Excel

Every BOQ, whether it’s for an office fit-out or a boundary wall, comes down to the same seven columns. Keep them in this order so anyone you send the sheet to reads it the same way.

Column	What goes in it	How it’s filled
A · Item no.	A fixed reference: section letter plus line number (B.02)	Typed
B · Description	The work, specific enough to price and to check on site	Typed
C · Unit	sqm, rmt, cum, kg, nos	Drop-down list
D · Qty	The quantity taken off the drawings	Linked from the measurement sheet
E · Rate (₹)	Cost per unit, including your margin	Linked from the rate analysis
F · Amount (₹)	Qty × Rate	Formula only
G · Remarks	Make, drawing reference, exclusions, provisional or PC	Typed

Description has to be specific: “Vitrified tiles 600 × 600 mm laid on cement mortar bed,” not “flooring.” A vague line can’t be priced or checked on site. Remarks is easy to skip, but it’s where arguments about scope get settled: the approved make, what’s excluded, which drawing the quantity came from.

Lay the workbook out as four linked sheets

A BOQ that holds up is four sheets that feed each other, not one long sheet:

1. Measurement sheet: each line’s length, breadth and height, and the quantity they produce.
2. Rate analysis: how each rate is built up.
3. BOQ: the seven-column table in lettered sections, with a sub-total under each.
4. Summary (abstract): one line per section, adding up to the works total, with tax below.

Make the sections match how you’ll buy the work. For an interior fit-out that’s usually civil, flooring, false ceiling and painting, partitions, joinery and furniture, electrical, HVAC and plumbing; for civil work, earthwork, concrete, masonry, plaster and finishes. A BOQ split along likely sub-contract packages is easier to price, because each package can go out to sub-contractors for prices (Designing Buildings Wiki).

Measure each line in the right unit

In India the reference for units is IS 1200, the Bureau of Indian Standards’ method of measurement of building and civil engineering works. It comes in parts, one for each kind of work, and each part sets the unit and what to deduct. The ones that come up in a typical fit-out or civil BOQ:

Work	Unit	Where IS 1200 sets it
Earthwork (excavation, filling)	cum	Part 1
Concrete	cum	Part 2
Structural steel and ironwork	kg	Part 8
False ceilings and linings	sqm	Part 10
Flooring and dado	sqm	Part 11
Skirting (state the height)	rmt	Part 11
Painting walls and ceilings (distemper, emulsion)	sqm	Part 13
Painting and polishing woodwork and metalwork	sqm	Part 15
Door and window shutters	sqm	Part 21
Workstations, fixtures, door sets priced as one item	nos	Not an IS 1200 unit; a common fit-out practice. Say what one number includes

A few of IS 1200’s rules are worth copying into your measurement sheet even on private jobs:

• Book dimensions in a consistent order: generally length, then breadth or width, then height, depth or thickness (Part 1).
• Measure each dimension to the nearest 0.01 m, and work out areas to the nearest 0.01 sqm and volumes to the nearest 0.01 cum (Part 1).
• Measure floors before skirting, dado or wall plaster goes on (Part 11, as amended).
• Don’t deduct small voids. Part 11 makes no deduction from floor finishes for voids of 0.2 sqm or less.

The parts linked above are older editions (1973 to 1994) made public by Public.Resource.Org, and BIS may have revised some of them since. If your contract names a method of measurement, follow the contract, and check which edition and parts it refers to. Where nothing is stated, a note saying “measured as per IS 1200” saves an argument at billing time. If your clients think in sq ft and rft, that’s fine, but use one system for the whole BOQ.

For UK readers looking for a bill of quantities template: the rulebook there is the RICS New Rules of Measurement, where NRM2 covers detailed measurement for building works. NRM formally replaced SMM7 on 1 July 2013, though take-up of NRM2 has been limited and many practitioners still use SMM7 or their own methods (Designing Buildings Wiki). The seven-column layout works the same either way.

A worked sample BOQ

Here’s a small office fit-out laid out the way it would sit in the BOQ sheet. The rates are illustrative, not market rates; the structure and the arithmetic are the point.

Item	Description	Unit	Qty	Rate (₹)	Amount (₹)	Remarks
A	Civil
A.01	Dismantle existing floor tiles, incl. debris removal	sqm	110	85	9,350	Debris carted off site
A.02	115 mm brick wall in cement mortar 1:4	sqm	18	1,250	22,500	Up to 3.0 m high
A.03	12 mm cement plaster 1:6 to new walls	sqm	36	320	11,520	Both faces of A.02
	Sub-total A				43,370
B	Flooring
B.01	Vitrified tiles 600 × 600 mm on mortar bed	sqm	110	1,180	1,29,800	Make as per approved sample
B.02	Tile skirting, 100 mm high	rmt	62	240	14,880
	Sub-total B				1,44,680
C	Ceiling and painting
C.01	Gypsum board false ceiling on GI frame	sqm	96	1,050	1,00,800	Light cut-outs included
C.02	Acrylic emulsion, 2 coats over 1 coat primer	sqm	240	95	22,800	Walls only
	Sub-total C				1,23,600
D	Joinery and furniture
D.01	Laminated flush door with hardware	nos	4	14,500	58,000	Hardware from approved list
D.02	Modular workstation 1,200 × 600 mm	nos	12	18,500	2,22,000	Chairs excluded
	Sub-total D				2,80,000
	Works total (A + B + C + D)				5,91,650

Look at A.03: its quantity is both faces of the new wall in A.02 (18 sqm × 2 = 36 sqm), and the remark says so. The summary sheet carries one line per section to the works total of ₹5,91,650, and GST goes below that at the rate that applies to your contract. Confirm the rate with your CA rather than copying it from an old BOQ.

The formulas are short. The first two follow the figure at the top of this page, where B.01 sits in row 7 and B.02 in row 8. The works-total range is only an example, so point it at your own sheet’s Amount column, from the first line to the last:

Cell	Formula	What it does
F7	=ROUND(D7*E7,0)	Amount for one line, in whole rupees
F9	=SUBTOTAL(9,F7:F8)	Sub-total for section B
F30	=SUBTOTAL(9,F6:F29)	Works total, for a BOQ whose lines run from row 6 to row 29

ROUND rounds each amount to whole rupees (Microsoft), so the amounts you see are the amounts being added. SUBTOTAL(9, …) adds a range but ignores any other SUBTOTAL results inside it, so nothing is counted twice (Microsoft). That’s why the works total can run down the whole Amount column, sub-total rows and all, and still come out right. One catch: SUBTOTAL also leaves out rows hidden by a filter, so clear any filters before you read off a total.

How to price each line

The rate is where your margin is. Build each one on the rate-analysis sheet from four parts: material at current supplier rates plus a wastage allowance, labour per unit, sundries (adhesives, fixings, consumables, cartage), and overheads and profit as a percentage on top.

An illustrative build-up for B.01, per sqm: tiles with wastage ₹620, mortar bed and joint filler ₹150, labour ₹200, sundries and cartage ₹56, making ₹1,026; add 15% overheads and profit (₹154) and the rate is ₹1,180. When the tile price changes, you change one cell and the BOQ follows.

Take base numbers from your own past jobs first, then current supplier quotes. For government-style civil work, CPWD’s Delhi Schedule of Rates, on its publications page, is a useful cross-check. Treat any published schedule as a reference point, not your price: your costs depend on your city, suppliers and crew.

Some lines can’t be priced yet, because the client hasn’t chosen the tile or the design isn’t finished. Don’t guess a firm rate; mark them in Remarks. A provisional sum is an allowance for work not yet defined in enough detail to price (Designing Buildings Wiki). A prime cost (PC) sum is an allowance for work or materials from a supplier the client chooses, and the contract sum goes up or down by the difference once the actual cost is known (Designing Buildings Wiki).

Set the sheet up so the totals can’t drift

• Units from a drop-down. Select the Unit column, go to Data > Data Validation, set Allow to List, and enter your units as the source (Microsoft). Nobody can type “Sqm.” in one row and “sq.m” in the next.
• Freeze the header row with View > Freeze Panes, so the column names stay on screen (Microsoft).
• Lock the formulas. By default, protecting a sheet locks every cell. So first unlock only the cells people should type in (Format Cells > Protection, clear Locked): Remarks on the BOQ, and the inputs on the measurement and rate-analysis sheets. Then use Review > Protect Sheet on each sheet. The formulas and links stay locked (Microsoft).
• Date and number every issue. Put the revision and date in the header (“Rev 2, 21 Oct 2026”) and keep the old file, so you can show a client why the number moved.

Common mistakes that break a BOQ

1. Lump-sum lines. “Electrical work: lump sum” hides every quantity in it. Use a lump sum only where a line really can’t be measured.
2. Vague descriptions. “Painting” could mean one coat of distemper or three coats of emulsion. Say which.
3. Mixed units. Skirting in rft on one sheet and rmt on another. One system, the whole way through.
4. Typed-in amounts. One hard-typed number and that row no longer equals qty × rate. If a client negotiates a line down, change the rate, not the amount.
5. SUM over sub-totals. SUM down a column that already holds section sub-totals counts the work twice. Use SUBTOTAL.
6. Quantities with no working. Without a measurement sheet nobody can check a quantity, and on a running project someone will, because every RA bill is measured against these same BOQ lines.
7. No exclusions. If chairs, approvals or rewiring aren’t included, say so in Remarks.

When Excel stops being enough

Excel works for one BOQ at a time. It gets harder once several jobs run at once: the same rates get retyped into every new file, variations get agreed on WhatsApp, and the BOQ the client signed stops matching what you bill. Our guide to the best BOQ software in India compares the options for that stage.

Boqos is quote-to-cash software for interior fit-out and design-build firms. You describe the scope or upload drawings, and Boqos builds the BOQ from India’s construction rate library, GST-aware and editable before you send it. From there it carries the job through proposal and client e-approval, priced and approved variations, and milestone invoicing, in one system instead of separate Excel files. There’s a 14-day free trial. But you don’t need software to start: a clean seven-column sheet with linked quantities and rates will carry a firm a long way.

The short version

Seven columns, lettered sections with sub-totals, and a summary sheet on top. Measure each line in its IS 1200 unit, build every rate from the bottom up, and keep quantities and rates on their own sheets, linked rather than retyped. Make Amount a formula, total with SUBTOTAL, and protect the sheet. Then the number the client signs is one you can defend line by line.