Function Problems

How to Build an Excel Inventory Tracker for Issued and Returned Items

John Wilson Oct 22, 2026 868 views

Question details

The user wants to create a comprehensive system in Excel to track inventory that is issued to and returned by individuals, calculating current stock and outstanding items.

How to Build an Excel Inventory Tracker for Issued and Returned Items

Product: Excel
Device & OS: not provided
Scenario: Setting up a functional spreadsheet to monitor company assets or inventory checking in and out, while maintaining a history of all transactions.
Observed behavior: The goal state is an Excel workbook that automatically calculates remaining stock levels, identifies who holds outstanding items, and keeps a clear log of all issue and return transactions using formulas and PivotTables.

Before you start

Before creating your tracker, prepare a master list of all inventory items and define the specific statuses (e.g., 'in' and 'out') you will use. Ensure your data is organized without blank rows so that Excel Tables and PivotTables can function seamlessly.

Solution 1Recommended

Create an Inventory Tracker Using Excel Tables and COUNTIFS

Use a combination of a Transaction table, an Inventory table, and the COUNTIFS function to accurately monitor issued and returned items in real-time.

By structuring your data into official Excel Tables, your formulas will automatically adapt as you add new transactions. Utilizing the COUNTIFS function allows you to subtract the number of returned items from the issued items to find the current outstanding balance for any specific person and item.

Create a Tracker Table

Set up a worksheet with columns for 'Status', 'Item', and 'Person'. Select these headers, go to the 'Insert' tab, and click 'Table' to format it as a dynamic table. Name this table 'Tracker' in the Table Design tab.

Record Transactions

Begin logging your data. Enter 'out' in the Status column when an item is issued, and 'in' when it is returned. Fill out the corresponding Item and Person fields for each transaction.

Calculate Outstanding Quantities

Add a new column called 'Outstanding' to your Tracker table. Use the formula: =COUNTIFS([Status],"out",[Item],[@Item],[Person],[@Person])-COUNTIFS([Status],"in",[Item],[@Item],[Person],[@Person]). This calculates if the specific item is currently held by that person.

Set Up an Inventory Summary

Create a second table named 'Inventory' on another sheet. List all unique items and their starting stock. Use the SUMIFS function to subtract the total outstanding items from the Tracker table to display the remaining stock.

Insert a PivotTable for Issued Items

Select your Tracker table, go to the 'Insert' tab, and click 'PivotTable'. Put 'Person' and 'Item' in the Rows area, and filter by outstanding quantities greater than zero. This provides a clear report of who currently holds which items.

Create an Inventory Tracker Using Excel Tables and COUNTIFS

Refreshing Data: PivotTables do not update automatically. Whenever you add new 'in' or 'out' transactions to your Tracker table, remember to right-click the PivotTable and select 'Refresh' to update your outstanding items report.

Easily Track Inventory with WPS Spreadsheet

Build Your Inventory Tracker in WPS Office

WPS Spreadsheet provides powerful data management tools, making it incredibly easy to build a dynamic inventory tracker. It fully supports advanced formulas like COUNTIFS and dynamic PivotTables, allowing you to monitor stock effectively.

1. Open WPS Spreadsheet: Launch WPS Office, select 'Spreadsheet', and open a new blank workbook.
2. Format as Table: Enter your headers (Status, Item, Person), select them, navigate to the 'Insert' tab, and click 'Table' to organize your tracker.
3. Apply the COUNTIFS Formula: Type the exact COUNTIFS formula used in Excel into a new column to calculate the outstanding items. WPS Spreadsheet processes this instantly.
4. Generate a PivotTable: Click 'PivotTable' under the 'Insert' tab to build a customized report that shows exactly who holds unreturned inventory.

100% compatible with Microsoft Excel formulas, tables, and formatting.Advanced built-in PivotTable features for generating outstanding item reports.Completely free and lightweight, ensuring smooth operation even with large datasets.Includes a wide range of free customizable templates for inventory management.

microsoft office alternative - wps office

Frequently Asked Questions

How do I update the PivotTable after adding new returned items?

To update your PivotTable report, right-click anywhere inside the PivotTable and select 'Refresh' from the context menu. Alternatively, you can navigate to the PivotTable Tools tab on the ribbon and click the 'Refresh' button.

Can I add electronic signatures when items are issued?

Yes. Instead of relying solely on a typed name in the 'Person' column, you can insert a controlled signature line or integrate a form control checkbox to enforce stronger accountability when an item changes hands.

Why is my COUNTIFS formula returning an error or zero?

Ensure that the column names in your formula exactly match the headers in your table (e.g., [Status], [Item]). Also, check for trailing spaces in your 'in' and 'out' text entries, as COUNTIFS requires an exact text match to calculate properly.

Excel & Data Problems