To turn an inventory report PDF to Excel, import item codes as text so leading zeros survive, keep quantity, unit of measure and unit cost in separate number columns, and give each location its own row. Then multiply quantity by unit cost on every line and check that the sum matches the report's printed inventory value total.
Item codes with leading zeros in an inventory sheet
An item code, also called a SKU or part number, identifies one product in the inventory system, and many systems pad codes with leading zeros to a fixed length. Excel strips those zeros from anything that looks like a number, so "000417" becomes 417. Set the item code column to text before the data arrives, not after.
Why after is too late
Once Excel has turned "000417" into 417, the zeros are gone from the cell. A custom number format can make it look like 000417 again, but the value underneath is still 417, and a lookup against a list of real codes will fail. The only safe fix is to import again with the column set to text.
In Excel's Data, From Text/CSV import, or in Power Query, change the column type to Text before loading. When pasting from a converter's output, format the destination column as Text first, then paste values.
Other code shapes that break
- Codes with a dash or slash, such as "2200-15" or "3/8-BOLT", can be read as dates. "3/8" becomes the 8th of March.
- Long numeric codes, such as barcodes, switch to scientific notation, and any digits past the fifteenth are lost.
- Codes with an E in the middle, such as "12E4", are read as 120,000.
Text format prevents all three. Add a check column with LEN on the code. If the system uses a fixed length, every row should show the same number.
Matching codes across reports
The item code is the key that joins the inventory report to purchase orders, sales and the general ledger. Keep it exactly as the system prints it, including case and dashes. If two reports disagree on format, add a cleaned key column on both, and leave the original code untouched beside it.
Inventory quantities, units of measure and unit costs as numbers
Quantity on hand, unit of measure and unit cost each belong in their own column, and quantity and cost must be real numbers. Reports often print "1,200 EA" or "48 GAL" in one cell. Split the unit of measure out, remove thousands separators, and confirm the status bar shows a sum when you select the column.
Units of measure
A unit of measure says what one counts: each, box, case, gallon, pound. It matters because the unit cost is per that unit. A line of 12 boxes at 30.00 per box is 360.00. If the report counts the same item in eaches elsewhere, at 2.50 each with 12 to a box, mixing the two lines without converting gives nonsense totals.
Keep the unit as printed in its own column. If one item appears in different units across reports, add a conversion factor column rather than changing the printed quantities.
Negative quantities
Some reports show negative quantity on hand, printed as "-14" or "(14)". A negative count means the system recorded more going out than coming in, often because a receipt was not entered. Keep the negative as a number, and flag it. It is a real signal about the books, and it will reduce the value total, just as it does in the report.
Unit cost with more decimals than shown
Inventory systems often hold unit costs to four or more decimal places but print only two. That gap is behind most small value differences, covered in its own section below. For now, keep whatever decimals the report prints, and do not round further.
Inventory locations and items listed more than once
Many inventory reports list the same item once per location: a warehouse, a bin, a store or a truck. That is correct, and each location row should stay a separate row in Excel with a "Location" column. Totals per item then come from a pivot table or SUMIFS, not from deleting what look like duplicates.
Location as a heading
Some reports print the location as a heading above a block of items, rather than on every line. After conversion, the heading arrives as a row with text but no quantity. Copy the location down onto each item row in its block, then delete the heading rows. A filter on blank quantity finds them quickly.
Subtotals by location
A report grouped by location usually prints a subtotal after each group. Those subtotal rows look like items with no code. Mark them with a "Row type" column or move them to a separate check sheet. Left in place, they double the value total.
Real duplicates
Sometimes a page break repeats the last line of one page at the top of the next. That is a true duplicate, and it inflates both quantity and value. Look for consecutive rows with the same code, location and quantity at page boundaries. A count of rows against the report's printed line count, if it has one, settles the question.
Does the inventory value total still match the report?
The inventory value total matches when, for every row, quantity times unit cost equals the extended value printed on that line, and the sum of the extended values equals the report's grand total. If both checks pass, the sheet holds the same inventory the report does. If either fails, the size of the gap shows where to look.
A worked example
The report below is an example, made up for illustration. "Printed value" is the extended value on the PDF. "Qty x cost" is calculated in Excel from the printed quantity and unit cost.
| Item code | Description | Location | Qty | UoM | Unit cost | Printed value | Qty x cost |
|---|---|---|---|---|---|---|---|
| 000417 | Hex bolt M6 | WH-A | 1,200 | EA | 0.18 | 219.00 | 216.00 |
| 000418 | Hex bolt M8 | WH-A | 850 | EA | 0.24 | 204.00 | 204.00 |
| 010233 | Steel hinge 3 in | WH-B | 340 | EA | 2.65 | 901.00 | 901.00 |
| 010233 | Steel hinge 3 in | WH-C | 60 | EA | 2.65 | 159.00 | 159.00 |
| 2200-15 | Paint, white | WH-B | 48 | GAL | 18.40 | 883.20 | 883.20 |
| Total | 2,366.20 | 2,363.20 |
The printed grand total on the report is 2,366.20, and the printed values add up to it, so no row is missing. But the calculated total is 3.00 lower. The whole gap sits on item 000417: 1,200 times 0.18 is 216.00, yet the report prints 219.00. The system's real unit cost is 0.1825, printed rounded to 0.18. 1,200 times 0.1825 is 219.00.
Item 010233 appears twice, once in WH-B and once in WH-C. Both rows stay. A SUMIFS on the code gives 400 hinges worth 1,060.00 across both warehouses.
Which number to trust
Trust the printed extended value for the total, because it comes from the system's full-precision cost. Use your calculated column only as a check. Where they differ by more than rounding, look at the scan or the PDF for a misread digit in quantity or cost.
Why is the inventory value off by a few cents or dollars?
An inventory value that is off by a few cents or dollars almost always comes from rounding: unit costs printed with fewer decimals than the system holds, extended values rounded line by line, or an average cost that changes with each receipt. A gap the size of one line's value is a different matter.
Sorting the gap by size
- Cents across the whole report. Line-level rounding. Each extended value is rounded to the cent, and the grand total adds the rounded values. Recalculating from rounded unit costs drifts a little. Accept it, or work from the printed values.
- A few dollars on one line. A hidden decimal on a high-quantity item, as with item 000417 above. Divide the printed value by the quantity to recover the real unit cost.
- Exactly one line's value. A row was dropped or doubled during conversion. Compare row counts and look at page breaks.
- A round number such as 100.00 or 1,000.00. Usually a misread digit in OCR on a scanned report, or a thousands separator read as a decimal point.
Costing methods
Average cost, FIFO and standard cost all produce different values for the same quantity. If you compare an inventory report against another report from the same system, check that both use the same method and date. A valuation run on a different day will not tie, even if nothing was lost.
Value is not the only aging question about stock. A receivables aging report groups money owed by how long it has been open, and getting an aging report into Excel with buckets that still total uses the same subtotal checks.
When an inventory report PDF should not be your count
An inventory report PDF should not be your count when you can export the same report from the system as Excel or CSV, when the report date is not the date you need, or when someone else will rely on the valuation. A PDF records what the system believed, not what is on the shelves.
Year-end and lender reviews are the usual high-stakes cases. When an inventory listing goes into a loan application, it is rarely the only PDF in the pack, and laying out a credit report PDF as one table of accounts covers another one that often comes up. For anything that matters that much, ask for the native export or a fresh physical count reconciled to the system.
Inventory files also hold cost data that a business may consider sensitive. If you converted one with an online tool, find out how to have the copy removed afterwards. How to ask a PDF converter to delete your data gives the questions and a request you can send.
The money side of stock, the supplier payments and the sales deposits, runs through the bank account. FastBankConvert is designed to turn those bank statement PDFs into spreadsheet rows, and to say when a statement's balances do not add up. It is not open yet; the get started page will tell you when trial conversions are ready.