Five Reasons Your Excel Cable Sizing Spreadsheet Will Fail a Technical Audit

I spent 15 years watching spreadsheets pass peer review that would have failed any independent audit. Here is the pattern: a senior engineer builds a cable sizing spreadsheet early in their career, it gets copied around the office, modified by three different people who each "improve" it, and eventually it becomes the firm's standard calculation tool. Everyone trusts it because it has always given "reasonable" results.

Then an independent technical audit happens — maybe for a major project, maybe after an incident, maybe because a client's insurer requires it. And the spreadsheet fails. Not because the engineer is incompetent, but because Excel is fundamentally the wrong tool for traceable, auditable engineering calculations.

These are the five failure patterns I have seen in every audit, across firms of every size, in every country I have worked in.

Excel does not track changes to cell formulas. When someone modifies a formula in cell D14, there is no record of what the formula was before, who changed it, when, or why. The cell simply contains the current formula, with no history.

In a cable sizing spreadsheet, this means:

• A derating factor formula that was correct in 2019 may have been accidentally overwritten when someone added a new column in 2021. Nobody noticed because the result still looked "about right."
• A voltage drop formula that used the correct mV/A/m values for V-90 cable was modified to also handle X-90 cable, but the IF statement has a boundary error that selects the wrong value for 16 mm² cables. The error is invisible because the cell shows a number, not an error.
• Someone copied the spreadsheet to a new project and typed over a formula cell with a hardcoded value "to fix a problem." The hardcoded value worked for that project. The next person to use the template inherits the hardcoded value without knowing it is there.

An auditor will ask: "Can you demonstrate that the formulas in this spreadsheet are the same as the formulas in the validated version?" With Excel, the answer is almost always no. There is no validated version. There is only "the spreadsheet we use."

Cable sizing requires applying multiple derating factors in sequence: ambient temperature, grouping, thermal insulation, soil thermal resistivity, depth of burial. The correct approach is to multiply all applicable factors together and divide the protective device rating by the combined factor to determine the minimum cable current rating.

In spreadsheets, I routinely find these errors:

• Missing factors: The spreadsheet applies ambient temperature and grouping derating but omits the thermal insulation factor. The engineer assumed it was "not applicable" for their original project and never added it. Every subsequent user inherits the omission.
• Double-counting: Two cells both apply a grouping factor — one in the derating section and one embedded in the current rating lookup. The cable is derated twice for the same condition, resulting in an oversized (and over-budget) cable selection.
• Wrong table reference: The derating factor is looked up from the wrong table. For example, using the BS 7671 grouping factors (Table 4C1) with AS/NZS 3008 current ratings (Table 13). The spreadsheet gives a result. The result is wrong. But it looks plausible because the factors are in a similar range.
• Hardcoded factors: Instead of looking up the derating factor from a table based on the actual conditions, someone has hardcoded "0.87" into the formula. The factor was correct for one specific installation condition on one specific project. For every other condition, it is wrong.

These errors compound. A missing 0.91 ambient temperature factor combined with a hardcoded 0.87 grouping factor (instead of the correct 0.79 for the actual installation) can result in a cable selection that is one size too small. The cable will work under normal conditions. It will not survive the combination of a hot summer day and a fully loaded cable tray.

Engineering calculations involve multiple unit systems, and Excel does not enforce dimensional consistency. A formula that divides volts by amperes produces a number. Excel does not know or care whether that number represents ohms, watts, or a dimensionally meaningless quantity.

Common unit errors in cable sizing spreadsheets:

• mV/A/m vs V/A/m: The AS/NZS 3008 voltage drop tables give values in mV/A/m. If the spreadsheet formula does not divide by 1000, the calculated voltage drop is 1000 times too high. This usually gets caught — but I have seen cases where a compensating error elsewhere in the formula masked it.
• Cable length in metres vs kilometres: A cell labelled "Length" contains a value. Is it metres or kilometres? If the voltage drop formula assumes metres and the user enters kilometres (or vice versa), the result is off by a factor of 1000.
• Temperature in °C vs K: Derating factor calculations sometimes require absolute temperature (Kelvin). A formula that uses Celsius where Kelvin is required produces a subtle error that is almost impossible to spot from the output alone.
• Percentage vs decimal: Is the voltage drop limit "5" or "0.05"? The spreadsheet comparison formula that checks "is voltage drop acceptable?" depends entirely on which convention was used. I have audited spreadsheets where the pass/fail check used a different convention from the calculation, resulting in a permanent "PASS" regardless of the actual voltage drop.

Excel will never flag these errors. The cell shows a number. The number is wrong. The user has no way to know without manually checking every formula against the methodology.

When AS/NZS 3008 updated from the 2009 to the 2017 edition, current rating values changed for several cable types and installation methods. When BS 7671 moved from Amendment 2 to Amendment 3, derating factors were revised.

A cable sizing spreadsheet built in 2015 uses the tables that were current in 2015. If the tables are hardcoded into the spreadsheet (as they almost always are), the spreadsheet continues to use those tables in 2025 — unless someone manually updates every table cell. And "someone manually updates every table cell" is another way of saying "someone introduces new transcription errors while fixing old ones."

An auditor will ask: "Which edition of the standard does this spreadsheet reference?" The honest answer for most spreadsheets is: "We think it is the 2017 edition, but we cannot be certain because the tables were last verified three years ago by someone who has since left the company."

This is not a theoretical risk. I have personally audited spreadsheets in 2024 that were still using AS/NZS 3008:2009 current rating values — 15 years out of date — because nobody had ever checked.

In a purpose-built calculation tool, changing a standard table value triggers a regression test: every stored calculation is re-evaluated to check whether the output changes. This immediately reveals which projects are affected by the update and by how much.

In Excel, there is no regression capability. If someone updates a derating factor table, there is no way to know which previous calculations are affected. The spreadsheet does not store previous calculations. It does not know that project X used grouping factor Y from the old table. Each use of the spreadsheet is independent and untraceable.

This becomes critical during standard transitions. When AS/NZS 3008:2025 replaced the 2017 edition, engineers using purpose-built tools could identify in minutes which of their designs were affected by the table changes. Engineers using spreadsheets had to manually re-enter every circuit from every recent project to check — or simply hope that nothing changed significantly.

"Hope" is not an engineering methodology.

The solution is not "a better spreadsheet." The solution is a purpose-built calculation tool that addresses each of these five failures:

1. Version-controlled formulas: The calculation engine is code, stored in version control, with every change tracked, reviewed, and tested.
2. Validated derating cascades: Derating factors are applied systematically based on the selected installation conditions, with each factor citing its specific table and clause reference.
3. Enforced units: The calculation engine uses typed quantities throughout — volts, amperes, metres, ohms — preventing dimensional errors at the formula level.
4. Standard edition management: Each standard edition is a separate, validated dataset. The calculation report states exactly which edition was used, down to the specific table and column.
5. Automatic regression: When a table is updated, all affected calculations can be identified and re-evaluated automatically.

ECalPro was built specifically to solve these problems. Every calculation shows its working, cites its sources, and produces an audit-ready report. Try a cable sizing calculation and compare the output quality to your current spreadsheet.