Inventory Invoice Spreadsheet: Fields, Formulas, and Use

Formulas & functions · Updated November 21, 2026

An inventory invoice spreadsheet is a workbook for recording supplier invoice lines and checking them against the goods received. Set it up with invoice, item, quantity, cost, and receipt fields so you can spot mismatches before updating stock or approving a bill.

What an inventory invoice spreadsheet is for

The phrase can mean either a spreadsheet that tracks inventory purchases shown on supplier invoices or an invoice template used to bill customers for goods. The first is the more useful meaning when managing stock: it connects what a supplier charged with what your business ordered and actually received. A customer invoice spreadsheet has a different purpose and normally records sales, amounts due, and payment status.

A supplier invoice is evidence of what the supplier says is payable; it is not, by itself, proof that every item arrived. Comparing the invoice with a purchase order and a receiving record can reveal short shipments, duplicate billing, incorrect prices, or damaged goods. The spreadsheet helps organize that comparison, but it does not replace the original documents, an accounting system, or your organization’s approval process.

For example, a supplier may invoice 12 storage bins at $8 each. If the warehouse received only 10, the invoice line amount can still correctly calculate as $96, while the receipt check shows a shortage of two. Keeping billed quantity and received quantity in separate columns makes that distinction visible.

Fields to include

Use one row for each product line, not one row for each whole invoice. An invoice with five different products should have five rows, with the invoice number and supplier repeated on each row. That layout makes filtering, sorting, summaries, and later reconciliation more dependable.

  • Invoice details: supplier invoice number, supplier name or ID, invoice date, due date if relevant, and purchase order number.
  • Item details: SKU or other consistent item code, description, unit of measure, and, if useful, a supplier’s product code.
  • Billing details: quantity invoiced, unit price, line amount, currency, and any discount or charge recorded on the invoice.
  • Receiving details: quantity received, receipt date, receiving record or delivery reference, and a note for shortages, damage, or substitutions.
  • Review status: a simple status such as Pending, Checked, Query, or Approved, plus the reviewer or date checked if your process requires it.

Decide what a row represents before entering data. If a supplier invoices the same SKU at two different prices on one invoice, retain two lines rather than combining them; that preserves the billed detail. If an invoice includes freight or tax at the document level rather than as an item line, record those amounts separately so they are not mistakenly treated as the unit cost of a product.

How to set up the workbook in Excel

In Microsoft Excel for Microsoft 365 on Windows, create a workbook with an invoice-lines sheet and, if you want to track stock movements, a separate transactions sheet. Keep the original supplier invoice and receiving documents in your normal records, and add a reference or file location in the workbook when useful. Avoid putting several unrelated kinds of information into one cell; separate columns make the records easier to check.

  1. On the invoice-lines sheet, add clear headers in row 1, such as Invoice No, Supplier, Invoice Date, PO No, SKU, Description, Qty Invoiced, Unit Cost, Line Amount, Qty Received, and Difference.
  2. Enter each invoice item on its own row starting in row 2. Use the same SKU spelling and format each time; codes with leading zeros should be stored consistently so Excel does not turn them into different-looking values.
  3. Select the data and choose Insert > Table. Confirm that the table has headers. A table expands as rows are added and makes it easier to filter by supplier, date, SKU, or review status.
  4. Format dates as dates and prices as currency or numbers with the appropriate decimal places. Use data validation or a controlled list for fields such as review status if multiple people enter records.

Excel for Mac also has Insert > Table, though the ribbon layout can differ slightly by release. Excel for the web can open and edit workbooks too, but people using different Excel versions should agree on the same columns and entry rules to avoid inconsistent records.

Useful formulas and a worked example

Formulas can calculate billed line values and highlight quantity differences, but they cannot establish whether a shipment was actually received. Enter and verify the source quantities first. In the following examples, use a consistent convention for units: a quantity of 12 must mean 12 of the same unit described on that line.

With Qty Invoiced in G2 and Unit Cost in H2, enter this in I2 for Line Amount: =G2*H2. If G2 contains 12 and H2 contains 8, the result is 96. Fill the formula down the column, or enter it in an Excel table column so the table can extend the calculation to new rows.

With Qty Invoiced in G2 and Qty Received in J2, enter this in K2 for Difference: =G2-J2. If the supplier billed 12 and the receiving record shows 10, the result is 2, meaning two units are not accounted for as received. A zero means the quantities match; a negative number means recorded receipts exceed the billed quantity and should also be checked. Label the column clearly so users know which direction the subtraction represents.

For a simple stock-on-hand calculation, keep a separate Transactions sheet with SKU in column A, transaction type in column C, and quantity in column D. Use R for a receipt and I for an issue, and record each stock movement as a separate row. On a Stock sheet, assume SKU is in A2 and opening quantity is in B2. Enter this in C2 to calculate current quantity from transactions: =B2+SUMIFS(Transactions!$D$2:$D$1000,Transactions!$A$2:$A$1000,A2,Transactions!$C$2:$C$1000,"R")-SUMIFS(Transactions!$D$2:$D$1000,Transactions!$A$2:$A$1000,A2,Transactions!$C$2:$C$1000,"I"). If the opening balance is 20, receipts total 12, and issues total 7 for that SKU, the formula returns 25. Extend the ranges if the transaction log grows beyond row 1000, or use Excel tables and structured references if you are comfortable maintaining them.

A receipt should be recorded once in the transaction log, not once on the invoice sheet and again on another sheet that feeds the stock calculation. Otherwise, stock may be counted twice. Likewise, do not treat an invoice date as a receipt date unless the goods arrived on that date.

A practical invoice-checking workflow

A consistent process is more valuable than a complicated workbook. When a bill arrives, record its identity and item lines, then compare the details with the order and receiving information before treating it as ready for payment. A straightforward sequence is:

  1. Enter the supplier invoice number, supplier, invoice date, and purchase order reference. Check for an existing invoice number from that supplier to help catch accidental duplicate entry.
  2. Enter each billed SKU, description, quantity, and unit price exactly as shown. Keep the supplier’s line detail rather than silently changing it to match your own records.
  3. Compare each line with the purchase order and receiving record. Record the actual quantity received and note any substitution, shortage, damage, or backorder.
  4. Review calculated line amounts against the invoice. Check any tax, discount, freight, or other document-level amount separately against the source document and your organization’s rules.
  5. Set a review status and retain a reference to the supporting documents. If a difference needs clarification, mark it as a query rather than making an unexplained edit.

Suppose an order called for 12 bins at $8 each, but the delivery record shows 10 received and the invoice charges for 12. The spreadsheet can show the $96 billed line amount and a quantity difference of 2. A reviewer can then check whether the remaining two are backordered, whether the supplier made an error, or whether the receiving record is incomplete. The spreadsheet identifies the question; it does not decide which explanation is correct.

Common problems and ways to prevent them

The most frequent spreadsheet errors come from inconsistent data entry and unclear responsibility, not from arithmetic. A SKU entered once as 00125 and elsewhere as 125 may fail to match, while a quantity recorded in cases on one line and individual units on another can produce a misleading comparison. Specify units and preserve item codes in a consistent format.

  • Duplicate invoices: check supplier plus invoice number, and investigate a possible duplicate instead of deleting a row without a record.
  • Overwriting formulas: distinguish input columns from calculated columns, protect formula cells if appropriate, and periodically check that formulas extend to new rows.
  • Mixing dates: keep invoice date, due date, and receipt date in separate fields. They answer different questions.
  • Unexplained adjustments: record why quantities or prices were changed and who reviewed the difference. Preserve the original billed values where possible.
  • Shared-file conflicts: decide who can edit the workbook, keep a backup, and avoid circulating multiple copies that may later be mistaken for the current version.

Also be careful with totals. A sum of invoice line amounts may not equal the amount due if the bill includes freight, taxes, discounts, credits, or rounding. Do not force the spreadsheet total to match by changing product prices; show the relevant components and reconcile them to the invoice total separately.

When a spreadsheet is enough—and when it is not

A spreadsheet can be a reasonable starting point for a small operation with modest transaction volume, a limited number of editors, and a clear review routine. It is flexible: you can tailor columns to the way your business receives products, and staff can filter a list without learning a specialized system. It can also support a temporary stock count or help organize supplier queries.

Its limits become more important as activity grows. Manual entry is vulnerable to omissions and duplicates; simultaneous edits need controls; and a workbook does not automatically provide the connected purchasing, receiving, accounting, permissions, and audit functions that dedicated systems may offer. If many people update stock, stock levels need to be current across locations, or approvals and traceability are important, compare an inventory or accounting system with spreadsheet-based tracking. The right choice depends on transaction volume, control requirements, integrations, and the cost of errors—not simply on the number of spreadsheet rows.

Whichever tool you use, keep supplier invoices and receiving records according to your organization’s retention process. A spreadsheet summary is useful for searching and reporting, but it should not be assumed to replace source records or satisfy every accounting, tax, or recordkeeping requirement.

Frequently Asked Questions

Is an inventory invoice spreadsheet the same as an invoice template?

No. An inventory invoice tracker usually records supplier bills and compares billed products with received stock. An invoice template is used to create a bill, often for a customer. The columns and workflow depend on which task you mean.

Should I enter one row per invoice or one row per product?

Use one row per product line, and repeat the invoice number and supplier on each row. This preserves the detail needed to compare prices and quantities, filter by item, and summarize purchases.

How do I track stock received from invoices?

Record actual receipts in a stock transaction log, using the SKU, date, quantity, and a receipt reference. Keep the invoiced quantity separate, then compare the two. Do not increase stock simply because an invoice has arrived.

Can Excel automatically flag a mismatch?

Yes. A difference formula such as billed quantity minus received quantity returns zero when the quantities agree and a nonzero result when they differ. You can also use Home > Conditional Formatting to highlight differences, but someone still needs to investigate why they occurred.

Can I use one spreadsheet for several suppliers?

Yes. A single invoice-lines table can include multiple suppliers if each row has a consistent supplier name or ID and invoice number. Keep source documents identifiable and filter by supplier when reviewing records; avoid relying on color alone to distinguish vendors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.