Workflow: Construction cost estimate

The standard workflow for a QS / cost estimator — take off the quantities, look up unit rates, roll up the estimate, check it and print the dossier for the main contractor / client, then carry on into the payment and variation records.

Goals

• Input: construction drawings (PDF / scanned images / an Excel quantity file).
• Output: a detailed estimate table + a summary table + the printed PDF dossier.

Workflow

Step 1 — Gather quantities from several sources

Source A — An existing Excel quantity file

Open the file → check the table structure (No., Code, Work item, Unit, Quantity).

If the table is multi-tier (1, 1.1, 1.1.1…), build the levels with Excel's Increase Indent button, then run DVD Addin → group Text and Number → Multilevel → Multilevel 1.1.1. The level comes from the cell's indent level, not from dots you typed by hand; up to 10 levels. If the table already carries old numbers, run (None) from the same menu first, otherwise the new numbers are written on top of the old ones.

Source B — Taking off from drawings / PDF documents

Command	Use it when
Read PDF	Open the drawing in a separate PDF viewer window and place it next to Excel so you can read off and type at the same time
Extract Table	The PDF/image already contains a quantity table — AI rebuilds the exact rows/columns, merged cells included
OCR file	A scanned drawing with no text layer

With Extract Table: select the target cell first (it becomes the top-left corner of the pasted area), choose the file, select only the pages that hold the table, correct any misread cells in the preview window, then click OK. Clean numbers are converted to real numbers so Excel can total them straight away.

Source C — Export from Revit / BIM

Export the Revit Schedule view to Excel, then clean it up:

• DVD Addin → group Text and Number → Text ops → Smart Data Cleaning… — six groups of operations in a single run: trimming excess whitespace, normalising case, repairing Vietnamese encoding damage (mojibake), normalising date formats, normalising phone numbers and removing duplicate rows. Formula cells and blank cells are skipped.
• SMART case (AI) (Ctrl+Shift+S) — normalises the capitalisation of work-item names while leaving TCVN, BTCT and proper nouns intact.
• UnDiacritics Folder if the exported folder names carry accents that break the path.

"Remove duplicate rows" deletes the whole row

This option deletes the entire row containing the matching cell, including columns outside the part you are looking at, and Ctrl+Z cannot undo it. Keep Fuzzy match mode at a threshold of 90–95%; go lower and you risk deleting two work items that are genuinely different.

Step 2 — Pull unit rates into the quantity table

Method 1 — Lookup formulas

The rate table sits on a sheet in the same workbook:

=dvdXlookup(MaHieu; BangGia!$A$2:$A$5000; BangGia!$H$2:$H$5000; "Chưa có đơn giá"; "Lỗi tra cứu"; 0)

dvdXlookup has built-in IfNotFound and IfError branches, so the table is not full of #N/A when you present it to the client.

Rate table spread over several sheets (one sheet per chapter)? Use dvdLookupAllSheets — it scans every sheet in the workbook by column number:

=dvdLookupAllSheets(MaHieu; 1; 8)

A matrix-style rate table (rows = code, columns = region / period)? Use dvdTableLookup:

=dvdTableLookup(MaHieu; "Khu vực 2"; BangGia!$A$1:$F$500)

Method 2 — Assign a direct reference

DVD Addin → Calculate Menu → Direct Ref matches the key between two tables and then writes a formula pointing straight at one single cell in the rate table. Lighter than a VLOOKUP that scans the whole table, and one look at the formula tells you which cell the figure came from.

Method 3 — Preliminary AI pricing PRO

You have not had time to look up the cost norms yet, or you need a quick reference figure to check a subcontractor quotation against:

Select the range containing the Item name + Unit columns → DVD Cons → QS → AI Construction Estimate → set the column positions → Read selection → Ask AI for prices.

The AI (Gemini) fills in an estimated unit price in VND per unit together with a material / labour / plant breakdown note. Review every row, untick the rows you do not want written, then set Price column + Note column and click Apply to Excel.

These are reference prices

AI results serve only the preliminary pricing and cross-checking stage; they do not replace cost norms and approved local price schedules.

The amount column

=KhoiLuong * DonGia

Step 3 — Multi-level roll-up

Use Autosum — the add-in inserts SUBTOTAL(9;…) into the data column at the markers of a key column:

	A (Package)	B (Detail)	C (Quantity)	D (Amount)
5	FOUNDATION		(SUBTOTAL C6:C8)	(SUBTOTAL D6:D8)
6		Foundation excavation	250	12.500.000
7		Blinding concrete	30	36.000.000
8		Foundation rebar	1,2	30.000.000
9	SUPERSTRUCTURE		(SUBTOTAL C10:C12)	(SUBTOTAL D10:D12)
10		Level 1 columns	…	…

In the window, set Reference column = A5:A12, Data column to sum = D5:D12, then click Create SUBTOTAL. The two ranges must have the same number of rows and be aligned with each other.

SUBTOTAL(9;…) skips rows hidden by a filter and does not double-count other SUBTOTALs — exactly what a package total row should be.

Want the table to collapse by level? DVD Addin → Merge cells → Auto group reads the index-number column (1, 1.1, 1.1.1) or a plain level number and builds Excel's Outline to match those levels.

Step 4 — Format the dossier

• Numbering the No. column: Numbering — hidden rows are skipped, and a merged cell only takes one number.

• Working shown with the result: Fill Results joins them into the familiar form 2 x 4,5 x 0,8 = 7,2 right inside the working cell. Or use dvdExplain (form 3=1+2) / dvdExplainE (form 1+2=3, like the example above) to generate the explanation string for a whole range.

• Rounding for good: Rounding Func wraps ROUND / ROUNDUP / ROUNDDOWN around the formula — it changes the actual value, not the display format, so the sums downstream do not drift by fractions.

• Amount in words for the final total row:

  =dvdVnd(D20; 1)

  dvdVnd with a second argument of 1 prepends Bằng chữ: for you. For foreign-currency dossiers use dvdUsd.

• Money formatting: use Excel's Number Format (#.##0 "đ") — keep the cell a real number so it can still be added up.

• Colour by level: Excel's own Conditional Formatting on the Level column, or Assign Level to assign the levels first.

• AutoFit merged cells: AutoFit merged — Excel does not size row height for merged cells by itself, so this command does it instead.

Specialist functions often used in estimating

• dvdSteelWeight(diameter; quantity) — steel weight from bar diameter and number of bars.
• dvdNoiSuy(x1; x2; y1; y2; xnew) — 1-D interpolation when reading a cost-norm table by band.
• dvdSumVisible(range) — sums exactly the part still showing after filtering.
• dvdSumIfColor(range; sample cell) — sums by fill colour, handy for a table marked up by hand.

Step 5 — Check before submitting

Command	What it does
Formula View	Breaks the current cell's formula into clickable components — click one and you see which range it points at and what value it holds
Lookup Trace	Select one cell containing VLOOKUP/HLOOKUP/XLOOKUP/INDEX → jump straight to the source cell the value was taken from. The trace always uses exact match, so an approximate-match formula may point at a cell other than the real result
Dependents	Lists the cells currently referencing the active cell — so you know in advance what shifts when you change one factor

"Error Check" is not a workbook error scanner

Error Check in the Calculate Menu turns Excel's formula error warnings on or off — the green triangles in the cell corners — for the selected range or for the whole application. A great many of those warnings in construction paperwork are false alarms (a formula that omits adjacent cells, numbers stored as text in the code column); switch them off for a clean table when printing, then switch them back on when you need to audit.

To find real #REF! / #N/A / #DIV/0!, use Excel's Home → Find & Select → Go To Special → Formulas → Errors.

Step 6 — Export the dossier

Option A — Print piece by piece in a loop

DVD Addin → Batch print:

• Driver cell (number) = the cell the table filters/looks up by (for example the level-1 package number).
• Sheet-print cell = the cell holding the names of the sheets to print each round, separated by +.
• Filename cell = the cell whose formula supplies the PDF file name.
• Tick combine all exported PDFs into one file if you want a single continuous volume.

The file name and the combine option only take effect when you click Export PDF; clicking Print ignores them.

Option B — Quick export of the selected sheets

Select several sheets (Quantities + Summary + Materials…) → DVD Addin → Sheets → PDF.

One file per sheet

This command exports each sheet as its own PDF file, named <workbook name>_<sheet name>.pdf, saved next to the workbook. For a single combined volume, use Option A with the combine-PDF option, or merge them afterwards with PDF Manager.

Option C — Generate Word documents from the table

DVD Addin → Merge to Word — each data row produces one Word file from a .docx template with [ColumnHeader] placeholders. Good for valuation certificates and per-package submission notes; not suited to building one single consolidated report.

Prepare the header row with Create Title, then convert the whole run to PDF with Word → PDF.

Step 7 — Carry on into the payment and variation records

A finished estimate table is the direct input for the three commands in the QS group on the DVD Cons tab:

Command	Result
Interim Payment	Select the BoQ → map the Item / Contract qty / Unit price / % complete columns → generates a sheet holding the value-of-completed-work table submitted for payment, with cumulative and remaining columns, a totals row and ready-drawn borders
Compare BoQ	Reconciles 2 BoQ versions by code → a sheet with the absolute delta, the percentage delta and a colour-coded Increased / Decreased / New / Removed status; map a unit-price column as well and you also get the delta in money
Variation Register	A list of variation work items → a priced register, additions and omissions distinguished by colour, closed off by three lines: Total increase / Total decrease / Net variation

All three commands map columns by column letter (B, E, F…) and produce a new sheet, leaving the original BoQ untouched.

Workflow optimisation

A master template with all the formulas in place

Create a DuToan_Master.xlsx file that already contains:

• Sheet KhoiLuong with dvdXlookup formulas pointing at BangGia.
• Sheet TongHop with SUMIF by level.
• Sheet BangGia — the rate schedule currently in force.
• Sheet Settings — project parameters (region, factors, price date).

New project → copy the template → swap the data → done.

Freeze the rates before submitting

When you are about to submit the estimate to the client, freeze the rate table:

1. Copy sheet BangGia → name it BangGia_NgayChot.
2. Repoint the lookup formulas at BangGia_NgayChot.
3. The prices no longer move even if the original rate table is updated later.

Before switching, run Dependents on a few unit-rate cells to see exactly which formulas depend on them.

Comparing several options

Create sheets KhoiLuong_PhuongAn1, KhoiLuong_PhuongAn2 that differ in scope or in substitute materials, then use Compare BoQ and pick Sheet A and Sheet B — faster and safer than building an INDEX/MATCH reconciliation table by hand.

License gating

Command	Requirement
Interim Payment, Compare BoQ, Variation Register	License required (Standard or PRO)
Fill Results, Direct Ref, Auto group, AutoFit merged	License required
Extract Table, OCR file, Batch print, Merge to Word, PDF Manager	License required
AI Construction Estimate	PRO
Autosum, Rounding Func, Formula View, Lookup Trace, Dependents, Read PDF, Sheets → PDF	Free

Everything works in full during the 30-day trial. After that you need to activate a license.

Related

• Formula Tools — Autosum, Fill Results, Direct Ref, Rounding Func, Lock Ref, and all three trace commands (Formula View, Lookup Trace, Dependents).
• QS Tools (Quantity Surveying) — Interim Payment, Compare BoQ, Variation Register, AI Construction Estimate.
• Range & lookup functions — dvdXlookup, dvdLookupAllSheets, dvdTableLookup, dvdSumVisible.
• Date, number & money functions — dvdVnd, dvdUsd, dvdExplain, dvdSteelWeight, dvdNoiSuy.
• Workflow: Schedule management — feeding the estimate into Cash-flow and EVM.