Best Excel Inventory In and Out Formula and Transaction Table Layout
Question details
The user needs a reliable Excel layout and formula approach for tracking inventory arrivals, checkouts, returns, asset types, and current quantities.
- Product
- Microsoft Excel workbook
- Device & OS
- not provided
- Scenario
- A manually arranged, horizontal inventory worksheet is being used to record asset movements over time.
- Observed behavior
- The paper-style layout becomes difficult to automate and maintain as the number of inventory transactions grows.
Decide on consistent names for each asset type and movement status, such as Arrival, Checkout, and Return. Assign a unique Asset ID to individually tracked items so that every checkout can be matched with its return.
Rebuild the sheet as a normalized inventory transaction table
Use this approach when the current horizontal layout is hard to expand, filter, or calculate accurately as transactions increase.
Enter one inventory event per row instead of updating a running paper-style layout across columns. A transaction table preserves history and makes stock totals, outstanding checkouts, and asset-level reporting easier to calculate.
On a new worksheet, create headers such as Date, Asset Type, Asset ID, Quantity, Status, Returned Date, and Signed Quantity. Select the range and use Insert > Table to create a structured Excel table.
Add a row for each arrival, checkout, and return. For example, enter Status as Arrival when stock is received, Checkout when an item leaves inventory, and Return when it comes back.
In the Signed Quantity column, enter =IF(OR([@Status]="Arrival",[@Status]="Return"),[@Quantity],-[@Quantity]) to add arrivals and returns while subtracting checkouts. Copy the formula down the table.
On a Summary sheet, list each Asset Type in column A. In B2, enter =SUMIFS(Transactions[Signed Quantity],Transactions[Asset Type],A2), replacing Transactions with your actual table name, then fill down.
Use the filter arrow in the Status column and select Checkout to review issued items. Include the Asset ID and Date columns to identify items that still need to be returned.
Create a PivotTable for inventory totals and movement history
Use a PivotTable when you need a quick summary by asset type, status, date, or person without building many separate formulas.
Track Inventory Movements in WPS Spreadsheets
WPS Spreadsheets can help you replace a horizontal inventory sheet with a row-based transaction table. Use SUMIFS to calculate current quantities by asset type, then create PivotTables to review checkouts, returns, and inventory movement history.
- 1. Open or create an inventory workbook: Open the existing workbook in WPS Spreadsheets, or select New > Spreadsheet to start a new inventory tracker.
- 2. Set up the transaction table: Add columns for Date, Asset Type, Asset ID, Quantity, Status, Returned Date, and Signed Quantity, then enter each inventory event on a separate row.
- 3. Calculate available inventory: Use the SUMIFS function on a summary sheet to total Signed Quantity for each asset type or Asset ID.
- 4. Create a movement report: Select the transaction data and choose Insert > PivotTable to summarize stock movements by asset type, status, and date.
Frequently Asked Questions
Why is a horizontal inventory layout difficult to automate in Excel?
A horizontal layout usually requires repeated manual updates across many columns and does not store each movement as a separate record. A row-based transaction table lets formulas, filters, and PivotTables process every arrival, checkout, and return consistently.
What columns should an inventory in and out transaction table include?
Include at least Date, Asset Type, Asset ID, Quantity, Status, and Returned Date. Add a Signed Quantity column when you want a single formula-based field that increases stock for arrivals and returns and decreases it for checkouts.
What Excel formula calculates current stock by asset type?
If the transaction table is named Transactions and contains Asset Type and Signed Quantity columns, use =SUMIFS(Transactions[Signed Quantity],Transactions[Asset Type],A2). Put the asset type to summarize in A2 and copy the formula down for other types.
Should a returned item overwrite the original checkout row?
No. Keep the original checkout row as part of the transaction history and add a separate Return row for the item. This creates an auditable record and allows the Signed Quantity calculation to restore the available quantity.
Can a PivotTable show inventory currently checked out?
Yes. Create a PivotTable from the transaction table, place Asset Type or Asset ID in Rows, and place Signed Quantity in Values. You can also use Status as a filter to review checkout activity, then refresh the PivotTable after adding new transactions.
