A defensive stock screener in Excel compares utilities, healthcare, and consumer staples names on five things: price versus the 50- and 200-day moving averages, dividend yield, beta, valuation (P/E and EPS), and momentum (RSI and 52-week range). With MarketXLS, each column is one formula, such as =Beta("JNJ"), =DividendYield("PG"), or =SimpleMovingAverage("NEE",200), and cyclical sector ETFs (XLE, XLI, XLY) sit alongside for comparison. This guide builds that screener for the April 2026 backdrop of elevated energy costs, sticky inflation, and a cautious Fed, and links a downloadable six-sheet workbook.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
Defensive sectors at a glance in April 2026
Here is the sector ETF setup in April 2026, before getting into workbook design.
| ETF | Sector | Recent price | 50-day average | 200-day average | Dividend yield | Why it matters now |
|---|---|---|---|---|---|---|
| XLU | Utilities | 46.96 | 45.59 | 43.26 | 2.59% | Defensive income and lower volatility profile |
| XLV | Healthcare | 147.31 | 151.89 | 145.01 | 1.71% | Earnings resilience and less direct energy sensitivity |
| XLP | Consumer Staples | 82.37 | 84.85 | 80.02 | 2.65% | Stable demand when growth expectations soften |
| XLE | Energy | 56.94 | 56.26 | 46.90 | 2.44% | Benefits from higher oil, but more cyclical and event-driven |
| XLI | Industrials | 171.52 | 169.08 | 156.76 | 1.27% | Sensitive to growth expectations and input costs |
| XLY | Consumer Discretionary | 112.89 | 113.90 | 115.80 | 0.82% | Often pressured when rates and inflation stay high |
That table does not tell you what to buy or sell. It shows why a defensive stock screener is useful right now. Utilities, healthcare, and staples are not identical. Some names are acting like income anchors. Some are simply less exposed to the growth scare narrative. Some are expensive enough that they need extra scrutiny. A structured Excel workflow helps separate those cases.
Why the defensive stock screener angle fits current market conditions
The April 2026 backdrop is not just about one headline. It is about overlapping pressure points.
First, energy prices remain a major market input. Higher oil and fuel costs can flow through to transportation, industrial margins, and broader inflation expectations. That keeps investors alert to sectors with more pricing stability or lower sensitivity to commodity spikes.
Second, the Fed backdrop still matters. Even when the market wants to talk about cuts, the practical question is whether inflation and energy costs let policymakers turn materially more supportive. A higher-for-longer rate view does not automatically mean defensive sectors win every week, but it does raise the value of businesses with steadier demand, clearer cash flow, and lower beta characteristics.
Third, earnings season changes the way investors process macro stories. When large companies begin reporting, broad narratives quickly turn into company-level questions. Which businesses can hold margins? Which names still support dividends comfortably? Which sectors keep trend support even when the tape gets noisy? A defensive screener helps answer those questions with a framework instead of intuition.
The goal of the screener is not prediction. It is a repeatable way to evaluate defensive ideas in an uncertain market.
What a good defensive stock screener should track
A weak defensive list is just a collection of familiar ticker symbols. A useful defensive stock screener should combine at least five layers of information.
1. Current price and trend
You need to know whether a stock or ETF is holding up technically. A so-called defensive name that has broken below long-term support may still deserve attention, but it should not be treated the same way as a stable leader.
In MarketXLS, the foundation is straightforward:
=QM_Last("NEE")
=SimpleMovingAverage("NEE",50)
=SimpleMovingAverage("NEE",200)
Formula documentation: QM_Last, SimpleMovingAverage
Those formulas let you compare the current price with both medium-term and long-term trend levels directly inside Excel.
2. Dividend yield
Defensive positioning often overlaps with income positioning, but not every high-yield stock is truly defensive. Yield should be one input, not the whole thesis.
=DividendYield("SO")
=DividendYield("PG")
=DividendYield("XLP")
Formula documentation: DividendYield
A screener can use this data to separate low-yield growth defensives from higher-yield income names.
3. Beta and volatility context
Defensive investors usually want lower market sensitivity. Beta is not perfect, but it is useful as a first-pass risk filter.
=Beta("JNJ")
=Beta("NEE")
=Beta("XLE")
Formula documentation: Beta
Lower beta does not guarantee downside protection, but it helps you compare how different names may behave when the broad market gets unstable.
4. Valuation and earnings support
Some defensive stocks become crowded and expensive. A good screener should surface that instead of assuming every utility or staples name is automatically attractive.
=PERatio("PG")
=EarningsPerShare("MRK")
=MarketCapitalization("JNJ")
Formula documentation: PERatio, EarningsPerShare, MarketCapitalization
This combination gives you a quick view of valuation, profitability support, and company size.
5. Momentum and range awareness
Defensive names can get overbought, especially when money crowds into the same safe-haven sectors.
=RelativeStrengthIndex("XLU",14)
=FiftyTwoWeekHigh("JNJ")
=FiftyTwoWeekLow("PFE")
Formula documentation: RelativeStrengthIndex, FiftyTwoWeekHigh, FiftyTwoWeekLow
If a defensive stock has already run close to its 52-week high and momentum is stretched, your interpretation of that setup should differ from a name that is steady but less extended.
The MarketXLS formulas used in this workbook
The workbook uses these core MarketXLS functions:
=QM_Last("TICKER")for current price snapshots=DividendYield("TICKER")for trailing yield=PERatio("TICKER")for trailing P/E ratio=SimpleMovingAverage("TICKER",50)for 50-day trend context=SimpleMovingAverage("TICKER",200)for 200-day trend context=RelativeStrengthIndex("TICKER",14)for RSI momentum=Beta("TICKER")for market sensitivity=Sector("TICKER")for sector tagging=Industry("TICKER")for business classification=MarketCapitalization("TICKER")for size context=EarningsPerShare("TICKER")for profitability context=FiftyTwoWeekHigh("TICKER")and=FiftyTwoWeekLow("TICKER")for annual range checks
That mix is enough to build a real defensive dashboard without drifting into made-up analytics or unsupported functions.
What is inside the Defensive Stock Screener Excel workbook
The downloadable package includes two files:
Download the templates:
- - Pre-filled with current data
- - Live-updating formulas
Both workbooks use the same six-sheet structure:
- How To Use
- Main Dashboard
- Scenario Analysis
- Strategy / Options
- Portfolio / Allocation
- Correlation / Comparison
The static file shows values as of its data date and lists which formula powers each field, so you can study the layout without the add-in.
The live template is the real operating file. It avoids static market data and keeps the core dashboard formula-driven so the workbook remains useful after publication.
Sheet-by-sheet walkthrough
1. How To Use
This sheet explains what each tab does and links directly to MarketXLS, Book a Demo, and your broader spreadsheet workflow. It is deliberately simple. The goal is to shorten the learning curve for advisors, analysts, and self-directed investors who want a repeatable process.
2. Main Dashboard
This is the heart of the workbook. It compares a watchlist of defensive names such as NextEra Energy, Southern Company, Duke Energy, Johnson & Johnson, Merck, Pfizer, Coca-Cola, PepsiCo, and Procter & Gamble. It also includes cyclical reference names so the user can keep context.
The dashboard tracks:
- sector and industry
- current price
- dividend yield
- P/E ratio
- 50-day and 200-day moving averages
- RSI
- beta
- market capitalization
- EPS
- 52-week high and low
- a simple defensive score
That last field is intentionally transparent. It is not meant to be a black-box rating. It simply rewards characteristics often associated with defensive behavior, such as stable yield, lower beta, positive trend support, and price not being too stretched relative to its annual range.
3. Scenario Analysis
This sheet is important because defensive investing is often context-driven. In April 2026, one investor may care most about an oil shock that keeps inflation firm. Another may care more about a higher-for-longer Fed. Another may worry about a growth scare.
The workbook includes input cells for:
- portfolio value
- base defensive weight
- oil shock severity
- Fed caution score
- target income need
Those yellow cells flow into scenario tables for soft landing, higher-for-longer Fed, oil shock persists, growth scare, and earnings resilience cases. That does not create a forecast. It helps you organize how you might think about defensive tilts under different macro conditions.
4. Strategy / Options
This section is educational, not promotional. The point is not that every defensive investor should use options. The point is that some users want a structured place to compare covered call reviews, collar planning, or simple watchlist monitoring.
For example, an income-focused investor reviewing Southern Company might want to monitor:
=QM_Last("SO")
=DividendYield("SO")
=RelativeStrengthIndex("SO",14)
Formula documentation: QM_Last, DividendYield, RelativeStrengthIndex
A holder of Johnson & Johnson who wants extra downside awareness during a volatile week could focus on current price, beta, and 52-week range context. The template frames these as research workflows, not recommendations.
5. Portfolio / Allocation
This tab translates the watchlist into position-sizing discipline. A defensive thesis without sizing rules can turn into random accumulation. The allocation sheet links target weights to a total portfolio input and estimates share counts using live prices in the formula version.
That gives users a cleaner answer to practical questions like:
- If I want a 10% utility allocation, how many shares does that imply at current prices?
- If I want healthcare and staples to carry most of the defensive sleeve, what does that look like in dollar terms?
- How much income exposure am I adding when I tilt toward higher-yield names?
6. Correlation / Comparison
The final tab puts defensive sectors side by side with cyclical comparison sectors. Utilities, healthcare, and staples are shown next to energy, industrials, and consumer discretionary so the user can compare price, valuation, beta, RSI, and annual range context without flipping between pages.
This matters because defensive investing only makes sense relative to the alternatives. If a user cannot compare XLU with XLE, or XLP with XLY, the screener is missing half of its value.
How to build your own defensive stock screener in Excel
If you want to customize the workbook beyond the download, here is a clean workflow.
Step 1. Start with your defensive universe
Pick the names you actually care about. For many users, that will include a mix of:
- utilities
- healthcare
- consumer staples
- dividend-focused large caps
- a few cyclical benchmarks for comparison
The workbook published here uses a blended watchlist so users can compare individual stocks and sector ETFs in the same file.
Step 2. Pull live price, yield, and trend data
The first formulas to add are usually price, yield, and moving averages:
=QM_Last("PG")
=DividendYield("PG")
=SimpleMovingAverage("PG",50)
=SimpleMovingAverage("PG",200)
Formula documentation: QM_Last, DividendYield, SimpleMovingAverage
That creates a strong base for interpreting whether a stock is steady, weakening, or extended.
Step 3. Add volatility and valuation filters
Once the price framework is in place, layer in beta, P/E, and EPS.
=Beta("MRK")
=PERatio("MRK")
=EarningsPerShare("MRK")
Formula documentation: Beta, PERatio, EarningsPerShare
This helps you distinguish between a stable business at a reasonable valuation and a defensive favorite that may already be richly priced.
Step 4. Add momentum context
RSI and annual range metrics help you avoid treating all defensive setups the same.
=RelativeStrengthIndex("KO",14)
=FiftyTwoWeekHigh("KO")
=FiftyTwoWeekLow("KO")
Formula documentation: RelativeStrengthIndex, FiftyTwoWeekHigh, FiftyTwoWeekLow
A stock trading near its highs with a stretched RSI tells a different story from one that is quietly holding trend support.
Step 5. Score only what you understand
This point matters more than people think. Excel makes it easy to build large scoring systems. That does not make them useful.
A good defensive score should stay readable. In the template, the score rewards a mix of yield, lower beta, reasonable momentum, and positive trend status. If you want to change the weights, you can. Just make sure the logic still matches the market question you are trying to answer.
Who uses a defensive stock screener
Advisors need a way to explain why a portfolio is tilting toward steadier sectors without sounding purely reactive. A workbook helps because it creates visible criteria.
Self-directed investors need a way to compare familiar defensive names without relying on scattered tabs, finance portals, or stale spreadsheets. A workbook helps because it centralizes the process.
Analysts need to pressure-test whether defensive leadership is broad, selective, or fading. A workbook helps because it tracks trend, valuation, and range data in one place.
MarketXLS puts the data inside Excel, so the screener lives in the same spreadsheets advisors and analysts already use. If you want to explore more of the platform, the main site, pricing page, and book a demo page are the best next stops.
What this workbook does well, and what it does not do
It is useful to be explicit here.
What it does well
- organizes defensive sectors for a timely macro theme
- keeps core market data formula-driven in Excel
- compares defensive names against cyclical benchmarks
- supports scenario planning without forcing a forecast
- gives users a concrete downloadable template instead of theory only
What it does not do
- predict the Fed
- guarantee downside protection
- replace full fundamental research
- tell you what to buy or sell
- act as investment advice
This is an educational template. It is designed to support research and portfolio organization, not to produce certainty.
FAQ
What is a defensive stock screener in Excel?
A defensive stock screener in Excel is a spreadsheet workflow that helps you compare stocks or ETFs often associated with lower volatility, steadier demand, or stronger income characteristics. In this template, the focus is on utilities, healthcare, and consumer staples during April 2026 macro uncertainty.
Why use MarketXLS instead of a static spreadsheet?
A static spreadsheet becomes stale quickly, especially during earnings season or macro-driven volatility. MarketXLS keeps the key cells connected to formulas for price, yield, valuation, beta, and trend, which refresh when Excel recalculates (prices are 15-minute delayed on the Standard plan).
Which MarketXLS formulas are most useful for defensive analysis?
The core formulas in this workbook are QM_Last, DividendYield, PERatio, SimpleMovingAverage, RelativeStrengthIndex, Beta, Sector, Industry, MarketCapitalization, EarningsPerShare, FiftyTwoWeekHigh, and FiftyTwoWeekLow.
Can I use this template for dividend stocks only?
Yes. You can narrow the watchlist to dividend-focused names and keep the same dashboard structure. The yield, beta, moving average, and valuation columns still work well for that use case.
Is this workbook suitable for financial advisors?
Yes, especially if you want a repeatable way to discuss sector tilts, income exposure, and risk context with clients or internal teams. It is also useful for self-directed investors who want a structured spreadsheet instead of a loose watchlist.
Does the template recommend any specific stocks?
No. The workbook is educational and analytical. It uses real tickers as examples of how to organize live data in Excel, but it does not make recommendations or provide investment advice.
The bottom line
In April 2026 investors are weighing higher energy costs, inflation pressure, a cautious Fed, and the start of earnings season at once. A defensive stock screener in Excel keeps that analysis organized: the same yield, beta, trend, and valuation columns for every name, updated by formula.
If you want a ready-made file, download the templates above and start with the dashboard. If you want a broader Excel-native market research workflow, visit MarketXLS or book a demo. If you want to compare this idea with another timely sector workflow, the recent Sector Rotation Model Excel post is a useful companion read.