How to Make an Inventory Spreadsheet
By Grant D. McKenzie
Updated September 28, 2017
An inventory is more than just a list of items: It also includes accompanying information. For instance, an inventory might include an item's name, model number, description and serial number. The data labels included in the inventory will change depending on its purpose, but the concept is still the same. In addition, by using Excel 2010, you can also link your spreadsheet to a Sharepoint list to receive automatic updates of your inventory for analysis using Excel's functions.
Open your software. If you need automatic updates with SharePoint, use Excel 2010. If you are creating a simple list for insurance purposes, use a word processing program with tables or a spreadsheet program.
Determine the data labels to include in your inventory spreadsheet. If you are doing a home inventory, for example, include "Value" as a column heading, in addition to labels such as "Item" and "Serial Number." For a retail inventory, create headings such as "Wholesale Cost" and "Retail Price."
Enter your column headings across the first row of your table. Set the first column as the index -- or unique identifier -- of the information for that row. For example, a SKU for business inventory, or an item's name for home use.
Enter the data in the rows and columns. Every item in a row provides further information related to its index. In a retail inventory, for example, enter the retail price, wholesale cost and item description for the same SKU. For a home inventory, enter information that further defines the item, such as size, value and cost.
Maintain your inventory manually by adding items as they are bought or otherwise acquired, and deleting items as they sold or otherwise removed.
Automatically update your inventory using SharePoint. SharePoint lists automatically update Excel spreadsheets provided you have read permission for the SharePoint list. In SharePoint, select the "Export to Spreadsheet" option from the List tab, in the Connect & Export group. Select "File Download," "Open," and then "Enable" to synchronize the data with your computer. In Excel, select "Refresh" in the External Table Data group on the Design tab to see the updated information.
SharePoint updates are sent only to the spreadsheet; updates will not reflect changes made in the spreadsheet itself.