A simple Excel model can help estimate the Cost of a Pre-Engineered Steel Building in under an hour. You only need the building’s basic dimensions, a handful of unit rates, and a few formulas that convert dimensions into quantities.
This tutorial builds that model step by step in Excel. It helps project managers prepare a budget, students learn construction cost estimation, and business owners compare a steel building with other options. By the end, you will have a two-sheet workbook: an Inputs sheet for assumptions and an Estimate sheet that calculates each cost block and the total. Every step shows the cell to use, the exact formula to type, and the result Excel returns.
Excel Formulas and Features Used in This Tutorial
- Arithmetic formulas with cross-sheet references, such as =Inputs!B2*Inputs!B3
- COS and ATAN to calculate the sloped roof area
- SUM to add the cost blocks into a subtotal
- Absolute references ($B$14) to calculate each block’s share of the cost
- ROUND to report a clean cost per square meter
- Data Table, Goal Seek, and Scenario Manager for what-if analysis.
Why Steel Buildings Are Easy to Estimate?
A pre-engineered steel building uses standard components: main frames, purlins, girts, bracing, and metal panels. Because the parts repeat bay after bay, cost scales predictably with floor area, height, and span. That makes a spreadsheet model useful for estimating the Cost of a Pre-Engineered Steel Building compared with a custom concrete or timber structure.
Most manufacturers price these buildings the same way, starting with steel weight and cladding area. If you look at how suppliers such as Xinguangzheng present their warehouse, workshop and hangar buildings, you will see the same few variables appear again and again: span, length, eave height and intended use. Those are exactly the inputs our model needs.
The estimate has five cost blocks:
- Steel frame (primary and secondary structure)
- Roof and wall cladding, including insulation
- Foundation
- Freight to site
- Erection labor and equipment
Add a contingency on top to cover what a first estimate cannot see.
Step 1: Set Up the Inputs Sheet
Create a sheet named Inputs and keep every assumption on it. When a price changes, you update one cell and the whole estimate recalculates.
Type the headings Input, Value, and Unit in A1:C1. Then enter the labels in column A, the values in column B, and the units in column C, starting in row 2. The example values are placeholders for a 30 m × 60 m warehouse; replace the prices with current supplier and contractor quotes.
| Cell | Input | Example Value | Unit |
| B2 | Span (width) | 30 | m |
| B3 | Length | 60 | m |
| B4 | Eave height | 8 | m |
| B5 | Roof slope (rise ÷ run) | 0.1 | ratio |
| B6 | Steel weight per floor area | 30 | kg/m² |
| B7 | Fabricated steel price | 1,200 | USD/t |
| B8 | Roof panel price, insulated | 25 | USD/m² |
| B9 | Wall panel price, insulated | 22 | USD/m² |
| B10 | Foundation cost | 35 | USD/m² of floor |
| B11 | Number of shipping containers | 8 | each |
| B12 | Freight per container | 3,000 | USD |
| B13 | Erection cost | 15 | USD/m² of floor |
| B14 | Contingency | 10% | % |
The Inputs sheet looks like this in Excel:
Tip: Format the input cells in blue so anyone using the workbook can see which cells should be changed. Every other cell in the model should contain a formula.
Optional – named ranges: Select B2, type Span in the Name Box and press Enter (or use Formulas > Define Name). Do the same for the other inputs, and a formula such as =Inputs!B2*Inputs!B3 can be written as =Span*Length. The formulas below use plain cell references so they work either way.
Steel weight per square meter (B6) is the most important assumption. It increases with longer spans, taller eaves, heavier snow or wind loads, and crane use. Ask a supplier for a figure that matches your building rather than guessing. This input can significantly affect the estimated Cost of a Pre-Engineered Steel Building.
Step 2: Calculate the Steel Frame Cost
Multiply the steel tonnage by the price per tonne. Create a second sheet named Estimate, type Item in A1 and Value in B1, then enter the labels in column A and the formulas in column B:
| Cell | Label (Column A) | Formula (Column B) | Res |
| B2 | Floor area (m²) | =Inputs!B2*Inputs!B3 | $1,800 |
| B3 | Steel weight (t) | =B2*Inputs!B6/1000 | $54 |
| B4 | Steel frame cost (USD) | =B3*Inputs!B7 | $64,800 |
The formula for the steel frame cost in B4 will be: =B3*Inputs!B7
Result: Excel returns 64,800, the frame cost in USD.
Explanation:
- Inputs!B2 tells Excel to take the value from cell B2 on the Inputs sheet. You can type the reference or click the Inputs tab and select the cell while writing the formula.
- B2 multiplies span by length: 30 × 60 = 1,800 m².
- B3 multiplies the floor area by 30 kg/m² and divides by 1,000 to convert kilograms to tonnes: 1,800 × 30 ÷ 1,000 = 54 t.
- B4 multiplies the tonnage by the price per tonne: 54 × 1,200 = USD 64,800.
This per-square-meter method is a budgeting shortcut, not a structural design. It works well for early decisions, such as whether a 30 m clear span is affordable or whether adding interior columns would save money. Once the design is fixed, type the actual tonnage from the supplier’s quote into B3 in place of the formula.
Step 3: Calculate Roof and Wall Cladding with COS and ATAN
Cladding is priced by area, so the job here is geometry. The roof is slightly larger than the floor because it slopes, and the walls include two triangular gable ends. These calculations help estimate the cladding portion of the Cost of a Pre-Engineered Steel Building.
Add rows 6 to 8 to the Estimate sheet.
| Cell | Label (Column A) | Formula (Column B) | Result |
| B6 | Roof area (m²) | =Inputs!B2/COS(ATAN(Inputs!B5))*Inputs!B3 | $1,809 |
| B7 | Wall area (m²) | =2*(Inputs!B2+Inputs!B3)*Inputs!B4+Inputs!B2^2*Inputs!B5/2 | $1,485 |
| B8 | Cladding cost (USD) | =B6*Inputs!B8+B7*Inputs!B9 | $77,894 |
Syntax: =ATAN(number) returns the angle, in radians, whose tangent is number. =COS(number) returns the cosine of an angle given in radians. Because ATAN returns radians and COS expects radians, the two can be nested directly without RADIANS or DEGREES.
The formula for the roof area in B6 will be: =Inputs!B2/COS(ATAN(Inputs!B5))*Inputs!B3
Result: Excel returns 1,808.98 m².
Explanation: Excel evaluates the formula from the inside out:
- ATAN(Inputs!B5) = ATAN(0.1) = 0.0997 radians (about 5.7°), the roof angle.
- COS(0.0997) = 0.99504.
- Inputs!B2/0.99504 = 30 ÷ 0.99504 = 30.15 m, the true sloped width of the roof.
- 30.15 × Inputs!B3 (60 m) = 1,808.98 m².
The formula for the wall area in B7 will be: =2*(Inputs!B2+Inputs!B3)*Inputs!B4+Inputs!B2^2*Inputs!B5/2
Result: Excel returns 1,485.00 m².
Explanation: The first part, 2*(Inputs!B2+Inputs!B3)*Inputs!B4, is the perimeter times the eave height: 2 × (30 + 60) × 8 = 1,440 m². The second part, Inputs!B2^2*Inputs!B5/2, adds both gable triangles. Each has a base equal to the span (30 m) and a height of span ÷ 2 × slope (1.5 m), so the two together are 30² × 0.1 ÷ 2 = 45 m².
The formula for the cladding cost in B8 will be: =B6*Inputs!B8+B7*Inputs!B9
Result: 1,808.98 × 25 + 1,485 × 22 = 77,894 USD.
For more precision, subtract the area of large doors and windows from B7, then price those openings separately.
Step 4: Add Foundation, Freight and Erection
These three costs depend more on location than on the building itself, so enter them as simple rates. Add rows 10 to 12 to the Estimate sheet:
| Cell | Label (Column A) | Formula (Column B) | Result |
| B10 | Foundation (USD) | =B2*Inputs!B10 | $63,000 |
| B11 | Freight (USD) | =Inputs!B11*Inputs!B12 | $24,000 |
| B12 | Erection (USD) | =B2*Inputs!B13 | 27,00 |
Result: Foundation 1,800 × 35 = 63,000; freight 8 × 3,000 = 24,000; erection 1,800 × 15 = 27,000 (all in USD).
Note: B10 and B12 reuse the floor area in B2 instead of multiplying span by length again. Referring to an existing result keeps formulas short, and changing the building size flows through every line automatically.
A few points to watch:
- Foundation cost swings widely with soil conditions. Weak ground or a crane building can double it, so get a local quote once you have a soil report.
- Freight for an imported building is usually priced per container. Your supplier can tell you how many containers the kit fills; panels often take more space than steel.
- Erection depends on local labor rates and crane hire. Some suppliers can send a supervisor to guide a local crew, which affects this line.
Include these location-specific expenses when estimating the overall Cost of a Pre-Engineered Steel Building, rather than focusing only on the steel frame and panels.
Step 5: Build the Summary with SUM, Contingency and ROUND
The summary pulls the five blocks together, adds contingency, and converts the total into a cost per square meter. Most people use that last figure to compare options. Add rows 14 to 17 to the Estimate sheet:
| Cell | Label (Column A) | Formula (Column B) | Result |
| B14 | Subtotal (USD) | =SUM(B4,B8,B10:B12) | $256,694 |
| B15 | Contingency (USD) | =B14*Inputs!B14 | $25,669 |
| B16 | Total estimate (USD) | =B14+B15 | $282,364 |
| B17 | Cost per m² (USD) | =ROUND(B16/B2,2) | 156.87 |
Syntax: =SUM(number1, [number2], …) adds individual cells, ranges or both. =ROUND(number, num_digits) rounds number to the stated number of decimal places.
The formula for the subtotal in B14 will be: =SUM(B4,B8,B10:B12)
Result: Excel returns 256,694 USD.
Explanation: The commas separate the arguments, so SUM adds B4 (steel frame), B8 (cladding), and the range B10:B12 (foundation, freight and erection). The quantity rows B2, B3, B6, and B7 are left out on purpose because adding square meters or tonnes to dollars would give a meaningless total.
The remaining rows follow from the subtotal. B15 multiplies it by the 10% in Inputs!B14 to give 25,669. B16 adds the two for a total estimate of 282,364 USD. B17 divides the total by the floor area and rounds the result to two decimals, so =ROUND(B16/B2,2) returns 156.87 USD per m².
This total provides a preliminary estimate of the Cost of a Pre-Engineered Steel Building using the sample dimensions and placeholder rates. Actual costs will vary based on supplier quotes, site conditions, specifications, and local labor expenses.
Show Each Block’s Share With an Absolute Reference
To see which cost block matters most, type Share of subtotal in C1, enter the formula below in C4, and format the cell as a percentage (Home > Number > %). Then copy C4 and paste it into C8 and C10:C12.
The formula in C4 will be: =B4/$B$14
Result:
| Cost Block | Cell | Formula | Amount (USD) | Share |
| Cladding | C8 | =B8/$B$14 | $77,894 | 30.30% |
| Steel frame | C4 | =B4/$B$14 | $64,800 | 25.20% |
| Foundation | C10 | =B10/$B$14 | $63,000 | 24.50% |
| Erection | C12 | =B12/$B$14 | 27,000 | 10.50% |
| Freight | C11 | =B11/$B$14 | 24,000 | 9.30% |
Explanation: The dollar signs lock both the column and the row. When you paste the formula into C8, the relative reference B4 becomes B8, but $B$14 still points to the subtotal. Without the dollar signs, C8 would divide by B18, an empty cell, and return #DIV/0!. To check the column, enter =SUM(C4,C8,C10:C12) in C14; it should return 100%.
Check the Whole Sheet in Formula View
Press Ctrl + ` (the grave accent key, left of the 1 key), or go to Formulas > Show Formulas, to display every formula instead of its result. This is the quickest way to spot a wrong cell reference before you share the workbook. Press the same shortcut again to switch back.
Step 6: Test Design Options with What-If Analysis
Once the model works, Excel’s What-If Analysis tools let you test design choices without rewriting any formulas. This is useful when comparing different building sizes, budgets, and material specifications to understand how each option affects the Cost of a Pre-Engineered Steel Building.
Data Table: See How the Span Changes the Total
A one-variable Data Table recalculates the total for a list of spans in one go. Excel requires the input cell to be on the same sheet as the table, so build it on the Inputs sheet:
- Type Span (m) in E1 and Total estimate (USD) in F1.
- Enter the spans 20, 25, 30, 35, and 40 in E3:E7.
- In F2, link to the total with =Estimate!B16. It shows 282,364.
- Select E2:F7 and go to Data > What-If Analysis > Data Table.
- Leave the Row input cell empty, enter B2 as the Column input cell, and click OK.
Result: Excel fills F3:F7 with {=TABLE(,B2)}, an array formula that returns:
| Span (m) | Floor Area (m²) | Total Estimate (USD) | Cost Per m² (USD) |
| 20 | $1,200 | 204,545 | $170 |
| 25 | $1,500 | 243,424 | $162 |
| 30 | $1,800 | 282,364 | $157 |
| 35 | 2,100 | 321,364 | 153 |
| 40 | 2,400 | 360,425 | 150 |
Explanation: For each row, Excel puts the span from column E into B2, recalculates the model, and writes the total from F2 into column F. The total rises with span, but the cost per square meter falls, because walls, gables and freight are spread over more floor area. You cannot edit a single cell inside a Data Table; to change it, select F3:F7 and delete it as a block.
Note: This model keeps steel at 30 kg/m² for every span. In practice, a wider clear span needs a heavier frame, so update B6 with the supplier’s figure for each span before relying on the comparison.
Goal Seek: Find the Size That Fits a Budget
Suppose the budget is USD 250,000 and the span has to stay at 30 m. Goal Seek can work out how long the building can be.
- On the Inputs sheet, go to Data > What-If Analysis > Goal Seek.
- Set cell: F2 (the cell linked to the total). To value: 250000. By changing cell: B3 (length).
- Click OK.
Result: Goal Seek changes B3 to 52.02 m and F2 shows 250,000. A 30 m × 52 m building (about 1,560 m²) fits the budget. Click OK in the Goal Seek Status box to keep the new length, or Cancel to restore 60 m.
Note: Freight stays at eight containers here. A shorter building may fit in fewer, so update B11 once you know the new kit size.
Scenario Manager: Compare Insulated and Uninsulated Panels
Scenario Manager saves sets of input values so you can switch between them. Its changing cells must be on the active sheet, so stay on Inputs:
- Go to Data > What-If Analysis > Scenario Manager and click Add.
- Name the scenario Insulated, enter B8:B9 as the changing cells, click OK, and keep the values 25 and 22.
- Click Add again, name it Uninsulated, use the same changing cells, and enter 12 and 10 (placeholder prices for single-skin panels).
- Select a scenario and click Show to load its prices, or click Summary and enter F2 as the result cell to get a side-by-side report on a new sheet.
Result:
| Scenario | Roof panel (B8) |
Wall panel (B9) |
Cladding (USD) |
Total (USD) |
Cost per m² |
| Insulated | $25 | 22 | $77,894 | $282,364 | $157 |
| Uninsulated | $12 | 10 | $36,558 | $236,894 | $132 |
Switching to uninsulated panels lowers the estimate by about USD 45,500. Whether that saving is worth it depends on how the building will be used and on heating or cooling costs over its life.
Comparing scenarios helps identify which material choices most affect the Cost of a Pre-Engineered Steel Building while keeping assumptions transparent.
Common Mistakes to Avoid
- Using one steel weight for every building: A 15 m shed and a 40 m crane building can differ by double or more per square meter. Always match B6 to the actual span and loads.
- Forgetting the gables and roof slope: Pricing cladding on floor area alone understates it, especially on wide buildings.
- Hard-coding prices inside formulas: Write =B3*Inputs!B7, not =B3*1200. Keep every rate on the Inputs sheet so updates take seconds, and nothing gets missed.
- Leaving out the $ signs when copying: A shared formula copied without an absolute reference to B14 returns #DIV/0! or a wrong percentage.
- Leaving out site costs: Permits, utilities, access roads, and interior fit-out sit outside this model. List them separately so the total is not mistaken for a full project budget.
- Skipping contingency: Early estimates always miss something. Ten percent is a sensible starting point; raise it in Inputs!B14 for remote sites or unusual designs.
Final Thoughts
Estimating the Cost of a Pre-Engineered Steel Building becomes more manageable when you divide the calculation into clear cost categories and support it with a well-organized Excel model. With an Inputs sheet, five cost blocks built from simple cell references, COS and ATAN for the roof, SUM for the subtotal, and an absolute reference for each block’s share, Excel turns basic building dimensions into a budget you can test and refine.
Start with placeholder rates, replace them with real supplier and contractor quotes as they arrive, and use Data Table, Goal Seek, and Scenario Manager to see which design choices move the total most. The resulting estimate provides a useful starting point for comparing options and planning a more accurate construction budget.
Frequently Asked Questions (FAQs)
Q1. My roof pitch is in degrees, not a ratio. How do I change the roof area formula?
Answer: Enter the pitch in degrees in B5 and convert it with RADIANS instead of ATAN: =Inputs!B2/COS(RADIANS(Inputs!B5))*Inputs!B3. For a 5.7° pitch, this returns almost the same 1,809 m² as the slope ratio of 0.1.
Q2. Why is ROUND used only in B17?
Answer: ROUND changes the stored value, not just how it is displayed. Rounding intermediate results such as the roof area would introduce small errors that add up. Rounding only the final cost per square meter keeps the calculation exact while giving a clean figure to report. To tidy the other cells, use number formatting (Home > Number) instead.
Q3. How do I add a mezzanine or another cost item?
Answer: Add its quantity and rate on the Inputs sheet, calculate the cost in a new row on the Estimate sheet (for example, B13), and include that cell in the subtotal: =SUM(B4,B8,B10:B13). Copy the share formula into column C as well so the percentages still add up to 100%.
Recommended Articles
We hope this guide helps you understand how to estimate the Cost of a Pre-Engineered Steel Building using Excel and plan your construction budget more effectively. Explore our recommended articles for more insights on steel building costs, construction budgeting, building materials, and project cost estimation.
