Formula Tools
The Formula group on the DVD Addin tab collects nine range-wide formula commands for BoQs, estimates and payment applications: building section totals, pulling rates across from a rate sheet, fixing decimals, locking $ references, and tracing a cell that returns a strange number.
What they share is that they rewrite the formulas themselves across hundreds of cells in one click — the kind of editing that is slow and error-prone to do cell by cell the night before a submission.
Commands in this group
| Command | What it does |
|---|---|
| Formula View | Breaks the active cell's formula into clickable tokens so you can inspect each one's value |
| Lookup Trace | Jumps to the exact source cell a lookup formula pulled its value from |
| Dependents | Lists the cells whose formulas reference the active cell |
| Autosum | Writes SUBTOTAL(9,…) on every row where a new key appears in the key column |
| Fill Results | Appends the computed value to the description cell as text = result |
| Direct Ref | Links two tables with direct reference formulas keyed on a shared column |
| Rounding Func | Adds or strips ROUND / ROUNDUP / ROUNDDOWN around the formulas in the selection |
| Lock Ref | Switches reference style between A1, $A1, A$1 and $A$1 across a range |
| Error Check | Turns Excel's green-triangle formula warnings on or off for a range or the whole application |
The first three sit in the Trace menu and share one Formula trace window (two tabs). Autosum is the group's main button and opens the tool window on the Calculate tab group, where Fill Results and Direct Ref are sibling tabs. The remaining Calculate Menu commands open their own windows in the same style: pick the options, click Apply, and the window stays open so you can change the selection and run again.
Finishing a quantity sheet end to end
- Pull the rates across — open Direct Ref, pick the Source range (first column = item code, last column empty and waiting for the formula) and the Target range (first column = item code, last column = rate), then click Assign.
- Fix the decimals — select the quantity column, open Rounding Func, leave Action = Add function, Function = ROUND, Decimal places =
2, click Apply. - Pin the lookup range before dragging formulas down — open Lock Ref, choose Lock mode = Lock both row and column — $A$1 and click Apply on a tight selection.
- Build the section totals — open Autosum, pick the Reference column (the package column) and the Data column to sum (the quantity column, same number of rows), click Create SUBTOTAL.
- Show the working in the description column — open Fill Results, pick Description and Result, click Fill results.
- Chase down odd numbers — select the suspect cell and use Formula View to click through the tokens, or Lookup Trace to land directly on the source rate cell.
- Clean the printout — select the range, open Error Check, leave Scope = Selected range, untick Enable error checking, click Apply.
Example
A foundation and first-floor-column take-off on sheet BOQ, after running Rounding Func → Autosum → Fill Results in that order:
| Row | A (Package) | C (Description) | D (Quantity) |
|---|---|---|---|
| 6 | FOOTING F1 | =SUBTOTAL(9,D7:D8) → 47.36 | |
| 7 | 4 pits x 2.5 x 2.5 x 1.6 = 40 | =ROUND(4*2.5*2.5*1.6,2) → 40.00 | |
| 8 | Tie beam: 2 x 18.4 x 0.4 x 0.5 = 7.36 | =ROUND(2*18.4*0.4*0.5,2) → 7.36 | |
| 9 | LEVEL 1 COLUMNS | =SUBTOTAL(9,D10:D11) → 8.63 | |
| 10 | 12 columns x 0.3 x 0.3 x 3.6 = 3.89 | =ROUND(12*0.3*0.3*3.6,2) → 3.89 | |
| 11 | 8 columns x 0.4 x 0.4 x 3.7 = 4.74 | =ROUND(8*0.4*0.4*3.7,2) → 4.74 |
For Autosum the two ranges are A6:A11 (key) and D6:D11 (data): formulas land only on D6 and D9, the rows where a new key appears, while the detail rows stay untouched. Because SUBTOTAL(9,…) ignores filtered-out rows, the section totals stay meaningful when you filter down to a single package.
Tips and caveats
- Rounding Func, Lock Ref, Fill Results and Direct Ref all overwrite in place and cannot be undone with
Ctrl+Z— save the file (or duplicate the sheet) before running them on a large table. - Autosum and Fill Results need the two ranges to have the same number of rows and be aligned; one row of offset sums the wrong block or pairs the wrong result with the wrong line.
- For Direct Ref the target range must have two or more columns: first column is always the key, last column the value. Rows whose key is not found get no formula, so check codes for typos and stray spaces first.
- Lock Ref applies the chosen mode to every reference in the formulas of the selection, including the ones that should have stayed relative — keep the selection tight.
- Error Check only toggles the warning flags; it does not edit formulas and does not hide real errors such as
#REF!. The Entire Excel application scope is a machine-level Excel setting, so switch it back after printing. - Lookup Trace understands XLOOKUP, VLOOKUP, HLOOKUP and INDEX only, and always matches exactly; formulas built on OFFSET, INDIRECT or approximate matching may land on the wrong cell.
- Fill Results and Direct Ref need an active DVDAddin account; the Trial period covers them in full.
See also
- Formula Coach (Explain + Fix) — hand the formula to the AI when tracing it by hand is not getting you there.
- Range & lookup functions —
dvdXlookup,dvdELookup,dvdSumVisiblefor cases the built-in Excel functions handle awkwardly. - Cells and Rows — insert/delete rows, merge cells and tidy the table before building formulas on it.