DISCOVER

How to Use Excel to Calculate Inventory Levels

Updated March 23, 2017

Microsoft Excel, a part of the Microsoft Office family of products, was designed to help individuals and businesses perform simple and more complex data calculations and analyses. If you have to maintain an inventory, you can use Excel to create a running list of the products you add and subtract from your stock. You can then calculate the remaining inventory automatically using formulas. This strategy requires a commitment to regular data entry, as your inventory levels change.

Create four columns in your Excel worksheet across the first row. Name each of the columns "Date," "Transaction," "Amount" and "Inventory Remaining" (feel free to modify the column names as you wish).

Double-click the tab at the bottom of the worksheet to select the name and enter the name and ID number, if applicable, of the product (for example, "White T-shirts—Item 1001).

Enter information about the current inventory level on the second row of the worksheet. That includes the date, a description (such as "Current Inventory"), and the amount of inventory available in both of the last two columns.

Enter a simple formula into the fourth column ("Inventory Remaining"), third row of the worksheet (cell D3) that will automatically adjust your inventory level. That formula should read "=D2+C3" where D2 represents the amount of "Inventory Remaining" in the previous row and C3 represents the amount you'll adjust the inventory in this row (remember that each row represents a new transaction).

Finish completing your transaction in the third row. For instance, if you've just sold 10 T-shirts to a customer, enter the date, "10 T-shirts Sold to Customer A" under the "Transaction" column, and then the number -10 (negative 10) under the "Amount" column. You'll see the "Inventory Remaining" column make the new calculation automatically. Enter a positive number for an addition to inventory and a negative number for a subtraction whenever you enter a new transaction into the inventory worksheet.

Click cell D3 again, hold, and drag your mouse down the column as far down the worksheet as you'd like. This will copy the formula for each new transaction you will enter and automatically calculate the new inventory level.

Right-click the tab at the bottom of the worksheet and click "Move or Copy…" to create a copy of that worksheet for each type of product in your inventory.