Inventory management in Excel: Techniques & tips

Inventory management is the operational backbone of any business with a product to sell. If you are just starting out and have a small number of products without many variants, Microsoft Excel is a useful tool that allows beginners to create an inventory list template.

However, inventory management with excel requires time, and setting up your initial spreadsheet template accurately is crucial. In this post, we’ll provide tips on building and using an Excel spreadsheet for basic inventory management and what to do when you outgrow this solution.

• Understanding inventory management with Excel
• Setting up your Excel inventory list
• Formulas you should know for inventory management in Excel
• 7 Tips on how to manage your Excel inventory spreadsheet 
• Advanced inventory management with QuickBooks Commerce

Understanding inventory management with Excel

If you’re looking for a low-cost way to manage and track inventory, Excel could be an effective solution. Many businesses already use  Excel for accounting or spreadsheet forms, and it is an alternative option for applications like inventory management. With integrated tools, features, and formulas to make spreadsheets more dynamic and interactive, Excel is also capable of handling basic inventory management for small businesses. While not ideal for a medium- or large-sized inventory, Excel is cost-effective or, if you use it in OneDrive, even a free inventory management tool.

Like anything that’s free or cheap, Excel inventory management isn’t perfect, but it has plenty of valuable features. While it is easy to make mistakes in Excel’s inventory management templates—and avoiding them will require time-consuming auditing, proper organization, and strict management—Excel is capable of handling a startup business’s inventory, especially in the early stages.

Setting up your Excel inventory list

There are a lot of ways to make an inventory sheet in Excel, however, a basic template for your Excel inventory tracker should include:

• Product number
• Product name
• Item description
• Item price
• Item cost or value
• Item stock
• Reorder quantity

If you want, you can also include sales quantity or value, reorder point, whether or not the product has been discontinued, and the total inventory value. However, keep in mind that the more columns you have, the more difficult it is to keep up with all the data and manage inventory control.

If you would like to gain a deeper understanding of crucial inventory management techniques and how you can apply them to your business, check-out our blog on inventory management tips.

You can then use Excel formulas to automatically calculate information for total sales and total profit, and organize your inventory based on products that sell the most using a few simple formulas. You can also modify Microsoft’s basic free template (available when you open Excel) to your needs, purchase a template, or create your own.

Formulas you should know for inventory management in Excel

Formulas can increase the value of your Excel spreadsheet while reducing the headache and stress associated with manual calculations. While they may seem a little complex at first, don’t worry, most of them are easy to get the hang of.

SUM

The SUM function can be used to add any figures that you put into two or more cells. For example, you can create a running sales quantity using the SUM function to automatically update how much revenue you’ve earned by doing math based on calculations you put in per line. Or, you can use it to total your stock levels for all products. This alleviates the need to go over each line with a calculator.

A SUM formula is written as: =Cell*Cell. This can also work out to =[@[Name of Column]]*[@[Name of Column]] to simply multiply any two cells in a row.

This is very useful for creating automatic value totals for the products in your inventory. Keep in mind, though, that if you have 300 lines, you will have to add it to each one. For example, if your products are worth $27 each and your in-stock number is 65, the SUM function would automatically update the “inventory value” section to $1,755 (65 x $27).

SORT

Sort allows you to order your inventory by number size or by color, so you can quickly and easily see and go through products based on sales, profitability, or remaining stock. Note that if data entry mistakes were made, the sort function will yield your desired results.

If you use a subtraction formula (=B1-B2) when you input how many items you sold, it will automatically deduct that number from your current stock number. You can then use this updated count to sort based on stock so that you can see which items you have the least of. Conveniently, this also helps you understand which products you need to order.

RANK

As the name implies, this function allows you to rank items based on sales quantity, inventory quantity, or the number of products you have received. Because it makes the most sense to rank high-sale items that you have to restock on the top, you can use it to organize your inventory based on either specification.

Rank differs from Sort in that Sort organizes your data in order when you tell Excel to do so by clicking “Sort,” while rank orders data automatically. Interestingly, this function doesn’t automatically adjust the order, so your #1 could still be in the middle of the page.

Your rank formula should be written as: =Rank (Cell, Cell:Cell), where the cells mentioned include all of the cells you want to rank.

7 Tips on how to manage your Excel inventory spreadsheet

Need help figuring out how to keep track of inventory in Excel? Use the tips below to manage your inventory spreadsheet for the most efficient and accurate inventory planning in Excel.

1. Avoid pitfalls and mistakes with inventory management in Excel

Many professionals avoid using Excel for inventory management for the simple reason that humans are error prone. Errors are a major issue when you’re dealing with things like reorder levels, pricing, and other key numbers that you use to complete stock inventory and complete purchase orders. Finding errors on sheets of numbers and hard data is also difficult unless you know exactly what you’re looking for. The only way to minimize errors is to create best practices for self-auditing and daily review.

2. Update immediately

Make sure that anyone handling orders updates the spreadsheet immediately to prevent issues. Excel is not a real-time inventory management solution, so you won’t be able to see the real inventory value at that moment. As such, it’s important to make sure that it is updated as often as possible to circumvent issues like overselling a product that you cannot backorder.

3. Use the cloud

Traditional offline Excel documents are severely limited because only one person can use the sheet at once. Using Excel in the cloud (OneDrive) or Google Sheets ensures that you can invite multiple people to the sheet and edit it in real time to improve self-auditing, input data from multiple sources, and speed up processes. It also provides the added benefits of automatic backups and syncing across all of your devices, so you can edit from anywhere.

4. Take the time to consolidate data

Taking the time to consolidate your data on a monthly basis allows you to create charts that outline how much you sold in each period. You should do this monthly and quarterly, paying special attention to holidays and special occasions.

One of the biggest issues many people have with Excel is not being able to easily analyze historical data. Taking the time to add your entire month’s data into an easy-to-read pivot table will fix this. It is time consuming, but it allows you to track your sales based on period and during different seasons, for better inventory optimization in the future.

Here’s how to create a pivot table in Excel:

1. Click “Insert,” Choose ‘“Tables,” select “PivotTable.” (Click the arrow below PivotTable to create a chart.)
2. Select the table range (for example, 1A-21G) in the “Create Pivot Table” dialog box that pops up.
3. Choose the location of the new pivot table (new worksheet or existing one).
4. Click “OK.”
5. Customize your Pivot Table as needed.

5. Review your data

Excel can be a valuable tool for inventory tracking, but it is up to you to make sure that you’re tracking and calculating everything necessary. For example:

• If you’re tracking time to reorder, have you included lead times (time from manufacturer to warehouse or 3PL) in your calculations?
• Are you calculating profits based on sales or based on your actual per-item profit?
• Are you using your inventory to calculate sales tax immediately?
• Is your total inventory value based on resale or purchase value?

Going over the figures you use in your calculations to ensure that they are accurate can save you expensive and time-consuming mistakes in the future.

6. Audit and review

Excel relies on people to input data unless you have a barcode scanner connected to your system, and even then, not everything is automated. This will inevitably lead to human error, which can throw off supply and demand,  when you reorder too much or not enough, or cause you to under- or overestimate profits.

A policy of keeping track of everything on paper as well as in your Excel document allows you to go over your inventory at the end of each day to correct any mistakes.

7. Know when to upgrade

Excel is not the right solution for every business. While it can work for some small businesses, it typically doesn’t work well for those with medium to large inventory levels because it becomes too complex, time consuming, and difficult to upkeep. At some point, you will have to upgrade to a dedicated inventory management system, and you should know when to do so.

The more sales you are making, the more your need for effective inventory management increases. Using Excel will allow you to cut costs at first, but eventually, a dedicated inventory management system will be necessary to save time and money in the long run.

As your business grows, the need to find a robust solution that streamlines inventory management will become increasingly important. The greater sales volume you have, the more data you’ll need to keep track of, and there’s only so much you can do on your own.

Inventory management software like  QuickBooks Commerce is well worth the investment if you:

• Have a growing inventory of products
• Frequently move products from location to location (such as from a supplier to 3PL warehouse, to your physical store, or to customers)
• Want to handle large volumes or multiple location tracking in real time

Upgrading your inventory management from Excel to a more powerful platform will help you work smarter so you can take your business to the next level.

Advanced inventory management with QuickBooks Commerce

QuickBooks Commerce allows you to manage your sales, inventory management, accounting, and operational processes, all from one dashboard. Unlike Excel and other basic inventory tools, QuickBooks Commerce also takes raw data and automatically generates powerful reports that give you easy-to-understand insights. These insights allow you to determine what aspects of your business are performing well and where improvements can be made.

This inventory management system helps you with everything from optimizing your inventory for better sales and return on investment, to growing your business and expanding to additional sales outlets as your consumer base grows. With QuickBooks Commerce, you’ll have an inventory management solution that can evolve with you as your business scales.

Improve your inventory management and sales performance

Don’t hold your business back while trying to work through the growing pains of a limited solution. If you’ve outgrown the basic Excel inventory templates, it may be the right time to upgrade to  QuickBooks Commerce for your inventory management needs. Unlike Excel, QuickBooks Commerce is built with businesses like yours in mind and has the advanced functionality you need to work more efficiently. Additionally, it integrates with  QuickBooks Online, so your accounting data merges into the same platform.

Once you have inventory management automated within this system, you can take advantage of the opportunity to reach more customers, expand across marketplaces, and drive more sales. With QuickBooks Commerce, you can take back control of your small business with everything you need to oversee multi-channel sales in one platform.