📅 Published by Subham Ghadge  |  Category: Corporate Procurement, Financial Auditing, Excel Automation  |  Free Download Workflow Automation Corporate Admin Data Validation

Purchase Requisition (PR) Management System in Excel

In corporate procurement, company budgets are rarely lost through major capital contracts. Instead, money leaks through small, unmonitored day-to-day operational expenses. When an IT manager orders spare network cables or an admin team orders printer toner, these requests often happen through informal emails, messaging apps, or paper slips.

Without a centralized tracking system, different departments frequently order the exact same items twice. These duplicate orders bypass proper audit controls, waste company budgets, and create extra work during annual financial audits.

To solve this operational friction, I created the Purchase Requisition (PR) Management System in Excel. It locks vendor selections using data validation, detects duplicate orders automatically with COUNTIFS formulas, and generates print-ready, auditor-compliant PR forms instantly using VLOOKUP arrays.

Locking Approved Vendors with Data Validation

Uncontrolled purchasing hurts a company's buying power. When different departments buy office hardware from different online retailers or local shops, the company loses out on bulk corporate discounts.

This Excel system prevents ad-hoc buying using a centralized Validation sheet. The Head of Procurement sets up an approved list of vendors and expense categories on a protected background tab.

The main entry screen relies on Data Validation dropdown menus linked to this master list. If an employee tries to enter an unapproved vendor name manually, Excel blocks the input and shows an error message. This keeps all purchases aligned with authorized suppliers automatically.

The Validation Master Sheet. Controls vendor and category dropdown lists to prevent unauthorized departmental purchases.

Automatic Duplicate Order Detection with COUNTIFS

Preventing duplicate requests is key to keeping operational spending under control. The master ledger uses background COUNTIFS formulas to scan existing entries for duplicate orders automatically.

The formula checks three fields at the same time: Company Name, Expense Category, and Month: =COUNTIFS(Company_Range, "MediaCorp", Category_Range, "IT Assets", Month_Range, "June")

If the formula finds that a request for "Housekeeping Supplies" for "MediaCorp" already exists within the active month, Conditional Formatting highlights the duplicate row in bright red.

This visual warning alerts staff before the PR form is printed or sent for manager sign-off—preventing double purchases early, just as our Panchwa Rent Income System prevents double-billing for overlapping utility readings.

The Central Registry. Duplicate checking formulas scan past entries to catch repeated requests within the same month.

Automated PR Form Generation using VLOOKUP

Creating purchase order forms manually leads to formatting errors and consumes valuable administrative time. This system separates data entry from document printing completely.

Once a purchase request is logged in the main registry, you open the printable 'Requisition Form' tab and select the PR serial number from a dropdown box.

An array of VLOOKUP and INDEX-MATCH formulas fetches the request details—date, requesting department, approving manager, item descriptions, and quantities—and populates the form automatically.

The sheet also builds a standardized PR Number using string concatenation (e.g., combining ="PR/" & Company_Code & "/" & TEXT(Date,"YY-MM") & "/" & TEXT(Row_Number,"000") to form PR/Admin/PUNE/25-26/014).

The result is a clean, printable A4 document ready for signatures from the Preparer, Reviewer, and Approving Manager.

The Automated Requisition Form. Selecting a PR serial number fills out the printable form automatically.

Managing Multiple Companies & Cost Centers

Large organizations often manage sister companies or different branch offices from a central administrative office. Managing separate Excel files for each branch can easily lead to mixed-up budgets.

This procurement tracker keeps all operations in one structured database. By including a "Company Name" column for every entry, the procurement team can filter and analyze spending for each branch or subsidiary independently.

During quarterly budget reviews, finance managers can filter the main data table by company name to inspect spending habits instantly without consolidating multiple files.

Workflow Best Practices & Sheet Protection

To get the best results from this tracking system, establish a simple rule: accounts payable will process vendor invoices only when attached to an official, sequentially numbered PR form generated by this system.

Keep the master Excel file on a secure shared network drive and password-protect the background Validation tab using Excel sheet protection. Leave only the data entry cells unlocked so team members can log purchase requests safely without modifying underlying formulas.

Frequently Asked Questions

Can we customize the PR number format to match our company naming rules?
Yes. You can edit the text concatenation formula in the PR Number cell to match your company's naming convention (e.g., adding branch codes or fiscal year prefixes).

Connect For Custom Procurement Automation

If your organization needs custom procurement spreadsheets, automated invoice workflows, or Power BI dashboards to track purchasing across multiple branches, feel free to reach out.