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.
- Skill level:
Things you need
- Excel 2000 or later
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.
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.
Enter the following labels in row 1:
Item ID #, Item Name, Quantity Purchased, Quantity Sold, Quantity Remaining and Reorder Threshold
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.
Enter the following formula in F2: "=D2-E2". This formula subtracts the quantity sold of an item from the original quantity purchased.
Copy cell F2 through as many rows as you have items. This will generate the inventory remaining for each item.
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 dialogue will pop up; in Excel 2007, select "Use a Formula"; earlier versions will take you to the same place by default.
Enter the following formula in the dialogue box: "=F2<G2". This will trigger the conditional formatting if the number in inventory is lower than the number you entered for the reorder threshold. Click the button that says "Apply."
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 dialogue box that appears.
Tips and 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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for