Options Spreadsheet: Track IV Rank, Greeks and Strategy Selection in Excel (2026)

Options spreadsheet - if you searched that, you are almost certainly looking for a single Excel workbook that does what most option-platform dashboards split across five different screens: track implied volatility, see where IV sits relative to its one-year range, watch put-call sentiment, screen for strategy fit, and run quick what-if scenarios without flipping between tabs. This guide ships exactly that. Two downloadable workbooks, six sheets each, every important data cell driven by a verified MarketXLS formula, plus a written walkthrough of how each piece of the workbook earns its place. The goal is not another generic options calculator. It is a practical, dashboard-style options spreadsheet that a self-directed trader or advisor can keep open during market hours and trust.

The thinking is plain. Option pricing moves with three things you can actually see in advance: where the underlying is trading, how rich or cheap the options are priced (implied volatility), and the prevailing positioning of other participants (put-call volume and open interest ratios). When all three are in one screen, strategy selection stops being a guess. That is what this options spreadsheet is built to deliver.

Options spreadsheet at a glance - the key data table

The first scroll of any good options spreadsheet should answer this question for every ticker on your watchlist: is implied volatility expensive or cheap right now, and which family of strategies does that suggest? Below is the data layout that drives the included templates.

Values shown are illustrative for the sample workbook. In the live template, every cell in the table above is a MarketXLS formula keyed off column A, so swapping symbols rebuilds the entire dashboard on refresh.

Options spreadsheet - why IV rank is the keystone column

Of every column in an options spreadsheet, the one that does the most work is IV rank. Implied volatility on its own does not tell you whether a 25 percent IV reading is high or low for that name. NVDA at 48 percent IV is comparatively calm. KO at 25 percent IV would be a five-alarm fire. IV rank fixes that by scaling the current IV against its trailing one-year high and low.

“

IV rank = (Current IV - 52-week low IV) / (52-week high IV - 52-week low IV)

A reading near 1.0 means current implied volatility is at the top of its one-year range. A reading near 0.0 means it is bottomed out. In the MarketXLS formula world, this is one function call:

=ImpliedVolatilityRank1y("NVDA")

The companion =ImpliedVolatilityPct1y("NVDA") returns the percentile, not the rank, which answers a slightly different question (what percentage of days over the last year had a lower IV than today). Both columns live in the included options spreadsheet because a high IV rank with a moderate percentile usually signals a recent spike, while a high IV rank with a high percentile signals a regime change.

Put-call ratios - what an options spreadsheet does that a chart cannot

Option positioning ratios are surprisingly hard to read off a brokerage screen. They are also one of the most useful pieces of data for context. A high put-call volume ratio (above roughly 1.0) suggests defensive flow. A low ratio (below 0.7) suggests call-buying enthusiasm. Open interest ratios filter out the day-trading noise and show where structural positioning sits.

In the workbook, two columns handle this:

=opt_PutCallVolRatio("SPY")
=opt_PutCallOIRatio("SPY")

A useful reading habit: when both ratios are elevated and IV rank is also high, the market is paying you to sell premium into fear that may or may not be justified. When ratios are very low and IV rank is also low, complacency is priced in and protective puts get cheap. The options spreadsheet keeps both ratios visible at all times so this judgment is fast.

Options spreadsheet sheet-by-sheet walkthrough

The two included workbooks share the same six-sheet structure. The static sample workbook has values frozen on the day of publication so you can see what the dashboard looks like with a normal market day plugged in. The live template wires every data cell to a MarketXLS function, so refreshing in Excel updates the entire surface.

Sheet 1 - How To Use

A purpose statement, edition note, links back to MarketXLS, a one-line description of what each downstream sheet contains, and a quick reference for interpreting IV rank and put-call ratios. The bottom of the sheet has the MarketXLS Functions Used box, which lists every function that drives anything on this sheet. Every sheet has one. That is how the workbook teaches itself.

Sheet 2 - Main Dashboard

This is the screen that should be visible during market hours. Five input cells in yellow at the top of the sheet hold the trader-level settings - account size, risk per trade, the IV rank thresholds that mark a name as a premium-buy or premium-sell candidate, and the days-to-expiration target. Below the inputs, the live watchlist is built one row per ticker, with these columns:

• Last price - =QM_Last(A10)
• 30 day implied volatility - =ImpliedVolatility30d(A10)
• 1 year IV rank - =ImpliedVolatilityRank1y(A10)
• 1 year IV percentile - =ImpliedVolatilityPct1y(A10)
• Put-call volume ratio - =opt_PutCallVolRatio(A10)
• Put-call open interest ratio - =opt_PutCallOIRatio(A10)
• Total option volume - =opt_TotalVolumeOptions(A10)
• Beta - =Beta(A10)
• Dividend yield - =DividendYield(A10)
• 14-day RSI - =RelativeStrengthIndex(A10,"14")
• Trend versus 50 DMA - an IF that compares QM_Last to SimpleMovingAverage(A10,"50")
• Strategy bias - an IF that maps IV rank against the threshold inputs

The bias column is the one trick worth calling out. It does not pick a strategy for you. It uses the IV rank thresholds in the input panel to color-code each ticker as a candidate for premium selling, premium buying, or neutral. That is a regime label, not a trade idea.

Sheet 3 - Scenario Analysis

A reference ticker is picked at the top of the sheet (yellow input). The price and IV are pulled live with =QM_Last(B2) and =ImpliedVolatility30d(B2). Below those, a seven-row scenario table runs through realistic underlying-and-IV combinations: a rally with IV crush, a slow grind up with IV slowly bleeding, a flat range with the same IV bleed, a slow grind down with IV expanding, a sharp selloff with vega exploding, a post-earnings IV crush, and a tail event with the full vega gap.

Each scenario shows the new price, the new IV, and an educational rough P and L per share for long calls and long puts at the reference strike. The math is intentionally simplified to keep the spreadsheet readable. The point is regime intuition, not exact Black-Scholes accuracy. For exact Greeks, the next sheet hands off to MarketXLS option-symbol functions.

Sheet 4 - Strategy / Options

This sheet maps the IV rank for each ticker to a family of educational strategies. The mapping logic is encoded as an IF formula in each row, so the strategy label updates the second the IV rank changes on Sheet 2.

• High IV rank (above 0.55): premium selling regime. Educational ideas include cash-secured puts, covered calls, iron condors, and credit spreads.
• Mid IV rank (between 0.30 and 0.55): mixed regime. Calendar spreads and diagonals shine here because they benefit from a moderate IV expansion without paying up for outright optionality.
• Low IV rank (below 0.30): premium buying regime. Long calls, long puts, and debit verticals get cheaper here. The risk is the slow theta bleed if the underlying does not move.

The risk note column reminds the trader that the loss profile depends entirely on the structure picked. For a credit spread, max loss is the strike width minus the credit received. For a debit spread, max loss is the net debit paid. None of these labels are recommendations. They are educational starting points for further analysis in your own brokerage tools.

Sheet 5 - Portfolio Allocation

The Portfolio-Allocation sheet enforces position sizing discipline. Three yellow inputs at the top - account size, max risk per position as a percent of account, and max notional beta - define the box that every position must fit inside.

Below the inputs, the ten-symbol watchlist gets a target weight and the formulas compute:

• Position dollar size = account size times target weight
• Approximate contracts = position size divided by (last price times 100)
• Notional dollar exposure = contracts times last price times 100
• Beta-weighted exposure = notional times Beta divided by account
• Yield drag from any long-stock leg (covered call writers care about this)

The formula list is on the sheet. The intent is that an advisor or self-directed trader uses this sheet as a sanity check before placing any trade, not as a portfolio recommendation.

Sheet 6 - Greeks Comparison

The Greeks-Comparison sheet is the bridge between watchlist-level data (which uses ticker-keyed functions like ImpliedVolatility30d) and contract-level data (which needs a full option symbol built with OptionSymbol(Ticker, Expiry, "Call"/"Put", Strike)). The sheet uses =QM_GetOptionChainActive(A8) to spill the most actively traded contracts for each ticker, and =QM_GetOptionQuotesAndGreeks(A8) to bring the full chain with Delta, Gamma, Theta, Vega and Rho already attached. The note column reminds the reader how to plug a specific option price, expiry and strike into the standalone opt_Delta, opt_Gamma, opt_Theta and opt_Vega functions when an exact Greek read is needed.

Real MarketXLS formulas the options spreadsheet uses

The point of an options spreadsheet that is not a static toy is that every important value is live. Here is the verified function list (every one of these was confirmed via the MarketXLS Function Docs MCP before being placed in the workbook):

No invented function names. No fake helper macros. If a function does not exist in MarketXLS, the workbook does not use it. The sample workbook also keeps a static version of every value for the publication date, with the corresponding formula listed in a Formula Reference column so a reader can see exactly what to type.

Options spreadsheet in practice - how a single screen earns its place

A trader using the workbook will typically open Main Dashboard, scan the IV Rank column, and use the Strategy Bias column as a first-pass filter. From there, the workflow looks like this:

1. Pick a row whose bias matches the current desk view (selling premium today, buying premium today, or wanting calendar exposure).
2. Confirm the put-call ratios are not pointing in the opposite direction. A sell-premium bias on a ticker where the P/C volume ratio is well above 1.5 is a signal to slow down and read why the market is buying so much downside protection.
3. Jump to Scenario Analysis, paste that ticker into the yellow input cell, and look at how a long call and a long put react to the seven canned scenarios. This is a quick intuition check, not a trade plan.
4. Open Strategy-Options to read the educational strategy label for that row, then move to Greeks-Comparison and use the spilled chain to look at the actual contracts that fit.
5. Before sizing a position, return to Portfolio-Allocation and verify the contract count does not break the max-risk-per-position or max-notional-beta inputs.

That is the loop. The workbook does not pick a trade. It compresses five different screens into one and forces the trader to look at IV rank and positioning before reaching for a strategy. That is the real value of a well-designed options spreadsheet.

Download the templates

Download the templates:

• - Pre-filled with values for 2026-05-29
• - Updates each session in Excel

Both files include a How To Use sheet, six sheets total, and a MarketXLS Functions Used box at the bottom of every sheet listing the exact formulas powering that sheet.

Reading the workbook like an options desk would

Three habits make any options spreadsheet much more useful than its first day:

Use IV rank as a filter, not as a signal. A high IV rank by itself does not mean sell. It means premium is rich relative to its one-year range. That is necessary but not sufficient. Always check why IV is elevated before assuming the premium is free money. Earnings, regulatory headlines, and macro events can all justify a high IV rank.

Pair the put-call ratios with IV rank. When IV rank is high and put-call ratios are elevated, the options market is paying you for taking the side of a crowded fear trade. When IV rank is low and put-call ratios are low, the market is in a complacent regime. Both columns sit next to each other on Main Dashboard for exactly this reason.

Size positions with the Portfolio Allocation sheet first. Most options accounts that struggle do not struggle because of strategy choice. They struggle because position sizing was set at the level of individual conviction rather than at the level of account-wide notional risk. The included sheet bakes that discipline in.

Beyond the included workbook - extending the options spreadsheet

The workbook is intentionally small enough to read in one sitting. If you want to extend it, here are the natural next steps, each of which uses MarketXLS functions already verified for this project:

• Earnings-aware IV. Add a column for =ExEarningsImpliedVolatility30d(Symbol). That returns 30-day IV with the earnings premium removed, so you can see how rich IV really is once the binary earnings event is stripped out. Comparing that to the raw IV30 column tells you exactly how much of the elevation is earnings.
• Historical IV term structure. Replace IV30 with a triplet of IV30 / IV60 / IV90 to spot regime shifts in the term structure. Backwardation (IV30 above IV60) tends to flag near-term stress.
• Tail-event monitor. Add =opt_PutCallVolRatioHistorical(Symbol, RefDate) against a reference date to see the change in P/C volume ratio. Sudden spikes often precede or accompany sharp moves.
• Spread builder. Use =Strikes("AAPL", NextExpiry) and =Expirations("AAPL") to power dropdowns that let the user build a custom vertical or calendar spread on the same sheet.

All of these are one-cell additions in MarketXLS. None require macros or third-party data libraries.

FAQ

Why use an options spreadsheet at all when most brokerages have option dashboards?

Brokerage dashboards optimize for placing the next trade. An options spreadsheet optimizes for context and discipline. The dashboard sheet shows where IV sits relative to its one-year range, which most trading screens do not surface directly. The portfolio sheet keeps a hard cap on notional beta and on dollar risk per position. The scenario sheet runs price and volatility moves side-by-side. None of these are research outputs you can copy out of a single broker screen. The spreadsheet stitches them together.

What is IV rank, and how is it different from IV percentile?

IV rank measures where current implied volatility sits between its one-year high and one-year low, scaled from 0 to 1. IV percentile measures what fraction of trading days over the last year had a lower IV than today. The two move together but answer different questions. Rank shows distance from the range edges. Percentile shows how unusual the current reading is in distribution terms. Both are useful, and both are included in the workbook.

Do I need every MarketXLS option function for the workbook to be useful?

No. The Main Dashboard runs entirely on a small core set: QM_Last, ImpliedVolatility30d, ImpliedVolatilityRank1y, opt_PutCallVolRatio, opt_PutCallOIRatio, Beta, DividendYield, and RelativeStrengthIndex. The Greeks-Comparison sheet adds option-symbol-level functions for users who want to drill down into specific contracts. Every workbook sheet shows the function list it uses at the bottom, so there is no mystery about what is on or off.

How often should the spreadsheet be refreshed?

For end-of-day analysis, refreshing once after the close is enough. For intraday work, MarketXLS supports streaming functions on the live data side (for example, =Stream_Last("AAPL")), but the workbook ships with non-streaming functions by default to avoid hitting refresh quotas during planning sessions. Switch to streaming functions inside Excel for live use.

Can the spreadsheet replace an options-trading platform?

No, and it is not designed to. The workbook is for context, screening, sizing and scenario thinking. Order entry, fills, and the actual execution side of the workflow stay with a brokerage account. The spreadsheet does the part the broker does not - it pre-filters which tickers and which strategy family are worth considering before any contract is ever loaded.

Is everything in the workbook a recommendation?

No. Every educational label in the workbook is exactly that, educational. The strategy bias column is a regime label, not a buy or sell call. The scenario P and L numbers are simplified for intuition. Nothing in this guide or the workbook constitutes investment advice, and no specific position size or trade should be entered without a full risk analysis appropriate to your account, jurisdiction and risk tolerance.

The bottom line

A serious options spreadsheet does three things at once: it shows where implied volatility sits in its own context (IV rank), it shows what the option flow is doing around that level (put-call ratios), and it forces every potential position through a sizing check before it gets entered. The included workbook is exactly that. Two files, six sheets each, every important cell driven by a verified MarketXLS function, and a written walkthrough of why each piece earns its place.

For more on the MarketXLS options function library, the MarketXLS documentation hub lists every supported function with examples. If you want to see a deeper, advisor-style walkthrough of how MarketXLS fits into a full options workflow, book a live demo.

Educational analysis only. Not investment advice.