How to How to Create Material Requisition Form in Excel - Practical Guide

Learn to create a professional Material Requisition Form in Excel for tracking inventory requests efficiently. This tutorial covers table setup, data validation, formulas, and formatting to build a functional form that streamlines material procurement workflows and maintains organized documentation for your business.

Why This Matters

A well-designed Material Requisition Form prevents inventory chaos, ensures accurate tracking, reduces procurement errors, and provides audit trails for compliance and cost control.

Prerequisites

•Basic Excel skills and familiarity with spreadsheet navigation
•Understanding of your organization's material categories and approval processes
•Knowledge of basic cell formatting and table creation

Step-by-Step Instructions

Set Up the Form Header

Open a new Excel workbook and create a header section with company name, form title, and date fields in the top rows. Use Home > Font > Bold and increase font size to 14pt for visibility.

Create Column Headers

Starting at row 6, create column headers: Item Number, Material Description, Quantity Requested, Unit of Measure, Requested By, Department, Date Needed, Priority, Approved By, and Status. Use Home > Fill Color to highlight header row in company color.

Apply Data Validation

Select Unit of Measure column and apply Data > Data Validation > List with options (Pcs, Boxes, Kg, Liters). Repeat for Priority (Low, Medium, High) and Status (Pending, Approved, Rejected, Received) columns.

Format Borders and Alignment

Select all data cells, then use Home > Borders > All Borders to create a professional grid. Use Home > Alignment > Center for headers and set text wrapping for longer descriptions.

Add Totals and Save as Template

Insert a total row at the bottom using SUM formulas for Quantity Requested column. Save via File > Save As > Excel Template (.xltx) to enable easy reuse for future requisitions.

Alternative Methods

Use Excel Form Controls

Build the form using Developer > Insert > Form Controls for dropdown menus and checkboxes instead of data validation lists. This provides more visual interactivity but requires more VBA knowledge.

Create a Pivot Table Summary

Link a Pivot Table to your requisition data via Insert > PivotTable to automatically summarize material requests by department or priority without manual calculations.

Integrate with Power Query

Use Data > Get Data > From Other Sources to pull material master lists from a database, automatically populating descriptions and unit costs for selected items.

Tips & Tricks

✓Use conditional formatting (Home > Conditional Formatting) to highlight overdue or high-priority requisitions in red for quick visual scanning.
✓Create a separate 'Instructions' sheet within the workbook explaining how to fill out each field for consistency.
✓Freeze the header row (View > Freeze Panes) so it remains visible when scrolling through large requisition lists.
✓Add a running item number using the formula =ROW()-5 to automatically number entries sequentially.

Pro Tips

★Create dropdown lists for 'Requested By' and 'Department' using Data > Validation > List > Source, referencing a hidden employee/department list sheet for accuracy and consistency.
★Add a hidden calculation column to compute estimated costs (Quantity × Unit Cost) using VLOOKUP to automatically pull unit prices from a master materials database.
★Use conditional formatting with formulas like =AND($J2="Approved",TODAY()-$G2>0) to flag approved requisitions with unfulfilled dates in yellow.
★Build a summary dashboard on a separate sheet using COUNTIF and SUMIF formulas to display total pending requests, items by priority, and departmental spending metrics.

Troubleshooting

Dropdown lists not appearing in data validation cells

Ensure you've selected the correct range and used Data > Data Validation > List. Check that source data is on the same sheet or properly referenced with absolute cell references ($A$1:$A$10).

VLOOKUP formula returns #N/A error for material descriptions

Verify the lookup value exists in your reference table and uses exact spelling. Check that the column index number matches the position in your lookup range (e.g., if description is in column 2, use 2 as index).

Form becomes slow when adding many rows of data

Remove volatile formulas like TODAY() where unnecessary, convert to a proper table format (Insert > Table), or split large datasets into separate worksheets by department.

Conditional formatting rules not highlighting correctly

Check formula syntax and cell references match your data range. Ensure rules are applied to correct range and test with a single cell first before applying to entire columns.

Related Excel Formulas

VLOOKUP SUMIF COUNTIF TODAY IF AND SUM

Frequently Asked Questions

Can I use this form across multiple departments?

Yes, add a Department column with a dropdown list for easy filtering. You can then use PivotTables or SUMIF formulas to analyze requests by department, or create separate sheets for each department if preferred.

How do I prevent unauthorized changes to approval fields?

Use Review > Protect Sheet and specify which cells users can edit (typically Item Description, Quantity, and Date Needed). Set a password to restrict protection changes. Keep approval and status columns locked for managers only.

Can I link this form to an inventory system?

Yes, use Power Query (Data > Get Data) to connect to a database or use VBA macros to sync with external systems. Alternatively, export requisition data and import it into your inventory management software.

What's the best way to archive old requisitions?

Create an 'Archive' sheet or separate workbook and move completed requisitions there monthly using cut/paste or filters. Keep active requisitions in the main sheet for faster processing and cleaner data management.

How do I add approval workflows?

Add an 'Approval Status' column with conditional formatting and formulas to auto-calculate next approver based on amount/department. For advanced workflows, consider using Microsoft Forms or Power Automate integration instead of pure Excel.