Microsoft Excel Can Help You Set Up Multi-Well Plates

When you are a newbie to qPCR or qRT-PCR, it is quite common to write everything down in your lab notebook and then tediously mark off reagents or samples as you add them. People typically start with a master mix for items you add to multiple samples such as (Mg2+, dNTPs, 10X PCR buffer, additives, Taq polymerase of choice and water). And one will usually have a unique template, that is added either as the first or the last reagent to the tubes. While this works great for simple setups, it gets confusing and error prone when you are doing more complex qRT-PCR set ups. For instance, one step qRT-PCRs may require that the volume of your starting RNA samples varies from sample to sample. Alternatively, if you are doing two step reactions, you may need to setup first strand synthesis reactions for many samples. I have faced both of these problems, and finally stumbled upon a workaround using Excel that definitely reduces the error if not the effort.

The Problem

I currently work with microRNAs and screen the expression of different miRNAs in clinical samples. These samples have low RNA abundance, which means that in my first strand cDNA synthesis (for a 2-step qPCR or a single step qRT-PCR), I end up having to add different volumes of RNA to each tube/ well of a 96-well plate. Since the volumes of the common components are identical across all tubes, I also end up having to add different volumes of molecular grade water to each well to equalize volumes.

I quickly found out that even with drawing plate maps in my lab notebook and full concentration, a momentary distraction (a curious / talkative colleague or activity in my vicinity) made me doubt which well I added the RNA or water to last. This doubting then nags me till I see the final data which I will end up repeating just to ensure that I did not commit a mistake during the first run. Unfortunately, the same doubt can creep up while setting up the second plate and things can thus go downhill from there.

Figure 1. Example table showing setup of large number of reactions

The Excel Work Around

Well, I figured out that I could use a little help here. I usually work with a laptop – Microsoft Surface Pro 3, on my lab bench. I started making my plate maps in Excel and making sheets to do my calculations as shown above in Figure 1. The first column is the sample number, the second shows RNA concentration (ng/ ul), the third shows volume needed for 100ng across all samples, the 4th and 5th show common reactants, and the 6th shows volume of water needed to make up the reaction volume to 10ul in the penultimate column. When I pipette up the corresponding sample from the ice bath, my attention goes from making sure that I picked up the right sample to ensuring that I add it to the proper well on my plate. As you can see this can get very confusing and causes errors when adding individual RNAs and water to each well. And it gets tiring too!

Using the Excel Voice Command

Well, Excel helps you here a bit. What if you had a live assistant who would speak out the volumes to you when you pressed enter? Then all you would have to do is to focus your attention on choosing the right sample and adding it to the right well. How is this possible? Excel has a nifty feature that speaks cells. To enable this feature go to Customize the Quick Access Toolbar as shown below (Figure 2). Next click on More Commands. Pull down All commands in the left panel and scroll down to Speak upon enter. Click Add and this shortcut moves it to the right panel (Figure 3).

Figure 2. Customizing the quick access toolbar to add speak upon Enter.

Select Ok and come back to main screen. The “speak upon Enter” should be now visible in the Quick Access Toolbar.

Figure 4. Customized toolbar

We are all set now. Arrange your samples in the same order as your spreadsheet. Select the RNA volumes in column N as shown below. Click on the Speak upon Enter button. Make sure that the volume of your PC or tablet is turned up and press enter. Now add the sample to the corresponding well without having to change views between the sample, plate, and spreadsheet. Once you are done with the RNA, just select the column containing the water volumes and repeat as above.

This method certainly works in reducing my error rate to near zero. It also removes any doubts, since you only draw the sample as the program speaks the volume. Hope this small tip helps those who need to set up big plates every so often.

Image credit: Kevin Doncaster


  1. Ayo James on August 24, 2017 at 8:45 pm

    Thank you so much for this great insight. It sure works very well

  2. Chris on August 17, 2017 at 3:27 pm

    Is there a way to customize the speed of Excel’s text-to-speech module? I can imagine having a hard time when you have to switch volumes in a broader range. At the current rate of my personal set-up, I might have trouble to even pipette precisely in this time.

Leave a Comment