moving average excel: build an SMA and EMA crossover dashboard that updates itself

moving average excel searches almost always start in the same place: someone wants a 50-day and a 200-day line next to a watchlist, and they discover that the hard part is not the average. =AVERAGE() has existed since 1985. The hard part is getting 200 clean daily closes for 13 tickers into a sheet, and then keeping them current tomorrow. This guide skips that problem entirely. You will see how to return a moving average of any period, for any symbol, as a single live formula, how to build a crossover dashboard on top of it, and what two decades of data actually say about how often those crossovers fire. A finished workbook is at the bottom, in both a static and a live-formula version.

Where the watchlist stands right now

Here is the state of a 13-name large-cap watchlist as of the close on 2026-08-07. Every column in this table is one formula per cell in the template.

Eleven of the thirteen names close above their 200-day average, nine sit above their 50-day, and ten carry a golden-cross state. That gap between the two counts is the whole reason to build a dashboard rather than stare at one chart: the long-term picture and the short-term picture disagree on four names right now, and knowing which four is the useful part.

The Microsoft problem, or why moving averages lag

Look at the MSFT row again. Price is $504.38. The 50-day simple average is $407.10. The stock is trading 23.9% above its own 50-day line, its RSI is 78.8, and its 50/200 crossover state still reads Death, a label it earned 138 trading sessions ago and has not shed.

That is not a data error. MSFT closed at $390.54 on July 29, then gapped to $451.10 on July 30 and ran to $504 over the following six sessions. A 50-day simple average is the arithmetic mean of the last 50 closes, so a single week of new prices, however dramatic, can only move one seventh of the window. The 200-day average moves even more slowly. Both lines are still digesting a spring that no longer exists.

This is the single most important thing to internalise before building anything: a moving average is a description of the past, computed with a deliberate delay. It is not a forecast, and the crossover label is a summary of where two slow lines sit relative to each other, not a statement about what comes next. The MSFT row is a live demonstration that a "death cross" can sit on a stock that has just gone vertical.

The honest use of a dashboard like this is context. How far has price travelled from its own recent mean? Which names are stretched? Where do the fast and slow reads disagree? Those are answerable questions. "Should I buy this" is not one this spreadsheet answers, and nothing below should be read as a recommendation on any security.

The formula that removes the data problem

In plain Excel, a 50-day moving average requires 50 rows of price history per ticker. Thirteen tickers with 200 days of history is 2,600 cells that someone has to refresh. MarketXLS collapses that into one call:

=SimpleMovingAverage("AAPL", 50)

The second argument is the period, so the same function covers every lookback you need. Omit it and it defaults to 30 periods.

=SimpleMovingAverage("AAPL", 20)     ' 20-day SMA
=SimpleMovingAverage("AAPL", 50)     ' 50-day SMA
=SimpleMovingAverage("AAPL", 200)    ' 200-day SMA
=SimpleMovingAverage("SPY", 10)      ' 10-day SMA on the index ETF

The exponential version has identical syntax:

=ExponentialMovingAverage("AAPL", 20)
=ExponentialMovingAverage("AAPL", 12)    ' fast MACD leg
=ExponentialMovingAverage("AAPL", 26)    ' slow MACD leg

Both accept a third argument that backdates the calculation, which is how you reconstruct what an average read on a past date without storing any history:

=SimpleMovingAverage("AAPL", 50, DATE(2026,1,15))

Point the symbol and the period at cells and one formula fills an entire grid:

=SimpleMovingAverage($A10, B$9)

With tickers running down column A and periods across row 9, that single expression, dragged across and down, produces every average in the dashboard.

If you only ever want the two standard lines, there are fixed-period shorthands that take no period argument at all:

=Fifty_dayMovingAverage("AAPL")
=TwoHundred_dayMovingAverage("AAPL")

And if what you actually want is the distance rather than the level, MarketXLS returns that directly, as a decimal, so the cell just needs a percentage format:

=PercentChangeFrom50_dayMovingAverage("AAPL")
=PercentChangeFrom200_dayMovingAverage("AAPL")
=ChangeFrom200_dayMovingAverage("AAPL")     ' same distance, in dollars

SMA or EMA: the difference that actually matters

Both are averages. The difference is weighting, and it shows up precisely in situations like the MSFT gap.

The drop-off effect is the one most people never think about. A simple average can move sharply on a day when price barely changes, purely because a large number from 50 sessions ago just left the window. The exponential version has no window edge, so it never does this.

In practice the two rarely sit far apart. Over 20 years of SPY daily closes, the 50-day EMA sat an average of 0.65% away from the 50-day SMA. The choice matters at turning points and almost nowhere else. On the current watchlist you can see it on AAPL, where price at $311.77 sits above the 50-day SMA at $309.75 but below the 20-day EMA at $317.66. The fast exponential read has already rolled over while the slower simple read has not.

What crossovers actually do, measured over 20 years

The golden cross and death cross are the best-known moving average signals and among the most oversold. Rather than repeat the folklore, here is what SPY daily closes from 2006-08-07 to 2026-08-07 actually contain.

Three things fall out of that table.

A faster pair gives you more signals, not better ones. The 10/50 pair flipped 130 times in 20 years against 18 for the 50/200. Whether the extra 112 signals contained information or noise is exactly the question a backtest exists to answer, and the answer depends on costs, slippage and holding period. What is not in dispute is that you get 7x the trading activity.

The signal confirms after the move, by construction. Across those 18 SPY golden and death crosses, the median price change in the 60 sessions before the cross printed was +1.3%. The cross is arithmetic downstream of price, so it cannot lead it. Anyone marketing a crossover as an early warning is describing something the maths forbids.

"Price above the 200-day" is not a rare condition. SPY closed above its 200-day average on 73.5% of sessions over those 20 years. A filter that is true three days in four is a weak filter on its own, which is why the dashboard scores four conditions rather than one.

None of this makes moving averages useless. It makes them a way to describe trend state consistently across a large watchlist, which is a genuinely hard thing to do by eye and a genuinely easy thing to do in a spreadsheet.

Building the dashboard

The workbook is seven sheets. Here is how each one is constructed.

Main Dashboard

One row per ticker. Four yellow input cells at the top hold the fast period, slow period, long period and a stretch threshold, and every formula on the sheet references them, so changing 20 to 10 in one cell rebuilds the entire dashboard.

=SimpleMovingAverage($A10, $B$5)       ' fast SMA, period from the input cell
=SimpleMovingAverage($A10, $D$5)       ' slow SMA
=SimpleMovingAverage($A10, $F$5)       ' long SMA
=ExponentialMovingAverage($A10, $B$5)  ' fast EMA
=QM_Last($A10)                         ' price
=RelativeStrengthIndex($A10, 14)       ' stretch filter
=AverageTrueRange($A10, 14)            ' volatility unit, in dollars
=Sector($A10)                          ' GICS sector label

The trend score is deliberately crude and therefore readable. Four conditions, 25 points each:

=25*(C10>D10)+25*(C10>F10)+25*(D10>E10)+25*(G10>H10)

Price above the fast SMA, price above the 200-day, fast SMA above the slow SMA, fast EMA above the slow EMA. A score of 100 means every read agrees; 50 means the timeframes are fighting. MSFT scores 75 today despite its death cross label, because price has recovered above all three averages even though the 50-day has not yet climbed back over the 200-day.

Conditional formatting drives the stretch flag off the yellow threshold cell, so any name trading further from its fast average than you are comfortable with turns amber automatically.

Scenario Analysis

Pick one ticker in a yellow cell and the sheet pulls its current price and three averages via VLOOKUP, then walks price through seven shocks from -25% to +25%, showing which averages survive each one:

=$B$5*(1+B9)                    ' shocked price
=(C9>$D$5)+(C9>$F$5)+(C9>$H$5)  ' how many averages still hold

Below the table sit the break-even moves: how far price has to travel to touch each average from here. For MSFT that is a 19% decline just to reach the 50-day, which quantifies the stretch that the RSI of 78.8 only hints at.

Crossover Signals

Golden and death cross state per name, days since the last flip, the count of crosses over the past year, and an ATR-based trade frame. The state itself is a comparison, not a lookup:

=IF(B8>C8, "Golden", "Death")

The stop and target columns reference two yellow inputs, an ATR multiple and a reward-to-risk ratio:

='Main Dashboard'!C10 - $B$4*H8          ' stop, N ATRs below price
='Main Dashboard'!C10 + $B$4*H8*$D$4     ' target at the chosen ratio

The 20-year SPY study sits at the bottom of the same sheet so the whipsaw numbers are visible next to the signals rather than buried in a footnote. Names that flipped three or more times in the past year highlight amber. UNH has flipped three times; CAT, NVDA, GOOGL and JNJ have not flipped at all.

Position Sizing

Portfolio value, risk per position and a maximum position weight are the three inputs. Everything else is arithmetic:

=$B$4*$D$4                              ' risk budget in dollars
=ROUNDDOWN($B$5/D9, 0)                  ' shares implied by the ATR stop
=MIN(E9, ROUNDDOWN($D$5/B9, 0))         ' capped by the weight limit

The cap matters. On a volatility-only basis a low-ATR name like WMT would take an enormous share count, so the weight ceiling binds before the risk budget does. Totals at the bottom show gross exposure and aggregate risk if every stop were hit at once.

SMA vs EMA

The same watchlist with both average types side by side, plus a column that flags where they disagree. "Split" means price is above one and below the other, which is the only situation where the SMA-or-EMA choice changes your read.

Trend Matrix

A colour-coded grid of every ticker against all five averages. Green above, red below. Reading down a column gives breadth: how many names lead each average. Reading across a row gives alignment for a single name. This is where a narrow trend becomes obvious, because a market carried by three stocks looks very different by column than the index level suggests.

Verifying formulas before you build

Every formula in this post was checked against the MarketXLS function documentation before it went into the workbook, and that habit is worth copying. Invented function names are the most common reason a technical analysis sheet returns #NAME? on the first refresh. The functions used here are QM_Last, SimpleMovingAverage, ExponentialMovingAverage, Fifty_dayMovingAverage, TwoHundred_dayMovingAverage, PercentChangeFrom50_dayMovingAverage, PercentChangeFrom200_dayMovingAverage, ChangeFrom200_dayMovingAverage, RelativeStrengthIndex, AverageTrueRange, TechnicalIndicator, Beta, Sector, AverageDailyVolume and StockVolatilityOneYear.

For anything without a dedicated function, the generic call takes an indicator code:

=TechnicalIndicator("AAPL", "MACD", 12)
=TechnicalIndicator("AAPL", "ADX", 14)
=TechnicalIndicator("AAPL", "BBANDS", 20)

MACD is worth noting here because it is itself a moving average construct: the 12-day EMA minus the 26-day EMA. If you have ExponentialMovingAverage, you can rebuild it by hand and see exactly what it is measuring.

Download the templates

Download the templates:

• - Pre-filled with data as of 2026-08-07, with the MarketXLS formula that produces each value shown alongside it
• - Live formulas throughout, updates on refresh

Both files carry a "MarketXLS Functions Used in This Sheet" box on every sheet, so you always know which function produced which column. Swap the tickers in column A of the Main Dashboard and the whole workbook follows.

Frequently asked questions

How do I calculate a moving average in Excel without downloading price history?

Use a function that returns the average directly rather than the underlying prices. =SimpleMovingAverage("AAPL", 50) returns the 50-day simple moving average as a single value, with no history rows in the sheet at all. The same pattern works for any period and any symbol, and =ExponentialMovingAverage("AAPL", 20) does the exponential equivalent. If you prefer to work from raw closes, =QM_GetHistory("AAPL") returns a history block you can run =AVERAGE() over, but for a multi-ticker dashboard the single-cell version is far easier to maintain.

What is the difference between the 50-day and 200-day moving average?

Only the window length. The 50-day averages the last 50 closes and the 200-day averages the last 200, which makes the 200-day roughly four times slower to respond. The pair is used together because their crossings divide price history into long stretches: over 20 years of SPY closes the 50/200 pair crossed just 18 times, with a median regime of 203 trading days. The 50-day is often described as the medium-term trend and the 200-day as the long-term trend, but both are simply means of past prices at different speeds.

Should I use SMA or EMA for crossovers?

It depends on how much signal turnover you are willing to absorb. The EMA weights recent closes more heavily, so it turns earlier and produces more crossings; the SMA is smoother and produces fewer. Over 20 years of SPY data the 50-day EMA and 50-day SMA sat an average of 0.65% apart, so the two agree the overwhelming majority of the time. The choice only bites at turning points. The included template shows both side by side and flags every name where they currently disagree, which is the practical way to decide rather than arguing about it in the abstract.

Does a golden cross mean a stock is going up?

No. A golden cross is a label applied when a shorter moving average closes above a longer one, and both averages are computed from prices that have already happened. Across the 18 SPY golden and death crosses in the past 20 years, the median price move in the 60 sessions before the signal printed was +1.3%, which is to say the move preceded the signal rather than following it. Crossovers describe the current relationship between two lagging lines. They carry no forecasting guarantee, and the MSFT row in the table above shows a stock trading 23.9% above its 50-day average while still labelled a death cross.

How often do moving average crossovers actually happen?

Far less often than most people assume for slow pairs, and far more often than is comfortable for fast ones. Using SPY daily closes over 20 years: the 50/200 pair crossed 18 times, roughly once a year; the 20/200 pair crossed 24 times; the 20/50 pair crossed 102 times, about five times a year; and the 10/50 pair crossed 130 times. Median regime lengths run from 29 days for the fastest pair to 203 days for the slowest. Choosing a pair is really choosing how much trading activity you are signing up for.

Can I change the moving average periods in the template?

Yes, and that is the point of the yellow input cells. The fast, slow and long periods live in three cells on the Main Dashboard, and every moving average formula in the workbook references them rather than hard-coding a number. Change 50 to 34 and all seven sheets recalculate, including the scenario table, the crossover states and the position sizing. The stretch threshold is a fourth input that controls which names get flagged as extended from their fast average.

The bottom line

moving average excel work is straightforward once the data problem disappears. =SimpleMovingAverage() and =ExponentialMovingAverage() take a symbol and a period and return a live value, which means a 13-name, five-average dashboard is a drag-and-fill exercise rather than a data management project.

What the dashboard is worth depends entirely on how you read it. The 20-year record says crossovers are infrequent for slow pairs, noisy for fast ones, and confirmatory by construction. The current watchlist says price and the averages can disagree violently, as MSFT is disagreeing right now at 23.9% above its own 50-day line with a death cross still on the books. A spreadsheet that shows you both facts at once is more useful than one that reduces everything to a single green or red cell.

Build it, change the periods, and watch how much the picture moves. That sensitivity is the most honest lesson a moving average dashboard has to teach.

Explore the full function library at MarketXLS, or book a demo to see the technical indicator functions running against your own watchlist.

This article is educational analysis, not investment advice. Tickers appear as examples of how the formulas behave. Moving averages are lagging descriptions of past price and carry no predictive guarantee. Nothing here is a recommendation to buy or sell any security.