How to Analyze qPCR Data in Excel (2 Easy Methods)

qPCR is a sophisticated technology used to measure the amount of DNA in a sample by using PCR. qPCR stands for the term quantitative polymerase chain reaction. Though it is an advanced procedure, we can analyze the qPCR data in Excel. In this article, we will demonstrate to you two methods on how to analyze qPCR data in Excel. If you are curious to know them, download our practice workbook and follow us.

What Is qPCR Analysis?

qPCR or quantitative polymerase chain reaction is a sophisticated technology used to measure the amount of DNA in a sample by using PCR (polymerase chain reaction). There are two ways to analyze qPCR data.

Double Delta Ct Method

In 2001, Livak and Schmittgen termed this analyzing procedure, the Double Delta Ct method. It is a specific case of the Pfaffl method. According to this method, we have to input the two average sets of experimental data (Gene Tested Experimental and Housekeeping Gene Experimental) and control data (Gene Tested Control and Housekeeping Gene Control). Using these values, we will get the values of ∆CTE and ∆CTC, respectively.

The value of ∆CTE represents the deduction value of Gene Tested Experimental (TE) and Housekeeping Gene Experimental (HE). The general expression of ∆CTE is:

Similarly, the value of ∆CTC stands for the deduction value of Housekeeping Gene Control (HC) from Gene Tested Control (TC). The mathematical expression of this term is:

After that, we need to estimate the value of ∆∆Ct. The formula to determine the value of ∆∆Ct is:

Finally, we will evaluate the Gene Expression Ratio. According to this method, we assume the Primer Efficiency for both experimental and control genes is 100%. Thus, the expression of the Gene Expression Ratio is:

Pfaffl Method

The Pfaffl method is a general method for qPCR analysis. This method has similarities with the Double Delta Ct method. However, the Primer Efficiencies value in this method is not 100%. It can be any number. Usually, this value remains between 90% to 110% for both House Keeping Gene (HKG) and Gene of Interest (GOI). In 2001, Michael Pfaffl offered this analysis formula in the Nucleic Acids Research journal.

In this approach, we have to input two sets of data. Using this data, we calculate their average value. Then, we estimate the average of the control samples. From this, we will determine the value of ∆Ct. The expression of ∆Ct is:

Moreover, the mathematical formula to analyze the Gene Expression Ratio is:

2 Easy Ways to Analyze qPCR Data in Excel

In this contest, we are going to analyze the qPCR data for three or five samples of data. We will first conduct the analyze for the Double Delta Ct method and then we will demonstrate the Pfaffl method.

1. Analyze qPCR Data Through Double Delta Ct Method

For analyzing the qPCR data through the Double Delta Ct method, we consider a dataset of five DNA samples. In this method, we have to consider the value of the PCR Primer Efficiencies for both the experimental gene and control gene is 100%. We can also claim that this is a specific case of the Pfaffl method. The steps will analyze given as follows:

📌 Steps:

First of all, input all the gene values accurately as shown in the image. We have to input all the experimental values of Gene Tested Experimental(TE) and Housekeeping Gene Experimental(HE) in columns B and C Similarly, input all the control values of Gene Tested Control(TC) and Housekeeping Gene Control(HC) in columns D and E respectively.

Now, in cell F5, to calculate the value of ∆CTE, write down the following formula.

=B6-C6

Press Enter.

Similarly, to estimate the value of ∆CTC, write down the following formula in cell G5.

=D6-E6

Again, press Enter.

Then, select cell H5 and write down the following formula to get the value of ∆∆Ct.

=F6-G6

Press the Enter.

Finally, to calculate the value of the Gene Expression Ratio or 2^(−∆∆Ct), write down the following formula in cell I5.

=2^(-H6)

Press Enter for the last time.

After that, select the range of cell F6:I6.

Double-click on the Fill Handle icon to copy the formula up to row 10.
You will get the values of the Gene Expression Ratio for every sample.

Thus, we can say that our method worked perfectly, and we are able to analyze qPCR data in Excel.

🔍 Interpretation of the Result

The value of the Gene Expression Ratio of 0.6507 in cell I5 means the gene samples in the tested conditions with respect to the control conditions all have been normalized to our housekeeping gene. Moreover, you can consider it in the percentage term. 0.6507 means 65.07% gene expression in our tested condition with our control condition.

2. Analyze qPCR Using Pfaffl Method

In this approach, we will use the Pfaffl method. The main difference between this method with the Double Delta Ct method is in the value of PCR Primer Efficiencies for both the experimental gene and control gene which are not 100%. In this example, we will assume the Primer Efficiency values for both cases. For the House Keeping Gene sample, we are presuming the value of Primer Efficiency is 93%, whereas for Gene of Interest this value will be 101%. The steps of this method are given below:

📌 Steps:

At first, input all the gene values accurately, as shown in the image. We have to input all the two sets of treated and control gene values of House Keeping Gene (HKG) and Gene of Interest (GOI). Both gene sets are denoted Ct-1 and Ct-2 respectively.

First, we will complete all the calculations for the House Keeping Gene (HKG) section.
Now, we will take the mean value of both sets. For that, we are going to use the AVERAGE function.
Write down the following formula in cell E6.

=AVERAGE(C6:D6)

Press Enter.

Then, double-click on the Fill Handle icon to copy the formula up to cell E11.

After that, we have to estimate the average of the three control values.
For that, write down the following formula in the merged F6

=AVERAGE(E9:E11)

Press the Enter.

Now, select cell G6 and write down the following formula to get the value of ∆Ct. Make sure you input the Absolute Cell Reference for cell F6.

=E6-$F$6

Again, press the Enter.

Then, double-click on the Fill Handle icon to copy the formula up to cell G11.
Our all calculations for the House Keeping Gene (HKG) are finished.

Similarly, following the same process complete all the calculations for the Gene of Interest (GOI) section.

Finally, in cell O6, write down the following formula to get the value of the expression ratio. Ensure the Absolute Cell Reference for cells N6 and H6.

=($N$6^M6)/($H$6^G6)

Press Enter for the last time.

Now, double-click on the Fill Handle icon to copy the formula up to cell O11.
You will get the values of the expression ratio for every sample.

So, we can say that our method worked successfully, and we are able to analyze qPCR data in Excel.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Analyze qPCR Data.xlsx

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to analyze qPCR data in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!