Generally, stock screeners let you set filters. A custom stock screener in Excel, however, lets you decide how those filters interact.
You might require positive free cash flow before a stock can even be considered. You might allow either a low P/E ratio or a high free cash flow yield to satisfy your valuation rule. You can give profitability more weight than growth, rank companies relative to one another, or use different thresholds for different investment strategies.
A screener can require positive free cash flow before a stock is even considered. It can also allow either a low P/E ratio or a high free cash flow yield to satisfy a valuation rule.
Profitability can be weighted more heavily than growth, companies can be ranked against one another, and different thresholds can be applied depending on the investment strategy.
That will be our layer of focus here. This guide assumes you already have a stock universe and the financial data you want to screen inside Excel.
New to spreadsheet screening? Start with our complete guide to building a Stock Screener in Excel.
Here, we will take an existing screener and build more advanced logic on top of it.
What Makes a Stock Screener Truly Custom?
Suppose you want to find companies that are:
- reasonably valued;
- profitable;
- generating cash;
- growing;
- and not carrying excessive debt.
A basic screener might turn those into five independent filters.
For example:
P/E < 18
ROIC > 15%
FCF > 0
Revenue Growth > 10%
Debt/Equity < 0.5That works, but it assumes every condition behaves in exactly the same way.
A more sophisticated investment process may instead say:
- positive free cash flow is mandatory;
- minimum profitability is mandatory;
- the stock can qualify as reasonably valued through either P/E or FCF yield;
- stronger profitability can compensate for somewhat higher leverage;
- quality should influence the final score more than growth;
- qualifying stocks should then be ranked against one another.
Now you have something much closer to an investment model.
The job of Excel is to translate that logic into formulas.
[Screenshot: Existing Excel screener containing several stocks and columns for P/E, FCF Yield, ROIC, Operating Margin, Revenue Growth, and Debt-to-Equity. No scores or screening results yet]
1. Separate Hard Filters From Soft Criteria
One of the most useful changes you can make to a custom stock screener is to stop treating every metric as equally negotiable.
Some conditions may be hard filters.
If the company fails one, it is excluded regardless of how attractive the other numbers look.
Other conditions may be soft criteria. They contribute to the final score without automatically disqualifying the stock.
Suppose your model uses:
| Factor | Treatment |
|---|---|
| Positive FCF | Mandatory |
| ROIC ≥ 8% | Mandatory |
| P/E | Scored |
| FCF Yield | Scored |
| Operating Margin | Scored |
| Revenue Growth | Scored |
| Debt-to-Equity | Scored |
If ROIC is in E2 and free cash flow is in G2, the initial eligibility rule could be:
=IF(AND(E2>=8%,G2>0),"ELIGIBLE","EXCLUDE")This creates a gate. Only companies that clear your minimum business-quality requirements move on to the scoring stage.
That solves an important problem with pure scoring models: a company should not necessarily be able to compensate for a serious weakness simply by accumulating points elsewhere.
A deeply indebted, cash-burning company, for example, may not belong in a conservative quality screen regardless of its revenue growth.
2. Use AND and OR to Express Real Investment Logic
Custom screening becomes much more useful when qualification does not depend on one rigid path.
Suppose your rule is:
The company must generate positive free cash flow and earn at least an 8% ROIC. It must also either trade below 18x earnings or have an FCF yield above 5%.
That can be expressed as:
=IF(
AND(
E2>=8%,
G2>0,
OR(C2<=18,D2>=5%)
),
"ELIGIBLE",
"EXCLUDE"
)The important part here is the nested OR.
The stock does not need to satisfy both valuation tests. Either one can establish that the stock deserves further consideration.
You can take this further.
Suppose you are willing to accept Debt-to-Equity above 0.5 if the company's ROIC is exceptional:
=IF(
AND(
G2>0,
E2>=8%,
OR(C2<=18,D2>=5%),
OR(H2<=0.5,E2>=20%)
),
"ELIGIBLE",
"EXCLUDE"
)Now the rule reflects a trade-off:
Prefer low leverage, but tolerate more leverage when capital efficiency is unusually strong.
That is difficult to represent with a series of independent filters. But in Excel, it is simple Boolean logic.
3. Build Category Scores Instead of One Giant Formula
Once a stock clears the hard filters, you can score it.
A common mistake here is to create one enormous formula containing every factor. We instead recommend separating the models into categories.
For example:
For example:
- Valuation
- Quality
- Growth
- Financial Strength
Each category receives its own score. You then combine those category scores into a final weighted result.
Suppose your spreadsheet contains:
| Column | Metric |
| C | P/E |
| D | FCF Yield |
| E | ROIC |
| F | Operating Margin |
| G | Revenue Growth |
| H | Debt-to-Equity |
Valuation Score
You could score P/E in bands:
=IF(C2<=12,100,
IF(C2<=18,75,
IF(C2<=25,50,25)))And FCF yield:
=IF(D2>=8%,100,
IF(D2>=5%,75,
IF(D2>=3%,50,25)))Then average the two:
=(PE_Score+FCF_Yield_Score)/2A stock does not simply become "cheap" or "expensive". It receives a valuation score.
If you want to go deeper on this input, our guide to free cash flow yield explains how the metric works and how investors can interpret it.
Quality Score
Do the same with ROIC:
=IF(E2>=20%,100,
IF(E2>=15%,75,
IF(E2>=10%,50,25)))And operating margin:
=IF(F2>=25%,100,
IF(F2>=15%,75,
IF(F2>=10%,50,25)))Then:
=(ROIC_Score+Margin_Score)/2You now have separate valuation and quality scores on the same 0-100 scale.
If ROIC plays a major role in your quality criteria, see our ROIC screener guide for a more focused screening workflow.
The exact thresholds above are examples; your own model should use assumptions appropriate to your investment strategy and the companies being compared.
4. Weight the Categories According to Your Strategy
Not every category needs to contribute equally.
Suppose you care most about business quality. Your model could use:
| Category | Weight |
| Quality | 35% |
| Valuation | 25% |
| Growth | 20% |
| Financial Strength | 20% |
If:
J2= Valuation ScoreK2= Quality ScoreL2= Growth ScoreM2= Financial Strength Score
the final composite score becomes:
=J2*25+K2*35%+L2*20%+M2*20
A company with:
- Valuation = 75
- Quality = 90
- Growth = 60
- Financial Strength = 80
would receive:
75 × 25% = 18.75
90 × 35% = 31.50
60 × 20% = 12.00
80 × 20% = 16.00
Composite Score = 78.25This is fundamentally different from giving one point for every condition.
You are telling Excel:
"Quality is more important to me than growth, and valuation is more important than growth but less important than quality."That weighting should reflect the investment strategy behind the screen.
[Screenshot: Excel screener showing separate Valuation, Quality, Growth, and Financial Strength scores, followed by a weighted Composite Score column]
5. Combine Hard Filters and Weighted Scoring
Now bring the two layers together. Suppose I2 contains the stock's eligibility result and N2 contains its weighted score.
Your composite formula could be:
=IF(I2="EXCLUDE","",J2*25%+K2*35%+L2*20%+M2*20%)Excluded companies receive no final score.
Eligible companies continue through the model.
Then classify the result:
=IF(
I2="EXCLUDE",
"EXCLUDE",
IF(
N2>=80,
"STRONG MATCH",
IF(N2>=65,"REVIEW","LOW MATCH")
)
)The output is no longer just Pass or Fail.
A company might be:
EXCLUDELOW MATCHREVIEWSTRONG MATCH
That gives you considerably more information about why a stock survives the screen and how closely it matches the strategy.
6. Move Your Thresholds Into an Assumptions Panel
Hard-coding every number directly into your formulas makes the model difficult to change.
For example:
=IF(C2<=18,75,50)contains an assumption – 18x earnings – inside the calculation.
Instead, create an assumptions panel. For example:
| Assumption | Cell | Value |
| Maximum preferred P/E | S2 | 18 |
| Minimum ROIC | S3 | 15% |
| Minimum FCF Yield | S4 | 5% |
| Maximum Debt/Equity | S5 | 0.50 |
| Minimum Revenue Growth | S6 | 10% |
Then reference those cells:
=IF(C2<=$S$2,75,50)or:
=IF(
AND(
E2>=$S$3,
D2>=$S$4,
H2<=$S$5
),
"PASS",
"FAIL"
)Change the P/E threshold from 18 to 20, and the entire workbook updates. Change your minimum ROIC from 15% to 18%, and every company is re-evaluated.
The screening engine stays the same; only the assumptions change.
7. Create Different Strategy Presets
Once the assumptions are separated from the formulas, the same model can support multiple investment styles.
For example:
| Factor | Value | Quality | Growth |
| Max P/E | 15 | 22 | 30 |
| Min ROIC | 10% | 18% | 12% |
| Min Revenue Growth | 5% | 5% | 20% |
| Max Debt/Equity | 0.75 | 0.50 | 1.00 |
| Quality Weight | 25% | 45% | 25% |
| Growth Weight | 15% | 15% | 40% |
Instead of maintaining three completely separate workbooks, you can change the active assumptions.
- A value screen might prioritize valuation and free cash flow.
- A quality screen might assign much more weight to ROIC, margins, and financial strength.
- A growth screen can place greater emphasis on revenue or earnings growth without sacrificing essential quality controls.
The stock data does not necessarily need to change. The decision rules do.
That is one of the biggest advantages of building the screening layer in Excel.
8. Use Relative Ranking When Fixed Thresholds Are Too Rigid
Thresholds are useful when you have a clear minimum requirement.
But sometimes, the more precise question is:
"Which companies are the strongest relative to the rest of my universe?"
Suppose you are screening 100 companies.
For P/E, where lower is preferable:
=RANK.EQ(C2,$C$2:$C$101,1)For ROIC, where higher is preferable:
=RANK.EQ(E2,$E$2:$E$101,0)For revenue growth:
=RANK.EQ(G2,$G$2:$G$101,0)For Debt-to-Equity:
=RANK.EQ(H2,$H$2:$H$101,1)You can then combine the factor ranks.
Suppose:
- P/E rank is in
J2 - ROIC rank is in
K2 - Revenue Growth rank is in
L2 - Debt rank is in
M2
A simple composite rank score would be:
= (J2 + K2 + L2 + M2) / 4
Lower is better because a rank of 1 represents the strongest result.
Then rank the composite score:
=RANK.EQ(N2,$N$2:$N$101,1)
This removes the need to decide that 18x P/E is acceptable while 18.1x is suddenly unacceptable.
Instead, companies are evaluated relative to their peers.
You can also combine both approaches:
- hard thresholds eliminate unsuitable stocks;
- relative ranking determines which remaining stocks deserve attention first.
That hybrid model is more useful than either method alone.
9. Adjust Screening Rules for Different Sectors
A single set of financial thresholds does not always make sense across every industry.
A Debt-to-Equity rule that works for a software company may not be appropriate for a bank.
Our guide to the financial leverage ratio goes deeper into how leverage can be measured and interpreted when analyzing companies.
Margins also differ substantially across business models.
One solution is to screen economically similar companies separately. Another is to make your assumptions conditional on sector.
For example, suppose:
B2contains the company's sector;S2contains your default maximum P/E.
You could use:
=IF(
B2="Technology",
30,
IF(
B2="Consumer Defensive",
20,
$S$2
)
)That formula generates a different valuation threshold depending on the sector.
For a larger model, you can keep the sector thresholds in a separate lookup table rather than nesting dozens of IF statements.
The principle is more important than the exact formula:
"If your screening rule should change depending on the type of business, encode that distinction into the model instead of pretending every company should pass the same test."
10. Handle Missing Data Before Calculating a Score
An advanced screener should also distinguish between:
'The company failed my rule' and 'I do not have enough information to evaluate the company'.
Suppose the six financial inputs are in C2:H2.
You can check whether all six contain numerical values:
=IF(COUNT(C2:H2)<6,"CHECK DATA","READY")Or integrate the check into the screening formula:
=IF(
COUNT(C2:H2)<6,
"CHECK DATA",
IF(
AND(E2>=8%,G2>0),
"ELIGIBLE",
"EXCLUDE"
)
)Now incomplete rows cannot quietly move through the model as if the missing value represented a genuine pass or fail.
Your output can explicitly distinguish between:
CHECK DATAEXCLUDEELIGIBLE
That becomes increasingly important as the stock universe grows.
11. Use LET to Make Complex Rules Easier to Audit
Once your formulas contain several conditions, cell references can become difficult to read.
Excel's LET function allows you to assign names to values inside the formula.
For example:
=LET(
pe,C2,
fcfYield,D2,
roic,E2,
debt,H2,
IF(
AND(
roic>=8%,
OR(pe<=18,fcfYield>=5%),
OR(debt<=0.5,roic>=20%)
),
"ELIGIBLE",
"EXCLUDE"
)
)The logic has not changed.
But:
roic>=8%is easier to audit than:
E2>=8%when you return to the workbook months later.
Readability is crucial for complex custom screeners. A formula you cannot confidently inspect is difficult to maintain.
12. Create a Dynamic Shortlist
Once Excel has classified and scored every stock, you can create a separate shortlist automatically.
Suppose:
A2:P101contains your full screener;Icontains eligibility;Ncontains composite score.
To return only eligible companies with scores of 80 or higher:
=FILTER(
A2:P101,
(I2:I101="ELIGIBLE")*(N2:N101>=80),
"No strong matches"
)Now the workbook can have two distinct layers:
Full Screening Model
Contains every company, metric, rule, score, and calculation.
Qualified Shortlist
Contains only the companies that currently meet your final requirements.
Change one assumption and the shortlist updates. You do not need to manually reapply several filters every time the strategy changes.
Once you have a shortlist, you can use our fundamental Excel stock analysis template to analyze the qualifying companies in greater depth.
Example: Build a Quality-at-a-Reasonable-Price Screener
Let's combine the ideas above into one complete model.
Suppose you want profitable companies with positive cash flow, reasonable valuations, healthy balance sheets, and at least some growth.
Step 1: Define the Hard Filters
A company must have:
- ROIC ≥ 8%
- positive free cash flow
Formula:
=IF(
AND(E2>=8%,G2>0),
"ELIGIBLE",
"EXCLUDE"
)These are your non-negotiables.
Step 2: Score Valuation
Use P/E and FCF yield.
For example:
P/E Score
=IF(C2<=12,100,
IF(C2<=18,75,
IF(C2<=25,50,25)))FCF Yield Score
=IF(D2>=8%,100,
IF(D2>=5%,75,
IF(D2>=3%,50,25)))Average them for the Valuation Score.
Step 3: Score Quality
Use ROIC and operating margin.
Assign each a score from 25 to 100 and average the two.
Step 4: Score Growth
For example:
=IF(Growth>=20%,100,
IF(Growth>=10%,75,
IF(Growth>=5%,50,25)))Step 5: Score Financial Strength
For Debt-to-Equity:
=IF(H2<=0.25,100,
IF(H2<=0.5,75,
IF(H2<=1,50,25)))
Step 6: Weight the Categories
Use:
- Quality: 35%
- Valuation: 25%
- Growth: 20%
- Financial Strength: 20%
Formula:
=IF(
I2="EXCLUDE",
"",
J2*25%+K2*35%+L2*20%+M2*20%
)Step 7: Classify the Result
=IF( I2="EXCLUDE", "EXCLUDE", IF( N2>=80, "STRONG MATCH", IF(N2>=65,"REVIEW","LOW MATCH") ) )
Step 8: Rank the Survivors
=IF(
N2="",
"",
RANK.EQ(N2,$N$2:$N$101,0)
)The complete workflow is now:
Financial data – hard filters – category scores – weights – composite score – classification – ranking
That is a custom stock screener. It does not merely ask whether P/E is below a number, but applies an investment framework.
Where Wisesheets Fits Into the Model
The screening logic above lives in Excel. The other half of the workflow is supplying the financial data the rules depend on.
With Wisesheets, fundamental metrics can be pulled into Excel using WISE, while market and price data can be retrieved using WISEPRICE.
For example:
=WISE(A2,"ROE","TTM")or:
=WISEPRICE(A2,"Price")Once the underlying metrics are connected to the spreadsheet, your custom formulas can exist on top of them.
So, the separation goes something like:
Wisesheets supplies the financial and market data. Excel applies your investment logic.
As the financial data updates, you can rerun the same model without rebuilding the screening framework from scratch.
Final Word: A Custom Screener Should Reflect the Investment Process
The point of an advanced Excel stock screener is to make the spreadsheet behave more like your actual investment process.
Some conditions can be mandatory, while others can contribute points. Some factors can be more important than others. Different investment strategies can use different assumptions. Different industries can use different thresholds.
And once the stocks clear those rules, ranking can show you which companies should be the first to get your attention.
And that is where Excel can turn out to be more useful than a collection of independent stock filters.
Now, you are not simply asking which stocks have a P/E ratio below 18. You are identifying which companies best fit the combination of valuation, quality, growth, and financial strength that you care about.
And because you control the formulas, thresholds, weights, and ranking system, the answer can be built around your rules rather than somebody else's preset screen.
Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.
After completing my BBA degree in Finance at the Schulich Program in Toronto, Canada. I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.
My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.
That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.
Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.
I'm excited for what's next and look forward to sharing my passion for finance and investing with others!
