Stock Screener in Excel: Build Your Own Live Watchlist in 2026

Stock screener in excel - if you came here looking for a way to rank your own watchlist by valuation, dividend yield, balance sheet quality, and risk without leaving the spreadsheet you already live in, this guide is built for that exact workflow. The web screeners are fine for a first cut, but the moment you want to score a list your way, weight inputs your way, and replay the same screen against three different threshold sets in under a minute, a workbook beats a website. The MarketXLS function library turns Excel into a live data layer, so the screen you build today refreshes by itself tomorrow.

This post walks through the design behind a dashboard-style stock screener in excel template that ships free at the bottom of the page. The workbook screens a twelve-ticker, cross-sector watchlist by seven user-controlled thresholds, scores each name on a quality and valuation rubric, runs a scenario sheet that compares five threshold sets side by side, and turns the surviving names into an educational position-sizing reference. You can keep the universe as is or replace every ticker in column A; the formulas extend automatically.

Stock Screener in Excel at a Glance

Before the deep dive, here is the headline output the dashboard surfaces for each ticker. The numbers below are an illustrative snapshot taken on 2026-05-13 from the static sample workbook. The live template pulls the same fields fresh from MarketXLS every time you open it.

The Screen Score column rolls up three checks. Pass the valuation gate (P/E under the threshold, yield above the threshold, beta under the threshold) and the ticker earns one point. Pass the quality gate (ROE above the threshold, operating margin above the threshold) and the ticker earns two more. The maximum score is three plus six bonus points from a separate trend sheet, which the dashboard maps onto a one-to-ten band. Sort by Screen Score to rank the watchlist; sort by P/E or Yield or Beta to slice the same list a different way.

Why Build a Stock Screener in Excel at All

Most public stock screeners are built around someone else's idea of what matters. They let you pick from a fixed menu of filters, a fixed set of universes, and a fixed display layout. That is fine for a first pass. The moment you want to overlay your own quality bar (say, ROE above 15% and operating margin above 12%), compare two threshold sets in the same view, weight the surviving names by score, and reuse the same rules next quarter on a different watchlist, you run out of room.

A workbook does not have that ceiling. The ticker universe is whatever you type in column A. The thresholds are whatever you put in the yellow input cells. The score formula is whatever logic you wire into the helper columns. Each sheet is a saved view of the same data, so a single refresh updates the dashboard, the scenarios, the sizing reference, and the trend comparison at once.

The other reason to keep this in Excel is repeatability. A web screen is a moment in time. A workbook is a process. Save it, share it with a colleague, run it weekly, archive the snapshot, compare to last quarter. The structure is reusable in a way that a screenshot of a website is not.

What Lives Inside a Useful Excel Stock Screener

A serviceable workbook splits the work across six sheets, each with a job.

1. How To Use sets context. It states the purpose, lists every MarketXLS function the workbook calls, and points readers at the inputs they should adjust. It is the first sheet new users see when they open the file.
2. Main Dashboard is the screener proper. Yellow input cells at the top hold the thresholds. A header row labels the data fields. A twelve-row table fills with live data from MarketXLS. Two helper columns convert the raw values into pass-or-fail flags, and a third column aggregates them into the Screen Score. Color bands shade scores into green, blue, and pink so the eye finds the leaders without sorting.
3. Scenario Analysis is the what-if sheet. It holds five preset threshold sets - Default, Strict Quality, Income Tilt, Growth Tilt, and Low Volatility - and counts how many names from the universe pass each set. Change a row, recount instantly. The same universe re-ranks five ways in the same view.
4. Strategy/Watchlist repurposes the screen for tactical use cases. Quality Compounders, Dividend Watchlist, Reasonable Growth, and Low Beta Defense are listed as example uses. Each has its own input recipe and a reference yield and beta so the cadence of the watchlist is obvious.
5. Portfolio/Allocation is the educational sizing reference. It pulls the Screen Score from the dashboard, normalizes it into raw weights, caps each weight at the user-supplied max position size, multiplies by the portfolio dollar amount, and computes approximate share counts off live prices. Nothing here is advice; it is a worked example of one way to translate a score into a sleeve.
6. Correlation/Comparison rounds out the workbook with trend and momentum context. It returns the 50-day and 200-day moving averages, the RSI, the beta, and the dividend yield for every ticker, plus a spilled history reference for charting. Use the history spill with =CORREL to build your own correlation matrix.

The point of splitting the work across six sheets is that each sheet has a single responsibility. You can rip any one out and rebuild it without breaking the rest.

The Seven Input Cells That Drive Everything

Every yellow cell on the Main Dashboard is a threshold the user controls. The default values shown on the sample are educational defaults, not recommendations.

The dashboard formulas read these seven cells. Change any one and the screen re-evaluates immediately. The scenario sheet uses its own copies so you can compare alternate threshold sets without overwriting the main inputs.

Real MarketXLS Formulas Doing the Work

The template uses only verified MarketXLS functions. No invented names, no placeholders. Below are the formulas that show up in the workbook with a short note on what each one returns.

=QM_Last(A12)                  -> Current traded price
=Sector(A12)                   -> Sector classification string
=PERatio(A12)                  -> Trailing twelve-month P/E
=DividendYield(A12)            -> Trailing dividend yield as a decimal
=Beta(A12)                     -> Beta versus the market
=TotalDebtToEquity(A12)        -> Total debt to total equity ratio
=ReturnOnEquity(A12)           -> Trailing return on equity
=OperatingMargin(A12)          -> Trailing operating margin
=MarketCapitalization(A12)     -> Market capitalization in dollars
=SimpleMovingAverage(A12,"50") -> 50-day simple moving average
=SimpleMovingAverage(A12,"200")-> 200-day simple moving average
=RSI(A12)                      -> Relative strength index, default lookback
=FiftyTwoWeekHigh(A12)         -> Trailing 52-week high
=FiftyTwoWeekLow(A12)          -> Trailing 52-week low
=QM_GetHistory(A12)            -> Spilled historical price array for charting

Every formula above is documented in the MarketXLS function library and verified before this template shipped. If you want to add more fields - revenue, EPS, cash flow per share, forward yield - the function library has them and the pattern in column A makes them trivial to wire in.

The pass-fail logic on the dashboard is plain Excel.

Pass Valuation:  =IF(AND(D12<=$B$4, E12>=$B$5, F12<=$B$6), 1, 0)
Pass Quality:    =IF(AND(H12>=$B$7, I12>=$B$8), 1, 0)
Screen Score:    =K12 + L12 * 2

You can rewrite the scoring rule. Weight quality higher, drop the beta gate, add a market-cap floor. The structure does not care what the score formula is, only that it lives in a single column the rest of the workbook reads.

The Scenario Sheet, in One Look

The scenario sheet is the most underrated feature in the template. Most screeners run one query and show one result. The scenario sheet runs five queries side by side and shows the universe re-ranked five ways at once.

The Pass Count column is a live COUNTIFS that reads back into the Main Dashboard table. Edit any row and the count re-evaluates. You can clone rows, add new scenarios, or rewrite every threshold to mirror your own quality bar. The whole sheet is twenty cells of formula logic; it just looks impressive because the layout matches how analysts already think.

Designing Your Own Screen Without Falling for Common Traps

The pitfalls of a homemade screener show up after you ship the workbook to colleagues, not before. A few rules earn their keep.

Pin the universe before the rules. A screen with too small a universe and too strict a rule set gives you the false confidence of a tight result. A screen with a wide universe and a loose rule set drowns you in noise. Decide on the universe first (say, the S&P 500, or the Russell 1000 ex-financials, or a thematic basket) and write the rules to fit that universe.

Avoid stacked filters that select for one thing. If you set Min ROE to 18%, Min Operating Margin to 18%, and Max Beta to 0.8, you are not screening for quality, you are selecting for one specific kind of business - large, defensive, structurally profitable. That can be fine if you know that is what you want. The trap is doing it without intending to.

Watch for survivor bias in the universe. Hard-coded ticker lists baked into a workbook tend to be the names that existed and mattered when the workbook was built. The universe drifts in the real world; the column A list does not, unless you maintain it.

Use the scenario sheet to stress-test rules, not to data-mine. Five scenarios in one view is a feature precisely because it discourages tweaking the same rule until you like what comes out. If two scenarios pass three names each and a third passes nine, that is signal about the rules' sensitivity, not a license to cherry-pick.

Refresh discipline matters more than formula novelty. A workbook that auto-refreshes once a week and is read every Monday morning will deliver more value than a workbook that calls fifty more functions but is opened once a quarter.

Sector Coverage in the Default Watchlist

The default twelve names cover most of the GICS sectors and are designed to be familiar enough that the metrics make sense on a first read.

Replace any of them. The screener does not care which tickers you use as long as MarketXLS recognizes them. You can replace the U.S. universe with a Canadian list, a UK list, or a sector-specific basket. The formulas extend automatically when you add rows.

A Light Position-Sizing Layer

The Portfolio/Allocation sheet is not a recommendation. It is a transparent example of how a Screen Score can be turned into educational target weights so the workbook tells a complete story end to end.

The math is intentionally plain.

Raw Weight       = Screen Score / Sum of Screen Scores
Capped Weight    = MIN(Raw Weight, Max Position Weight input)
Dollar Allocation= Capped Weight * Portfolio Size input
Shares Approx    = Dollar Allocation / Live Price

Names that fail the screen have a Screen Score of zero, so they receive a zero weight. Names with high scores share the bulk of the sleeve. The cap is what stops a single top scorer from owning the entire allocation. Adjust the cap on the Main Dashboard input to widen or tighten concentration.

If you prefer score-squared weighting, or risk-parity weighting, or equal weighting among survivors, you can replace the Raw Weight formula in one cell. The sheet was built for one obvious approach so it would be easy to swap.

How This Workbook Compares to a Web Screener

A web screener and a workbook screener are complementary tools, not rivals. The web screener is best when you have no shortlist and need to start from a universe of 8,000 names. The workbook is best when you already have a shortlist and want to score, weight, stress-test, and reuse it consistently.

Use the web for discovery. Use the workbook to operationalize the names you keep.

The Templates - Download Both

Download the templates:

• - Pre-filled with a 2026-05-13 snapshot and a formula reference column so you can see exactly which MarketXLS function powers each cell.
• - Fully live workbook that recalculates against current data every time you open it in Excel with MarketXLS installed.

Open the static one first to see how every cell is structured. Then switch to the live one and watch the same workbook refresh itself.

Frequently Asked Questions

What is the best stock screener in excel for fundamental investing?

The best stock screener in excel is one that lives where your other research already lives, refreshes itself, and lets you weight inputs the way you actually invest. A MarketXLS-powered workbook checks all three boxes because the function library returns live valuation, quality, and risk data directly into cells you control. The template at the top of this post is a starting point you can edit indefinitely.

Can I build a stock screener in excel without subscribing to a data feed?

You can wire up a static screener using values you type in by hand, but it is hard to keep that workbook current beyond the first day. A live screener needs a refreshable data layer. MarketXLS is the cleanest way to put one inside Excel because every function behaves like a normal cell formula, so the workbook stays a workbook rather than turning into an integration project.

How many tickers should be in a custom stock screener?

A custom stock screener works best with a deliberate universe of fifty to five hundred names. Fewer than fifty and the threshold logic does not have room to discriminate. More than five hundred and the workbook gets slow to refresh and hard to eyeball. The default template ships with twelve names so the structure is easy to read; the formulas extend the moment you paste a longer list into column A.

What formulas should a stock screener in excel use?

A stock screener in excel should call live data functions, never hard-coded numbers, anywhere a price, ratio, or balance-sheet metric appears. The MarketXLS basics worth pinning include =QM_Last, =PERatio, =DividendYield, =Beta, =ReturnOnEquity, =OperatingMargin, =TotalDebtToEquity, =MarketCapitalization, =SimpleMovingAverage, and =RSI. Together they cover valuation, quality, risk, and trend in nine cells per ticker.

How do I make a dividend stock screener in excel?

A dividend stock screener in excel raises the Min Dividend Yield threshold above 2%, lowers the Max Beta threshold below 1, and adds a payout-coverage check via =DividendPerShare divided by =EarningsPerShare. The Strategy/Watchlist sheet in the template includes a Dividend Watchlist recipe as an example. You can clone it, override the thresholds, and save the workbook as a dividend-only screener for monthly review.

How do I refresh a MarketXLS stock screener?

You refresh a MarketXLS stock screener by opening it in Excel with MarketXLS installed, signing in, and clicking the refresh control inside the MarketXLS ribbon. Every cell that calls a MarketXLS function will re-evaluate. The workbook does not require any manual data pasting because every data cell is a formula.

The Bottom Line

A stock screener in excel earns its keep when it does three things at once. It scores a universe the way you actually invest. It re-evaluates that scoring against alternate threshold sets without overwriting your inputs. And it turns the surviving names into something you can keep working on - sizing, watchlists, trend context, history charts - inside the same file. The workbook in this post does all three, and every data cell behind it is a live MarketXLS formula rather than a static number that goes stale the moment you save.

If you build daily research workflows around spreadsheets, see what else MarketXLS can do at marketxls.com or book a personalized demo to see the function library in action.

Nothing in this post is investment advice. The tickers used are illustrative examples of how the formulas behave. The screening thresholds, scoring formulas, and sizing math are educational defaults you should adjust to match your own process.