Stock analysis in Excel is one of those workflows that every self-directed investor, equity analyst, and financial advisor reinvents from scratch at least three times in their career. The first version is a sheet of typed prices. The second version adds a screenshot of a PE ratio from a financial portal. The third version, if you stick with it long enough, finally becomes a workbook where every data cell is a formula, every input changes the answer everywhere downstream, and a single ticker change rebuilds the entire view in a couple of seconds. This guide walks through what that third version looks like, why each sheet earns its place in a complete stock analysis workflow, which MarketXLS formulas power the live cells, and how the included template lets you run that workflow on any US-listed ticker without typing a number twice.
Stock Analysis in Excel: Headline Snapshot
The sample workbook is a complete single-stock analysis using AAPL as the subject ticker. It includes six sheets, twenty plus live data cells on the Main Dashboard alone, fair-value scenarios, a margin scorecard, a technical signal panel, and an eight-stock peer table. Every figure below comes from the static sample dated May 13, 2026, and is replaced by a live MarketXLS formula in the template file.
| Section | What It Answers |
|---|---|
| Main Dashboard | What does this stock do, what is it worth, what are its key multiples? |
| Valuation Analysis | Is the stock cheap or expensive vs sector medians and against a P/E anchored fair value? |
| Financial Health | Is the underlying business profitable, well-funded, and cash-generative? |
| Technical Analysis | Where is the stock in its trend, momentum, and 52-week range? |
| Peer Comparison | How does the stock stack up against a chosen peer group on multiples and returns? |
| How To Use | Workflow, formula reference, and disclosures for opening users |
Every cell on every sheet is either an input you type or a function that pulls from a live data source. There is no place where you have to remember to update a number by hand.
Stock Analysis in Excel: Why a Workbook Beats a Web Tab
Stock research is a layered problem. You need a snapshot before you start, fundamentals before you size a position, technicals before you choose an entry, peers before you decide whether the multiple is reasonable, and a place to log your conclusion before you forget the thread you were following. Web tools are useful for any one of those layers in isolation. They are weaker when you want to hold all five layers in one screen and rerun the entire view against a new ticker in three seconds.
Stock analysis in Excel solves that. One ticker drives one workbook. Each sheet is one lens. Every cell is auditable. You can extend any column, reference any cell from any sheet, plug in your own peer set, change a hurdle rate, and watch the rest of the workbook respond. And because the workbook is a file rather than a session, you keep the analysis you did six months ago instead of refreshing the page and losing it.
The MarketXLS layer is what turns a static spreadsheet into a living analysis. Functions like =QM_Last("AAPL"), =PERatio("AAPL"), =Beta("AAPL"), =ReturnOnEquity("AAPL"), =OperatingMargin("AAPL"), =SimpleMovingAverage("AAPL","50"), and =DividendYield("AAPL") resolve in Excel exactly like =SUM does. The values are current the next time you press F9.
Stock Analysis in Excel: The Six-Sheet Structure
Each sheet answers a different question. The flow runs from "what is this stock" to "how does it compare to its peers."
| Sheet | Question it answers |
|---|---|
| How To Use | What is in this workbook and how do I drive it? |
| Main Dashboard | Snapshot of price, multiples, margins, and income for the subject stock |
| Valuation Analysis | Where do the multiples sit vs sector medians, and what is the P/E anchored fair value? |
| Financial Health | Is the business profitable, well-capitalized, and cash-generative? |
| Technical Analysis | Where is the stock in trend, momentum, and 52-week context? |
| Peer Comparison | How does the subject stock compare to a chosen peer set? |
At the bottom of every sheet there is a "MarketXLS Functions Used in This Sheet" reference box listing the exact functions referenced on that sheet. That way a first-time reader sees what to look up in the function reference without hunting through cells.
Main Dashboard: The Snapshot
The Main Dashboard is the screen you open first. The yellow input cell at the top holds the subject ticker. Below that are three more inputs you can edit (hurdle rate of return, holding period, position size dollars) and below those a tall column of metrics that recompute as soon as the ticker changes.
A short selection of the live cells:
=Name(B4) -> issuer name from ticker
=Sector(B4) -> GICS sector classification
=Industry(B4) -> GICS sub-industry
=QM_Last(B4) -> most recent traded price
=MarketCapitalization(B4) -> equity value in dollars
=PERatio(B4) -> trailing 12-month earnings multiple
=EarningsPerShare(B4) -> trailing 12-month EPS
=Revenue(B4) -> trailing 12-month revenue
=DividendYield(B4) -> annual dividend / current price
=DividendPerShare(B4) -> annual dividend per share
=OperatingMargin(B4) -> operating income / revenue
=ReturnOnEquity(B4) -> net income / shareholder equity
=TotalDebtToEquity(B4) -> total debt / equity book value
=Beta(B4) -> beta vs broad market
=CashFlowPerShare(B4) -> cash flow per share
=SimpleMovingAverage(B4,"50") -> 50-day moving average
=SimpleMovingAverage(B4,"200") -> 200-day moving average
=RSI(B4) -> Relative Strength Index (14)
=FiftyTwoWeekHigh(B4) -> 52-week price high
=FiftyTwoWeekLow(B4) -> 52-week price low
Three design choices are worth calling out:
- The ticker is a single yellow cell. Every other cell on this sheet references that one cell. Change AAPL to MSFT and the entire dashboard rebuilds. Change it to a thinly traded small cap and the dashboard rebuilds again, showing wider ranges and a different margin profile.
- The Formula column is the most useful column for new users. Each row carries the exact MarketXLS function being called next to the live cell. You read across the row and learn what each function does without leaving the dashboard.
- Educational notes in the rightmost column describe what each metric measures and what a higher or lower value typically implies. The sheet teaches as it computes.
When you build a position memo or one-pager, the Main Dashboard is the source of truth. The dollar figures, multiples, and margin readings on every later sheet trace back to its inputs.
Valuation Analysis: Multiples, Fair-Value Bands, and a DDM Sanity Check
The Valuation Analysis sheet starts by asking one question: is the subject stock trading at a premium, discount, or in line with a benchmark? It uses three multiples for that comparison.
=PERatio(B2) -> trailing P/E
=B3/CashFlowPerShare(B2) -> price to cash flow
B3/(Revenue per share) -> price to sales (derived)
Each row pairs the subject multiple with a sector median in a yellow input cell. The user supplies the median based on whatever benchmark they prefer (industry sub-group, equal-weighted peer set, broader sector). The variance column computes premium or discount, with red highlighting for premium and green for discount. The reading column writes out "Premium", "Discount", or "In line" so the result is readable at a glance.
Below the multiples panel sits a three-scenario fair-value table.
Bear: Sector Median P/E x EPS -> implied price under conservative re-rating
Base: Subject P/E x EPS -> implied price at current multiple
Bull: Subject P/E x 1.20 x EPS -> implied price under 20% re-rating
The "vs Current" column tells you where each implied price lands relative to today's quote. The bear case is the conservative anchor. The base case is what the stock prints today. The bull case captures upside if the multiple expands by 20%, which is a reasonable shorthand for "the market warms up to the story." None of those scenarios is a target or a forecast. They are educational anchors that show how sensitive the implied price is to a small change in the multiple.
The Dividend Discount Sanity Check at the bottom of the sheet runs a Gordon growth model.
DDM Value = DividendPerShare * (1 + g) / (Ke - g)
Ke is the required return on equity, g is the assumed perpetual dividend growth rate. Both are yellow input cells. The output is the per-share value the model implies, plus a "vs Current Price" cell that says whether the model agrees with the market. The DDM is a sanity check, not a primary tool: for high-growth or non-dividend names it returns numbers that should not be taken seriously. For mature dividend payers like consumer staples or utilities, it sits in a reasonable corridor.
The point of the sheet is to walk a reader through the same three steps a sell-side analyst would: compare multiples, sketch fair-value bands, sanity-check against a discount model. Each step has a number you can change and a result you can defend.
Financial Health: Margins, Returns, Leverage, and Cash Flow
The Financial Health sheet asks whether the business behind the ticker is well run. Six rows cover the most useful metrics.
=OperatingMargin(B2) -> operating income / revenue
=ReturnOnEquity(B2) -> net income / equity
=TotalDebtToEquity(B2) -> total debt / equity book value
=CashFlowPerShare(B2) -> cash flow per share
=EarningsPerShare(B2) -> trailing earnings per share
=Revenue(B2) -> trailing twelve-month revenue
For the AAPL sample, the Quality Scorecard at the bottom counts eight checkmark conditions: gross margin above 40%, operating margin above 20%, net margin above 15%, ROE above 15%, ROA above 7%, debt-to-equity under 1.5, current ratio above 1.0, and FCF per share above $4. AAPL clears seven of eight in the sample data (current ratio sits just under 1.0 for the snapshot, which is normal for working-capital-light businesses with large supplier financing).
The scorecard is not a buy signal. It is a reading on the underlying business quality. A high score means the business prints durable margins and returns; it says nothing about whether the price you would pay for the stock is reasonable. That is what the Valuation Analysis sheet handles. The two sheets work as a pair: high-quality businesses at reasonable multiples are the kind of names a long-term portfolio is built around, and the workbook helps you spot when those two conditions are both true.
The educational notes on each row explain what the metric measures, why higher or lower matters, and where the typical thresholds sit for "strong", "average", and "weak". Read top to bottom and you have a short course in fundamental analysis.
Technical Analysis: Trend, Momentum, and 52-Week Context
The Technical Analysis sheet rounds out the picture with eight indicators that summarize where the stock is in its own price history.
=QM_Last(B2) -> last price
=SimpleMovingAverage(B2,"50") -> 50-day SMA
=SimpleMovingAverage(B2,"200") -> 200-day SMA
=RSI(B2) -> 14-day RSI
=FiftyTwoWeekHigh(B2) -> 52-week high
=FiftyTwoWeekLow(B2) -> 52-week low
=QM_Last(B2)/FiftyTwoWeekHigh(B2)-1 -> distance to 52-week high (derived)
=QM_GetHistory(B2) -> full price history (array)
Three rows have explicit Reading cells:
- 50-Day SMA Reading. "Above" means the last price is above the 50-day moving average; "Below" means the opposite. Above the 50 DMA is the short-term up-trend reading.
- 200-Day SMA Reading. Same logic against the 200 DMA. Above the 200 is the long-term up-trend reading.
- RSI Reading. "Overbought" above 70, "Oversold" below 30, "Neutral" in between. RSI alone is not a signal, but in combination with the moving averages it adds useful context.
The Signal Summary at the bottom collapses those readings to a single short-term trend, long-term trend, and momentum label. That summary is what you would write in a one-line technical comment in a memo.
The =QM_GetHistory(B2) formula is the most powerful cell on the sheet. It returns the full historical OHLC array for the subject ticker. You can lift that cell into a separate sheet and build any chart, drawdown table, or moving-average crossover backtest you want. The technical sheet itself is a summary; the history function is the door to deeper work.
Peer Comparison: One Subject, Eight Peers, Ten Columns
Stock analysis without peer context is incomplete. A P/E of 32 is expensive for a utility and cheap for a hyperscaler. The Peer Comparison sheet builds out that context.
The structure is a ten-column table. The first row is the subject stock (highlighted yellow) and the next seven rows are peers chosen by the user. Each column is a live MarketXLS formula:
=Name(A4) -> issuer name
=QM_Last(A4) -> last price
=MarketCapitalization(A4) -> market cap
=PERatio(A4) -> P/E ratio
=ReturnOnEquity(A4) -> return on equity
=OperatingMargin(A4) -> operating margin
=DividendYield(A4) -> dividend yield
=Beta(A4) -> beta vs broad market
=Industry(A4) -> industry classification
A "PEER AVG" row at the bottom averages each numeric column with =AVERAGE() so you can compare the subject ticker against the group instantly.
The peer set is editable. The sample workbook ships with a broad consumer technology peer set around AAPL (MSFT, GOOGL, AMZN, META, NVDA, SONY, HPQ). For your own analysis you would tighten the peer set: AAPL plus four hardware peers, or AAPL plus four hyperscaler peers, depending on which lens you care about. The yellow input cells make that swap one-keystroke per peer.
A meaningful peer comparison is the difference between "the stock looks expensive" and "the stock looks expensive on P/E but is the highest-margin business in its peer set with the highest ROE." Those are different conclusions, and the workbook lets you arrive at the second one in less than a minute.
Stock Analysis in Excel: Building It From Scratch
If you would rather construct the workbook yourself than download the template, the high-level recipe is short.
Step 1. Set up the Main Dashboard. One yellow input cell for the ticker at the top. A column of metrics down the left, a column for the live formula, and a column for the educational note. Use =Name(B4), =Sector(B4), =QM_Last(B4), =MarketCapitalization(B4), =PERatio(B4), =EarningsPerShare(B4), =Revenue(B4), =DividendYield(B4), =OperatingMargin(B4), =ReturnOnEquity(B4), =TotalDebtToEquity(B4), =Beta(B4), =SimpleMovingAverage(B4,"50"), =SimpleMovingAverage(B4,"200"), =RSI(B4), =FiftyTwoWeekHigh(B4), =FiftyTwoWeekLow(B4).
Step 2. Build the Valuation Analysis sheet. Pull the live multiple in column B, take a yellow user input for the sector median in column C, compute premium/discount in column D, and write a reading in column E. Add a fair-value table that multiplies a multiple by EPS to produce implied prices for bear, base, and bull cases. Add a Gordon growth model with two yellow inputs (Ke and g).
Step 3. Build the Financial Health sheet. One row per metric, live formula for the value, hard-coded thresholds for "Strong / Average / Weak" reading, conditional formatting on the reading. Tally a score at the bottom.
Step 4. Build the Technical Analysis sheet. Live cells for last, 50 DMA, 200 DMA, RSI, 52-week high, 52-week low. Reading cells with =IF(price > sma, "Above", "Below"). RSI reading with nested =IF. A summary block at the bottom.
Step 5. Build the Peer Comparison sheet. Yellow input cells for ticker in column A, live formulas for the remaining nine columns. Add an =AVERAGE() row at the bottom.
Step 6. Polish. Freeze panes on every sheet. Apply the MarketXLS blue header style. Add the "MarketXLS Functions Used in This Sheet" reference box at the bottom of every sheet. Add links back to marketxls.com and book a demo.
Step 7. Test. Type a different ticker in the Main Dashboard. Every sheet should recompute. Type an invalid ticker. Cells should show #N/A or 0 cleanly without breaking the workbook structure.
The whole build takes about an hour the first time. After that, every subsequent stock analysis is a thirty-second ticker change.
Stock Analysis in Excel: Things to Avoid
A few patterns show up over and over in homemade stock analysis sheets. Each one looks reasonable at first and ages badly.
Typed prices. Pasting a price into a cell turns your analysis into a museum exhibit within a week. Every price should be a =QM_Last() call.
Hardcoded multiples. A P/E typed in from a screenshot is wrong the moment the price moves. Use =PERatio() so the multiple recomputes when the price does.
Single-anchor valuation. Anchoring a fair-value view to one multiple (P/E only, or DCF only) hides risk. Show the multiple comparison, the fair-value bands, and the discount model side by side and write down where they disagree.
No peer set. A multiple without a peer set is a number without a meaning. Always benchmark.
Conflating quality and price. A high-quality business at any price is not automatically a good investment. The Financial Health sheet measures quality. The Valuation Analysis sheet measures price. Keep the two answers separate.
Skipping the technical sheet entirely. Fundamentals tell you whether you want to own a stock. Technicals tell you whether the market currently agrees with you. Both matter for entries and exits, even on long-term positions.
No disclosure line. Every analysis sheet should carry a one-line educational-use disclosure. The point is to make the boundary between analysis and advice obvious.
Stock Analysis in Excel: FAQ
What is the best Excel template for stock analysis?
The best template for stock analysis in Excel covers five layers in one workbook: snapshot, valuation, financial health, technicals, and peer comparison. Every data cell should be a live formula rather than a typed value, and every input the user controls should be a clearly marked yellow cell. The MarketXLS template included with this post is built to that pattern with six sheets and twenty-plus live cells on the dashboard alone.
Can I do real stock analysis in Excel without a paid data add-in?
You can record positions, compute returns, and build basic charts using only Excel. You cannot fetch live prices, multiples, margins, betas, dividend yields, or technical indicators directly from cells without a data add-in. With MarketXLS, every data point is a function call that refreshes the next time you press F9. That removes the typing-numbers step that breaks every homemade analysis sheet within a month.
What MarketXLS formulas should I use for fundamental analysis?
For fundamentals, the most useful MarketXLS functions are =PERatio(), =EarningsPerShare(), =Revenue(), =OperatingMargin(), =ReturnOnEquity(), =TotalDebtToEquity(), =DividendYield(), =DividendPerShare(), =CashFlowPerShare(), and =MarketCapitalization(). Each of those resolves in Excel exactly like =SUM does and pulls from a maintained financial data backend.
How is the fair-value band computed in the template?
The fair-value bands in the Valuation Analysis sheet multiply an EPS figure by three different P/E multiples (a sector median bear case, the subject stock's own P/E as the base case, and a 20% re-rated bull case) to arrive at three implied prices. The "vs Current" column shows where each implied price sits relative to today's quote. These are educational anchors, not price targets.
How does the Peer Comparison sheet work?
The Peer Comparison sheet has yellow input cells for the subject ticker plus seven peer tickers. For each ticker the workbook pulls live name, price, market cap, P/E, ROE, operating margin, dividend yield, beta, and industry from MarketXLS. A bottom row averages each column so you can read the subject stock's metrics against the peer mean instantly. You can change any peer ticker without breaking the rest of the workbook.
Can I extend the workbook to handle multiple stocks at once?
Yes. The single-stock workbook in this post is a vertical layout (one ticker, many metrics). If you want a horizontal layout for ten or fifty tickers at once, copy the formula columns from the dashboard, change the cell reference from a single yellow input to a column of tickers, and you have a multi-stock screener. The peer comparison sheet is already structured that way and can serve as a starting point.
Stock Analysis in Excel: Download the Templates
Two files ship with this post.
Download the templates:
- - Pre-filled snapshot for AAPL dated May 13, 2026 with a reference column showing the exact MarketXLS formula that powers each cell in the live version.
- - Every data cell is a live MarketXLS formula. Open in Excel with the MarketXLS add-in installed, type a ticker into the yellow input cell on the Main Dashboard, refresh, and the workbook fills itself.
Both files include all six sheets, conditional formatting, frozen panes, the "MarketXLS Functions Used in This Sheet" reference box, and links back to MarketXLS and the book a demo page.
Stock Analysis in Excel: The Bottom Line
Stock analysis in Excel earns its keep when every cell tells you something current and every sheet answers a different question. The Main Dashboard tells you what the stock is. The Valuation Analysis sheet tells you what the multiples imply. The Financial Health sheet tells you whether the business behind the multiples is well run. The Technical Analysis sheet tells you where the market currently has the stock priced relative to its own range. The Peer Comparison sheet tells you whether your conclusions hold against a comparable peer set.
MarketXLS is the layer that ties those sheets together. Functions like =QM_Last(), =PERatio(), =ReturnOnEquity(), =OperatingMargin(), =Beta(), =SimpleMovingAverage(), =RSI(), =DividendYield(), =FiftyTwoWeekHigh(), and =Revenue() mean you never type a price, a multiple, a margin, a return ratio, or a moving average. You type a ticker and the workbook fills itself.
If you want to see the full MarketXLS function catalog or build out features beyond a single-stock workbook (option chains, screeners, model portfolios, backtests, sector dashboards), the MarketXLS feature library covers it. To see the platform in action with one of our team walking through your specific workflow, you can book a demo.
Educational use only. Not investment advice. The subject ticker used in the sample workbook is illustrative and chosen to demonstrate the workbook structure, not to suggest a buy or sell.
