Excel inventory problems usually appear as a number nobody can explain: a stock count that does not match, a total that skips new rows or several versions of the same sheet. Check the recorded movements, the calculation and the stock being compared. Then correct the cause with evidence, rather than simply changing the closing quantity.
A wrong quantity can come from missing activity, an incorrect calculation or a comparison between different kinds of stock. Use the symptom to choose the first check. Keep the source documents and original workbook available while you investigate.
| What you notice | What to inspect | A practical correction |
|---|---|---|
| The workbook shows more units than the physical count. | Completed deliveries, unrecorded damage, duplicate receipts and the stock-out formula for the same item and cut-off. | Trace the difference to an event or calculation. Recount uncertain stock before reviewing any adjustment. |
| A new row does not change the total. | Whether the total includes that row, whether a lookup matches its item code and whether the quantity is numeric. | Repair the range or reference, correct the input type and test another new row in a copy of the workbook. |
| One item appears under several names or codes. | The item master and movement rows, including spaces, pack sizes and units of measure. | Agree one product identity and unit. Review historical rows before combining records that may represent different items. |
| A movement seems to have been recorded twice. | Source reference, document line, movement type, quantity and completion date. | Confirm whether it is a duplicate or a genuine partial delivery. Reverse or correct only the entry proven to be wrong. |
| The available quantity is unclear. | Whether the sheet mixes physical stock, purchase orders, customer commitments and goods on hold. | Define each quantity separately and make the report's scope explicit before staff promise stock to a customer. |
| Pasted rows produce unexpected results. | Validation rules, inconsistent formulas, unknown item codes, dates and numbers stored as text. | Review pasted values and retest calculations. Do not assume every invalid value will trigger a validation message. |
| Different staff report different balances. | Which workbook each person opened, the latest completed movement recorded and any offline or downloaded copies. | Agree one operational workbook and update owner. Reconcile other copies before retiring them. |
| Yesterday's transactions appear only at the end of the week. | The physical completion time, entry time and the team's agreed posting window. | Assign responsibility for timely recording and flag unposted movements before anyone uses the balance. |
These three checks answer different questions. A formula can be correct even when a delivery was never entered. A complete list of movements can still feed the wrong formula. A correct on-hand total can differ from a figure that includes incoming goods or customer commitments.
Match receipts, deliveries, returns and other relevant physical movements to identifiable rows. Check what actually happened and when. A purchase order alone does not establish that stock arrived, and a customer order alone does not establish that stock left.
Inspect the range, lookup key, sign convention and references used by the affected item. Check new rows, copied formulas and numeric inputs. Test the calculation against a small set of source-backed movements instead of judging it only by the absence of an Excel error.
Use the same item, unit, location and cut-off on both sides of the comparison. Identify whether the figure means physically on hand, usable, reserved or expected stock. Count damaged or held goods under the agreed scope and show their status separately.
Consider this invented example for one item, A-100, recorded in individual units at one storeroom. The opening quantity is 100. A receipt adds 24 units, then completed deliveries remove 6 and 12 units. A count after those movements finds 106 units. The movement evidence and arithmetic agree when every row is included.
Suppose the D-002 row is present but sits outside the stock-out total's range. The workbook calculates 100 + 24 − 6 = 118. The formula can return a normal number while omitting the 12-unit delivery. Check which rows the formula actually uses.
Extend or repair the calculation so it includes the reviewed D-002 row. The full calculation is 100 + 24 − 6 − 12 = 106. Retest the next appended movement and retain a record of the formula change and reviewer.
The delivery already exists and explains the stock change. Adding a second negative 12-unit adjustment to compensate for the broken total would create another stock reduction once the formula is fixed. Repair the calculation first; use an adjustment only for a separately established physical variance.
| Recorded event | Signed quantity | Running balance |
|---|---|---|
| Agreed opening quantity | +100 | 100 units |
| Completed receipt: R-001 | +24 | 124 units |
| Completed delivery: D-001 | −6 | 118 units |
| Completed delivery: D-002 | −12 | 106 units |
Choose one item and one defined stock position before reviewing the whole workbook. The aim is an explained correction that another person can check. If stock is still moving during the investigation, record those movements separately so they do not distort the agreed cut-off.
Record the current facts in one shared place.
Confirm what is known and what needs attention.
Make the next decision or follow-up accountable.
Complete the next task and record the outcome.
Refresh the shared view when facts change.
A dependable workflow keeps the shared record and the next action aligned.
Set the scope. Record the item code, unit, location, stock state and cut-off time. Confirm that the workbook balance and physical count use that same definition.
Save the evidence. Preserve the original workbook or relevant version, source references and count sheet. Make a review copy for formula testing and name the person responsible for the investigation.
Trace the rows. Match completed receipts and deliveries to movement entries. Check returns, duplicate postings, partial quantities and late entries. Separate missing events from rows that exist but are calculated incorrectly.
Check the formula. Inspect ranges, lookup keys, signs, copied formulas and values stored as text. Test known movements in the review copy and compare the result with the supporting documents.
Review the fix. Describe the cause, proposed correction, source evidence and reviewer. Correct the wrong row or formula through the agreed process. If a physical variance remains, investigate and approve an adjustment separately.
Recount and retest. Confirm uncertain quantities, recalculate the same stock position and test a fresh normal entry. Apply the reviewed repair to the authoritative workbook and communicate the affected balance to its users.
Some shortcuts remove the visible mismatch while leaving its cause unresolved. A useful repair preserves the event history and explains what changed, so the next reviewer does not have to reconstruct the same problem.
Recurring issues usually point to workflow-control gaps, not one isolated data-entry mistake.
Typing the physical count over a calculated balance breaks the explanation between opening stock and movements. Find the missing event or calculation defect first. Document a reviewed physical variance through a separate adjustment process when one remains.
One document can contain several item lines or support genuine partial movements. Compare the item, line, movement type, quantity and completion evidence before treating a repeated reference as a duplicate. Retain a reason for any correction.
Expected receipts and planned deliveries need their own status. Adding an entire purchase order to on-hand stock before receipt, or removing a customer order before dispatch, changes the balance ahead of the physical event. Check actual completed quantities.
An earlier workbook version may help explain a formula change. Restoring it also risks losing valid movements entered afterwards. Compare the versions and later source documents, coordinate with current users and decide which changes need to be retained.
A corrected total may work for today's rows but omit the next entry. Test an appended receipt, a stock-out entry and a relevant return or other exception in the review copy. Check that formulas and validation still cover the new data.
Excel has useful calculation, validation and collaboration features. Their setup matters. Combine workbook controls with clear movement ownership and source evidence so the team can explain both the calculation and the physical activity behind it.
Excel's error checking can flag common problems such as inconsistent formulas or omitted cells. Microsoft explains that these checks do not guarantee an error-free worksheet. Also test whether the total includes the intended movements; an unrecorded delivery cannot be found by checking a formula alone.
Use agreed lists and quantity rules to reduce entry mistakes. Microsoft notes that copying, filling, formulas and macros can introduce values outside validation criteria. Check new codes, dates and quantities after bulk entry, and use invalid-data review where appropriate.
Separate editable inputs from calculations and protect the cells users should not change during routine entry. Keep the formulas documented and assign a workbook owner who can maintain them. Locked cells help prevent accidental edits; they do not prove that a stock event was entered correctly.
Excel supports co-authoring with compatible versions and file formats when the workbook is stored in OneDrive or SharePoint Online. Version history can help investigate changes. Agree the authoritative file, update responsibilities and posting window; collaboration features still need those operating rules.
The best practice is to make the next action clear before the situation becomes urgent.
A single column labelled stock can be misleading when different teams use it for different questions. Define the quantity a report answers and preserve its unit, location and cut-off. Make commitments and exceptions visible alongside the physical position.
Base the balance on the opening position and completed movements for that item and location. Show whether held or damaged goods are included. Do not combine individual units with cartons unless a defined conversion has been applied consistently.
Track expected quantities against their purchase or receiving references. A partial receipt changes what has arrived and what remains outstanding. Keep expected stock separate from the quantity a storekeeper can count or pick now.
Customer commitments can affect what the team may promise next even while goods remain physically on hand. Define how reservations, holds and cancellations affect the saleable quantity. Avoid subtracting a commitment twice when its completed delivery is recorded.
The best practice is to make the next action clear before the situation becomes urgent.
If one workbook repair resolves the issue and staff can maintain the controls, keep using the improved process and review it. If missing references, repeated entry or unclear document status keep returning across people, assess whether supported inventory records in TREX Grow fit the workflow you need.
TREX Grow supports shared product records with product codes and units of measure, plus optional SKU or barcode details. Prepare consistent item definitions first so purchasing, stock and sales teams refer to the same product.
Supported stock entries, delivery orders and inventory adjustments give the team structured records to review. Check the source reference, quantity and relevant document status rather than relying on an unexplained closing number in a standalone sheet.
Relevant operational records include creator and status information that can support review and follow-up. Agree who records completed events, who checks exceptions and which actions need approval. Software still depends on timely entry and defined operating rules.
Review your receiving, dispatch, return and adjustment needs before changing tools. Confirm any location, transfer or warehouse requirements against the applicable TREX Grow configuration and plan. Continue physical checks and use Excel for analysis where it remains useful.
Start with one item, one location and an agreed cut-off. Match its completed movements to the rows, check the formula and record the cause of any difference. Use the stock movement guide to strengthen the underlying record, then recheck whether the same problem returns.
Yes, when the team uses consistent item codes and units, records completed movements promptly, maintains formulas and compares counts with the same stock scope. Review whether staff can explain the balance and keep those controls working. Workbook row count alone does not decide whether the process is suitable.