How to Calculate the SuperTrend Indicator using Excel

Last Updated on September 21, 2023 by Mark Ursell

This article shows you how to calculate the SuperTrend Indicator in Excel. Watch the video to see me demonstrate the calculations.

The article also explains why the indicator works, how to identify the best markets to use and gives you a link to a SuperTrend trading strategy.

Video – How to Calculate the SuperTrend Indicator in Excel

Watch the video to see me demonstrate how to calculate the SuperTrend Indicator. The Excel formulas I use for the SuperTrend are shown at the bottom of this article.

What is the SuperTrend Indicator?

‘Super’ + ‘Trend’

The SuperTrend is a technical indicator used by traders and investors to identify the dominant trend. It follows the trend until the price closes below the indicator (for long trends) or above the indicator (for short trends).

The SuperTrend indicator works for stocks, ETFs, forex and futures trading and is valid on any timeframe.

What Else Does It Do?

The SuperTrend uses another great technical indicator – the ATR (Average True Range). The ATR measures the absolute range of each bar.

The ATR has two powerful features:

• It adjusts to recent volatility
• It adjusts to the absolute size of the market

The SuperTrend bands are wider in volatile markets and tighter in quiet markets.

The SuperTrend indicator only moves in the direction of the trend, and it only changes direction on the close of a bar (which is why you see price spikes through the indicator)

What Markets Should I Trade the SuperTrend On?

“Disregarding the big swing and trying to jump in and out was fatal to me. Nobody can catch all the fluctuations. In a bull market your game is to buy and hold until you believe that the bull market is near its end.”

Lefèvre, Edwin. Reminiscences of a Stock Operator

The SuperTrend indicator works better on markets that tend to trend. You will get better results by focusing on these markets. You can find them by focusing on:

• Markets that have trended in the past – these have a much higher tendency to trend in the future.
• Markets that have a strong identity among traders and investors – eg tech stocks from 2010-2022.

Markets That Have Trended In The Past

There are many ways to identify these markets.

The simplest approach is to scan through charts showing a long time frame. You are looking for markets that have tended to trend smoothly over the years and have not been too volatile or spent too long stuck in ranges.

To process more data, you can use an online screener to look for markets that meet the criteria. You want to find markets that have moved up and down regularly without experiencing large amounts of volatility.

Markets That Have a Strong Identity with Traders and Investors

To use the example of the tech-heavy Nasdaq 100. This has shown an amazing tendency to trend over the years. This is because investors and traders understand that buying this index (or the underlying stocks) is a clear bet on ‘risk-on’ technology stocks.

By comparison, the FTSE 100 index of the largest UK-listed companies contains a disparate bunch. It has a heavy weighting of banks, oil producers and miners. It is unusual for all the elements to be performing well (or badly) at the same time, and it tends to trend more slowly and have frequent retracements.

These days it is easy to select ETFs with a coherent identity with the expectation that they will trend in the future.

Excel Formulas to Calculate the SuperTrend

The SuperTrend indicator is calculated using the ATR to offset the indicator from the average price. When the price touches the SuperTrend line, it turns over to the other direction.

The formulas below can be copied directly into Excel. Watch the video above to see me demonstrate.

Step 1: Calculate the Upper and Lower SuperTrend Basic Bands

In this step, you calculate the basic bands.

Cell O3 is your SuperTrend multiplier.

Calculate the Average Price (High+Low)/2 and then offset this by the ATR * Multiplier.

Upper Band Basic O24 =((D24+E24)/2)+($O$3N24)

Lower Band Basic P24=((D24+E24)/2)-($P$3N24)

Step 2: Calculate the Final Upper and Lower Bands

Now you need to make sure that the SuperTrend always moves in the direction of the trend.

You will do this by using an IF Statement.

Upper Band Q24 =IF(OR(O24<Q23,F23>Q23),O24,Q23)
Lower Band R24 =IF(OR(P24>R23,F23<R23),P24,R23)

Step 3: Get the Final SuperTrend Indicator

You now need to complete the indicator. Remeber: the indicator only shows either the upper or lower band and changes direction when the price closes beyond the indicator.

This IF Statement checks when the closing price has crossed and the indicator changes direction.

SuperTrend S24 =IF(AND(S23=Q23,F24<=Q24),Q24,IF(AND(S23=Q23,F24>=Q24),R24,IF(AND(S23=R23,F24>=R24),R24,IF(AND(S23=R23,F24<=R24),Q24,””))))

Get Extraordinary Trading Results

The power to backtest strategies will transform your trading:

• You can test new trading ideas as they occur to you.
• You can choose only the best and most profitable strategies.
• You can trade with confidence, knowing that your strategy has performed well.