How to Calculate ATR (Average True Range) Manually in Excel

To calculate Average True Range in Excel, first find each bar's true range: the largest of high minus low, the absolute difference between high and the previous close, and the absolute difference between low and the previous close. Average the first 14 true ranges to initialise a 14-period ATR. For every later bar, use (previous ATR × 13 + current true range) ÷ 14. This is Wilder's smoothing method, not a new rolling average on every row. 1 2

The steps below build a transparent worksheet you can inspect cell by cell. They use a short historical excerpt from StockCharts' published ATR example, not a current Indian stock price or a trading recommendation. The same arithmetic can be applied to properly sourced rupee-denominated share prices or index-point data without changing its definition.

Key Takeaways

• True range captures gaps that a simple high–low calculation can miss.
• A 14-period ATR needs 14 valid true-range observations before its first result.
• Wilder smoothing, a rolling simple average and a standard EMA can produce different values.
• ATR measures volatility in the input's price units. It does not tell you which direction price will move.

What ATR measures and what data you need

ATR is a volatility indicator developed by J. Welles Wilder. Its input is a series of high, low and closing prices, together with the preceding bar's close. A rising ATR reflects wider recent true ranges; it can occur during a rise or a fall. It is not a buy-or-sell instruction or an estimate of the next session's exact range. 1 2

A period means one bar, not necessarily one calendar day. Fourteen daily bars normally represent 14 trading sessions. Fourteen five-minute bars represent 14 five-minute observations. Do not mix daily highs with intraday closes, or add artificial weekend rows to a daily stock series.

Export the data from a source that identifies the instrument, exchange, interval and timestamp. Sort it oldest to newest, remove duplicates and keep one consistent adjustment basis. Mixing adjusted closes with unadjusted highs and lows can manufacture gaps around corporate actions. Check the original source before interpreting an unusual value; our guide to verifying a stock-market claim in India explains that evidence-first habit.

Step 1: Create the Excel worksheet

For consistency with the downloadable workbook, put the column headings in row 8 and the first observation in row 9. Leave columns A and B empty. This is a fixed 14-period teaching layout, with optional rolling-SMA results kept separately.

Use real Excel dates and numeric prices. Imported numbers stored as text, empty cells and duplicate timestamps are data-quality issues, not zero-volatility observations. Keep the original precision: some source prices in this example have three decimal places.

Step 2: Calculate the first true range

There are two reasonable starting conventions, but they cannot be mixed unnoticed. StockCharts' published example has no previous close for its first row, so it sets the first true range to high minus low. This tutorial follows that convention. Leave G9, I9 and J9 blank, and enter: 1

H9 =D9-E9
K9 =H9

The first example bar is 1 April 2010, with high 48.70, low 47.79 and close 48.16, in the source's historical US-dollar price series. Its true range is 48.70 − 47.79 = 0.91. The first observation is an initialization choice; it does not prove that no gap occurred before the available sample.

If you have a verified close from the immediately preceding bar, you can instead calculate full three-component true range for the first observation. Record that choice. Your seed may differ from this example and from a chart that starts its history earlier. Do not use zero as an invented previous close.

Step 3: Calculate true range for every later bar

In row 10, the previous close is the close from row 9. Calculate the three distances separately so you can see which one wins:

G10 =F9
H10 =D10-E10
I10 =ABS(D10-G10)
J10 =ABS(E10-G10)
K10 =MAX(H10:J10)

Fill G10:K10 downward for the remaining valid rows. Excel's relative references move to the correct previous row. Microsoft defines ABS as the absolute value and MAX as the largest argument, which gives the required gap-aware calculation. 3 4

The compact equivalent, once the inputs have been validated, is:

=MAX(D10-E10,ABS(D10-F9),ABS(E10-F9))

A real gap example from the same historical dataset is 14 April 2010. High is 49.92, low is 49.50, and the previous close is 49.32. The three candidates are 0.42, 0.60 and 0.18. True range is therefore 0.60, not 0.42. Using only high minus low would miss part of that bar's movement relative to the prior close. 1

Step 4: Seed the 14-period ATR

Leave L9:L21 empty. Row 22 contains observation 14, so the first ATR belongs in L22:

=AVERAGE(K9:K22)

There are exactly 14 true-range cells in that range. The source excerpt's values are:

0.91, 0.58, 0.51, 0.50, 0.58, 0.415, 0.26,
0.49, 0.60, 0.32, 0.93, 0.76, 0.45, 0.465

Their total is 7.77, so the seed is 7.77 ÷ 14 = 0.555. It appears on 21 April 2010 in this sample. StockCharts displays its initial ATR rounded to two decimals; the worksheet keeps the unrounded seed for subsequent calculations. 1

Do not let Excel average an incomplete window. Microsoft documents that AVERAGE ignores blank and text cells in a referenced range. The formula can return a plausible number even if only 13 of your supposed 14 observations are numeric. Use this completeness guard for the seed: 5

=IF(COUNT(K9:K22)=14,AVERAGE(K9:K22),NA())

A displayed #N/A is a request to repair the inputs, not a number to replace with zero.

Step 5: Apply Wilder smoothing from the next row

In L23, enter the recurrence:

=(L22*13+K23)/14

Copy it downward from L23. For a general period n, the method is (previous ATR × (n − 1) + current TR) ÷ n. The weight on the newest true range is 1/n; the remainder stays in the previous smoothed value. 1 2

The next source bar has true range 1.10. Its ATR is (0.555 × 13 + 1.10) ÷ 14 = 0.5939285714, displayed here to ten decimal places. The following true range is 0.48, producing 0.5857908163 from the full-precision previous result. Do not type the shortened displayed value back into the chain.

These are attributed historical observations from StockCharts' worked spreadsheet. The dates and input precision have been retained; the results have been independently recalculated. They are not 2026 prices, rupee quotes or evidence of future returns. 1

Download the checked Excel worked example

The 14-period ATR worked-example workbook includes those first 16 source observations, separate true-range components, a guarded initial seed, the Wilder updates and a rolling-SMA comparison. It is a macro-free .xlsx file with no broker connection, external-data connection or live-feed dependency.

Its row and column references match this article. Before reusing it, keep a copy of the historical example, then replace C9:F24 with your own verified chronological data. For additional bars, copy the formulas in G24:M24 into the next row and enter the new date, high, low and close. Fill only rows for actual bars; do not average an incomplete session as though it were finished.

The workbook includes cached results for reading and live formulas for recalculation in Excel. It is a fixed 14-period example, not a universal chart-platform clone. To use a different period, change the seed location, its counted range and every recurrence weight together.

Wilder ATR versus a rolling average or EMA

The first Wilder ATR and a 14-period simple average share the same seed. Afterwards they differ. In M22, enter =AVERAGE(K9:K22); in M23, enter =AVERAGE(K10:K23) and fill downward. Column M drops the oldest true range from each window. Column L carries forward the previous ATR through Wilder smoothing.

For this example, M23 becomes 7.96 ÷ 14 = 0.5685714286, whereas L23 is 0.5939285714. Neither is an Excel malfunction: they are different definitions applied to the same inputs. Call column M a rolling SMA of true range, not Wilder ATR.

A standard n-period EMA commonly uses weight 2/(n + 1). Wilder smoothing uses 1/n. Do not select an EMA setting and assume it reproduces Wilder's method simply because both labels say 14. Verify the chart platform's documented smoothing and seed rules before comparing results.

Prevent missing-data and import errors

Validate the inputs before taking the maximum. A blank previous-close cell can be treated as zero in arithmetic and create an enormous false gap. Formatting a cell as a number does not necessarily convert an imported text value into a number.

For later rows, test the actual previous close cell, rather than the derived Previous close formula cell. A stricter K10 formula is:

=IF(AND(COUNT(D10:F10)=3,COUNT(F9)=1,D10>=E10,F10>=E10,F10<=D10),MAX(D10-E10,ABS(D10-F9),ABS(E10-F9)),NA())

For the first row, use the corresponding three-input guard:

=IF(AND(COUNT(D9:F9)=3,D9>=E9,F9>=E9,F9<=D9),D9-E9,NA())

A simple G10 = F9 can turn a blank F9 into zero, so checking only G10 is insufficient. The workbook tests the raw prior close and guards the previous-close component too.

These guards reject missing numeric inputs and obvious inconsistent OHLC data. They do not verify that the source is authentic, that the exchange session is complete, or that every expected bar exists. Reconcile those separately.

Avoid IFERROR(formula,0) for missing prices: it hides the problem and can bias the ATR downward. Keep formula errors visible until you have corrected the row. Some Excel locales use semicolons instead of commas, and translated function names may differ; adapt the syntax to your installation without changing the calculation.

Why your Excel ATR may not match a trading chart

Check the instrument and interval first. A cash index, futures contract and ETF can have different highs, lows and closes even when their names look related. A five-minute ATR is not a daily ATR. Futures rollover treatment, exchange time zone and inclusion of extended sessions can also change the bars.

Next, compare the smoothing setting and starting history. A chart may call Wilder smoothing RMA or SMMA, but names alone are not proof of identical implementation. Ask whether the provider seeds with a simple average, which first-bar convention it uses and how many earlier observations it includes.

StockCharts explicitly warns that a short spreadsheet sample may not equal a chart value because initialization affects later ATRs. Its charts calculate back at least 250 periods, typically more; that is a statement about StockCharts, not a universal requirement for every platform. 1

Finally, retain full precision in the input and recurrence, check adjustment consistency, and confirm you are not comparing a completed bar with an evolving live one. Start with the same historical bar and compare true range before trying to explain the smoothed result. Our guide to beginner trading indicators provides context for treating an indicator as a measurement rather than a forecast.

Read the result without turning it into a trade signal

ATR has the same units as the prices. Rupee-denominated OHLC produces rupees; index OHLC produces index points. A ₹20 ATR does not say that tomorrow's move will be ₹20 or that loss is limited to ₹20. A large ATR also does not establish whether the next move is upward or downward. 1 2

For a separate descriptive percentage, calculate ATR ÷ close × 100. With Excel's percentage cell format, enter =L24/F24 and let formatting display the percentage. Do not also multiply by 100 in that percentage-formatted cell, or the display will be inflated. Label the result ATR as a percentage of close, since it is not the raw ATR.

Some trading methods use volatility when considering stop distances or position sizes. That does not make an ATR multiple a suitable recommendation for every reader. Gaps, slippage, liquidity and leverage can create losses beyond a planned threshold. Use the common trading mistakes guide to keep calculation accuracy separate from risk-taking decisions.

Frequently asked questions

What is the Excel formula for true range?

For high in D10, low in E10 and the previous close in F9, use =MAX(D10-E10,ABS(D10-F9),ABS(E10-F9)) after checking that all three inputs are valid numbers. The maximum includes gaps relative to the previous close. 1 3 4

Where should the first 14-period ATR appear?

With the first true range in K9, the fourteenth is in K22. Seed L22 using the average of K9:K22. Leave earlier ATR cells blank; do not label a shorter average as a complete 14-period ATR.

Can I use AVERAGE for every ATR row?

Only if you deliberately want a rolling simple average of true range. For Wilder ATR, use the arithmetic average once as the seed, then apply the previous-ATR recurrence. Name the two series separately.

How do I handle the first row without a previous close?

This example uses high minus low for its first true range, following StockCharts. If you have a verified preceding close, full true range is another documented starting choice. Keep the choice consistent and disclose it when comparing outputs. 1

Does ATR work for Indian stocks and intraday charts?

The arithmetic is independent of currency and can be applied to consistent OHLC bars. Match the stock or contract, exchange, session and interval. Fourteen intraday bars are not 14 days, and an incomplete bar may change before it closes. 2

Is ATR a percentage or a prediction of profit?

Neither. Raw ATR is an absolute volatility measurement in the price unit. ATR divided by close is a separately labelled relative measure. No ATR value guarantees direction, profit, a maximum loss or the next bar's exact range. 1 2

Sources and educational limits

Sources were checked on 15 October 2026. Formula definitions come from StockCharts and Fidelity; Excel function behavior comes from Microsoft. The worked OHLC excerpt covers 1–23 April 2010 from StockCharts' source-linked spreadsheet and is used solely to explain the arithmetic.

This article and workbook are general education, not personalized financial advice or a recommendation to trade. Neither connects to an account or supplies current market data. Check your data provider's terms and methodology before reusing prices, and see TradeGrows' financial-information disclaimer and editorial policy.

Direct sources

1. StockCharts ChartSchool — Average True Range, calculation and downloadable historical spreadsheet
2. Fidelity — Average True Range: description and Wilder recurrence
3. Microsoft Support — MAX function
4. Microsoft Support — ABS function
5. Microsoft Support — AVERAGE function and blank-cell behavior