How to Keep Track of Inventory in Excel

Managing your inventory is crucial for productivity and profitability regardless of your business’s industry, scale, or size. Unfortunately, with the exception of very small businesses that stock only a few dozen items, most manual methods of inventory tracking are error-prone and time-consuming. This leaves companies vulnerable to stockouts, overstocking, and lost profits.

This article focuses on how to use Excel spreadsheets to track inventory. We’ll also touch on why inventory management software might be the next step in your inventory management strategy—even if you choose to begin with a spreadsheet. 

We’ll also include a customizable, free Excel inventory template to help you get started sooner.

Create an inventory spreadsheet from scratch

You can use an inventory tracking spreadsheet to record and organize important information about each item in your inventory. Start by opening a blank spreadsheet and entering the categories of your choice as columns. 

Common categories include:

• Item name
• SKU
• Barcode digits
• Description
• Location
• Bin number
• Units
• Quantity
• Reorder quantity
• Cost
• Inventory value
• Reorder point or inventory minimum
• Expiration or warranty end dates
• Maintenance information
• Custom notes
• Supplier details
• Storage instructions

You can also include pictures of items, but visuals usually do not display well on spreadsheets and can affect usability and readability. 

You can include formulas and calculations on your spreadsheet. Safety stock is a popular choice, as is the inventory turnover rate. You can also create a workbook with additional tabs for separate locations, storage areas, and more. 

Related: What is Visual Inventory Management?

Creating inventory records

While inventory software automates inventory record-keeping, you can also organize such data over time using a spreadsheet. 

To do this, rename the first tab of your workbook (likely named Sheet1 by default) something like “Inventory Master List.” Then, make copies of the tab in the same workbook as needed to track inventory levels and details regularly. Remember to rename each tab to reference the date you counted inventory. 

Over time, you’ll gather meaningful information about how your business uses inventory that can inform demand forecasting, supplier relationships, and more.

How to keep track of inventory using a spreadsheet 

To keep track of inventory using a spreadsheet, you’ll first need to implement an inventory tracking strategy, which is usually codified in a standard operating procedure. In summary, your business will need to articulate exactly when and how inventory is counted and reordered and determine how it should be physically organized and tracked.

Once you have a system in place, you can begin tracking inventory using your spreadsheet. Typically, this is done by printing out the spreadsheet, manually counting inventory, recording data on the printout, and then (if desired) transferring the data back onto the spreadsheet for record-keeping. 

This is chiefly because it is too difficult to accurately record inventory data directly onto a spreadsheet, even using a tablet. There is simply too much room for data entry errors. That said, team members should be careful not to introduce additional errors when transposing data from printouts to spreadsheets as well.

Use a free, customizable inventory tracking spreadsheet

Sortly’s 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. Feel free to make edits to the template so it works for you.

This template is also designed for a frustration-free bulk upload to Sortly inventory management software. That means that if you decide to upgrade to an inventory app, you can instantly sync your inventory spreadsheet data with Sortly. 

What are the pros and cons of an Excel inventory tracking spreadsheet?

Pros

• Inexpensive – Download free spreadsheets to use on Excel or make your own on Google Sheets at no cost.
• Straightforward – If you only want to track a few aspects of your inventory (such as item and location), spreadsheets are a simple, straightforward way to do that.
• Shareable – Upload the file to cloud storage like Dropbox or Google Docs and share it with your team.

Cons

• Complicated – As your business grows, so do the columns and rows on your spreadsheet. The more your company scales, the harder it’ll be to find information or track new inventory. Spreadsheets can get unwieldy fast!
• Time-consuming – It takes time to create and manually update a spreadsheet as your business scales, especially if multiple employees contribute across multiple locations.
• Prone to human error – If you mistakenly delete or alter information on a spreadsheet, it may not be easy to restore it. Plus, it’s easy to jumble numbers and cells, especially when items sound alike, and there’s no barcode or QR code scanning system preventing such mix-ups.
• No automation – Excel inventory spreadsheets are completely manual, which means they are only as accurate and up-to-date as your team keeps them.

These are just a few reasons why you might consider switching to easy-to-use inventory software like Sortly. Learn more about Sortly vs. spreadsheets and how our inventory software can save you time, money, and frustration.

About Sortly

Sortly is an inventory management solution that helps you track, manage, and organize your inventory from any device, in any location. We’re an easy-to-use inventory software that’s perfect for large or small companies. Sortly builds inventory tracking seamlessly into your workday so you can save time and money, satisfy your customers, and help your projects succeed.

With Sortly, you can track inventory, supplies, parts, tools, assets, and anything else that matters to your business. It comes equipped with smart features like barcoding & QR coding, low stock alerts, customizable folders, data-rich reporting, and much more. Best of all, you can update inventory right from your smartphone, whether you’re on the job, in the warehouse, or on the go.

Whether you’re just getting started with inventory management or you’re an expert looking for a more efficient solution, we can transform how your company manages inventory—so you can focus on your business. That’s why over 15,000 businesses globally trust us as their inventory management solution.

Start your two-week free trial of Sortly today.