How to Track Lab Inventory and Expiration Dates Using Excel
Managing lab reagents and consumables can quickly become overwhelming without a good system in place. While dedicated inventory management software like Quartzy exists (allowing you to track stock levels and requests across a lab team), many laboratories still rely on Excel spreadsheets for day-to-day inventory tracking.
In this tutorial, I’ll show you a simple way to set up Excel to:
Track reagent stock levels
Flag items that are about to expire
Highlight items that are already expired
This setup works with just a few formulas and conditional formatting rules.
Step 1: Set up your inventory sheet
Example: create a new Excel spreadsheet with three columns:
Column A → Item Name
Column B → Stock Quantity
Column C → Expiration Date
That’s all you need to start tracking your reagents.
Step 2: Highlight reagents close to expiring
We want Excel to automatically warn us when an item’s expiration date is approaching.
Select all the expiration dates in Column C.
Go to: Home → Conditional Formatting → New Rule → Use a Formula to Determine Which Cells to Format.
Enter this formula (assuming your dates start in cell C2):
=AND(C2-TODAY()<=30, C2>=TODAY())
Here’s what the formula does:
C2-TODAY()<=30 → Flags items expiring in the next 30 days.
C2>=TODAY() → Ensures that only items with a valid future date are highlighted (ignores blanks or dates far in the future).
Choose a formatting color (yellow or orange works well for “about to expire”).
Step 3: Highlight reagents that are already expired
To flag expired reagents, repeat the steps above with this formula:
=C2<TODAY()
Now, any reagent past its expiration date will be highlighted in red.
Step 4: Optional — Flag low stock items
You can add another rule in Column B to flag items with critically low stock. For example:
=B2<=2
This will highlight items with a stock quantity of 2 or less. Adjust the threshold depending on your lab’s needs.
Why this works
Using conditional formatting gives you a visual, automated system to track expiration dates and stock levels without having to manually check each entry. While Excel may not be as sophisticated as dedicated inventory software, it remains a practical, free, and widely accessible tool for many labs.
Pro tip: If your lab requires multi-user access or request management, tools like Quartzy may be a better fit. But if you just need a quick and reliable way to monitor reagents, Excel is a great option.
For the visual learners
Watch our video on how to track lab inventory with Excel.
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments