Track Inventory with Excel

February 01, 2023 - by Bill Jelen

During my 15 years as an Excel consultant, there was one common request that I dreaded more than any other: “Could you create an Excel application to track inventory?”

Why did I hate this request? Tracking inventory in Excel can become difficult for several reasons:

• Limited real-time updates: In Excel, inventory information must be manually entered and updated, making it difficult to track changes in real-time.
• The formulas to remove items from inventory are easy when you have access to sales data. But if you have returns, now you need a way to put items back into inventory.
• Once you get two storage locations, you need to be able to transfer from one inventory location to another.
• Complexity: As inventory information grows, it can become challenging to manage and track data in an organized and efficient manner. Excel sheets can become cluttered and difficult to navigate, especially if multiple sheets are used to track different aspects of inventory.
• Accuracy: Manually entering and updating inventory information in Excel increases the risk of errors and inaccuracies. These errors can be difficult to identify and correct, especially in complex spreadsheets.
• Limited reporting and analysis capabilities: While Excel provides some basic reporting and analysis tools, they may not be sufficient for complex inventory tracking needs.
• Scalability: As the number of products, suppliers, and customers increases, tracking inventory in Excel can become increasingly time-consuming and difficult to manage.

Given these challenges, many businesses choose to use specialized inventory management software that provides real-time updates, improved accuracy, and more advanced reporting and analysis capabilities.

And, there was the cost factor. Anyone could easily track inventory with Quickbooks. Back in the day, you would go to the Office Supply store and you could buy Quickbooks for $229. This tells me that you are not willing to spend $229 for your solution. If I was consulting at $100 per hour, it would give me two hours to try to develop something that would be as robust as Quickbooks. It simply was not feasible.

The only hope would have been to develop a generic inventory tracker and hope you could sell it to dozens of people at a lower price.

Enter Bob Schuster. He has developed an Excel spreadsheet to help you manage and control inventory. You can buy the complete system for $99 or subscribe for $10 a month if you want to try it out. If you write to me to build an inventory system, I will either recommend Quickbooks at US $549 or Inventory Tracker from RWS for $99.