How to Create Inventory Spreadsheets

By Shawn McClain

A spreadsheet can help track changing inventory levels.
i Images

As a business owner, tracking constantly changing inventory levels can be one of the most time-consuming and tedious tasks, but it's a requirement if you need to know exactly how much of a given product you have on hand. While a spreadsheet program like Microsoft Excel 2010 can't automatically track your inventory, it does provide a document structure, namely cells broken down into rows and columns, that is ideal for recording inventory levels. With Excel, you can create your own inventory spreadsheet tailored to your exact needs or you can save time by using an inventory template.

Create Your Own Spreadsheet

Launch Microsoft Excel 2010, bringing up a blank spreadsheet.

Click cell "A1" and type in "Item Name." This column will hold the names of each item in the inventory.

Click cell "B1" and type in "Item SKU." Use this column for numeric or alphanumeric tracking numbers for your products.

Click cell "C1" and type in "QTY." Use this column to track the current inventory levels for your items.

Continue across the top row, adding column headers as necessary for your business. These column headers could include cost and retail prices, item age, item vendor, item location or any other important factors.

Click the "View" tab, then click "Freeze Panes." Select "Freeze Top Row" from the menu that appears. This will allow you to scroll down without losing sight of the column headers at the top of your inventory spreadsheet.

Save your spreadsheet with the filename "Inventory" or some other relevant name.

Use a Template

Open Excel 2010 and click "File," then click "New."

Select "Inventories" from the list of template types that appear. A list of inventory template options will display.

Scroll down through the list of inventory templates until you find one that will work for your business. If you click on a template, you will see a larger preview of it on the right side of the screen.

Click "Download" when you have found the template that is right for you. The inventory spreadsheet will open in a new Excel window.

Save the spreadsheet with an appropriate filename.