Stock Screener Excel: Build a Powerful Custom Screener with MarketXLS

To build a stock screener in Excel, put tickers in column A, pull each metric into its own column with MarketXLS formulas (for example =PERatio(A7), =ReturnOnEquity(A7), =SalesOneYearGrowth(A7), =VolumeAverageThirtyDays(A7)), add a TRUE/FALSE column for each rule that compares the metric to a threshold cell, combine them with =AND(...), and list the survivors with FILTER and SORT. Because every rule is a cell, you can see exactly why each stock passed or failed and feed the results into your own models. This guide walks through that build and includes a 24-stock template.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

The workbook ships in two files. The sample workbook is pre-filled with representative values so you can study the layout and the screening math without any add-in installed. The template workbook is the same design with MarketXLS formulas wired into every data cell, so the screen recalculates when you press F9. Both are linked in the "Get the workbook" section near the end.

Stock screener Excel: what the dashboard produces

Instead of a fixed web filter, the dashboard runs a 24-stock large-cap universe through a stack of fundamental, technical, and liquidity rules, then flags exactly which criteria each name passes. Here is the kind of snapshot it produces under the balanced threshold set (including maximum P/E of 35, minimum revenue growth of 5%, minimum return on equity of 12%, a price-to-book ceiling, and a liquidity floor of one million shares a day).

The value is not just the pass or fail. It is the reason. Because every rule lives in a cell, you can see precisely why AAPL was rejected on its price-to-book multiple and why TSLA failed on three separate criteria at once. That auditability is the entire reason to screen in a spreadsheet rather than a black box.

Why build a stock screener in Excel?

Web-based screeners are convenient, but they lock you into a fixed set of criteria, a fixed display format, and a fixed export cadence. Excel removes all three constraints. You decide which metrics matter, how they are weighted, and what happens to the results downstream. That flexibility is especially valuable for investors who combine fundamental and technical criteria, or who want to rank candidates rather than simply pass or fail them.

The tradeoff is data. A blank Excel workbook has no market data. Historically, that gap forced people to download a static CSV, paste it in, and watch it go stale within the hour. MarketXLS closes that gap: each metric is a function call that refreshes on recalculation, not a frozen number.

There are three practical reasons a spreadsheet screen beats a hosted one for a serious investor:

• Transparency. Every threshold is a cell you can read and change. No hidden defaults, no proprietary scoring you cannot inspect.
• Integration. The passing list flows straight into your valuation model, position sizer, or watchlist without a copy-paste step that breaks every week.
• Ownership. You keep the logic. When your process evolves, you change a formula rather than waiting for a vendor to add a filter.

What MarketXLS adds to a native Excel screener

MarketXLS is an Excel add-in (Windows desktop, plus a Microsoft 365 add-in for Mac and Excel for the web) that adds 1,000+ market-data worksheet functions. Stock quotes are 15-minute delayed on the Standard plan and real-time on the Advanced and Business plans; some functions differ by platform (for example, SalesOneYearGrowth is listed for the Windows desktop add-in), so check /formulas. Instead of a static export, you call a function in a cell and the value updates on recalculation. For a screener, that means the raw material for every filter arrives as a formula:

• Price data through =QM_Last("AAPL"), plus volume and intraday statistics.
• Fundamental data such as =PERatio("AAPL"), =ReturnOnEquity("AAPL"), =SalesOneYearGrowth("AAPL"), =TotalDebtToEquity("AAPL"), and =FreeCashFlowPerShare("AAPL").
• Technical indicators including =SimpleMovingAverage("AAPL","50"), =SimpleMovingAverage("AAPL","200"), and =RSI("AAPL").
• Liquidity and risk context via =VolumeAverageThirtyDays("AAPL"), =Beta("AAPL"), and =MarketCapitalization("AAPL").

Because all of these values live in cells, you reference them in standard Excel formulas, IF, AND, OR, RANK, FILTER, SORT, and XLOOKUP, exactly as you would with any other spreadsheet data. MarketXLS supplies the raw material; Excel's formula engine does the screening logic. You can see the full function library at /formulas. If you prefer a ready-made web screener, MarketXLS also has one at /screener.

Setting up your screening workbook

A well-structured workbook is the foundation of a reliable screener. Before writing a single formula, spend five minutes on architecture. The template in this post uses seven sheets, but the core idea is to separate raw data from logic from output so debugging stays simple.

Separating the criteria sheet from the screening logic is the single most useful habit. When a result looks wrong, you check the data cell first, then the flag formula, then the threshold. You never hunt through a tangle of hard-coded numbers.

Building the universe. Start with a list of tickers in column A of the Screener sheet. This can be a broad index constituent list, a sector subset, or a custom watchlist. Keep one ticker per row with no blank rows. Every metric column to the right reads the ticker in column A, so replacing the list re-screens the whole universe automatically. To screen a larger universe, extend the rows and copy the formula columns down; the logic does not change.

Choosing and organizing your screening criteria

Before building formulas, decide which criteria define a qualifying stock. Mixing too many unrelated metrics produces a screener that passes nothing or passes everything. A focused set of five to ten criteria works better in practice. The template ships with a balanced default set you can tighten or relax on the Screening Criteria sheet.

Common fundamental criteria:

• Price-to-earnings ratio below a sector-adjusted ceiling
• Revenue growth above a minimum year-over-year rate
• Debt-to-equity ratio below a limit
• Return on equity above a quality floor
• Positive free cash flow per share for the trailing twelve months
• Operating margin above a profitability floor

Common technical and liquidity criteria:

• Price above the 50-day moving average (trend confirmation)
• Relative strength inside a defined band, rather than deeply overbought
• Average daily volume above a liquidity minimum

Combining the two. A hybrid screener requires all fundamental and liquidity criteria to pass as hard filters, then uses a composite score to rank the survivors. This narrows the universe with quality and value, then surfaces the strongest names within that subset. Document your thresholds on the Screening Criteria sheet with one value per row, label each clearly, and reference those cells in every formula rather than hard-coding numbers. When you want to relax or tighten a filter, you change one cell, not dozens of formulas.

Step-by-step: building the screener formula layer

Step 1 - Pull raw data with MarketXLS functions

On the Screener sheet, each metric column is a MarketXLS function that reads the ticker in column A. For the ticker in cell A7, the data row looks like this:

=QM_Last(A7)                       ' Last price
=PERatio(A7)                       ' Trailing P/E
=SalesOneYearGrowth(A7)            ' 1-year revenue growth
=TotalDebtToEquity(A7)             ' Debt-to-equity
=ReturnOnEquity(A7)                ' Return on equity
=FreeCashFlowPerShare(A7)          ' Trailing FCF per share
=OperatingMargin(A7)               ' Operating margin
=PricePerBook(A7)                  ' Price-to-book
=SimpleMovingAverage(A7,"50")      ' 50-day moving average
=VolumeAverageThirtyDays(A7)       ' 30-day average volume
=RSI(A7)                           ' 14-day relative strength

Formula documentation: QM_Last, PERatio, SalesOneYearGrowth, TotalDebtToEquity, ReturnOnEquity, FreeCashFlowPerShare, OperatingMargin, PricePerBook, SimpleMovingAverage, VolumeAverageThirtyDays, RSI

Copy each formula down through all rows in your universe. At this point the sheet is a live table of raw metrics with no filtering yet.

Step 2 - Create pass/fail columns

For each criterion, add a column that returns TRUE or FALSE by comparing the raw metric against the matching threshold on the Screening Criteria sheet. Using absolute references to the criteria cells keeps the logic stable when you copy the formula down:

=IF(E7="","",AND(E7>0,E7<='Screening Criteria'!$B$6))   ' P/E within ceiling
=F7>='Screening Criteria'!$B$7                           ' Revenue growth floor
=G7<='Screening Criteria'!$B$8                           ' Debt-to-equity limit
=H7>='Screening Criteria'!$B$9                           ' ROE floor
=I7>'Screening Criteria'!$B$10                           ' Positive free cash flow
=J7>='Screening Criteria'!$B$11                          ' Operating margin floor
=K7<='Screening Criteria'!$B$12                          ' Price-to-book ceiling
=D7>M7                                                    ' Price above 50-day MA

Step 3 - Create an overall pass column

Combine all the pass/fail columns with AND so a single column tells you at a glance whether a name clears every hard filter:

=AND(S7:AB7)

This ALL PASS column is the gate. The template shades it green when TRUE and red when FALSE, so qualifying names jump off the sheet.

Step 4 - Add a composite ranking score

For names that pass every hard filter, compute a relative score so you can rank the survivors instead of treating them as a flat list. The template blends four percentile ranks, return on equity, revenue growth, inverse P/E, and operating margin, into a 0 to 100 figure:

=IF(AC7=TRUE,ROUND(AVERAGE(
  PERCENTRANK($H$7:$H$30,H7)*100,
  PERCENTRANK($F$7:$F$30,F7)*100,
  (1-PERCENTRANK($E$7:$E$30,E7))*100,
  PERCENTRANK($J$7:$J$30,J7)*100),1),"")

Names that fail the hard filters receive a blank score and sink to the bottom when sorted. The score is a relative ranking inside your universe, not a price target or a recommendation.

Applying filters and ranking results

With pass/fail and score columns in place, the Ranked Results sheet uses Excel's dynamic array functions to surface only the qualifying tickers, already sorted. A single formula does the whole job:

=SORT(FILTER(CHOOSE({1,2,3,4,5,6,7},
  Screener!$A$7:$A$30, Screener!$B$7:$B$30, Screener!$C$7:$C$30,
  Screener!$D$7:$D$30, Screener!$E$7:$E$30, Screener!$H$7:$H$30,
  Screener!$AD$7:$AD$30),
  Screener!$AC$7:$AC$30=TRUE,
  "No stocks currently pass all criteria"),7,-1)

FILTER keeps only the rows where ALL PASS is TRUE, CHOOSE assembles the columns you want to display, and SORT orders them by the score column in descending order. The result is a live, auto-resizing table that shrinks and grows as your universe or thresholds change.

For older Excel versions. FILTER, SORT, and CHOOSE array behavior need Excel 365 or Excel 2021. If dynamic arrays are not available, apply an AutoFilter to the ALL PASS column on the Screener sheet, filter to TRUE, and copy the visible rows. The screening logic is identical; only the output mechanism changes.

Automating refreshes and keeping data current

MarketXLS data refreshes when Excel recalculates. You can trigger a recalculation manually with Ctrl+Alt+F9, or set automatic recalculation so the sheet updates as you work.

• Refresh before the open to capture overnight fundamental updates and pre-market price changes.
• Refresh during the session for intraday price and volume if your criteria include live price metrics.
• Avoid continuous auto-refresh on very large universes. Recalculating hundreds of live calls at once can slow Excel, so batch large lists or refresh on demand.
• Timestamp your refreshes by writing =NOW() to a dedicated cell and formatting it as a date-time, so you always know how current your results are.

Distinguish between data types when you interpret results. Fundamental metrics such as P/E or revenue growth update quarterly or annually, while price, volume, and RSI change intraday. A screener that mixes both will have some columns that sit still between earnings seasons and others that move by the minute.

Validating data quality before you act

Raw data from any provider can contain anomalies: stale values, corporate-action distortions, or missing entries for thinly traded securities. Build a validation layer before you trust a result.

1. Blank and error detection. Flag any metric cell that is empty or returns an error. A missing P/E often means negative earnings rather than absent data, so treat the two differently. Wrapping a filter in IF(cell="","",...) keeps blanks from silently passing.
2. Outlier detection. Use PERCENTRANK to spot metrics in the extreme top or bottom of the universe, then investigate before accepting them.
3. Liquidity guard. Names with very low average daily volume can show extreme metric values that are not tradable. The =VolumeAverageThirtyDays filter enforces a hard floor.
4. Cross-check the top names. Compare the MarketXLS last price for your top few results against a second reference before acting on them.
5. Period alignment. Confirm fundamental data is drawn from comparable fiscal periods. Comparing a December fiscal year-end company to a June year-end company on a trailing metric can distort rankings.

The template: what is inside

The download includes both editions of a seven-sheet workbook, built to the same design so you can move from studying to running with no relearning.

• Screener. The heart of the tool. A 24-name large-cap universe with eighteen live metric columns, ten pass/fail flags, an ALL PASS gate, and a composite score. Green and red shading on the gate and a heat scale on the score make the winners obvious.
• Screening Criteria. Ten yellow input cells for maximum P/E, minimum revenue growth, maximum debt-to-equity, minimum ROE, minimum free cash flow per share, minimum operating margin, maximum price-to-book, minimum average daily volume, and an RSI band. Change one cell and the whole universe re-screens.
• Scenario Analysis. Conservative, Balanced, and Aggressive presets with a live COUNTIFS count of how many names survive each, so you can see how sensitive your screen is before committing.
• Ranked Results. The live FILTER plus SORT output described above.
• Sector Comparison. Average P/E, ROE, and revenue growth by sector, plus a count of how many names in each sector pass, computed with AVERAGEIF and COUNTIFS.

Every sheet carries a "MarketXLS Functions Used" reference box, so you always know which formula powers which column. In the sample edition, each data cell shows its MarketXLS formula as a hover comment; in the template edition, that formula is live in the cell.

Get the workbook:

• - pre-filled representative values, opens with no add-in
• - every metric is a live formula that refreshes on F9

To wire the template to live data you need the MarketXLS add-in installed and signed in. You can compare plans on the pricing page or book a demo for a guided walkthrough.

Troubleshooting common issues

The screener returns no results. Your thresholds are too restrictive. On the Screening Criteria sheet, relax one filter at a time to find which one is eliminating every candidate. The Scenario Analysis sheet also shows how many names survive under looser presets.

Results include obvious junk tickers. Tighten the volume and price-to-book filters. Micro-cap and thinly traded names often pass fundamental screens on paper but are not liquid enough to act on.

Formulas return errors after you add tickers. Extend the formula columns and the score and flag ranges to cover the new rows. If you referenced a fixed range like rows 7 to 30, add tickers inside it or widen the ranges consistently.

Data appears stale. Force a full recalculation with Ctrl+Alt+F9. If values still do not update, confirm the MarketXLS add-in is connected and signed in.

The Ranked Results sheet shows a spill error. You are on an Excel version without dynamic arrays. Use AutoFilter on the ALL PASS column instead, as described above.

Frequently asked questions

What data does an Excel stock screener need to work?

At minimum it needs live prices and the fundamental and technical metrics you screen on. In this build, MarketXLS supplies all of them through worksheet functions like =QM_Last, =PERatio, =ReturnOnEquity, and =SimpleMovingAverage, so nothing is a manual paste. The screening logic itself, the pass/fail flags and the score, is native Excel and runs even in the sample file.

How many stocks can the screener handle?

Excel handles thousands of rows without issue. The practical limit is how many live data calls you make at once and how often you refresh. For a very large universe, split it into sector-based workbooks or refresh on demand rather than continuously, then consolidate the passing lists.

Can I screen on my own custom criteria?

Yes. Add a metric column with the relevant MarketXLS function, add a pass/fail column that compares it to a new threshold on the Screening Criteria sheet, and include that column in the AND gate. Because every rule is a cell, extending the screen is a matter of adding columns, not rebuilding the tool.

Does this work in Google Sheets?

The workbook architecture (separate sheets for the universe, criteria, screen, results, and comparison) translates directly to Google Sheets. MarketXLS has a Google Sheets add-on at the same pricing as the Excel product; a few function names and array behaviors differ between the two platforms.

Can an AI assistant run the screen instead of formulas?

Yes. MarketXLS exposes its financial-data tools to compatible AI assistants through a Model Context Protocol connector, so you can describe your criteria in plain language and hand the results to a spreadsheet for deeper analysis. The two workflows complement each other: conversational screening for a first pass, the spreadsheet for auditable, repeatable rules.

Is a screener suitable for day trading?

This build is best suited for end-of-day and swing-trading workflows where criteria are evaluated on daily or longer timeframes. Intraday screening needs faster refresh cycles and tighter latency than a spreadsheet-based approach typically provides.

The bottom line

A stock screener Excel workbook powered by MarketXLS data is transparent, customizable, and integrated with the rest of your analytical workflow rather than walled off in someone else's platform. Start with a focused set of criteria, validate your data before you trust it, and expand the screen incrementally as you gain confidence in its output. The result is a screening process that is genuinely yours: not a black box, but a clear, auditable system you understand from top to bottom. Remember that passing a filter is a starting point for research, not a recommendation, so this workbook is an educational framework, not investment advice. Validate every data point and consult a licensed professional before acting.

Built with MarketXLS - market data and financial functions for Excel. Visit marketxls.com to see the full add-in, or book a demo for a guided walkthrough.