How to Create an Inventory Sheet for Used and New Items in Excel
Question details
The user needs to build an Excel inventory tracking sheet that calculates current stock by subtracting used items and adding newly received stock.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a worksheet to manage and track physical inventory levels, accommodating both incoming and outgoing items based on a specific workflow.
- Observed behavior
- Requires proper data layout and basic mathematical formulas (addition and subtraction) to ensure accurate stock balances based on daily usage and receipts.
Before creating your spreadsheet, outline your current inventory workflow on paper to identify necessary fields like product names, tracking locations, and starting balances.
Create a Basic Inventory Tracking Sheet
Set up a structured layout and use basic arithmetic formulas to calculate current stock dynamically.
A simple inventory sheet requires designated columns for your opening stock, incoming items (received), outgoing items (used), and the final balance. The core formula logic is: Opening Stock + Received Items - Used Items.
Open a blank worksheet and create column headers in row 1, such as 'Item ID' in A1, 'Item Name' in B1, 'Opening Stock' in C1, 'Received (New)' in D1, 'Used (Outgoing)' in E1, and 'Current Balance' in F1.
Input your product names and their initial quantities into the 'Item Name' and 'Opening Stock' columns.
Click the first empty cell in your 'Current Balance' column (e.g., F2). Type the formula =C2+D2-E2 and press Enter to calculate the current stock level.
Select cell F2, click and hold the small square (Fill Handle) at the bottom-right corner of the cell, and drag it down to apply the formula to all your inventory items.
Create and Manage Inventory Sheets with WPS Spreadsheet
WPS Spreadsheet provides powerful formula calculation, built-in templates, and a familiar interface to help you build and maintain accurate inventory trackers efficiently.
- 1. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to start a new blank workbook, or search 'Inventory' in the template library.
- 2. Set Up Your Columns: Input headings for Item Name, Starting Stock, New Items, Used Items, and Balance.
- 3. Insert Calculations: Use simple addition and subtraction formulas in the Balance column, then save your file seamlessly as an Excel-compatible document.
Frequently Asked Questions
How do I handle inventory across multiple locations?
You can add a 'Location' column to your spreadsheet and use the SUMIFS function to calculate total stock based on both the item name and its specific location.
Can I get an alert when an item's stock is running low?
Yes, you can use Conditional Formatting. Select your 'Current Balance' column, go to Home > Conditional Formatting > Highlight Cells Rules > Less Than, and set a threshold value (e.g., 10) to highlight low stock automatically in red.
Is there a way to track daily received and used items without cluttering the main sheet?
It is highly recommended to use separate 'Transactions' sheets to record daily incoming and outgoing items. You can then use the SUMIF function on your main 'Dashboard' sheet to aggregate these totals automatically without manually updating the main balance.
