How to Build a Stock Portfolio Tracker in Excel

A stock portfolio tracker in Excel is one table: a row for each holding, with the shares you own, what you paid and today's price. A few simple formulas turn that into cost basis, market value, gain and percent return, and SUMIF and XLOOKUP add sector totals and a price list. This guide builds the whole tracker step by step, and it is also a good practice project for the formulas job tests ask about.

The stocks below are placeholders (Stock A to Stock F) and every figure is an example, checked in Python. Nothing here is investment advice or a real price.

What columns does a portfolio tracker need?#

A tracker needs three inputs per holding and four formula columns. You type the inputs; Excel works out the rest.

Format the data as an Excel table (Windows: Ctrl+T; Mac: ⌘T). New rows then pick up the formulas automatically.

Step 1: Enter your holdings#

Type one row per stock. Here is the example portfolio used throughout:

If you bought the same stock more than once, use the average cost per share. Step 5 shows how to work that out from a purchase log.

Step 2: Calculate cost basis, value and gain#

Cost basis is what you paid in total; market value is what the shares are worth at the current price. In row 2:

F2: =C2*D2
G2: =C2*E2
H2: =G2-F2

For Stock A, that gives a cost basis of 1,000, a value of 1,240 and a gain of 240. Fill the formulas down to row 7 (Windows: Ctrl+D; Mac: ⌘D). Stock B shows a gain of -120: a negative gain is a loss.

Step 3: Calculate the percent return#

Percent return is the gain divided by the cost basis. In I2:

=H2/F2

Format the column as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). Stock A returns 24%, Stock C 15% and Stock B -10%. This is the classic percent change formula, (new − old) / old, explained in full in the guide to the percent change formula. Always divide by the cost basis, the starting value, not by today's value.

Add a total row underneath. Total cost is 4,990, total value 5,275 and total gain 285. For the portfolio's return, divide the total gain by the total cost: =H8/F8 gives about 5.71%. Don't average the percent column instead. That treats a small holding the same as a large one.

Step 4: Total by sector with SUMIF#

SUMIF adds the values in one column where another column matches a condition. To total the Tech holdings:

=SUMIF($B$2:$B$7,"Tech",$G$2:$G$7)

The result is 2,320 (Stock A's 1,240 plus Stock B's 1,080). Put the sector names in a small summary table and point the criteria at those cells instead of typing "Tech". Then divide by the total value for each sector's share of the portfolio:

The share formula is =SUMIF($B$2:$B$7,K2,$G$2:$G$7)/$G$8, with the sector name in K2 and the total value in G8. The $ signs keep the ranges fixed as you fill down. See how SUMIF works and how to calculate a percentage of a total for more examples, or check the SUMIF function guide for the syntax.

Step 5: Average cost from a purchase log#

If you buy the same stock several times, keep a purchase log and let SUMIF work out the average cost. A log has one row per purchase:

The average cost of Stock A is the total paid divided by the total shares:

=SUMIF(Log!B:B,"Stock A",Log!D:D)/SUMIF(Log!B:B,"Stock A",Log!C:C)

That is 1,000 / 20 = 50 per share, the figure used in the holdings table. The shares column in the holdings table can use the same idea: =SUMIF(Log!B:B,A2,Log!C:C). Sales can go in the same log as negative shares.

Step 6: Pull prices from a price list with XLOOKUP#

Keep prices on their own sheet and look them up, so updating prices never means editing formulas. Put a Prices sheet with stock names in column A and prices in column B. Then in E2 of the holdings table:

=XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found")

XLOOKUP finds the stock name in the price list and returns its price. The fourth argument shows "Not found" instead of #N/A when a name is missing or misspelled. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use =VLOOKUP(A2,Prices!A:B,2,FALSE). The guide to XLOOKUP and the XLOOKUP function guide cover the details.

Where do the prices come from? You can paste them in from a statement. In Microsoft 365, the Stocks data type (Data > Stocks) can also fill in prices for many listed companies, and the STOCKHISTORY function returns past prices. Both depend on your Excel version and region, and quotes can be delayed, so note the date you updated them.

Going further: dividends and yearly returns#

A few extra columns make the tracker more useful:

• Dividends. Log each dividend with the stock and amount, and total it with =SUMIF(Div!B:B,A2,Div!C:C). Add it to the gain for a total return.
• Annual return. A 20% gain over four years is not 5% a year. Use the CAGR formula to turn a multi-year return into a yearly rate.
• Biggest movers. Sort by the Return % column, or use conditional formatting to colour gains green and losses red.

Practise it#

This tracker uses three core skills: totals with conditions, percent change and lookups. Practise SUMIF in a live spreadsheet, then practise percentages and percent change and practise XLOOKUP. Each lesson checks your formula as soon as you type it. Then try the exercise on this page to work out the Tech share of the example portfolio.