Inventory Management

How Do You Keep Track of Inventory in Excel?

November 2, 2023 • 5 min read

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

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.

Experience the simplest inventory management software.

Are you ready to transform how your business does inventory?

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:

Free Download: Inventory Template

Download our free inventory template today! The template is pre-populated with common inventory examples and categories but is totally customizable–feel free to add your items along with custom details, rows, and columns specific to your business's inventory. 

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

best inventory excel sheet

Sortly inventory management software simplifies the inventory management process. Sortly helps you track, manage, and organize your inventory—from any device, in any location. You can easily track inventory, supplies, parts, tools, assets like equipment and machinery, and anything else that matters to your business. Sortly 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. Can a spreadsheet do that?

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 10,000 businesses globally trust us as their inventory management solution.

Before you begin typing data in Excel, why not try an inventory management app? Get started with a free trial of Sortly.

 

Download Sortly free on iOS and Android:

  app store icon1google play icon 1