ETF Holdings in Excel: Pull Live Fund Constituents With MarketXLS Formulas

ETF holdings in Excel is one of the most common questions we hear from analysts and self-directed investors, and the honest answer is that issuer web pages were never built for serious comparison work. Morningstar, the ETF.com pages, the Vanguard and iShares fact sheets - they each show the top ten or top twenty constituents one fund at a time, behind a paginated table, with no way to stack four ETFs side by side or check what is doubled up across them. The job of comparing what you actually own gets pushed into copy-paste workflows that go stale the next day. This post walks through how to bring ETF holdings directly into Excel using a small set of MarketXLS functions, then how to layer overlap, concentration, and sector-exposure analytics on top so the workbook stays useful past the first refresh.

Quick reference: the MarketXLS formulas for ETF holdings work

Every formula above is part of the MarketXLS function library, callable as a normal cell formula. The Fund family of functions is what unlocks ETF-specific data; the rest are the same functions you would use on individual equities, which is exactly the point. Once a holding is on the sheet you can analyze it like any other stock.

Why the holdings, not the ticker, are what matter

A ticker is a label. A holdings list is the actual portfolio. Two ETFs with similar names and similar one-line descriptions can hold very different things, and two ETFs with very different categories can hold a surprising amount of overlap. The "S&P 500" family and the "Total Market" family share the same ten mega caps in the top of the book. The "Growth" family and the "Technology" family share the same names, just with different weights. The "Dividend" family and the "Value" family carry a different mix entirely. If you are building a multi-ETF portfolio, the only way to know what you really own is to pull each fund's constituents into one place and look at them side by side.

Issuer pages will not do this for you. They show one fund at a time. They paginate. They lag. The reporting frequency for ETF holdings is generally monthly for index funds and quarterly for active funds, and the website is usually rebuilt only after each rebalance. The data is there; it is just not arranged for analysis. Excel is. So the move is to pipe the holdings into Excel with one formula per fund and let the workbook do the comparison.

A practical workflow: from one ETF to a watch list of twelve

The template attached to this post drives a twelve-ETF workbook with five working sheets plus a how-to tab. The roster covers the typical building blocks an advisor would mix and match: SPY, QQQ, VTI, and IWM for broad US equity; VXUS and VWO for international; XLK, XLF, and XLE for sectors; SCHD for dividends; VUG and VTV for growth and value factors. These are not recommendations. They are the ETFs that show up most often in client portfolios, which makes them useful to study together.

The workflow has four steps. First, drop a ticker into the roster sheet. Second, point =FundHoldings(ticker) at it on the drilldown sheet so the top positions spill down a column. Third, let the overlap matrix tally which underlying stocks appear in multiple ETFs. Fourth, read the concentration tracker and sector exposure heatmap to see whether the funds you stacked behave the way the labels suggest. None of this requires a programming step. All of it is formulas referencing other formulas.

Roster sheet: fund metadata at a glance

The roster sheet is the index. One row per ETF, with the columns that decide whether a fund deserves a place in the portfolio at all:

Above the table sit three yellow input cells: the concentration threshold for the "Concentrated" tag, the minimum AUM for a "Strong" verdict, and the maximum expense ratio for a "Strong" verdict. Change the inputs and the verdict column recalculates. This is the kind of trivial logic that is painful to maintain on a vendor website and trivial to maintain in a spreadsheet.

A note on AUM: the column shows raw dollar values rather than abbreviated text. Excel's number format string "$"#,##0,,,"B" does the abbreviation on the display side. Keeping the raw number underneath makes downstream math (filtering, ratios, ranking) work correctly.

Holdings drilldown: the heart of the workbook

The drilldown sheet is where =FundHoldings does its job. The template version uses a single yellow selector cell at B4 and pipes everything through it:

B4  :  SPY                         (selector)
B5  :  =FundFamily(B4)             (issuer)
B6  :  =FundInceptionDate(B4)      (launch date)
B7  :  =FundNetAssets(B4)          (AUM)
B8  :  =FundExpenseRatio(B4)       (expense ratio)
B9  :  =FundReportPeriodEndDate(B4)  (holdings as of)
A12 :  =FundHoldings(B4)           (spill: top holdings)

Type a different ticker into B4 and the entire panel refreshes. No copy-paste, no website tab-switching, no stale data. The FundReportPeriodEndDate cell is worth noting because it tells you how fresh the constituents list actually is. ETF holdings are not real-time; they are filed on a schedule. Knowing the as-of date matters when you are comparing two funds reported a quarter apart.

The drilldown also has a per-holding lookup block. Once a holding ticker is in the cell B17, you can pull =Sector(B17), =Industry(B17), =MarketCapitalization(B17), =QM_Last(B17), and =DividendYield(B17) against it. That is what unlocks the sector exposure sheet later: every constituent on the holdings drilldown can be enriched into a full fundamental row in seconds.

If you prefer a "horizontal" layout, you can lay the spilled FundHoldings output into a structured table by referencing the cells around the spill anchor. The function returns ticker and weight; building parallel columns of name, sector, and industry is straightforward with INDEX or OFFSET.

Overlap matrix: the hidden concentration check

Owning two large-cap US ETFs does not double your diversification. It double-counts the same ten mega caps. The overlap matrix takes every top-10 holdings list across the funds you track and flags the names that appear in more than one. The sample data, snapshot for illustrative purposes only and not a recommendation, shows the cleanest possible example:

A naive sum of those repeated weights overstates real portfolio exposure because the same stock cannot be counted as two distinct positions. But the count column is exactly what you want to see. If a name shows up in five of the twelve funds you track, then "rebalancing" the portfolio by trimming one of those funds and adding another from the list barely changes what you own. The overlap matrix forces this question into view.

The mechanics in Excel are simple once the holdings are on a single sheet. A COUNTIF over the holdings column counts occurrences. A SUMIF over the matching weight column sums the weights. Combine them and you have a single-cell hidden-concentration meter for any candidate purchase: paste a ticker, see how many of your existing ETFs already hold it.

Concentration tracker: how lumpy is each fund

Concentration is not bad by itself, but it has to be priced into the decision. A sector ETF is built to be concentrated; that is the product. A "total market" fund usually is not. The tracker shows top-10 weight and the single largest position for each ETF, with a risk tag that toggles between High, Moderate, and Low.

The point of the tracker is not to label "good" and "bad" funds. The point is to make sure the level of concentration you accept matches what the label implies. A fund that says "diversified large cap" with a 45% top-10 weight is a cap-weighted index where six names drive the returns. That can be perfectly fine; you just want to know.

A common analytical move at this point is to scale the concentration tag by where in the cycle the top-10 names sit. When mega caps have run hard, the cap-weighted top-10 is meaningfully bigger than it was a year prior, even if the index methodology has not changed. The workbook helps you watch that drift over time. Save a copy each quarter, diff the top-10 weight column, and you have a rolling concentration log.

Sector exposure heatmap: trust but verify

The sector exposure heatmap aggregates the sector tag of each top-10 holding and tallies the weight by sector. It is intentionally not the full fund sector breakdown - that requires every holding, not just the top ten - but it is enough to catch labels that mislead. A "broad market" fund that lands at 35% technology in the top-10 is not a broad-market hold in any practical sense; it is a tech-tilted index that happens to own other things at small weights.

When you stack two ETFs, the heatmap also makes overweights obvious. Two funds at 25% technology in their top-10 stack to a much larger absolute tech weight in the combined portfolio than the names would suggest. This is the question the heatmap is built to answer at a glance.

The formulas behind it are again ordinary Excel. The drilldown sheet produces a column of holding tickers and a column of weights. For each holding ticker, the sector exposure sheet pulls =Sector(ticker) to label it. A SUMIFS then aggregates the weight by sector across all the holdings of a given fund. The same pattern works for industry, market cap bucket, or any other axis you want to roll up by.

Building the Excel file step by step

If you would rather build it yourself rather than open the template, here is the minimum viable version in six cells. Start a blank workbook, add the headers, and:

A1: Ticker        B1: AUM                       C1: Expense %
A2: SPY           B2: =FundNetAssets(A2)        C2: =FundExpenseRatio(A2)
A3: QQQ           B3: =FundNetAssets(A3)        C3: =FundExpenseRatio(A3)
A4: VTI           B4: =FundNetAssets(A4)        C4: =FundExpenseRatio(A4)

D1: Top holdings spill
D2: =FundHoldings(A2)

That is the entire foundation. The roster and the drilldown both grow from those primitives. Add =FundFamily, =FundInceptionDate, =DividendYield, and you have the roster. Use one selector cell pointing at A2 to A4 and you have a switchable drilldown.

Comparing two ETFs side by side

A side-by-side comparison fits in a small block. Suppose you want to decide between VTI (total market) and VOO (S&P 500). Both are Vanguard, both cap-weighted, both extremely cheap. The relevant differences are the breadth of the holdings list, the weight on the largest names, and the long-run return contribution of small and mid-cap names. With MarketXLS functions:

       VTI                              VOO
=FundFamily("VTI")                =FundFamily("VOO")
=FundNetAssets("VTI")             =FundNetAssets("VOO")
=FundExpenseRatio("VTI")          =FundExpenseRatio("VOO")
=DividendYield("VTI")             =DividendYield("VOO")
=FundHoldings("VTI")              =FundHoldings("VOO")

The dollar-weighted overlap is going to be high, because VOO's holdings are a subset of VTI's by construction. The difference is in the long tail. Knowing the size of that tail, and what it has contributed historically, is the kind of question that pays back the small investment of building this workbook once.

Stress-testing a real portfolio

A useful exercise: list every ETF in your real portfolio. Add a column for your dollar allocation. Pull each fund's top-10 with =FundHoldings. Multiply each constituent weight by its parent fund weight in your portfolio. Sum the products by underlying ticker. You now have your effective single-name exposure across the portfolio - the percentage of your total dollars that sits in AAPL, MSFT, NVDA, and so on, summed across every ETF that holds them. This is the calculation that retail "diversification" claims rarely survive. It is also the calculation that takes thirty seconds to do once the holdings are in Excel and an hour to do without.

A common output of that exercise: a "diversified" five-ETF portfolio that is fundamentally a concentrated bet on the same ten mega caps, with a small overlay of bonds and gold to soften the variance. This is not necessarily wrong - many investors are happy to be passively long the largest growth names. The point is to know it.

Caveats that matter

A workbook is only as fresh as the data feeding it. A few things to keep in mind when using FundHoldings for serious analytical work:

• Holdings are reported on a schedule. The FundReportPeriodEndDate field tells you the as-of date. If two funds were reported a month apart and the market moved meaningfully in between, the snapshot weights are not directly comparable. The cap-weighted indexes drift between rebalance dates as the underlying stocks move.
• The function returns the top positions, which for very broad funds is a small share of total assets. A "complete" sector breakdown for an ETF holding 500 names is not available from the top-10 alone. Use the top-10 as a signal, not as a precise allocation report.
• Foreign tickers can look unfamiliar. Some emerging-market and developed international funds report holdings with local exchange suffixes (Samsung as 005930.KS, for example). The companion =Sector and =MarketCapitalization calls may need adjusted tickers. The workbook handles the common US cases automatically.
• The blog tickers, prices, and weights inside this post are educational illustrations. They are point-in-time snapshots for the date stamped on the workbook, not real-time data. Use the template version of the workbook for live numbers.

Download the templates

Download the templates:

• - Pre-filled holdings for 12 well-known ETFs as of 2026-05-13
• - Live =FundHoldings and companion fund functions

Both workbooks share the same five working sheets plus the how-to: ETF Roster, Holdings Drilldown, Overlap Matrix, Concentration Tracker, and Sector Exposure. The static version is meant as a tour of the layout. The formula version is meant to be plugged in and used.

Frequently asked questions

What formula pulls ETF holdings into Excel?

=FundHoldings("ticker") from MarketXLS. The function returns the top holdings of the ETF and spills them down the column. Pair it with =FundFamily, =FundNetAssets, =FundExpenseRatio, and =FundInceptionDate for the surrounding fund metadata.

How often do ETF holdings update?

Index ETFs typically report holdings monthly. Active ETFs typically report quarterly. The =FundReportPeriodEndDate(ticker) function returns the as-of date for the holdings file in MarketXLS, so the workbook always tells you how recent the numbers are.

Can I see holdings for international or emerging market ETFs?

Yes. The =FundHoldings function works for international ETFs the same way it does for US funds. Some constituents will have local exchange suffixes (Samsung as 005930.KS, for example). The workbook in this post includes VXUS and VWO so you can see the pattern in action.

How do I check overlap between two ETFs in Excel?

Pull each fund's top holdings onto the same sheet with =FundHoldings. Run a COUNTIF across both columns - any ticker with a count of 2 appears in both funds. A SUMIF over the matching weights gives a naive cross-fund weight tally. The Overlap Matrix sheet in the template does exactly this for twelve ETFs at once.

Is the top-10 weight a good measure of concentration?

It is a good first pass. A top-10 weight above 50% is a strong signal that the fund is concentrated; a top-10 weight below 25% almost always means the fund is broadly diversified. For more precision, pull every holding in the fund (not only the top-10) and compute a Herfindahl index or look at the percentage of assets held in the top 100 positions.

What is the most useful single formula for ETF analysis?

=FundHoldings(ticker) for breadth of analysis, =FundExpenseRatio(ticker) for cost decisions. If you are building a watchlist, start with those two and add fund family, AUM, and yield as the second tier.

The bottom line

ETF holdings in Excel is a small project with an outsized return on time. Six MarketXLS functions and one switchable selector cell are enough to replace a half-dozen issuer web pages and a copy-paste workflow that goes stale every month. The drilldown answers "what is in this fund," the overlap matrix answers "what am I really buying when I stack these funds," the concentration tracker answers "how lumpy is this exposure," and the sector heatmap answers "is the label telling the truth." Each of those questions is one formula deep in MarketXLS and several dozen clicks deep in any vendor website. Build the workbook once and the rest is editing input cells.

If you want to put the formulas to work right now, grab the , drop your tickers into the roster, and start with one ETF on the drilldown. The rest of the workbook updates as you go.

Learn more at marketxls.com or book a demo to see the full MarketXLS function library live.