Recently, a spreadsheet template for lab inventory management gained widespread attention on Twitter, sparking a renewed discussion – is spreadsheet software, such as Excel or Google Sheets, sufficient for handling the day-to-day lab inventory management requirements of a research laboratory?
In this article, we will discuss the advantages and disadvantages associated with using spreadsheets to manage lab inventory and equipment. We will also explore various options to manage lab inventory, from specialized systems, electronic lab notebooks, and more.
By the end of this, we hope you will come away with a better understanding of these tools, and know which is the best option that aligns with your specific lab inventory management needs.
Table of contents:
- The default for managing lab inventory – spreadsheets
- Heavy reliance on manual inputs
- Lab inventory management systems
- Electronic lab notebooks with built-in lab inventory management
- Are there other options for lab inventory management?
- Moving lab inventories between systems
- Lab inventory management recommendations
The default for managing lab inventory – spreadsheets
Spreadsheets like Excel and Google Sheets are often the initial choice for managing lab inventories, equipment, and samples. It’s not surprising that many people opt for spreadsheets because they have prior experience using them to record data and create basic graphics. Utilizing spreadsheets for organizing lab inventory seems like a natural choice.
One significant advantage of using spreadsheets for lab inventory management is their widespread accessibility and cost-effectiveness. They are readily available and either free or included as part of a software package. This accessibility factor grants spreadsheets a distinct advantage in the realm of inventory management.
However, using spreadsheets to handle lab inventory management comes with limitations and challenges.
Heavy reliance on manual inputs
The use of spreadsheets heavily relies on manual inputs, which can be time-consuming and necessitates a thorough understanding of how the spreadsheet functions for all lab members. Although spreadsheets are typically free to use, they come at the expense of your valuable time spent organizing and managing inventories.
While attempts can be made to automate certain aspects of the spreadsheet using functions and equations, manual input is inherently prone to errors. This brings us to the next drawback of spreadsheets, namely the difficulty in tracking activities within the spreadsheet.
We have all encountered situations where a minor error or unfamiliar behavior disrupts the entire functionality of the spreadsheet. This lack of reliability becomes particularly problematic when there is a large volume of inventory items or samples to track, or when multiple team members are involved in entering data.
The lack of activity logs means that it is not possible to track who made what mistakes; even a minor mistake can have far-reaching consequences and potentially require hours to rectify.
Furthermore, spreadsheets typically exist as standalone files, lacking the ability to indicate the relationships between each inventory item and the experiments or projects they are associated with.
This lack of traceability becomes more pronounced when attempting to meet regulatory or industry requirements for data traceability, or even when revisiting specific reagent lots used in experiments that yielded the best results.
“While the flexibility and power of Excel can be considered a virtue, it is surprising that Excel and similar programs do not have a mode where dependability can be rigorously enforced…Users and their managers need to be aware that number entry is not as dependable as is commonly assumed: keying errors may remain undetected and may result in erroneous numbers being processed without warning.”
Harold & Cairns, Paul. (2010), Reducing number entry errors: Solving a widespread, serious problem. Journal of the Royal Society, Interface / the Royal Society.
Lab inventory management systems
Specialized lab inventory management software, such as Quartzy and Labspend, addresses several issues encountered when using spreadsheets to manage lab inventories. These systems not only assist in inventory management, but also streamline the process of requesting and ordering supplies.
By automating various aspects of inventory management and tracking system changes, they effectively reduce the occurrence of human errors.
These software solutions offer user-friendly interfaces, making it easier to onboard new lab members and ensure their understanding of the system for requesting and ordering supplies and reagents. Additionally, these systems facilitate labelling and barcoding, contributing to a well-organized inventory system.
Finally, they are usually integrated with online order systems, which streamline the process of restocking necessary supplies. Taken together, they will help improve the efficiency in the lab and save time.
However, it’s important to note that some of these software solutions come with costs, so labs will need to evaluate whether the investment is worthwhile for them. These systems also have specific purposes and may not facilitate the tracing of materials and reagents from source lots to experiments and projects.
In such cases, consider software that offers APIs or integration with other tools, such as electronic lab notebooks (ELNs) or laboratory information management systems (LIMS), to meet comprehensive data management and traceability needs.
Electronic lab notebooks with built-in lab inventory management
While ELN notebooks were not originally designed solely for inventory tracking, there are clear benefits to managing your inventory directly within your electronic lab notebook.
The built-in system allows you to directly associate reagents and materials with your protocols, experiments, and results. Consequently, you can easily trace back to the specific reagent stock or sample batch required to reproduce your results successfully.
Another significant advantage of utilizing a built-in inventory management system within an ELN is the potential for automating stock management as you complete and document an experiment in your electronic lab notebook.
Once configured, the ELN notebook can deduct the amount of reagent used during an experiment directly from your inventory. When the stock reaches a low level, a low-stock warning will alert you to restock, mitigating any delays in your projects.
Similar to specialized inventory management systems, ELNs do come with associated costs. However, they often serve as the primary data management hub for labs and fulfill various compliance and data protection requirements; inventory management functionalities could be included or come with minimal cost.
Specialized systems may offer additional features, such as supply requesting and ordering, that may not be available in electronic lab notebooks; a combination of the two systems can give you the best of both worlds. It such cases, it is worth exploring ELNs that offer software integrations with these systems or API.
Lastly, if you plan to migrate your inventory from spreadsheets or specialized inventory management systems into an ELN, ensure that your ELN notebook supports the import of inventories to facilitate a smooth transition.
Pro-tip: If you find it difficult to abandon your inventory spreadsheet, but want to take advantage of the inventory management system offered by SciNote or other platforms, consider using SciNote’s API to sync the spreadsheet with SciNote. You will need a bit of technical knowledge to do this, though, but we will provide all the API documentation necessary.
Download SciNote's Lab Inventory Management Guide
Get a complete overview of inventory management system in SciNote ELN.Download the guide
Are there other options for lab inventory management?
One option that is often overlooked for managing inventories is using a productivity tool like Notion. Notion provides a highly flexible workspace that enables teams to collaboratively manage projects and organize content.
One of its notable features is its database and spreadsheet functionalities, which offer a more user-friendly interface compared to traditional spreadsheets. This can be beneficial in ensuring that lab members accurately enter inventory information and easily adapt to the spreadsheet format. While Notion does come with a cost for certain users, it is generally more affordable compared to many other alternatives.
However, it is important to consider that Notion, as a rather open-ended and flexible workspace, is not specifically designed with lab work in mind and can be overwhelming to get started. You will need to familiarize yourself with how Notion operates and customize the database based on your lab’s specific requirements.
Unless you invest a significant amount of time in building it from scratch, it may not reach the same level of inventory management capabilities as specialized systems or ELNs with built-in inventory management. It also won’t facilitate data traceability or automate stock management as ELNs do.
It’s worth noting that Notion has limitations in terms of the number of formulas it supports and lacks certain features, such as advanced charting and graphics, which are available in dedicated spreadsheet software. Therefore, when it comes to complex calculations and plotting, spreadsheets still hold an advantage over Notion.
Pro-tip: You can start with some existing Notion inventory templates.
Moving lab inventories between systems
Your needs could change, or the software you use might change. It is important to consider how you will migrate your inventories in such circumstances.
The majority of laboratory inventory management systems offer the option to export your inventory in .csv format, facilitating the transfer of your inventory from one system to another.
Before importing the data into the new system, it is critical to ensure that you properly organize the columns and rows based on the structure of the target system to accurately map the information during the migration process.
Investing the effort to set it up correctly from the start is far more advantageous than attempting to fix the database after the migration. By taking the time to organize and map the data appropriately, you can avoid potential complications and ensure a smooth transition to the new system.
Pro-tip: SciNote allows import of inventory data in comma separated (.csv), tab separated (.txt and .tsv) or Excel (.xlsx) formats. It also allows full export of inventory items in .csv format.
Lab inventory management recommendations
Use spreadsheets if you
- Have a small lab (3-5 members)
- Are confident that all lab members will be familiar with how they should track and submit requests to order reagents
- Don’t plan to improve your existing data management and sharing practices
Use a specialized inventory management system (e.g., Quartzy or Labspend) if you
- Want to focus only on inventory management, supply requests, and supply ordering
- Don’t plan to improve your existing data management and sharing practices
- Have funding for it (some software might be free, but it is hard to say if that will continue to be the case in the future)
Use an ELN with a built-in inventory management system (e.g., SciNote) if you
- Need to meet data management and sharing requirements, OR
- Have other compliance, data protection, or data traceability needs
- Want to connect your experimental data to inventories
- Would like automate stock tracking to avoid delays in projects
- Have funding for data management
- Want to streamline your research data collection with lab automation (API)
Use a combination of a specialized inventory management system with an ELN (with existing integration or via API) if you
- Want to ensure full data traceability from inventories, protocols, to results
- Want to maximize the efficiency of your supply and material procurement processes
- Have the budget to cover the cost
Use Notion if you
- Want something that’s cloud-based with a nicer user interface (vs spreadsheets)
- Want to customize the system specifically for your own use
- Are familiar with Notion, OR
- Don’t mind spending a significant amount of time to build the database in Notion
Pro-tip: if you are planning on using a spreadsheet, consider the inventory spreadsheet shared by Christina Termini, a cell biologist and assistant professor at the Fred Hutchinson Cancer Center. It’s this inventory spreadsheet that went viral on Twitter, that it ended up being featured in Nature.