How Do You Keep Track of Inventory in Excel?

In today’s fast-paced business environment, having a handle on your inventory is crucial for success. Manual methods of inventory tracking are error-prone and time-consuming, leaving you vulnerable to stockouts, overstocking, and lost profits.

This article explores the power of Excel spreadsheets for streamlined inventory tracking, guiding you through the creation of a user-friendly system to categorize, monitor, and manage your stock with ease. We’ll also touch on why inventory management software is an important evolution for your inventory management—even if you’re already tracking inventory on a spreadsheet. Plus, you can download our pre-filled Excel inventory template below to help you manage your inventory. Let’s get started!

How to Keep Track of Inventory in Excel

You can use an inventory tracking spreadsheet to track important information like each item in your inventory, such as 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.

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:

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. Read on to learn more about the pros and cons of tracking in Excel vs. inventory management software.

Create your own inventory tracking 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 Tracking 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.

Why Sortly is Better Than Spreadsheets

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 businesses. Sortly builds inventory tracking seamlessly into your workday so you can save time and money, satisfy your customers, and help your business succeed.

With Sortly, you can track inventory, supplies, parts, tools, assets like equipment and machinery, 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 building 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.