How to Calculate the Williams %R Using Excel

Last Updated on November 28, 2022 by Mark Ursell

In this article and video, I show how to calculate the Williams %R using Excel.

The Williams %R is a technical indicator developed by Larry Williams. It is an oscillator that is used by traders to identify underlying market strength.

Video: How to Calculate Williams %R

YouTube video

Williams Percent Range

Williams %R on the FTSE 100 Index

The %R is a fast-moving oscillator that is similar to the stochastic oscillator. It calculates how the current price compares to the recent highs and lows.

It does not have any smoothing which means that it responds quickly to a change in the price action. The indicator moves in a range from 0 to -100.

One of the best ways to trade using the %R is to identify divergences between price and the oscillator. If the price is setting new highs and the oscillator is not making new highs this is a warning that a divergence is happening.

In the chart above the FTSE 100 index was making new highs during April 2015. However, the %R was getting weaker. By the end of April, the price was following the %R lower.

How to Calculate the Williams %R in Excel

Excel is a tremendous tool for all your trading analysis. You can use it to calculate technical indicators, backtest trading strategies and develop new trading strategies. On this website, there are lots of resources that make it easy to test your own strategies using Excel.

Calculate the Williams %R Using Excel

Total Time: 20 hours and 10 minutes

Start with Your Historical Price Data in an Excel Spreadsheet

Create three new column headings:
Column G: Recent Highs
Column H: Recent Lows
Column I: %R
Calculate Williams %R in Excel

Enter the Formulas for Recent Highs and Recent Lows

Recent Highs use the formula =MAX()
Recent Lows use the formula =MIN()

Cell G15 =MAX(D2:D15)
Cell H15 =MIN(E2:E15)

Calculate Williams %R in Excel 2

Enter the Formula for Williams %R

The formula for %R is: (high-close)/(recent highs-recent lows)*-100

Cell I15 =(G15-F15)/(G15-H15)*-100

If you need some more guidance, watch the video above showing me demonstrating how to set up the spreadsheet to calculate %R:

Calculate Williams %R in Excel 2

Larry Williams

“Always think that the next trade will be a loser… a big loser”

Larry Williams

Larry Williams has been a trader and teacher of traders for many years. One of his market philosophies is to “always think that the next trade will be a loser … a big loser”. It that sounds a bit strange to you then check out his book on Amazon: Long-Term Secrets to Short-Term Trading.

More Resources

Discover the secrets of the markets you trade by backtesting. Find hidden opportunities and develop a solid reliable trading strategy. Tradinformed Backtest Models use Excel, and make it easy for you to develop and optimize any trading strategy.