How to Create a Purchase Order in Excel - Practical Guide

This tutorial teaches you to create a professional purchase order in Excel from scratch. You'll learn to structure a PO template with vendor details, itemized products, pricing, and totals. A well-formatted purchase order ensures clear communication with suppliers, reduces order errors, and maintains proper business documentation for accounting and compliance purposes.

Why This Matters

Purchase orders are legally binding documents that protect your business by clearly defining quantities, prices, and delivery terms. They streamline procurement, reduce disputes, and create an audit trail for financial records.

Prerequisites

•Basic Excel knowledge (creating sheets and formatting cells)
•Understanding of business terminology (SKU, unit price, VAT/GST)
•Access to Excel 2016 or later version

Step-by-Step Instructions

Set up the header section

Create your company name, logo, and contact details in the top rows. Go to Home > Merge & Center to combine cells for the title, then add your company address, phone, and email below.

Add purchase order details

In the upper right area, add PO Number, Date, and Due Date fields. Use Home > Borders to create boxes around these cells for clarity.

Create vendor information section

Add labels like 'Bill To:' and 'Ship To:' with corresponding fields for vendor name, address, and contact details. Use a light background color via Home > Fill Color to distinguish this section.

Build the itemized table

Create column headers: Item #, Description, Quantity, Unit, Unit Price, and Total. Use Home > Font > Bold to emphasize headers, then add borders via Home > Borders > All Borders.

Add pricing calculations and totals

In the Total column, enter formula =C5*E5 (Quantity × Unit Price) and copy down for each row. At the bottom, add Subtotal, Tax (use =F15*0.1 for 10% tax), and Grand Total rows with SUM formulas.

Alternative Methods

Use Excel templates

Go to File > New and search 'Purchase Order' in the template gallery for pre-built layouts. This saves time on formatting but may require customization to match your branding.

Create from Google Sheets

Use Google Sheets for collaborative PO creation, then export as .xlsx for Excel compatibility. This allows multiple team members to edit simultaneously before finalizing.

Tips & Tricks

✓Add a 'Notes' or 'Special Instructions' section at the bottom for payment terms and delivery conditions.
✓Use conditional formatting to highlight overdue POs or high-value orders for quick reference.
✓Lock the header and totals sections by going to Review > Protect Sheet to prevent accidental edits.
✓Save your PO template as a macro-enabled workbook (.xlsm) to automate PO number sequences.

Pro Tips

★Create a dropdown list for vendor names using Data > Data Validation to ensure consistency and reduce typos.
★Use VLOOKUP formulas to auto-populate unit prices from a master product database sheet, saving time and reducing errors.
★Implement a PO numbering system by adding a column linked to TODAY() function so each new PO auto-dates.
★Create a summary dashboard sheet that tracks all active POs using pivot tables and status indicators.

Troubleshooting

Formulas showing as text instead of calculating

Go to Home > Number Format and change from 'Text' to 'Number'. Then press F2 on the cell and Enter to recalculate.

Merged cells causing alignment issues in the table

Avoid merging cells in the itemized table section. Instead, use Home > Format Cells > Alignment > Center Across Selection for a cleaner layout.

Unable to print entire PO on one page

Go to Page Layout > Orientation > Landscape, then adjust margins via Page Layout > Margins > Narrow to fit content on one page.

Dropdown list not appearing for vendor selection

Ensure Data > Data Validation is applied to the correct range and that the source list (vendor names) is on the same or a separate protected sheet.

Related Excel Formulas

SUM VLOOKUP IFERROR TODAY IF CONCATENATE ROUND

Frequently Asked Questions

Can I add multiple pages to a single purchase order?

Yes, you can add additional item rows by copying the table rows and extending it. Use Insert > Page Break if you need the table to span multiple printed pages. Ensure the header and total sections appear on each page by using Print Titles via Page Layout > Print Titles.

What's the best way to track changes to a PO after it's issued?

Create a revision history column or separate sheet noting date, what changed, and who approved it. Alternatively, enable Track Changes via Review > Track Changes > Record Edits to maintain a complete audit trail.

How do I convert a PO to a PDF for emailing to vendors?

Go to File > Export As > Create PDF (or File > Save As and select PDF from the dropdown). This ensures the formatted PO looks consistent across devices and prevents accidental edits after sending.

Should I use the same PO number format for all vendors?

Yes, using a consistent format (e.g., PO-2024-001) helps with tracking and organization. You can automate this by combining text and row numbers using a formula like ='PO-'&YEAR(TODAY())&'-'&ROW()-10.

Can I link my PO to an inventory system?

Advanced users can use VBA macros or integrate Excel with external databases via Power Query (Data > Get Data). For basic inventory tracking, use VLOOKUP to pull product details from a master product sheet.