Excel Automation for Structural Engineers

Excel is already in the structural workflow whether anyone planned it or not. Reactions land in a workbook. Bolt lists get reconciled in a workbook. Drawing indexes, hold lists and transmittal registers live there because everyone can open the file. Automation in this setting does not mean a hidden macro that “does the project.” It means a workbook that imports cleanly, calculates in visible cells, validates obvious mistakes, and prints a sheet a checker will actually read instead of a dashboard nobody can audit.

The failure mode is familiar. A clever file grows until nobody knows which tab is live. Units sit in a comment. A colour means “do not order” to the person who built the file and means nothing to the shop. Macros require a personal licence and a disabled security warning. At that point the workbook is slower than a paper list. This article stays on the side of boring: named ranges, input tabs that stay input, output tabs that stay output, and print layouts that match the way engineers already review fabrication information.

StruTools keeps a set of Excel Programs for the tables this industry repeats: take-offs, reaction layouts, checklists. They are starting points, not a substitute for project judgement. Pair them with a written detailing checklist so the workbook is one artefact in a process, not the process itself. If the numbers come from MBS or STAAD, keep the import raw and the formatting separate so a checker can still see what the analysis said before the table looked pretty enough to plot.

Tables engineers already trust

A structural workbook succeeds when a senior engineer can audit it in fifteen minutes without asking the author. That requirement kills most dashboard styling. Use a tab named INPUT, a tab named CALC if needed, and a tab named PRINT. Date the print tab. Put units in column headers.

• One purpose per workbook, stated on a cover tab
• Inputs in a single colour, never mixed into result columns
• No merged cells in data regions
• Print area set, headers repeating, paper size matching the office plotter

Reactions, BOMs and checklists

These three tables cover most of the value. Reactions tell foundations and base-plate designers what the analysis claims. BOMs tell purchasing what to order. Checklists tell the checker what was looked at. Each one fails in a different way if automation hides the source.

Reactions

Import the listing into a raw tab. Build the pretty table from formulas that point at that tab. Do not paste values over the import. When the analysis is re-run, replace the raw tab and confirm row counts moved.

Bills of materials

The mark list has to come from the drawings or the model, not from memory. Flag marks that appear on the plan but not on the BOM, and the reverse. That flag is worth more than a chart of tonnage.

Checklists

A submission checklist in Excel is useful if empty items block a “ready” flag. It is theatre if every box is pre-ticked in the template.

Named ranges, validation and print layouts

Named ranges let print sheets stay stable when rows are inserted. Data validation stops “W21” and “W 21” living in the same column. Neither feature is glamorous. Both prevent the checker from becoming a data clerk.

From workbook to CAD and notes

Sometimes a table has to become drawing text. Export a CSV with agreed columns and let CAD read it, or place a plotted table as a PDF xref. Avoid live links from DWG to XLSX across a WAN; they break on issue day. A dated export is slower and reliable.

Limits of spreadsheet automation

Excel will not number a steel model, will not catch a brace-to-purlin clash, and will not apply your CAD standard. Macros that try to run the whole office become untestable. When the job is file translation or CAD selection, leave the workbook. When the job is a table, stay.

VBA is optional. Many durable engineering workbooks have no macros at all — only formulas, validation and a print tab. Add VBA when you are repeating a mechanical import, and store it in a signed, shared location, not in a personal add-in nobody else can run.

Building a workbook the checker will use

1. Write the purpose and the units on a cover tab.
2. Create INPUT, optional CALC, and PRINT. Do not mix them.
3. Bring a real project file in as the first import, not invented demo numbers.
4. Add flags for missing marks, row-count mismatches and blank required fields.
5. Set print areas and plot a PDF the same way you would issue it.
6. Have someone who did not build the file complete one review with it.
7. Store the template next to the CAD standard, with a version date.

Engineering tips

• Put the analysis filename and date on the PRINT tab. Future you will need it.
• Keep a ‘COUNTS’ box: imported rows versus tabulated rows.
• Avoid true/false colour-only coding. Repeat the status in a text column.
• If two disciplines share the file, give each an INPUT tab rather than a shared grey zone.

Common mistakes

Pasting analysis output over formulas

The next revision has nowhere to land. Always keep a raw import tab.

A 30-tab ‘project control’ workbook

Nobody knows the live tab. Split by purpose: reactions, BOM, index.

Macros that only run on the author’s machine

If the checker cannot run it, it is not office automation. Prefer formula-only files unless the macro is shared and documented.

Tonnage that does not match the drawings

A summary cell nobody can trace is worse than no summary. Link it to the mark list or remove it.

Checklist

• Purpose and units on the cover tab
• Raw import separated from PRINT
• Headers include units and load case names where relevant
• Validation on repeating fields such as grade and bolt type
• Print area produces a readable PDF
• Someone other than the author has used the file on a live issue
• Template version dated in a shared folder

Frequently asked questions

Should every calculation live in Excel?

No. Design calculations that belong in analysis software should stay there. Excel is for tables, registers and small checks that a person will read. Do not rebuild a frame analysis in a workbook because the licence seat was busy.

Are Google Sheets or other online grids acceptable?

Only if your office already treats them as controlled documents with access and revision. Many steel shops still need a dated PDF of the table in the issue package. Plan for that export.

How do we handle multiple load cases in a reaction table?

Keep load case names from the analysis. Do not rename them to ‘worst’ unless you also show which case that was. Filters on a raw tab beat a single mysterious envelope column.

When should we move from Excel to a database?

When several projects need concurrent editors and a workbook lock is the weekly problem. Until then, a well-structured workbook is easier to archive with the drawing issue.

Where this leaves the work

Excel automation for structural engineers is layout, validation and a print sheet — not a hidden engine. If a checker can see the import, the units and the flags, the workbook will last. If they cannot, it will be rebuilt during the next night issue.

Start from Excel Programs where the table already exists, and keep the human checklists in steel detailing and approval submission beside it so the spreadsheet does not pretend to be the whole quality system.