sortly logo BUSINESS

by Shaz K

How Do You Keep Track of Inventory in Excel?

Tuesday 21, Apr 2020

share this article

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 a pre-filled template to help you manage your inventory.

How Do You 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.

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.

Where to Find an Inventory Spreadsheet

Create your own

You can complete a blank spreadsheet and enter the categories and formulas of your choice.

Download a pre-filled template

It’s easy to find and download a pre-filled inventory template in Excel or by searching online.

Search in Excel

  • Open Excel
  • Click New
  • In the search for online templates box, type “inventory.”
  • Press the Enter key

You’ll see a display of a variety of inventory spreadsheets and descriptions of their function. When you think you’ve found a match for your needs, click Create. Save and name the original version. And save yourself some time and frustration by using a separate copy as a playground for testing out the formulas and features.

Search online

You can search online for an Excel inventory template. You’ll find free templates for download. And there are templates you can buy—some with advanced features like those offered in inventory management software but not as robust.

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

Sortly inventory management software is shown on multiple devices.

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.

Recommended