Purchase Order Register (Excel)
Most purchase follow-up goes wrong in the gap between "we ordered it" and "it has all arrived". This register keeps one row for every line of every purchase order and matches each receipt against it, so it always shows how much is still pending, what that pending material is worth, which lines are past their due date and by how many days. A summary sheet gives each supplier's open and overdue position for your weekly follow-up call.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| PO no. | PO date | Supplier | Item | Unit | Qty ordered | Rate (INR) | Tax % | Due date | Short-close? | Line value (INR) | Tax (INR) | Line total (INR) | Received | Pending qty | Pending value (INR) | Status |
| PO-101 | Sep-26 | Bharat Steel Traders | MS round bar 20 mm | kg | 2,500 | 68 | 18 | Oct-26 | 1,70,000 | 30,600 | 2,00,600 | 2,500 | 0 | 0 | Closed | |
| PO-101 | Sep-26 | Bharat Steel Traders | MS plate 10 mm | kg | 1,200 | 72 | 18 | Oct-26 | 86,400 | 15,552 | 1,01,952 | 700 | 500 | 42,480 | Overdue | |
| PO-102 | Oct-26 | Precision Bearings Co. | Ball bearing 6205-2RS | Nos | 400 | 145 | 18 | Oct-26 | 58,000 | 10,440 | 68,440 | 250 | 150 | 25,665 | Overdue | |
| PO-102 | Oct-26 | Precision Bearings Co. | Taper roller bearing 30206 | Nos | 150 | 410 | 18 | Oct-26 | 61,500 | 11,070 | 72,570 | 0 | 150 | 72,570 | Overdue | |
| PO-103 | Oct-26 | Shree Packaging Industries | 5-ply corrugated box 600x400x400 | Nos | 1,000 | 46 | 5 | Oct-26 | 46,000 | 2,300 | 48,300 | 1,000 | 0 | 0 | Closed | |
| PO-104 | Oct-26 | Bharat Steel Traders | MS angle 50x50x6 | kg | 800 | 70 | 18 | Nov-26 | 56,000 | 10,080 | 66,080 | 0 | 800 | 66,080 | Open | |
| PO-104 | Oct-26 | Bharat Steel Traders | GI sheet 1.2 mm | kg | 300 | 88 | 18 | Nov-26 | Yes | 26,400 | 4,752 | 31,152 | 120 | 0 | 0 | Short-closed |
What's inside
- Orders sheet: PO number, PO date, supplier, item, unit, quantity, rate, tax percent and due date for each PO line, 500 ready rows
- Line value, tax and total calculate themselves; received quantity is added up from the Receipts sheet by PO number and item
- Pending quantity and pending value, status (Open, Part received, Overdue, Closed, Short-closed) and days late, with overdue lines highlighted
- Receipts sheet: date, PO number, item, quantity and GRN or challan number, with a check that warns when a receipt does not match any PO line
- Pending by supplier: ordered value, pending value, open lines, overdue lines and the oldest delay for each supplier
- Settings for your company name, currency code and an "as on" date (today unless you type another date)
How to use it
- On the Settings sheet type your company name and your currency code (for example INR, USD, EUR or AED). Leave "As on" as today.
- On the Suppliers sheet replace the sample suppliers with your own. The supplier drop-down on the Orders sheet reads this list.
- When a PO is issued, add one row per item on the Orders sheet: PO number, date, supplier, item, unit, quantity, rate, tax percent and the delivery due date.
- Each time material arrives, add a row on the Receipts sheet with the date, PO number, the item exactly as written on the PO line and the quantity received. The "Match" column must say OK.
- The Orders sheet now shows received and pending quantity for each line. Lines past their due date turn red with the days late.
- If a supplier will never send the balance (item discontinued, order cancelled), type Yes in the Short-close column so the line stops showing as pending.
- Open the Pending by supplier sheet before your follow-up calls, and filter the Orders sheet by Status = Overdue (Data › Filter) to see what to chase.
Tips
- Write the item name the same way on the PO line and on every receipt; copy and paste it if needed. A receipt that says "No such PO line" is not counted anywhere.
- For a PO with several delivery dates, enter the item twice with the quantity and due date of each delivery and add -1, -2 to the PO number (PO-104-1, PO-104-2).
- Received more than ordered? Received shows the true figure, pending stays at zero and the line closes; discuss the excess with the supplier before accepting it.
- Tax percent is only used for the value columns. Leave it 0 if your rates already include tax.
Questions and answers
What is a PO register?
A list of every purchase order line with what was ordered, what has been received and what is still pending. Purchase teams use it to follow up suppliers and stores use it to know what to expect.
How is pending quantity worked out?
Quantity ordered minus the total received for that PO number and item on the Receipts sheet, never below zero. A short-closed line has nothing pending.
When is a line marked Overdue?
When something is still pending and the "As on" date on the Settings sheet is after the line's due date. Change the "As on" date to see the position on any day.
Can I use it with my accounting or ERP software?
Yes. Paste PO lines and receipts exported from your software as values (right-click › Paste Special › Values) into the yellow columns; the grey columns calculate as usual.