2 Ways to Calculate Bacterial Growth Rate in Excel

Calculating bacteria growth rates manually can be very tiring and time-consuming for anyone because the numbers are very large. But the good news is you don’t need to calculate the bacterial growth rate manually anymore. Because you can use Microsoft Excel to calculate the bacterial growth rate quite easily. And, also it can generate curves portraying the results. Now, I am going to show you 2 ways to calculate the bacterial growth rate in Excel.

What is Bacterial Growth Rate?

Under favorable conditions, the bacterial population increases in a geometric sequence. The bacterial population doubles its number in a periodic pattern. Like if the culture has 2 bacteria in 1 hour, it will be 4 in 2 hours, 8 in 3 hours, 16 in 4 hours, and the number goes on.

This phenomenon is called Exponential growth. Exponential growth is only possible in favorable conditions, in the laboratory.

🔗 Calculate Percentage of a Number in Excel [4 Cases Explained]

Bacterial Growth Rate Formula

The increase in bacterial cultures with duration is constant for a bacterial medium. It is symbolized using μ.

The equation for bacterial growth is:

Rate of increase of bacterias = μ*(Change of time)

ln N_t – ln N₀ = μ (t – t₀)

∴ μ = (ln Nt - ln N0) / (t - t0)

Or,

μ = (log Nt - log N0)*2.303/(t - t0)

---

Formula Explanation

• μ = Bacterial growth rate
• N_t = Total bacteria number at the time (hour), t
• N₀ = Total bacteria number at the time, t₀ 
• t = Time of calculating the growth rate
• t₀ = Beginning time of bacteria counting

---

You can calculate the time in hours or minutes. It depends on your required interval.

🔗 How to Calculate Annual Growth Rate in Excel (5 Different Cases)

Introduction to the Dataset

For giving you an example, I am using a dataset of two bacteria cultured plates in a given range of time. Plate A (column B) and Plate B (column C) cultured different numbers of bacteria at the same time range (column A).

🔗 Calculate Dividend Growth Rate in Excel [2+ Formulas]

Quickest Way to Calculate Bacterial Growth Rate in Excel

First, I am going to show you the most used method of finding the microbial growth rate. For this purpose, I’m using the LOG function.

Now Follow The Guide ↓

Step_1: In the cell D3, write down this formula:

=(LOG(B3)-LOG(B2))*2.303/(A3-A2)

---

Formula Explanation

• LOG(B3): Final bacteria number in Plate A
• LOG(B2): Initial bacteria number in Plate A
• A3: Final time (hour)
• A2: Initial time (hour)

---

Step_2: Press ENTER to apply the formula.

Step_3: Double click on the Fill Handle ‘+’ icon to copy down the formula.

The Fill Handle copied the formula through the entire column D.

Here, I didn’t apply the formula from the top cell (D2) because there is no change in the bacterial number at the culture’s beginning time.

Step_4: Again type the formula in the cell E3 for the bacteria of Plate B:

=(LOG(C3)-LOG(C2))*2.303/(A3-A2)

Step_5: Press ENTER.

Step_6: Double-click on the Fill Handle to fill down the formula.

You will see the results in decimal format. But usually, we don’t express the rate in decimal formats. So I like to change the form to a percentage (%).

Step_7: Now select the range of decimal numbers, from D3 to E11.

Step_8: Click on the Home tab.

Step_9: From the Number group, click on the % option.  

Final Result ↓

Now you will find the results are nicely presented in percentage form.

🔗 3 Ways to Forecast Growth Rate in Excel

🔗 2 Ways to Calculate Revenue Growth Rate in Excel

Alternative Way: Using Chart to Calculate Bacterial Growth Rate in Excel

We can input the bacteria growth number with the corresponding time to generate a curve. Then we will use an Excel command to generate an equation that represents the curve. From the equation, we can easily find out the bacterial growth rate.

The equation that Excel will generate out of the curve has the following format:

Y = b*m^x

---

Formula Explanation

• Y depends on the value of x. So it is the total number of bacteria in the x given time.
• m is used as the base for x. Here, m is the bacterial growth rate.
• b is a fixed or constant value.

---

Now Follow The Guide ↓

Step_1: First, select the two columns, Column A [Time (Hour)] and Column B [Bacteria Culture (Plate A)].

Step_2: Click on the Insert tab.

Step_3: From the Charts group, click on the Scatter drop-down menu.

It depends on your preference which scattering style you like. I picked the second option from the list.

Step_4: Now click on the blue curve and select the Select Data command.

The Select Data Source window will come along on the screen.

Step_5:  Hit the Add button.

The Edit Series window will pop up.

Now Input the ranges in the window.

Step_6: For the Series Name, click on cell B1 to input the name Bacteria Culture (Plate A).

Step_7:  For Series X Values, select the cell range A2:A11.

Step_8: In the Series Y Values, input the range from cell B2 to B11.

To make you understand better, you will see the outputs are delivered on the right side of the input boxes. I have yellow-marked the examples.

Step_9: Then hit the OK button.

This will lead you back to the previous Select Data Source window.

Step_10: Repeat the process from step_5 to step_9 for Bacteria Culture (Plate B).

Step_11: Press ok the OK button.

Two curves will appear on the diagram. One is blue and the other one is orange.

Display Equation on Chart

Step_12: Now right-click on the blue curve.

Step_13: From the context menu, click on the Add Trendline option.

On the right side of your screen, the Format Trendline common box will come along.

Step_14: Click on the Exponential radio button from the Trendline Options.

Step_15: After that, scroll down and give a tick mark on the Display Equation on the Chart checkbox.

Step_16: Now select the orange curve, and repeat from step_12 to step_15.

The followed steps returned the equations on the chart beside each curve.

Normally, I prefer to remove the Chart title and add the Axis titles for preparing the reports.

A Chart title is generally appropriate for making a PowerPoint presentation or something like that.

Step_17: Click on the big ‘+’ sign and checkmark the Axis Titles.

 

Step_18: Change the Axis Titles with appropriate names.

Final Result ↓

Here is the final outcome. Have a look.

The basic equation we used for this method was Y = b*m^x.

Now you look at the results. One is y=64e^0.6931x and the other one is y=32e^0.6931x.

Compare your findings with the basic equation and take the numbers before the X variable as the bacterial growth rate.

In my case, the results are 69% for both bacterial cultures.

🔗 2+ Ways to Calculate Monthly Growth Rate in Excel

🔗 3 Scenarios of Exponential Growth Formula in Excel

Conclusion

I hope these two methods will help you count the bacterial growth rate in Excel. Feel free to let us know if you have any questions regarding bacterial growth rate calculation.