How to Create a Sample Inventory Excel Form

By Ken Burnside

Inventory control is a basic business function, and Excel is one of the most commonly used tools to perform it. Excel's ability to handle the basic sums allows an inventory tracking sheet to be as basic or as elaborate as desired. The exact requirements of your inventory control form will depend on your business needs and may have additional restrictions put on you by regulations and local inventory taxes.

Things You'll Need

  • Excel 2000 or later

Tracking Stock Purchased And Sold

Step 1

Open Excel.

Step 2

Select row 1. Right-click and select "Format" from the list. In the Format menu, select "Alignment" and set the text at a 45-degree angle.

Step 3

Select cell B2 and click on the "View" tab. Click on the icon that says "Freeze Panes." This will ensure that your first column and the header row remain visible.

Step 4

Enter the following labels in row 1: Item ID #, Item Name, Quantity Purchased, Quantity Sold, Quantity Remaining and Reorder Threshold

Step 5

Enter the data for the products you're tracking inventory on starting in row 2. Enter the Item ID numbers, the Item Name, the quantity purchased and the quantity sold. Tab past column F and enter the quantity of each item that triggers a reorder threshold.

Step 6

Enter the following formula in F2: "=D2-E2". This formula subtracts the quantity sold of an item from the original quantity purchased.

Step 7

Copy cell F2 through as many rows as you have items. This will generate the inventory remaining for each item.

Triggering Reorders

Step 1

Select cell F2. Click on the "Home" tab in Excel 2007, and select Conditional Formatting. In earlier versions of Excel, click on "Format" and go to "Conditional Formatting." A dialog will pop up; in Excel 2007, select "Use a Formula"; earlier versions will take you to the same place by default.

Step 2

Enter the following formula in the dialog box: "=F2

Step 3

Click cell F2 and hit "Ctrl"-"C." Select the other cells in column F and right-click. Select "Paste Special" from the menu that comes up. Choose "Formats" from the dialog box that appears.

Tips & Warnings

  • This is a very basic inventory control form; more-elaborate forms can also be built. Consult with your accounting department or business manager about other things that would be useful to add to this form for your specific business needs.