How to Calculate Bollinger Bands Using Excel – 3 Easy Steps

Last Updated on September 21, 2023 by Mark Ursell

This article and video shows you how to calculate Bollinger Bands using Excel.

Traders use these bands for their remarkable predictive power as well as their simplicity and ease of use.

Bollinger Bands are a technical indicator that are placed on charts to show when the price is at an extreme relative to recent price action. You can use them to take profits or identify changes in the market direction.

Bollinger Bands expand and contract depending on price action. The width of the bands is a useful guide to volatility.

YouTube Video

Watch the YouTube video to see me demonstrate how to calculate Bollinger Bands in Excel.

Calculate Bollinger Bands using Excel

The first stage in calculating Bollinger Bands is to take a simple moving average. In Excel, we use the formula =AVERAGE().

Next, we need to calculate the standard deviation of the closing price over the same number of periods. The standard deviation is a measure of volatility, and it increases when the price moves away from the average. We calculate the standard deviation using the formula =STDEVPA().

The standard deviation is then multiplied by a factor (typically 2). We calculate the upper band by adding the standard deviation multiplied by the factor to the moving average. We calculate the lower band by subtracting the standard deviation multiplied by the factor from the moving average.

I don’t typically have Bollinger Bands on my trading screen because they clutter the charts and distract from the price action. However, I often add them to my charts to see whether the current price is inside or outside the bands.  I also like using them when developing automatic trading strategies because they are self-scaling. This means that you can apply them to any market and timeframe without adjusting the parameters.

Formulas Used

SMA H23 =AVERAGE(F4:F23) Upper Bollinger Band I23 =H23+(STDEVPA(F5:F23)$I$3) Lower Bollinger Band J23 =H23-(STDEVPA(F5:F23)$J$3)

Learn to Backtest

You can learn how to backtest your trading strategies using a Tradinformed Backtest Model. The models are built using Excel and you can use them to test on any timeframe and market. See the latest models in the Tradinformed Shop.

Resources

The real power of Bollinger Bands becomes evident when they are
combined with indicators.

John Bollinger – Bollinger on Bollinger Bands

Not many technical indicators have been so memorably named after their creator as Bollinger Bands. Bollinger on Bollinger Bands is an excellent guide to trading with the bands. This book has phenomenal information showing how the inventor uses them to trade.

It also contains some interesting historical detail about the background of how John Bolinger created the bands.

You can see more by checking out the book on Amazon: Bollinger on Bollinger Bands