# How to Calculate Bollinger Bands in Excel

by Suman Medda ; Updated July 27, 2017### Items you will need

- Microsoft Excel
- Recent security price information

Bollinger bands are a mathematical tool used to predict the prices of securities based on their past performance. There are three bands: middle, upper and lower. The bands are a high, low and average prediction of where the security is traveling. A feature of Bollinger bands is that the upper and lower bands contract and expand based on the volatility of the security. The bands can be calculated manually or they can be streamlined and automatically calculated in Microsoft Excel.

Open a "Worksheet" in Microsoft Excel.

Label columns "A" through "G" by writing a name in the first row of the column. "A" is the Closing Price, "B" is the Simple Moving Average, "C" is the Deviation, "D" is the Deviation Squared, "E" is the Standard Deviation, "F" is the Upper Band, and "G" is the Lower Band.

Enter the closing price of the security for the past 20 days into column "A." Cells "A2" to "A21" should now be filled.

Calculate the mean of the 20 days by entering "=SUM(A2:A21)/20" in the "A22" cell.

Copy the value from cell "A22" into column "B." Cells "B2" to "B21" should now be filled with the same value from cell "A22."

Enter "=B2-A2" in cell "C2." Copy the value in cell "C2" and paste it into the entire column from cells "C2" to "C21."

Enter "=POWER(C2,2)" in cell "D2." Copy the value in cell "D2" and paste it into the column from cells "D2" to "D21."

Enter "=SQRT(SUM(D2:D21)/20)" into cell "E2." Copy the value in cell "E2" and paste it into the entire column from cells "E2" to "E21."

Enter "=B2+(2*E2)" in cell "F2." Copy the value in cell "F2" and paste it into the entire column from cells "F2" to "F21." This column is the Upper Band.

Enter "=B2-(2*E2)" in cell "G2." Copy the value in cell "G2" and paste it into the entire column from cells "G2" to "G21." This column is the Lower Band.

Locate the three band columns. The Middle Band is in column "B." The Upper Band is in column "F." And the Lower Band is in column "G."

#### References

#### Photo Credits

- John Foxx/Stockbyte/Getty Images