Excel Material List Template: Plan, Price, and Track

How-to basics · Updated November 19, 2026

An Excel material list template is a worksheet for recording the items a project needs, how much to buy, what each item costs, and whether it has been ordered. Start with columns for item, description, unit, quantity, unit cost, supplier, and status; add waste and total-cost columns if they suit the project. In Microsoft Excel for Microsoft 365 on Windows, turn the range into a table with Ctrl+T so it is easier to filter, sort, and expand.

What a material list is for

A material list, sometimes called a materials schedule or bill of materials, connects a project’s requirements to purchasing and inventory. It can cover construction supplies, event materials, manufacturing components, classroom resources, or parts for a repair. The exact fields depend on the work: a renovation list may need dimensions and finish, while an assembly list may need a part number and approved supplier.

A spreadsheet is useful when a list needs to be edited, priced, and shared without specialist software. It can calculate estimated line costs and show which items are not yet ordered. It does not automatically establish that quantities are correct, that a supplier has stock, or that a product meets a technical or safety requirement. Those details still need checking against drawings, specifications, and supplier information.

Recommended columns for a useful template

Use one row per distinct item and give each column one clear purpose. A practical starting layout is:

  • Item ID: A short reference such as MAT-001 that helps people discuss an item without relying on row numbers.
  • Category: For example, electrical, timber, fasteners, or packaging; this makes filtering easier.
  • Description and specification: Record the material, grade, size, colour, model, or other feature needed to distinguish the correct product.
  • Unit: State how it is measured or bought, such as each, box, metre, kilogram, or litre.
  • Required quantity and waste rate: Keep the project requirement separate from any allowance for cutting, breakage, or spoilage.
  • Order quantity: The amount to purchase after applying the allowance and any package-size rules.
  • Unit cost and line total: Use a consistent currency and note whether the quoted price is per item, pack, or another unit.
  • Supplier, status, and notes: Track the vendor, purchasing stage, delivery information, substitutions, or other decisions.

For example, a row might describe a specified screw, show that it is sold by the box, list the number of boxes required, and include the supplier’s part number. Merely writing screws, 100 can be ambiguous if the project needs a particular length, head type, material, or corrosion resistance. Put measurable requirements in the list and link to a drawing or specification when the details are too extensive for a cell.

Set up the worksheet in Excel

  1. Open a blank workbook and enter the column headings in row 1. Keep headings short but meaningful; avoid merged cells in the data area.
  2. Enter one material per row, using consistent units and a consistent way to write specifications. Do not insert blank rows between items.
  3. Select the heading row and sample data range, then press Ctrl+T on Windows. Confirm that My table has headers is selected. Excel tables provide filter buttons and generally extend formatting and formulas when rows are added. On a Mac, use Insert > Table if the Windows shortcut differs in your setup.
  4. Choose a readable table style from the Table Design tab. Freeze the heading row with View > Freeze Panes > Freeze Top Row if the list is long.
  5. Format quantity and cost columns appropriately. Set cost cells to the project currency, and format waste rates as percentages. Check that values are numeric rather than text before relying on calculations.

For a recurring list, save a clean copy as an Excel template using File > Save As and select Excel Template (*.xltx). Keep the blank version separate from each project workbook so that entering one project’s quantities or supplier details does not overwrite the starting layout.

Calculate order quantities and costs

Formulas reduce repeated arithmetic, but they should reflect how the material is actually purchased. In the example layout, column F contains required quantity, column G contains unit cost, column H contains waste rate, column I contains order quantity, and column J contains line total. Enter percentages as percentages, such as 10%, rather than typing 10 when the cell is meant to represent ten percent.

With required quantity in F2 and waste rate in H2, enter =ROUNDUP(F2*(1+H2),0) in I2 when the item must be purchased in whole units. If F2 is 12 and H2 is 10%, the formula returns 14: 12 multiplied by 1.10 is 13.2, rounded up to the next whole unit. Copy the formula down the column or let the Excel table fill it into new rows.

That rounding rule is not right for every material. If an item is sold by the metre and the supplier allows decimal quantities, rounding to a whole metre could overstate or misstate the requirement. With required quantity in F2 and waste rate in H2, use =F2*(1+H2) in I2 when fractional quantities are appropriate. If a product is sold in packs of a fixed size, calculate the number of packs from the pack size rather than treating the number of individual pieces as the order quantity.

With order quantity in I2 and unit cost in G2, enter =I2*G2 in J2 for the line total. If I2 is 14 and G2 is $8.50 per unit, J2 returns $119.00. The unit cost and order quantity must use the same basis: a price per box cannot be multiplied by a quantity expressed as individual screws unless the pack conversion is handled first.

With line totals in J2:J20, enter =SUM(J2:J20) in a separate project-total cell. If J2 is $119 and J3 is $40 and the other cells in the range are blank, the formula returns $159. Keep the total outside the table’s item rows or use a table total row, so the total is not accidentally counted as another material.

Example: from project need to purchase list

Imagine a small shelving project that needs 12 boards, 24 brackets, and 48 screws. The list should identify the board dimensions and grade, the bracket load or product specification where relevant, and the screw size and finish. If boards are sold individually and the project applies a 10% cutting allowance, the whole-unit order quantity is 14 boards. If brackets are sold in packs of four, the required amount of 24 brackets means six packs, not 24 packs.

This example shows why a quantity column alone is not enough. The list needs to distinguish project units from purchase units. A useful addition is a pack size or purchase unit column when those differ. Record both the calculated requirement and the order quantity, rather than replacing the original requirement with a rounded number. That makes the allowance visible and helps someone review whether it is reasonable.

Make the list easier to review

Use consistent categories and status values, such as Not started, Quoted, Ordered, Part received, and Received. A dropdown can help prevent variations such as ordered, Ordered, and order placed from splitting the same status across several spellings. Select the status cells and use Data > Data Validation; choose List and provide the approved values as the source. Keep the choices short and define what each status means for the people maintaining the file.

Filters can show only one category, supplier, or status without deleting other rows. Sort by item ID or category when that helps with review, but sort the whole table rather than a single column so descriptions remain attached to the right quantities. Conditional formatting can make overdue or not-yet-ordered entries stand out; in Microsoft 365 for Windows, select the relevant cells and use Home > Conditional Formatting. Use colour as a prompt, not as the only way to communicate status, since a printed copy or a reader with colour-vision differences may not convey the same meaning.

Consider adding a last-checked date or quotation reference for prices that may change. Separate estimated costs from confirmed prices with a clear label, and note whether delivery, taxes, or other charges are included. A material list is an estimate and purchasing aid, not proof that a supplier will honour a price or deliver by a particular date.

Common mistakes and how to avoid them

  • Mixing units: Metres, lengths, boxes, and pieces are not interchangeable. Name the unit in every row and convert quantities explicitly when needed.
  • Using vague descriptions: A brand name or broad category may not identify the required size, grade, finish, or performance. Include the relevant specification and confirm it with project documentation.
  • Applying waste twice: If the quantity already includes a cutting allowance, do not apply the waste percentage again when calculating the purchase quantity.
  • Rounding everything to whole numbers: Whole-unit rounding suits countable items, but not necessarily materials sold by length, weight, or volume. Match the formula to supplier increments.
  • Comparing prices on different bases: Check whether a quote is per item, pack, case, or bulk unit before comparing costs or calculating totals.
  • Overwriting formulas: Use a distinct input format or column shading, and consider protecting formula cells if several people edit the workbook. Keep an editable backup before applying protection or major restructuring.
  • Treating a template as a technical approval: A spreadsheet can organize requirements, but it cannot verify structural suitability, compatibility, or compliance. Have appropriate project specifications reviewed by the responsible person.

When Excel is enough—and when to use another tool

Excel is often a good fit for a small or moderately sized project when a few people need a flexible list, simple calculations, and printable purchasing information. It is especially useful when the required fields change between projects or when the list needs to be analyzed alongside a budget or schedule.

A shared Excel workbook stored in OneDrive or SharePoint can support collaboration, subject to the organization’s account, permissions, and version setup. Agree who maintains quantities and status, and avoid circulating multiple files named final. For larger inventories, frequent purchasing, barcode tracking, approvals, or links to accounting and production systems, dedicated inventory, procurement, or project-management software may provide stronger controls. Choose the tool based on the workflow, not just the number of spreadsheet rows.

Frequently Asked Questions

How do I make a simple material list in Excel?
Put one item on each row and use columns for its description, specification, unit, quantity, unit cost, supplier, and status. Format the range as a table with Ctrl+T on Windows so you can filter and extend it.

Can Excel calculate a material list total automatically?
Yes. Put the line cost for each item in its own column and use the SUM function on that column. Confirm that quantities and prices use compatible units, and keep the project total outside the item rows.

Should waste be included in the required quantity?
Keep the base requirement and waste allowance separate when possible. This makes the calculation visible and easier to check. The appropriate allowance depends on the material, design, cutting method, and project requirements; a spreadsheet should not supply that judgment by itself.

What is the difference between a material list and a bill of materials?
The terms can overlap. A bill of materials often presents structured components for a product or assembly, sometimes with part numbers and quantities per assembly. A material list may be a broader project purchasing list. Use the terminology and detail level expected by the project or organization.

Can I use the same template for every project?
Use a shared core layout, but adapt fields to the work. A construction project may need dimensions and finish, while an event plan may need delivery date and storage location. Preserve consistent units, clear descriptions, and traceable changes even when the columns vary.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.