Skip to content

Tips for Constructing Lab Databases in Excel

Posted in: Organization and Productivity
Tips for Constructing Lab Databases in Excel

Good organization is essential for keeping a lab in good running order.  Databases of strains, plasmids, primers, and stocks are useful for keeping track of your materials, and allow your work to be continued easily after you’ve left the lab.  In this article, I’ll talk about a few tools in Microsoft Excel that will make your databases easier to use and more efficient to navigate.

1.     Drag and fill function

When setting up your database for the first time, chances are you’ll want to number each entry (or assign some other kind of identifying code).  Instead of individually entering the log number, use the drag and fill function to auto fill these fields for you.  To set up the pattern, enter two sequential values in adjacent cells.

Tips for Constructing Lab Databases in Excel

Then, select these two cells, click on the little black box on the lower right hand corner of the selected area, and drag the box down to auto fill values for as many cells as you like.  Unclick only when you have selected all of the cells you want filled.  This function works for series’ of numbers, dates…basically any logical numerical sequence you can think of.

Tips for Constructing Lab Databases in Excel

2.     Format dates

One problem that can arise from having many people using one database is inconsistency in notation.  You can avoid this to a large extent by pre-formatting cells so that their content automatically snaps to the correct form when entered.  This works especially well for dates.  To format cells for your desired date notation, select the cells you want to format (hint: you can also select the entire column).

Tips for Constructing Lab Databases in Excel

From the Home menu, open the Number dialog box and select Date.  Choose the format you like best from the list of options.

Tips for Constructing Lab Databases in Excel

Then hit okay to apply this format to the selected cells.

Tips for Constructing Lab Databases in Excel

3.     Drop-down lists

For databases including information like antibiotic resistance, storage location, sources, or any other field that has a limited number of choices, consider adding a drop-down list to some of your fields.  This simplifies information entry, and has the added benefit of standardizing the information that multiple users input.  To set up a drop-down option, you’ll first need to define a list of options.  This list can be anywhere within the document – in the same sheet, or a separate sheet.  In this example, I’ve made a list of antibiotic resistance markers for plasmids.

Tips for Constructing Lab Databases in Excel

When you’ve made your list, select the cells (or column) that you want to apply this list to.  Then, under the Data menu, select Data validation.  Under the Allow menu, choose List, and define the Source data as your list of options, then hit okay to apply.

Tips for Constructing Lab Databases in Excel

Now when you click in a cell with a list, an arrow appears to the right-hand side; clicking the arrow cause the list to drop down so you can select from your list of options.

Tips for Constructing Lab Databases in Excel

4.     Sorting

Many times you will want to sort a database by a particular parameters in order to find a specific entry or group of entries (for example, every plasmid that one lab member made).  It’s important to select all of the data for each “record” (i.e. all of the columns with data in them), so that data in individual columns doesn’t get mixed up and associated with the wrong record.  The safest way to do this is to select rows by clicking and dragging down the far left of the Excel spreadsheet (be sure to include your “title” row).

Tips for Constructing Lab Databases in Excel

On the Data menu, select Sort.  When the Sort window opens, you will see a variety of options: the drop-down menus allow you to choose which column you want to sort by, and what order you want the records to be sorted into.

Tips for Constructing Lab Databases in Excel

Choose your parameters, and hit okay to apply.

Tips for Constructing Lab Databases in Excel

5.     Conditional formatting

Finally, a useful tool for identifying records of interest is using conditional formatting to highlight specific values.  This can come in handy when scanning a list for values above a certain threshold, an item entered on a particular date, plasmids containing the same fluorescent tag…almost anything can be identified by this search function.  To highlight a class of entries, first select the cells you want to search (it’s often simplest to just select the whole sheet).

Tips for Constructing Lab Databases in Excel

On the Home menu, select Conditional formatting and the Highlight cells rule.  In this case, I’ve chosen the “text that contains” option to search for plasmids encoding RFP.  Enter the item you want to search for – you can also choose which highlighting option suits you best, or just use the default settings.

Tips for Constructing Lab Databases in Excel

Hit okay to apply.  Now every entry that matches your search parameters is highlighted and easy to find.

Tips for Constructing Lab Databases in Excel

What are your favorite tips for organizing lab databases?

Share this to your network:

9 Comments

  1. BioGeek on December 14, 2016 at 9:02 am

    We use a super user friendly https://www.labsuit.com A free lab management tool for research labs.
    It help us manage plasmids, antibodies, chemicals. LabSuit has easy to way to add new items to the inventory list, like automated hints. It saves us tons of time.

  2. Nick on August 9, 2011 at 11:05 am

    There are some good points here. In the lab I often used Excel for “mini” databases of plasmid and cell stocks. Excel is, of course, very easy to deploy and worked fine for me for keeping track of my personal stocks, as long as I was very careful about how I handled it. Emily’s advice in this article will be useful for people who want to use it for this purpose.

    When I tried to use Excel for communal stocks it was much less successful. So I did as yokofakun and David suggested, and tried to set up proper databases, first in MS Access and then using MySQL. I consider myself fairly technophilic, but without the specific knowledge of how to handle them, I found these solutions very difficult to wrestle with and maintain. In the end we decided we didn’t have the time to spend on this and went back to the archaic and flawed Excel “databases”.

    So I can see the problem here — Excel is not suitable, but setting up a true database is just a too difficult for most people.

    Do you guys know of a simple way to tackle this problem? Is there an “easy” way to set up an maintain an SQL database, or is there a robust and simple DB application that is as easy to set up as Excel, but offers the benefits of a true database? LibreOffice looks promising in this regard — does it fit the bill?

    I’d be very interested to hear your thoughts — perhaps one of you would like to write something about it for the website?

    • Peter Blazso on August 9, 2011 at 11:44 am

      Nick, when data consistency counts it is essential taking some time to get familiar with the term referential integrity (https://en.wikipedia.org/wiki/Referential_integrity) and relational databases. I would suggest LibreOffice Base as a start to get the concept of creating and maintaining small databases. There are tutorials (e.g. https://wiki.documentfoundation.org/cgi_img_auth.php/0/02/Base_tutorial.pdf) out there. Many things in this software are just click-and-go and it has easy-to-use wizards built-in as well.

    • Chen Guttman on August 9, 2011 at 1:49 pm

      Hey Nick,
      Would like to comment on: “So I can see the problem here — Excel is not suitable, but setting up a true database is just a too difficult for most people.” and also in regard to what have been raised here in context to database management through Excel or other solutions.
      Everybody knows laboratory data is extremely sensitive, especially when it is accumulating over the years and many ppl in the lab are depending upon it. This is why it is very important to use the best tools that will be both easily accessible and safe. SQL, Access or other database DIY tools are excellent tools for database management when they are used by ppl who know what they are doing as they can be too complex for management/maintenance not to mention construct one from scratch. There are other options, though. You can use an already built database which is both constructed, maintained and serviced by professional software engineers.
      One such option is BioKM. These guys, which I am working with, have invented BioKM for the following reason: to enable sharing, documenting, managing and safe-guarding your data indefinitely, among other things BioKM can do.
      I ask: Why should a PhD student, which suppose to do science, start to learn how to use access/SQL so the lab will have a better database? Or, worse, work with Excel which can be easy to setup but can have unhappy and terminal surprises in regard to safeguarding your data? The answer is students should not mess with it. Time is money, and in this regard, even the time of a (cheap) and talented student.
      So, if you’re data is precious, treat it that way. Don’t try to save a nickel here and there when you have years of hard work depending on it. It ain’t worth it. Buy a good laboratory database/management system and save the headache and heaps of trouble!
      Chen

    • ScottP on May 1, 2012 at 3:06 pm

      Great points and great discussion. I’ll be up front right now and tell you that I work with Quartzy (https://www.quartzy.com), a free, online suite of lab management tools that can help solve this very problem of how to organize copious amounts of important research data. Excel definitely has its place, but a high-paced research lab is not one of them. Emily points out some good tools that Excel has and Nick points out that for some “mini” databases it could be sufficient. But the amount of data being produced and accessed in today’s research environment continues to skyrocket and the ability to share this data with other lab members is becoming more and more important. Using Excel to manage this information is time-consuming, frustrating, and inefficient. Nick also points out that setting up a true database is too difficult for most people and Chen notes that most scientists don’t have the time to create or even learn how to use a complex home-grown database solution.

      In searching for an answer to this quandry, Chen points out that there are solutions out there that help tackle this issue. Quartzy is one of those platforms that can help alleviate this problem. It’s built for just this type of work — housing, organizing, and managing large amounts of data that 99% of the time aren’t needed. But when that experiment is on the line, and you need that plasmid quick — Quartzy is there to help! Aside from managing inventories, Quartzy can help streamline ordering, house common protocols, and even has a calendar functionality for signing up for facilities and equipment. Another aspect that sets Quartzy apart from standard database systems is the ability to rate and comment on products and protocols so that you can share with lab mates and other scientists.

      There is a lot going on in a lab and the last thing someone needs is to get frustrated because they can’t find that antibody they need by digging through their archaic or difficult to use database. Quartzy is free and easy to use and is a great solution to database management within the research environment.

  3. DavidM on August 9, 2011 at 9:49 am

    Oh my goodness. That is a bitesize recipe for annoying anyone who has to maintain this info in the future. It is bad practice. It is the wrong tool.
    Excel might be fine if you use it as a front end but build your database in something that looks like a database, ie Access, Filemaker and so on for the easy to use ones, or for something that should be more robust, mysql/postgres via a web interface such as phpmyadmin.

    Excel is a tower of cards. When it comes crashing down you will be crying. And it will, just when the deadline is looming. All it takes is one misclick and a column is resorted independently of the others. Goodbye referential integrity.

  4. yokofakun on August 8, 2011 at 7:02 pm

    Oh, please, please, please. Don’t use excel as a database. Learn how to use a real database (mysql ,sqlite) or even a interactive product (e.g: LibreOffice-Base https://www.libreoffice.org/features/base/ ). But please, make the world better, make the bioinformaticians happy, don’t use excel as a database. See also, https://www.biomedcentral.com/1471-2105/5/80 . Please, please, please, don’t use excel as a database.

    • Emily Crow on August 8, 2011 at 9:06 pm

      That’s an interesting article about Excel – thanks for pointing it out!

      Excel seems to be the most user-friendly “database” application for those of us who aren’t bioinformaticians. Do you find that the database options you mentioned are usable for most scientists?

    • Peter Blazso on August 9, 2011 at 9:21 am

      I agree partially with yokofaun. Modeling and maintaining a relational database instead of making flat tables in Excel saves you from lots of future headaches but its usage depends on the task to solve. When you only need to maintain simple lists of items quickly (e.g.: an inventory of plasmids, reagents, etc.), a spreadsheet application (instead of the expensive MS-Excel the free LibreOffice Calc) is better and easier to use. In these cases there is no need to tackle with the complexity of database design. However, as things get serious with lots of inventory lists or for example a whole group of people want to maintain an up-to-date lab repository you really should consider designing a true relational database (like PostgreSQL or MySQL with a LibreOffice Base or MS-Access frontend) and moving everything into it.

Leave a Comment

You must be logged in to post a comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll To Top