Dividend Tracker Excel: Build a Real-Time Income Portfolio Dashboard in 2026

A dividend tracker in Excel lists your holdings with shares owned, current price, dividend yield, dividend per share, payment frequency, and projected annual income. You can type that data in by hand, or fill it with formulas: with the MarketXLS add-in, =DividendYield("KO"), =DividendPerShare("KO"), and =DividendFrequency("KO") return the dividend data, =Last("KO") returns the price, and annual income is =DividendPerShare(A2) * B2. The formulas refresh when the workbook recalculates, so the tracker stays current without copying numbers from websites.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

This guide compares four ways to build the tracker (manual, Google Sheets GOOGLEFINANCE, dedicated apps, and MarketXLS formulas), then walks through the formula build step by step.

---

Methods Compared

Before diving into the step-by-step builds, here's how the four main approaches to dividend tracking in Excel stack up:

Each method has its place. The right choice depends on your portfolio size, how much customization you need, and whether you want to stay inside Excel.

---

Method 1: The Manual Spreadsheet

The simplest approach — and the one most people start with — is a plain Excel spreadsheet where you type in dividend data by hand.

Step-by-Step

1. Create your column headers: Ticker, Company Name, Shares Owned, Current Price, Dividend Yield (%), Dividend Per Share ($), Annual Income, Dividend Frequency.
2. Enter your holdings in column A: AAPL, JNJ, KO, PG, O, SCHD — whatever you own.
3. Look up each ticker on Yahoo Finance, Seeking Alpha, or your broker's site. Copy the current price, dividend yield, and per-share amount into the corresponding cells.
4. Calculate annual income with a standard formula: =Shares * DividendPerShare. For quarterly payers, multiply by 4. For monthly payers, multiply by 12.
5. Sum the income column for your total projected annual dividend income.

Limitations

This approach works fine for a small portfolio, but it falls apart quickly:

• Stale data. The moment you close the spreadsheet, every number is out of date. Yields change daily. Stock prices move by the minute.
• Time-consuming updates. With 20+ holdings, manual updates can take 2-4 hours per month. That's time you could spend on actual analysis.
• Error-prone. Copying numbers from a website into Excel is a recipe for typos. One misplaced decimal in a yield figure can throw off your entire income projection.
• No historical context. A static spreadsheet tells you what the yield is today, but not how it's trended over the past year.

For investors with fewer than five dividend stocks and no urgency for current data, manual tracking is adequate. For everyone else, there are better options.

---

Method 2: Google Sheets with GOOGLEFINANCE

Google Sheets offers a built-in GOOGLEFINANCE function that can pull some stock data automatically. It's free and requires no add-ins.

Step-by-Step

1. Open Google Sheets and create the same column layout as above.
2. For current price, use: =GOOGLEFINANCE("AAPL", "price")
3. For dividend yield, try: =GOOGLEFINANCE("AAPL", "dividendyield") — note that this doesn't work for all tickers and may return errors.
4. For historical prices, use: =GOOGLEFINANCE("AAPL", "close", DATE(2025,2,13), DATE(2026,2,13), "DAILY")

Limitations

• No dividend per share. GOOGLEFINANCE doesn't reliably provide per-share dividend amounts, so you still need to look those up manually.
• No dividend frequency. You can't pull whether a stock pays monthly, quarterly, or annually.
• No fundamentals. Want P/E ratios or revenue data alongside your dividend tracker? Not available.
• No options data. If you sell covered calls on dividend stocks (a popular income strategy), Google Sheets can't help.
• Delayed data. Prices are delayed by at least 15 minutes, sometimes more.
• It's not Excel. If your models, macros, and workflow live in Excel, switching to Google Sheets means rebuilding everything.

Google Sheets works as a basic starting point, but dividend investors who need comprehensive data quickly outgrow it.

---

Method 3: Dedicated Dividend Tracking Apps

Platforms like Simply Wall St, Koyfin, Seeking Alpha, and Stock Analysis offer built-in dividend tracking features with polished interfaces.

What They Do Well

• Visual dashboards with dividend calendars, income projections, and portfolio breakdowns.
• Automatic updates — no manual data entry needed.
• Research tools — analyst ratings, news feeds, and community features.

Where They Fall Short

• No spreadsheet control. You can't add custom columns, build your own models, or integrate dividend data with your existing Excel workflow. You're locked into their interface.
• Separate platform. Another login, another tab, another tool to learn. If you're already running your analysis in Excel, switching context breaks your flow.
• Limited customization. Want to calculate a custom metric like "yield on cost" adjusted for your specific purchase dates? Most apps don't support custom formulas.
• Export friction. Getting data out of these platforms and into Excel usually means CSV downloads — which are stale the moment you save them.
• Subscription costs. Premium features typically require paid plans, and you're paying for an entire platform when you might only need the dividend data.

Dedicated apps are great for investors who prefer a visual, browser-based experience. But if you're already in Excel — and you want your dividend tracker to live alongside your valuation models, portfolio analytics, and screening tools — there's a more direct approach.

---

Method 4: The Formula Approach (MarketXLS)

MarketXLS is an Excel add-in with 1,000+ financial formulas, including the dividend, price, and fundamentals functions a self-updating dividend tracker needs.

You're already in Excel. Why leave it?

Step-by-Step: Build Your Dividend Tracker in 10 Minutes

Step 1: Set Up Your Portfolio Layout

Create a table with these columns:

In column A, enter your dividend stocks: AAPL, JNJ, KO, PG, O, SCHD, VZ, T, MO, ABBV — or whatever your portfolio holds.

Step 2: Pull Live Prices

In cell C2, type:

=Last("AAPL")

Formula documentation: Last

Drag the formula down for every ticker in your list. =Last returns a snapshot that updates when Excel recalculates (15-minute delayed on the Standard plan).

For streaming prices that update continuously (real-time on the Advanced and Business plans), use:

=QM_Stream_Last("AAPL")

Formula documentation: QM_Stream_Last

Step 3: Get Dividend Data Automatically

In the Dividend Yield column (D2):

=DividendYield("AAPL")

Formula documentation: DividendYield

In the Dividend Per Share column (E2):

=DividendPerShare("AAPL")

Formula documentation: DividendPerShare

In the Frequency column (G2):

=DividendFrequency("AAPL")

Formula documentation: DividendFrequency

Each formula pulls live data. No copying from websites. No CSV imports. No stale numbers. Drag each formula down your ticker list and your entire dividend dashboard populates in seconds.

Step 4: Calculate Your Annual Income

With Dividend Per Share and Shares Owned already in your spreadsheet, annual income is just standard Excel math. In cell F2:

=DividendPerShare("AAPL") * B2

Formula documentation: DividendPerShare

Where B2 is your shares owned. Drag this down, then add a SUM at the bottom:

=SUM(F2:F20)

That is your total projected annual dividend income, recalculated every time the workbook refreshes. It is a projection based on the current annual dividend, not a guarantee of future payouts.

Step 5: Add Valuation Context

Smart dividend investors don't just chase yield. They want to know if a stock is fairly valued before adding to a position. Add these columns to catch potential dividend traps:

P/E Ratio:

=PERatio("AAPL")

Formula documentation: PERatio

A high yield with a sky-high P/E ratio might signal an overvalued stock where the dividend isn't sustainable. Conversely, a moderate yield with a low P/E could indicate a buying opportunity.

Revenue (for growth verification):

=hf_Revenue("AAPL", 2024, 2)

Formula documentation: hf_Revenue

This pulls quarterly revenue data. Declining revenue alongside a high yield is a classic dividend trap warning sign. You want to see stable or growing revenue supporting those payouts.

Market Capitalization:

=MarketCapitalization("AAPL")

Formula documentation: MarketCapitalization

Useful for filtering your dividend stocks by size — large-cap dividend aristocrats tend to have more sustainable payouts than small-cap high yielders.

Step 6: Track Historical Performance

Want to see how a dividend stock has performed over the past year? Use:

=GetHistory("AAPL", "2025-02-13", "2026-02-13", "Daily")

Formula documentation: GetHistory

This pulls historical price data directly into Excel — perfect for charting price trends alongside your dividend income. You can visualize whether a stock's price appreciation (or depreciation) is complementing or undermining your dividend returns.

For longer time horizons, simply adjust the dates:

=GetHistory("KO", "2021-02-13", "2026-02-13", "Weekly")

Formula documentation: GetHistory

Five years of weekly data for Coca-Cola, ready to chart in seconds.

Step 7: Add Technical Signals for Better Entry Points

Timing matters, even for dividend investors. If you're adding to a position, you want to buy at favorable prices. These technical indicators help you identify entry points:

RSI (Relative Strength Index):

=RSI("AAPL")

Formula documentation: RSI

An RSI below 30 suggests a stock may be oversold — potentially a good time to add shares and lock in a higher yield on cost.

50-Day Moving Average:

=SimpleMovingAverage("AAPL", 50)

Formula documentation: SimpleMovingAverage

When a dividend stock drops below its 50-day moving average with an RSI under 30, experienced income investors take notice. With MarketXLS, you see these signals in real time right next to your dividend data — no need to check a separate charting platform.

Going Further: Covered Call Income on Dividend Stocks

Some dividend investors add income by selling covered calls on shares they hold. Pull the option chain for a holding with:

=QM_GetOptionChain("KO")

Formula documentation: QM_GetOptionChain

This returns all available strikes, expirations, and premiums. You can see call premiums alongside your dividend income to calculate total yield — dividends plus options premium — all in one spreadsheet.

For financial advisors managing client portfolios, this means you can show clients their total income picture: dividend yield plus covered call premium, net of any capital gains risk. Options data is end-of-day on the Standard plan and real-time on the Advanced and Business plans.

---

Who Needs a Dividend Tracker in Excel?

Financial Advisors and Wealth Managers

If you manage income-focused client portfolios, a live dividend tracker in Excel gives you an edge. You can:

• Show clients their projected annual income across all holdings in real time
• Quickly model what happens if you swap one dividend stock for another
• Generate custom reports without being locked into a platform's export format
• Integrate dividend data with your existing portfolio models and risk analysis

Dividend Income Investors

Whether you're building a dividend growth portfolio or living off dividend income in retirement, having a self-updating tracker means:

• Never missing a yield change that signals a potential dividend cut
• Seeing your actual income projections update as prices and payouts shift
• Comparing yields across sectors without leaving your spreadsheet
• Tracking yield on cost for positions you've held for years

Asset Managers and Fund Analysts

For professionals analyzing dividend-paying securities across large universes:

• Screen hundreds of tickers for yield, payout ratio, and growth simultaneously
• Use MarketXLS's 1,000+ functions to combine dividend data with fundamentals, technicals, and options analytics
• Build custom scoring models that weight dividend metrics alongside other factors

---

Pro Tips for Your Dividend Tracker

1. Use Conditional Formatting for Yield Alerts

Set up Excel's conditional formatting to highlight cells when:

• Dividend yield exceeds 6% (potential dividend trap warning — investigate further)
• Yield drops below your minimum threshold (signal to review the position)
• P/E ratio exceeds 25 while yield is above 5% (possible unsustainable payout)

2. Build a Dividend Calendar View

Create a separate sheet that lists your holdings by payment month. Most quarterly payers stagger their payments, so with strategic selection, you can build a portfolio that pays you every month. Use =DividendFrequency() to verify payment schedules.

3. Track Yield on Cost

Your yield on cost — the current annual dividend divided by your original purchase price — tells you how your income has grown. Add a "Purchase Price" column and calculate: =DividendPerShare("AAPL") / PurchasePrice. Over time, dividend growth stocks can deliver yield-on-cost figures far above the current headline yield.

4. Create a Dividend Growth Tracker

Compare this year's per-share dividend with last year's to track dividend growth rates. Companies that raise dividends every year for 25+ years are called Dividend Aristocrats.

5. Set Up a Watchlist Sheet

Use a separate sheet for dividend stocks you're monitoring but don't yet own. Pull their yields, P/E ratios, and technical signals with MarketXLS formulas so you're ready to act when valuations become attractive.

---

Frequently Asked Questions

Can I track dividends from ETFs and REITs in Excel?

Yes. MarketXLS formulas work with ETFs, REITs, and individual stocks. Use =DividendYield("SCHD") for the Schwab US Dividend Equity ETF or =DividendYield("O") for Realty Income, a monthly-paying REIT. The formulas pull data for any ticker that pays distributions, regardless of the security type.

How often does the dividend data update?

MarketXLS formulas refresh when the workbook opens or recalculates. Stock prices are 15-minute delayed on the Standard plan and real-time on the Advanced and Business plans. Dividend yield moves with price (since yield = annual dividend / current price). Per-share amounts and frequency data update when the underlying data provider reflects changes, typically within one business day of a company's announcement.

Do I need to know VBA or programming to use MarketXLS?

No. Every MarketXLS function works like a standard Excel formula. If you can type =SUM(), you can type =DividendYield("AAPL"). No coding is needed. (VBA can call MarketXLS functions if you want to automate further.)

Can I track international dividend stocks?

MarketXLS covers US, Canadian, UK, German, and Australian markets. For US-listed ADRs of international companies (like NESN for Nestlé or UL for Unilever), the dividend formulas work the same way. Check the MarketXLS functions list for full coverage details.

What's the difference between dividend yield and dividend per share?

Dividend yield is a percentage — it tells you what annual return you're getting from dividends relative to the current stock price (annual dividend ÷ price × 100). Dividend per share is the actual dollar amount paid per share annually. Both are useful: yield helps you compare across stocks, while per-share amounts let you calculate your actual income. In MarketXLS: =DividendYield("AAPL") returns the percentage, =DividendPerShare("AAPL") returns the dollar amount.

Can I combine dividend tracking with options analysis?

Yes. Use =QM_GetOptionChain("KO") to pull the chain for a holding next to your dividend tracker, then add covered call premium to dividend income for a total-income view. See the MarketXLS options tools for more details.

---

Pricing and Getting Started

MarketXLS runs as a Windows desktop Excel add-in and as a Microsoft 365 add-in for Excel on Mac and Excel for the web (installed from Microsoft AppSource, where formulas use the mxls. prefix, e.g. =mxls.DividendYield("KO")). Some functions differ by platform; check /formulas.

Building the dividend tracker described in this guide takes about 10 minutes. After that, your spreadsheet maintains itself. Every time you open it, yields, prices, per-share amounts, and income projections update automatically.

If you're currently spending hours each month manually updating dividend data in Excel, switching to formula-driven tracking saves significant time — and eliminates the data entry errors that can distort your income projections.

US plans start at $70/month billed annually (Standard, 15-minute delayed quotes); Advanced ($125/month annually) and Business ($200/month annually) add real-time streaming. See current pricing and plans.

---

The Bottom Line

There are many ways to track dividends in Excel — from fully manual spreadsheets to Google Sheets functions to dedicated tracking apps. Each has its place.

If you already work in Excel and want a tracker that updates itself, the formula-driven approach removes manual data entry: current yields, per-share amounts, payment frequencies, valuation metrics, historical data, and options analytics, all from simple formulas in your existing spreadsheet.

You're already in Excel. Your dividend tracker should be too.

Get started with MarketXLS →