The Problem

An inventory manager recently reached out for help with inventory problems that had become hard to control. On the surface, the issue looked like a spreadsheet problem. In reality, the bigger problem was a messy process.

The company already had an inventory spreadsheet, but the system was breaking down. 

  • Formulas had been overwritten. 
  • Data was inconsistent. 
  • Staff described the same item in different ways.
  • Requests came in through Post-it notes, Teams messages, and verbal requests. 
  • Items were sometimes removed from inventory without anyone recording it.

The result was confusion. People could not always match what was being requested to what was listed in the system. In some cases, the same item appeared more than once under different descriptions. Data in the spreadsheet became unreliable once users unprotected the file and typed numbers directly over formulas.

The first step was rebuilding the system behind the spreadsheet. Here is what changed:

  • The data was cleaned up so item names, descriptions, and IDs were consistent.
  • Duplicate entries were removed.
  • Extra spaces and formatting issues that caused search problems were corrected.
  • Each item was given one unique SKU.
  • Barcodes were rebuilt from those SKUs so they could be scanned reliably.

This made the data easier to trust and easier to use.

Improving the Spreadsheet

The spreadsheet itself was also improved. Instead of one very wide sheet that forced users to scroll left and right, the workbook was divided into simpler tabs. Separate tabs were used for incoming items, outgoing items, and summary information. That made the tool easier to use for different people with different responsibilities.

Creating One Clear Ordering Process

Another major improvement was the ordering process. Before the fix, requests came in through too many channels. Afterward, the goal was to create one clear digital process.

Two options were developed:

  • an order form inside the workbook
  • a Microsoft form that worked better on phones

This mattered because staff wanted something fast and simple. A digital form reduced confusion, reduced rework, and kept requests in one place.

Better Reporting

Reporting was also made easier. The business needed clean output such as packing lists, patient-item records, and item catalogs that could be printed when needed. These were created by using AI prompts to extract data from the spreadsheet.

Key Lesson

One of the biggest lessons from this project is that inventory problems are rarely solved by software alone. A better tool helps, but it only works when the workflow is clear, the data is clean, and the people using the system stay involved.

The most practical lesson is simple: keep the process digital from the start. When businesses depend on paper notes, verbal requests, or disconnected messages, mistakes multiply. But when requests, tracking, and reporting are digital, inventory becomes much easier to manage.

Further Reading

YouTube videos

Fixing Inventory Management – A Real Story (a video of this article):
https://youtu.be/T7WiJS4iirM

How to Fix a Messy Inventory:
https://youtu.be/iQ8OPOmJtXg

Explanation of barcoding: 
https://youtu.be/GxO2SvlUHj8

A spreadsheet you can use
https://www.improvingyoursmallbusiness.com/inventory/

Online class
https://www.improvingyoursmallbusiness.com/how-to-fix-a-messy-inventory/