Market Breadth Dashboard Excel: Track Advance-Decline, 200-Day MA, and Sector Strength (May 2026)

Market breadth dashboard Excel is what separates investors who follow the index headline from investors who read the market underneath the index. The S&P 500 can print a record close while half its members slide. It can grind lower while leadership quietly rotates underneath. The headline level is one number; breadth is the texture of the whole tape. A breadth dashboard in Excel turns that texture into a small set of KPIs you can scan in seconds: how many names are above their 200-day moving average, how many advanced today, how many sit near 52-week highs versus lows, where RSI is distributed across the watchlist, and which sectors are leading or lagging. This guide walks through every piece of that dashboard, the MarketXLS formulas that power it, and how to use the output without overreading any single signal.

Market Breadth Dashboard at a Glance

That snapshot is the headline. Everything else in the dashboard zooms in on a single dimension - the sector view, the trend ranking, the RSI distribution, the bullish-versus-bearish setup tagger. Used together they answer the question that pure index watching cannot: is this rally broad, or is it three stocks dragging 497 along for the ride?

Why Breadth Matters Right Now

Cap-weighted indices have spent the last several years being dominated by their largest members. When five or seven mega-cap names compose a fifth of the index, their individual performance overwhelms the median stock. The headline price level is a weighted average, not a participation count. Breadth indicators were invented precisely because the headline often lies about how many investors are actually winning or losing.

Market technicians have tracked breadth metrics since the 1930s for the same reason oncologists order blood panels - the index level is the patient's pulse, but breadth is the bloodwork. Some practical examples of why this matters:

• Narrowing leadership before a top. Most cyclical peaks see the index make a new high while the share of stocks above the 200-day moving average rolls over. Fewer names are doing the heavy lifting.
• Breadth thrust off a bottom. Recoveries from deep drawdowns are often signaled when the share of stocks above their 50-day MA flips from below 30 percent to above 80 percent in a short window.
• Sector rotation versus market trend. A market trend that is broad will see most sectors participating in similar measure. A market trend that is narrow will see one or two sectors carrying the rest.
• Bear market rallies. Counter-trend rallies in down markets typically show poor breadth - the index bounces but new-high counts stay low and new-low counts barely budge.

None of these patterns predict the future. They describe the current quality of the move. That is what makes them useful: they give you context for whatever the price action is doing.

The MarketXLS Approach to Building Breadth in Excel

Most published breadth indicators use the full universe of NYSE or NASDAQ-listed stocks. That data is harder to assemble in a spreadsheet. The pragmatic alternative is to build a representative watchlist - in the template, 40 large-cap S&P 500 names across all 11 GICS sectors - and compute breadth on that sample. The sample will not exactly match the wire-service A/D line, but it will track the same regime changes with the same directional signal.

Every metric in the dashboard is computed from five core MarketXLS functions:

=QM_Last("AAPL")                          // current live price
=SimpleMovingAverage("AAPL", 50)          // 50-day SMA
=SimpleMovingAverage("AAPL", 200)         // 200-day SMA
=FiftyTwoWeekHigh("AAPL")                 // trailing 52-week high
=FiftyTwoWeekLow("AAPL")                  // trailing 52-week low
=RSI("AAPL")                              // 14-day Relative Strength Index
=ChangeinPercent("AAPL")                  // daily change vs previous close
=StockReturnOneYear("AAPL")               // trailing one-year return
=Sector("AAPL")                           // GICS sector classification

From those building blocks you derive every breadth statistic the dashboard reports. The trick is in the aggregation, not the inputs.

Above the 200-Day Moving Average

The single most-watched breadth metric is "percent of stocks above their 200-day moving average." It is the cleanest binary trend filter for a name and an honest proxy for participation when averaged across an index.

For each ticker, the worksheet computes:

=IF(QM_Last("AAPL") > SimpleMovingAverage("AAPL", 200), "Y", "N")

Then aggregate:

=COUNTIF(H11:H50, "Y") / COUNT(H11:H50)

A reading above 60 percent generally indicates a healthy long-term uptrend regime. A reading below 40 percent indicates a meaningful downtrend or correction. Readings between 50 and 60 percent are mixed; readings above 80 percent occur during euphoric phases and have historically preceded short-term cool-offs more than continuations.

Advance-Decline Tally

Daily advancers and decliners are even simpler. For each name:

=ChangeinPercent("AAPL")

Then tally:

=COUNTIF(N11:N50, ">0")   // advancers
=COUNTIF(N11:N50, "<0")   // decliners

The classic divergence to watch is the index making a new high while advance-decline turns negative for multiple sessions. That means fewer names are participating in the leg up - a tell that the rally is consolidating in fewer hands.

52-Week High and Low Counts

A name within 5 percent of its trailing 52-week high is at the upper edge of its yearly range; a name within 5 percent of its 52-week low is at the bottom. These are the new-high and new-low counts that wire services have tracked since the 1920s.

Per ticker:

=(QM_Last("AAPL") - FiftyTwoWeekHigh("AAPL")) / FiftyTwoWeekHigh("AAPL")
=(QM_Last("AAPL") - FiftyTwoWeekLow("AAPL")) / FiftyTwoWeekLow("AAPL")

Then count the names where the first value is greater than or equal to -5 percent (within 5 percent of the high) and where the second value is less than or equal to 5 percent (within 5 percent of the low).

When the count of names at new highs is much greater than the count at new lows, the trend is broad. When new lows expand even as the index holds up, that is a textbook breadth deterioration warning.

RSI Distribution

The 14-day RSI is bounded between 0 and 100. Readings above 70 are conventionally overbought; below 30 are oversold. But the level of any one stock matters less than the distribution across the watchlist. A market with most names sitting in the 40-to-60 range is balanced. A market with a long tail of overbought names is stretched; a market with a long tail of oversold names is washed out.

The Distribution Analysis sheet bins the watchlist into five RSI buckets and counts how many names fall into each. The chart that renders alongside it makes a multi-week regime change obvious - as the histogram drifts right, the market is heating up; as it drifts left, it is cooling off.

What Is Inside the Template

The premium template ships with 10 sheets, every one tab-colored, frozen, and built to look like a dashboard product rather than a free spreadsheet handout. Here is the tour.

1. Cover

The first page is pure presentation. A deep navy background, gold subtitle, version number, "Data as of" date stamp, and a table of contents with every sheet listed. No data, no formulas - it sets the visual tone for the rest of the workbook and gives anyone you share the file with an instant overview.

2. How To Use

A six-step tutorial that walks through the watchlist, the inputs, the dashboard, the signals tab, the sector view, and the trend strength ranking. Every section is wrapped, formatted, and ends with a small "MarketXLS Functions Used" box so you can copy formulas straight into your own work.

3. Dashboard

The headline sheet. Six KPI tiles span the top row: percent above the 50-day moving average, percent above the 200-day moving average, advancers count, names near 52-week highs, names near 52-week lows, and a composite breadth score from 0 to 100. Below the tiles are two embedded horizontal bar charts - 1-year return by ticker and RSI by ticker - and at the bottom is the breadth screener: every ticker with price, both moving averages, the above/below MA flags (color-coded green or red), 52-week range, RSI, daily change with icon-set arrows, and 1-year return with a color-scale heatmap. Conditional formatting on RSI flips red as it pushes toward 80 and green as it falls toward 20.

4. Inputs and Controls

A dedicated sheet with yellow input cells for the four thresholds that drive the signal logic: bullish breadth threshold, oversold RSI level, overbought RSI level, and the 52-week range proximity percentage. Three additional dropdowns (Breadth Regime, Sector Focus, Signal Strictness) act as scenario toggles. The watchlist itself - 40 tickers with names and sector tags - sits below in editable form. Edit any ticker and the entire workbook re-prices.

5. Breadth Signals

Each ticker gets a setup tag - Bullish, Neutral, Bearish, or Bearish (Oversold) - derived from three factors: position relative to the 50/200-day MAs, RSI bucket, and where the price sits within the 52-week range. The "Why this signal" column explains the tag in plain language. Color fills on the signal column make it possible to scan the entire watchlist in two seconds and see how the setups cluster.

6. Sector Breadth

The sector-level breadth aggregator. For each of the 11 GICS sectors it reports the count of names, the percent above the 50-day MA, the percent above the 200-day MA, the day's advancers and decliners, the median RSI, and the median 1-year return. A red-amber-green color scale on the moving-average columns instantly shows which sectors are riding above trend and which are below. An embedded chart of "% of sector above 200-day MA" makes the leadership picture visual.

7. Trend Strength

Every name in the watchlist ranked by a composite trend score: 1-year return + (RSI - 50) + (% off 52-week high). The top names are the index's leaders; the bottom names are its laggards. Each row is bucketed into Leader, Strong, Lagging, or Laggard based on score thresholds. A color scale on the score column visualizes the spread; a data bar on the 1-year return column makes the leadership gap obvious.

8. Distribution Analysis

Two histogram tables and two embedded column charts. The first counts how many names sit in each of five RSI buckets (Deep Oversold, Oversold, Neutral, Strong, Overbought). The second counts how many names are at 52-week highs, in the upper half of their range, in the lower half, or at 52-week lows. Both charts let you compare today's distribution to a screenshot from a week or month ago - regime shifts show up as the histogram bars migrating left or right.

9. Methodology

A one-page explainer. Every formula is documented, every assumption stated, and every limitation called out. If you are sharing the template with a colleague or a client, the methodology page answers the questions before they get asked.

10. Glossary and Disclaimer

A 14-term glossary covering market breadth, advance-decline, SMA, golden cross, RSI, breadth thrust, sector rotation, beta, GICS, and the supporting concepts. The disclaimer block at the bottom states clearly that the dashboard is for educational use only.

Reading the Dashboard Like a Pro

A dashboard you cannot read is a dashboard you do not use. Here is the analyst's mental model for working through the layout in 60 seconds.

Start with the breadth score tile. That is your one-number summary. Above 65 is bullish, between 45 and 65 is mixed, below 45 is weak. If the score and the index price are both rising you have a confirmed trend; if the score is falling while the index rises you have a divergence to watch.

Compare the 50-day and 200-day percentages. When both are high (>60 percent) the market is in a clear uptrend. When the 50-day measure is high but the 200-day measure is still climbing, you are in a recovery phase. When both are below 50 percent and falling, the market is in or near a correction.

Check the advance-decline number against the daily close. If the S&P closed up but the watchlist had more decliners than advancers, today's tape was narrow.

Scan the new-high vs new-low count. A healthy uptrend has 4-to-10 times more names near highs than near lows. When that ratio compresses or flips, leadership is changing.

Glance at the sector breadth heatmap. If most sectors are green on both moving-average columns, the rally is broad. If two sectors are green and nine are red, leadership is narrow and dependent on those two sectors continuing to lead.

Look at the trend strength ranking. Are leaders concentrated in one sector? Are the laggards clustered in another? That answers the rotation question - which sectors are driving and which are dragging.

Finally, eyeball the RSI distribution. A balanced histogram with most names in the 40-60 bucket is healthy. A long tail of overbought names is a heat warning. A long tail of oversold names is a washout signal.

You can run that mental loop in under a minute once the dashboard is open. That is the point of dashboard-grade reporting - you read the picture, not the spreadsheet.

Sector Breadth: Where the Real Information Lives

The index-level breadth number is the headline, but the sector breakdown is where actionable nuance lives. When the headline reads "55 percent of names above 200-day MA" the obvious next question is which sectors are pulling the average up and which are dragging it down. Sector breadth answers exactly that.

A few patterns worth knowing:

• Defensive leadership in late cycle. When Consumer Staples, Healthcare, and Utilities all show high "% above 200-day MA" while Technology and Consumer Discretionary lag, the market is rotating defensively. Historically this pattern accompanies late-cycle conditions.
• Cyclical leadership in early cycle. Industrials, Materials, Financials, and Consumer Discretionary all showing high breadth simultaneously is a classic early-cycle signature.
• Single-sector dominance. If Information Technology is the only sector with breadth above 70 percent while every other sector is below 50, the rally is narrow and dependent on tech continuing to lead.
• Energy decoupling. Energy often runs its own cycle. High Energy breadth alongside weak breadth elsewhere can signal a commodity-driven episode rather than a broad equity move.

Build the sector view side-by-side with the index-level KPIs and you will catch rotations one or two weeks before they show up as a story in the financial press.

The Trend Strength Score, Explained

The composite trend score on the Trend Strength sheet is a deliberately simple, transparent factor: 1-year return + (RSI - 50) + (% off 52-week high). Each input has a clear role:

• The 1-year return rewards names that have outperformed the broad market over a 12-month window.
• (RSI - 50) is a centered momentum input - positive when RSI is bullish, negative when it is bearish.
• The percent off 52-week high is negative when a stock is below its highs, less negative as it approaches them. Names trading at or near 52-week highs get the smallest penalty.

The sum is a clean leadership rank. A name with a 40 percent 1-year return, an RSI of 65, and a price 2 percent off its 52-week high scores 40 + 15 - 2 = +53 - clearly a Leader. A name down 25 percent over the year, with an RSI of 35, sitting 35 percent off its 52-week high scores -25 - 15 - 35 = -75 - clearly a Laggard.

The thresholds we use to bucket the score (>30 Leader, >0 Strong, >-20 Lagging, otherwise Laggard) are intentionally tunable. Rewrite them in the Trend Strength sheet to match your own framework.

How To Adapt the Template To Your Universe

The default watchlist is 40 mega-cap and large-cap S&P 500 names spread across all 11 GICS sectors. That is a reasonable sample for a broad-market breadth read. But the template is built so you can swap the entire universe in a few minutes.

To switch to a sector ETF universe (XLF, XLE, XLU, etc.), to the Russell 2000 top 30, to the NASDAQ-100, or to your own portfolio:

1. On the Inputs sheet, replace each ticker in column B with the new ticker.
2. Update the company name and sector columns to match.
3. The Dashboard, Breadth Signals, Sector Breadth, Trend Strength, and Distribution sheets all reference the watchlist by row position, so they update automatically.
4. If you change the watchlist length, drag the formulas down or shorten them so they cover the full new range.

For a single-sector deep dive, use a watchlist of 15-20 names within that sector. The breadth readings then describe sector internals rather than market internals, but the framework is identical.

For a portfolio-level dashboard, paste in your own holdings. The sector breakdown then tells you where your portfolio is concentrated above and below trend - useful for sizing decisions and risk reviews.

MarketXLS Implementation Details

A few practical notes for getting clean reads from the template:

• Moving averages. SimpleMovingAverage requires daily history data. MarketXLS caches recent daily bars; the function returns instantly for liquid US-listed names. For thinly traded names or foreign listings, the first call may be slower as the cache populates.
• RSI parameter. RSI defaults to 14-day. Override with RSI("AAPL", 21) for a slower oscillator. The template uses the default; consider adjusting if you want a smoother distribution.
• 52-week stats. FiftyTwoWeekHigh and FiftyTwoWeekLow use trailing 252 trading days. They roll forward each day, so a stock that drops sharply will see its 52-week high age out of the window 252 sessions after the peak.
• Sector classification. Sector returns the GICS sector. For more granular subindustry breakdowns use Industry.
• Live recalculation. All formulas refresh when you press F9 or when MarketXLS auto-refresh is enabled. For a static snapshot, copy the data and paste-as-values into a separate sheet.

To explore the full set of MarketXLS technical indicator functions, see the MarketXLS features page and the in-Excel function reference.

Download the Templates

Download the templates:

• - Pre-filled with current snapshot data and formula comments on every cell so you can see exactly which MarketXLS function produces each value.
• - Live-updating formulas across all 10 sheets. Open in Excel with the MarketXLS add-in active and every metric recalculates with current market data.

Both files include the full premium design - cover page, KPI tile dashboard, two embedded charts, conditional formatting heatmaps, scenario toggle dropdowns, sector heatmap, trend ranking, RSI distribution, methodology, and glossary. Frozen panes on every sheet, tab colors set, gridlines hidden on Cover and Dashboard.

FAQ

What is market breadth in simple terms?

Market breadth measures how many stocks are participating in a market move, not just whether the index level is up or down. A market with broad breadth has many stocks rising together; a market with narrow breadth has a small number of large stocks dragging the index higher while most components stagnate or fall. Breadth indicators include percent above moving averages, advance-decline counts, new-highs versus new-lows, and RSI distributions.

How do I calculate percent of stocks above the 200-day moving average in Excel?

For each ticker, use IF(QM_Last(ticker) > SimpleMovingAverage(ticker, 200), "Y", "N") to flag whether the price is above its 200-day SMA. Then aggregate with COUNTIF(range, "Y") divided by COUNT(range) to get the percent. With the MarketXLS Excel add-in, both functions pull live data on every recalc.

What is the difference between the advance-decline line and breadth?

The advance-decline line is one specific breadth indicator - a running cumulative tally of (advancers - decliners) each day. "Market breadth" is the broader category of indicators that measure participation; it includes the A/D line, new-high counts, percent above moving averages, breadth thrusts, and RSI distributions. A breadth dashboard typically reports several of these side by side.

Can I use this dashboard for the Russell 2000 or NASDAQ-100 instead of the S&P 500?

Yes. The template is built so you can swap the 40-stock watchlist on the Inputs sheet with any universe. Replace the tickers, company names, and sector tags; every other sheet references the watchlist by row position and updates automatically. For the NASDAQ-100 use the top 40 by weight; for the Russell 2000 use a representative sector-balanced sample.

What is a breadth thrust signal?

A breadth thrust is a sudden, large increase in the share of stocks moving higher in a short window - typically when the percent of stocks above the 50-day moving average flips from below 30 percent to above 80 percent in two weeks or less. Historically these readings have marked the start of new uptrends after deep corrections. The dashboard does not flag a thrust automatically; you spot it by watching the % Above 50d MA tile day over day.

How often should I refresh the dashboard?

Daily is typical. Breadth indicators do not update intraday in a useful way - moving averages and RSI only meaningfully shift when a new daily close prints. Refreshing once after the US market close gives you the cleanest read for the next session's planning.

Is market breadth predictive of future returns?

Breadth describes participation in the current move. It is descriptive, not predictive. Strong breadth readings can persist for months before changing; weak breadth can persist for months too. The pattern most often cited as predictive is a divergence (index makes new high, breadth fails to confirm), but divergences can resolve in either direction - sometimes breadth catches up, sometimes price rolls over. Use breadth as context for risk management, not as a timing signal.

The Bottom Line

Market breadth turns the question "is the market up?" into the better question "is the move broad?" A dashboard that puts six breadth tiles, two charts, a sector heatmap, a trend ranking, and an RSI distribution on one page lets you answer that question in a single glance. The MarketXLS formulas behind the dashboard - SimpleMovingAverage, FiftyTwoWeekHigh, FiftyTwoWeekLow, RSI, ChangeinPercent, StockReturnOneYear - are the same five building blocks that professional desks use; the value is in how you aggregate and present them.

Drop the watchlist of your choice into the Inputs sheet. Adjust the bullish breadth threshold and RSI bands to your taste. Check the dashboard once a day after the close. Over time the regime patterns become obvious - and the next time the index pushes a new high on three stocks, you will see it before the headlines do.

Learn more at marketxls.com or book a demo to see the full MarketXLS feature set in action.