**Creating Bollinger Bands in Excel involves several steps.** You must

- Calculating the moving average
- Then, the standard deviation
- Calculating the upper and lower band
- Plot the bands in a Line Chart.

## What are the Bollinger Bands?

Bollinger Bands are a popular technical analysis tool used by traders and investors to assess a security's volatility and relative price levels. They consist of a moving average and two standard deviation lines (bands) plotted above and below the moving average. The purpose of Bollinger Bands can be summarized as follows:

**Identify Overbought/Oversold:**Upper band signals overbought; lower band signals oversold.**Signal Reversals:**Price moving outside then inside bands may indicate trend reversals.**Trend Analysis:**Upward bands show uptrend; downward bands show downtrend.**Pattern Recognition:**Helps identify double tops and bottoms.**Confirmation Tool:**Used with other indicators for better signal accuracy.

## Prepare your data in Excel to build your Bollinger Bands

Ensure you have a dataset with **dates and corresponding closing prices**. That's all you need.

If you work with Excel 365, the STOCKHISTORY function helps you to collect this information easily.

### #1: Calculate the Moving Average:

Decide on the period for the moving average, e.g., 20 days.

- Select cell B21 (20 rows after the first cell)
- In B21, enter the formula for the moving average
**=AVERAGE(B2:B21)** - Drag this formula down to calculate the moving average for the entire dataset.

### #3: Calculate the Standard Deviation

In cell C21, enter the formula to calculate the standard deviation **for the same range used in the moving average**.

### #3: Calculate the Lower Bollinger Band

Bollinger Bands use 2 times the standard deviation to define the upper and lower bands around a moving average. According to statistical theory, approximately 95% of data points in a normal distribution lie within Â±2 standard deviations of the mean. Using 2 standard deviations helps capture most price movements, providing a meaningful boundary for volatility.

- In cell E21, enter the formula to calculate the upper band
**=C21 - (D21 * 2)**

### #4: Calculate the Upper Bollinger Band

- In cell F2, enter the formula to calculate the lower band
**=C21 - (D21 * 2)**

## Plot the Data in a Line Chart

Drawing a chart in Excel is very easy.

- Select all your data
- Go the menu
**Insert > Chart > Line**

Then you have this chart, but you need to customize some chart elements.

- Click on the
**+ sign**on the right side of the chart - Remove
**Chart Title** - Remove
**Legend.**

- Right-click on your chart.
- Select the option "
**Select Data Source**" - Unchecked the Stdev series

- Select the Vertical axis
- Open the right pane (
**double-click on the axis**) - Go to the menu
**Axis option**(the chart icon) - Change the
**value of the minimum**according to the data in your chart

- Change the color of the lines and the background to present your chart like this

## How to Analyze a Bollinger Chart?

This is just the theory. There is no foolproof way to make perfect buy or sell decisions in equities.

- If the stock price reaches or exceeds the upper Bollinger Band, it signals a potential selling opportunity.
- Conversely, if the stock price hits the lower Bollinger Band, it indicates a potential buying opportunity.