Inventory reconciliation means comparing what your system says with what is actually on the shelves. Many companies do it once a year when it should be done much more often, simply because it seems long and complicated. With Excel and an organized method, a reconciliation that used to take days can be done in an afternoon. This guide shows you how.
What you need before you start
To reconcile, you need two files:
- The system inventory: an export from your management software, ERP or online store with the code, description and theoretical stock of each product.
- The physical count: the sheet where the team recorded what they found in the warehouse, with the same product code and the quantity counted.
Ideally both files are produced on the same day and at the same time, with no goods coming in or out in between. If that is not possible, note any movements that happened during the count so you can adjust for them later.
Step 1: standardize the formats
System exports usually come as CSV, while the physical count is done in an Excel sheet or even on paper that someone types up. First of all:
- Convert the CSV to Excel with the right encoding so that leading zeros in codes are not lost.
- Make sure codes use the same format in both files. “00123” and “123” will not match when you compare.
- Check that quantities are numbers.
Step 2: clean the data
Physical counts often contain blank rows, partial totals and products recorded twice because they were in two locations. To clean it up:
- Delete blank rows and subtotals.
- If a product appears in several locations, add up its quantities in a single row (a PivotTable is perfect for this).
- Check for exact duplicates with our duplicate remover, as they are usually typing mistakes.
Step 3: build a reconciliation table
On a new sheet, place the full list of codes (the union of both files). Then add these columns:
| Column | Content |
|---|---|
| Code | Unique product code |
| Description | Product name |
| System | Stock according to the system |
| Physical | Quantity counted |
| Difference | Physical minus System |
| Value | Difference times unit cost |
To bring in the values from each file you can use XLOOKUP or VLOOKUP with the code as the key. Use IFERROR so that products missing from one file show zero instead of an error.
Step 4: compare and locate differences
Sort the table by the absolute value of the Value column. That way you will see the differences with the biggest financial impact first, which are the ones worth investigating. A shortfall of 200 screws may be worth less than a single missing laptop.
If you also want to see what changed between two consecutive reconciliations (for example, August and September), upload both files to the comparison tool on our Excel tools page. You will get a report of the products whose discrepancies appeared, disappeared or changed.
Step 5: investigate the causes
Inventory differences almost always have an explanation. The most common ones are:
- Counting errors: items were counted in another unit (boxes instead of units).
- Unrecorded movements: a return that went back on the shelf but was never entered in the system.
- Code errors: a sale was recorded under the code of a similar product.
- Shrinkage: damaged, expired or lost products.
- Forgotten locations: stock in a second warehouse or in transit.
Before adjusting, recount the largest differences. A second count resolves many discrepancies.
Step 6: adjust and document
Once confirmed, enter the adjustments in your system and save the reconciliation table with the date. Add a column with the cause of each difference. Over time this history will reveal patterns, such as a product that is always missing or a location that always causes errors.
Tips to reconcile more often
- Do cycle counts: instead of counting everything once a year, count part of the warehouse every week.
- Prioritize by value: count expensive or fast-moving products more often.
- Reuse the template: save the reconciliation table as a model and only change the data.
- Combine files from several people: if different teams count different zones, join their sheets with our Merge Excel tool before you start.
Frequently asked questions
How often should I reconcile inventory?
It depends on volume and the value of your goods, but the usual recommendation is weekly or monthly cycle counts of part of the warehouse and a full reconciliation at least once a year, normally at year end.
What if a product appears in the count but not in the system?
It may be a product that was never set up, goods received without being recorded or a code error. Check recent delivery notes and, if you confirm the product is correct, register it before adjusting stock.
Can I reconcile if the system exports to PDF instead of Excel?
Yes. Convert the report with a PDF to Excel tool, check that the columns were extracted correctly and continue with the process described in this guide.
How do I stop the same discrepancies from recurring?
Record the cause of each difference and review the history every few months. If a cause keeps repeating, such as unrecorded returns, the problem is not the count but the process, and that is where it needs fixing.
Conclusion
Reconciling inventory with Excel is a matter of method: data in the same format, cleaning beforehand, a table with system and physical stock side by side, and a review prioritized by value. The free tools on our site help you at every step, from converting the export to comparing reconciliations from different months. Visit our Excel tools page and start matching your inventory with less effort.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
