Material Gate Pass Format in Excel (Free Template)

Published on 10/18/2026 • Updated on 10/23/2026

Many plants run their gate passes in an Excel sheet. It costs nothing, everyone knows it and it can be set up in an hour. Built well, it gives you a searchable register and an overdue list. Built badly, it becomes a file named final_v7 on three desktops.

This guide gives a column layout you can copy into Excel, the formulas that flag overdue returnable items, simple drop-down lists to keep data clean, and an honest view of where a spreadsheet stops being enough. There is no download here. You can build the sheet from the tables below in a few minutes.

The column layout

Make one sheet called Register, with one row per pass. Put the headings in row 1 and freeze the row.

Eighteen columns look long, but each answers a question an auditor will ask. Cut columns only if you can say why.

Formulas that do the work

The status column can be a single formula. With Date returned in P, Expected return in O and Type in B, put this in R2 and fill down.

Add days overdue in column S: =IF(R2="OVERDUE",TODAY()-O2,""). Then use conditional formatting to colour OVERDUE rows red.

For a count of open returnables, use =COUNTIF(R:R,"Open")+COUNTIF(R:R,"OVERDUE").

Drop-down lists and rules

• Type: use Data Validation with the list RGP,NRGP.
• Department: a list on a second sheet called Lists.
• Reason: Repair, Calibration, Trial, Loan, Sale, Scrap, Transfer, Gift, Write-off.
• Expected return: add a rule that it cannot be blank when Type is RGP.
• Qty and Value: allow numbers only.

Data validation is the single biggest quality gain in a spreadsheet. It stops three spellings of the same department and blank return dates.

Sample rows

The rows are examples made up for this guide.

Simple reports from the same data

Once the register has clean columns, a pivot table gives useful reports in a minute. Select the whole table, choose Insert, then PivotTable.

• Passes by department and month, to see who sends most.
• Open and overdue RGPs by party, to see which vendor is slowest.
• Value of NRGPs by reason, to see scrap, sales and write-offs separately.
• Passes by hour, to plan guard shifts around peak times.

Review the reports monthly with the security and finance heads. A report that no one reads is wasted effort, so agree who reads which one.

An inward register on a second sheet

If goods also come in, add a second sheet called Inward with the same style. Use these columns: inward no., date and time, supplier, vehicle, challan or invoice number, item, quantity received, quantity per document, checked by and remarks. The difference between the received and document quantity is the first sign of a shortage. A simple formula in the next column, received minus document, shows it. Our guide on the inward and outward material register format covers it in more depth.

Setting up drop-downs step by step

Repeat for type, reason and unit. When a new department appears, add it to the list and every drop-down updates at once.

A printable pass from the same sheet

You can keep the register and print the pass from the same workbook. Make a second sheet called Pass that looks up a pass number with a drop-down in one cell and fills the rest with INDEX and MATCH formulas. Set the print area to one A5 half page, add a box for signatures and print three copies for security, store and the receiver.

Where Excel stops being enough

Excel suits a small site with one or two people raising passes. When a second gate opens, or approvals are held up by paper, it is time to move.

Rules to keep the sheet honest

• One master file in a shared folder, not copies on desktops.
• Protect the formula columns and lock the headings.
• Never delete a row. Mark it cancelled with a reason.
• Back up the file daily.
• Name one owner who reviews overdue items every Monday.

Common mistakes

Another mistake is deleting rows when a pass is raised in error. Cancel it and keep the line.

A third is leaving the sheet on the guard's computer only. If that machine fails, the register fails with it.

A fourth is typing dates as text, such as 3rd March. The overdue formula cannot read them. Use proper date cells and a single format everywhere.

A fifth is sharing the file by email attachment. Each person edits a different copy and the register splits. Use a shared drive link with edit rights for the owner and view rights for others.

An hour to a working register

Build the sheet in one sitting.

Enter last month's passes to test it, then start using it for new ones. Run the old paper book and the sheet side by side for one week, so that you can compare and catch any missing column before you drop the paper. After the week, tell the guards and the store that the sheet is now the official record, and keep the paper book only as a fallback for power cuts. Set a reminder to review the layout after one month, when real use will show which columns are missing or never filled.

Five-minute self-check

Check your register against these points.

Two or more no answers mean this is worth fixing before anything else on your list.

Reader Q&A

Bottom line

A well built Excel register gives a small site real control: a clean layout, drop-down lists, an overdue formula and one owner.

Build it from the tables above, and move to a digital system when volume or the number of gates grows. Make that call on facts, such as how many overdue items you find each week.