How to Track Inventory in Excel Spreadsheets

February 14, 2022

Imagine you just got a loan to start the business of your dreams. You need to make the best use of your time and money, which includes choosing how to track inventory. Before you set up an expensive program, consider if a free Excel spreadsheet can work for you. Then, you'll be able to start your business on a lean budget and expand as you get more sales.

Read on to learn how to use Excel as an inventory tracker.

List Out Inventory Categories

The first step in how to track inventory with a spreadsheet is to list out the categories you want to track. You can track a lot of the same categories in a spreadsheet as in inventory management software.

Be sure to track data like SKUs, descriptions, inventory locations, and QR codes or barcodes. It also helps to keep track of bin numbers, units, reorder quantities, and reorder flags.

You can put all of the SKUs on the left side and add the other details along the top to track different items. Then, you'll be able to update information for specific items that you sell.

Determine What You Want to Calculate

Next, you have to think about what you want to calculate using your inventory spreadsheet. Some good things to track include the quantity of inventory in stock, inventory value, and purchase costs.

You might also want to track the number of items that you want to reorder. Then, you can keep from having popular items go out of stock, which can cost you sales.

Excel lets you set up formulas to automatically calculate values like the number of items or the value of your inventory. Once you set up those formulas, you can change out the inventory values and watch the other numbers change.

Find a Template

One of the easiest options for how to create an inventory spreadsheet in Excel is to use an existing template. You may be able to find a template online that you can download and use on your computer.

Knowing what categories and calculations you want can help you search for the perfect template. Then, you won't spend time or money on a spreadsheet that won't meet your needs.

There are tons of free templates out there, but you may find premium templates. Consider your needs to make sure you get a spreadsheet that you'll be able to use.

Design Your Own Template

If you can't find a template that lets you track all of your inventory data, you can create an Excel inventory tracking spreadsheet yourself. You can design the spreadsheet and add any columns or rows you want.

Then, you'll be able to set up auto-calculations so that you don't have to do the math every time you sell or purchase inventory. The software will update the numbers for you, so you don't need to track as much yourself.

If you don't know how to design a spreadsheet, have someone on your team do it. They can make the document look good and set up all of the categories for you.

Input Your Inventory and Data

After you find a template you like and want to use you will need to input your inventory and other data. Depending on how many SKUs you have and the data you want to track, this may take a while.

However, once you set up the spreadsheet, you'll only have to worry about adding new products. You won't need to take as much time to add and track your inventory every time.

If you want to make this step easier, consider writing the numbers down on paper as you go through your inventory. Then, you won't have to go back and forth from your warehouse to a computer.

Update the Spreadsheet Regularly

As you make sales or bring in new inventory, you will need to update your spreadsheet. You can update the spreadsheet any time you make a sale or receive new inventory.

However, that will eventually become too time-consuming, especially if you make multiple sales per day. At that point, you may start to update your spreadsheet once or twice per day.

If that's not possible, you can update the spreadsheet every week. Try not to go longer than a week because you might run out of stock without knowing about the problem.

Why Use an Inventory Spreadsheet

Knowing how to keep track of inventory in Excel is great, but it's not your only option. If you're trying to decide how to track all of your items, consider some of the benefits of a spreadsheet.

That way, you can make sure it's the right strategy for you.

Shareable

If you use an inventory tracker in Excel, you can easily share it with people on your team. For example, you might share it with managers to place purchase orders.

When you're looking to get rid of stock, you may share it with the marketing team so that they know what items to promote. If you use something like Microsoft OneDrive, you don't even need to email the file.

You can get a link that you can share with others to view or edit the inventory tracker. Plus, you'll be able to share it with as many people as you want.

Customizable

Whether you design your own spreadsheet or find a template, you get to customize it as much as you want. You don't have to worry about limiting your tracking to existing categories.

And if your strategy changes later on, you can add or delete categories as necessary. That way, you get to view the most important details regarding your inventory.

Affordable

Of course, you also have to consider your business budget. If you don't have a ton of money to spend, you might want to at least start with a free inventory tracking spreadsheet.

The internet has plenty of options you can consider when looking for a free template. As your business grows, you can always upgrade to proper inventory management software for more features.

No Downloads Necessary

Another advantage of learning how to track inventory in Excel is that you don't need to download anything. Most likely, you already use Excel for some things in your business.

If you don't use it, you may still have it on your computer. Downloading a bunch of software can slow your computer down, especially if you aren't going to use the program, so starting with what you have is useful.

Why Not Use an Inventory Spreadsheet

While it can be great to create an Excel inventory tracker, you may not want to rely on a spreadsheet. They can be useful in the beginning when you don't have a ton of inventory.

If your business is a bit bigger, consider if you should use a spreadsheet. The following drawbacks may outweigh the benefits.

Time-Consuming

Setting up and maintaining an Excel spreadsheet for your inventory can take a lot of time. You have to sit down for quite a while when you first add everything to a new spreadsheet.

Then, you'll need to take time each week or day to update the data. As you get more sales, this will take even more time because you will have to update different SKUs and other fields.

Of course, you can have someone else update the spreadsheet. But that will keep them from doing other things that can help grow the business, such as sourcing products or marketing the business.

Limitations

When you rely on a spreadsheet, you may face a lot of limitations that inventory management software doesn't have. For example, a good program to manage inventory may have a barcode scanner.

That makes it quick and easy to update the stock number for each SKU that you sell. You can only do so much with a spreadsheet, even if you put a lot of time into creating it.

Accidental Deletions

If you aren't careful, you may accidentally delete a row or column of data in your spreadsheet. Depending on how it saves or if you walk away from the spreadsheet before realizing your mistake, you might not get that data back.

You also have to worry about other people deleting data on accident or even on purpose. Think about that before you share edit access with others or before you spend a lot of time on a spreadsheet.

Hard to Scale

As your business grows, you may find your spreadsheet system isn't as efficient as it used to be. You'll have more products to track, and you'll have to update the document more often.

Plus, spreadsheets can't show you real-time data on sales or other details. You could easily get behind and accidentally sell inventory you don't have, causing shipping delays or unhappy customers.

Do You Know How to Track Inventory?

Deciding how to track inventory for your business isn't easy. If your business is new and you have a tight budget, you may want to use Excel with a free spreadsheet template.

However, once your business starts to grow, you might want to invest in inventory management software. That way, you won't have to miss tracking vital details.

Are you ready to move from a spreadsheet to inventory tracking software? View our pricing to decide which plan is best for you.