How to track stockroom inventory

Written by frank luger Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to track stockroom inventory
Keep tabs on stock with a spreadsheet. (Christopher Robbins/Photodisc/Getty Images)

Stockrooms are sometimes run inefficiently, with nobody in an organisation fully aware of items present or in what quantity. A stock check gives you the chance to put that situation right and to introduce a new, more efficient stockroom inventory system. A paper-based system has the drawback that oft-used paper soon gets grubby and tatty so follow the approach of Mike Elbert, president of Elbert Lean Business Systems, LLC, and use a spreadsheet application instead.

Skill level:

Things you need

  • Laptop or other mobile computer
  • Spreadsheet application

Show MoreHide


  1. 1

    Run a spreadsheet application on a laptop or other mobile computer. Click cell A1 and enter Item. Click cell B1 and enter "Description." Click cell C1 and enter "Quantity at stock check." Widen the columns optionally by dragging the column width boundary lines to the right. Save the file as "Stockroom inventory."

  2. 2

    Ask a colleague to help you do a stock check. Make a notice saying “Stock check in progress, please do not enter” or similar and stick it on the door of the stockroom to keep other colleagues from coming in and taking things, which would compromise the integrity of the operation. Go into the stockroom with your colleague.

  3. 3

    Have your colleague work logically from one end of the stockroom to the other, calling out the name of each item, a brief description and its quantity, while you enter the data under the three column headings. Save the file regularly to preserve the work done thus far.

  4. 4

    Return to your office with the laptop. Click the column heading of column A, which is the box marked A at the top of the column, and drag across to the column heading of column C, so columns A, B and C become selected. Click the "Sort Ascending" icon and the item list will rearrange itself alphabetically, making it easier for colleagues to find items listed when you publish the file.

  5. 5

    Click cell D1 and enter "Current quantity." Click cell E1 and enter "Delivered." Note that this column will reflect the quantity of stock items delivered after being purchased. Enter the names of staff members or departments with authority to take items from the stockroom in cells F1 to K1 and beyond, if necessary. Be aware that to make the stockroom inventory spreadsheet work effectively, you need to add a formula.

  6. 6

    Click cell D2, which gives the current quantity of the first item in the stock list. Click the formula bar and enter the formula =C2+E2-F2-G2-H2-I2-J2-K2. Extend the formula to include cells from K2 onwards if necessary, depending on how many staff members or departments you entered in cell F1 onwards. Press Enter. Notice that cell D2 now contains the same number as cell C2, indicating that the current quantity of the first item listed is the same as the quantity at stock check, which is how it should be. Be aware that the number in cell D2 will change when the spreadsheet is in use, as the current quantity of the first item listed changes.

  7. 7

    Be aware that now you have entered the formula to perform quantity calculations, you can replicate this formula easily without having to do any more typing, using a fill handle. Click cell D2. Click and hold the cell’s fill handle, which is at the bottom right corner of the cell. Drag the fill handle down the column as far as the last item listed. This will replicate the formula you entered previously to cells D3, D4 and so on, making it applicable for each row.

  8. 8

    Note that when you click in cell F2 and enter a number, replicating what the first staff member or representative from the department listed should do after removing a quantity of the first item listed from the stock room, the number in cell D2, indicating the current quantity of that item, reduces to reflect the current quantity. Note also that the current quantity is updated when you enter a number in cell E2, as it would when new stock was delivered and recorded in the inventory.

  9. 9

    Inform colleagues that from now on, all stock removed from the stockroom will need to be recorded in the stockroom inventory file, so stock levels can be tracked efficiently. Share the file across the network so it can be written to by anyone with authority to take items from the stockroom.

Tips and warnings

  • When you enter a formula into the formula bar, you can click cells to include them in the formula instead of typing their cell references.
  • Perform a stock check annually to ensure actual stock equates to recorded inventory.
  • Checking the stockroom inventory file regularly will allow you to see when stock is at critical levels and reorder accordingly.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.