Excel is an inexpensive way to keep track of inventory, although it does have limitations (and room for error) that inventory management software does not. A spreadsheet offers virtually endless columns for categorizing and sorting the data you need. You can create a spreadsheet or download our pre-filled Excel inventory template below to help you manage your inventory.
How to Keep Track of Inventory in Excel
Use a spreadsheet to track important inventory information like each product’s SKU, barcode, description, location, quantity in stock, reorder point, value, and more. You can also include expiration dates, customized notes, and pictures.
If you wish, you can include formulas and calculations on your spreadsheet. And you can create a workbook with interdependent spreadsheets, or tabs, each displaying data differently. But before you begin tracking data, ensure you’re capturing the right details.
Experience the simplest inventory management software.
Make a List of Categories and Calculations Needed
Before you begin entering data in Excel, make a list of categories and calculations that you’ll need for inventory tracking.
Inventory Categories
Below are categories—some commonly used in inventory management software—that you might want to include in the columns of your Excel spreadsheet:
- SKU
- Barcode or QR code numbers
- Description
- Location
- Bin number
- Units
- Quantity
- Reorder quantity
- Cost
- Inventory value
- Reorder flag
Inventory Calculations
You can add formulas to your inventory spreadsheet. And you can use the Help feature in Excel or search online for tutorials on how to create formulas. Think about what calculations you’ll need. Some examples include:
- Quantity in stock
- Purchase costs
- Inventory value
- Quantity in reorder
You’ll need additional spreadsheets, categories, and calculations to track sales, business performance, and other data. Learn more about inventory formulas commonly used in inventory tracking.
Download a Free Inventory Template
The team at Sortly has put together an easy, totally customizable inventory template for small businesses. Here are two different formats for our inventory spreadsheet templates — just click to download!
This free, easy-to-use template is the best inventory excel sheet for performing basic inventory tracking. This template is a good fit for those just starting out with inventory tracking for their business. Feel free to make edits to the template so it works for your specific inventory.
Another free option for tracking your inventory is Sortly inventory management software, which is far easier than tracking manually in Excel. You can also upload the above inventory template directly into Sortly and populate your inventory in the app instantly. You also get a 14-day free trial of our Advanced or Ultra plans, which enable you to track up to 10,000 items. Read on to learn more about the pros and cons of tracking in Excel vs. inventory management software.
Free Ebook: Supplies & Materials Tracking
- Discover the three methods to track and manage inventory
- Get actionable tips and best practices for inventory tracking
- Learn how to track and maintain an inventory list
Create your own inventory spreadsheet
You can also create your own template by opening a blank spreadsheet and entering the categories and formulas of your choice. To make an inventory spreadsheet in Excel, open a new spreadsheet and write every little thing you want to track in a different column of the top row. Most inventory managers use the first column to track item name, then add columns for information like UPC/serial number, location, description, quantity, par, vendor, item value, and more.
Next, if you’d like, you can rename this tab (likely named Sheet1 by default) something like “Inventory Master List.” You can then make copies of the tabs in the same spreadsheet. You can use these copies to record inventory data each time you take inventory. Remember to rename the tab to the date you counted inventory. Over time, you’ll gather meaningful information about how your business uses inventory.
What Are the Pros and Cons of an Excel Inventory Spreadsheet?
Pros
- Inexpensive – Download free or low-cost spreadsheets.
- Customizable – Add or remove columns and use formulas.
- Shareable – Upload the document to cloud storage like Dropbox or Google Docs and share it with your team.
Cons
- Complications – As your business grows, so do the columns and rows on your spreadsheet. “At-a-glance” is not a feature on a lengthy spreadsheet.
- Time investment – It takes time to create or adapt formulas and to increase spreadsheet function to match your business growth.
- Data protection – If you mistakenly delete or alter information on a spreadsheet, it may not be easy to restore it.
- Limitations – Unlike inventory management software that scans QR codes and barcodes and captures the data they provide, an Excel inventory spreadsheet requires manual entries. And you won’t have real-time data.
A Time-Saving Alternative to Spreadsheets
Inventory management software simplifies the process. Some features include:
- Scans and uploads QR codes and barcodes and their associated product details
- Customizable, sortable views
- Built-in formulas and calculations
- Real-time data that is safely stored and easy to sync, retrieve, and share
Before you begin typing data in Excel, why not check out an inventory management app? Get started with a free trial of Sortly.
Download Sortly free on iOS and Android:
