Crypto Portfolio Tracker in Google Sheets (Free Template, 2026)

To build a crypto portfolio tracker in Google Sheets, make a copy of the free template below, type your coins, quantities and average costs, and let =COINTABLE(B2)Copy fill in the current price. Value, P&L, 24h change and allocation calculate from there, and a second tab tracks dollar-cost averaging with your running average cost. The prices come through CoinTable, a Google Sheets add-on for crypto prices that needs no API key to paste and no signup.

If you only need a price in a cell and not a whole tracker, start with how to get crypto prices in Google Sheets, which compares every method, free ones included. This post is for the next step: a spreadsheet that tells you whether you're up.

Get the free template

Make a copy of the Crypto Portfolio Tracker template

Google asks "Copy document?" and puts the copy in your own Drive. It's yours from that point: private, editable, and nobody else sees what you type into it. Until CoinTable is installed, the price columns show an unknown-function error. Installing takes about 30 seconds.

The template has two tabs:

Delete the sample rows, add your own, done. If you'd rather know what every cell does, the next two sections describe the template column by column, and the build guide after that recreates it from a blank sheet.

The Portfolio tab, column by column

Row 1 holds headers. One coin per row from row 2 down. The template ships with five sample holdings (BTC, ETH, SOL, LINK, ADA) so you can see it working before you type anything.

The formulas for row 2. Fill them down for every row that has a ticker:

E2  Price         =COINTABLE(B2)
F2  Value         =C2*E2
G2  P&L           =F2-C2*D2
H2  P&L %         =IF(C2*D2=0,"",G2/(C2*D2))
I2  24h change    =COINTABLE(B2, "changepct")
J2  Allocation %  =F2/SUM(F$2:F$51)

A few of those deserve a sentence.

Value multiplies by the Price column instead of calling COINTABLE again. One price per coin keeps the sheet easy to read and fix.

P&L % divides by your cost basis, quantity times average cost. The IF guard is there for airdrops and staking rewards you got for free: a cost of zero would otherwise show #DIV/0!.

24h change looks wrong if you format it as Percent. "changepct" returns a plain number where 2.4 means +2.4%, so Percent format shows 240%. The custom format 0.00"%" adds the sign without multiplying. The COINTABLE page also covers 1-hour, 7-day, 30-day and 1-year periods, if 24 hours is too twitchy for you; periods other than 24h may need Plus.

Allocation % uses F$2:F$51, so it works for up to 50 coins. The dollar signs lock the range when you fill down. More than 50 coins? Change 51 to something bigger, in every row.

To the right, cells L1:M4 hold a summary:

L1  Total value   M1  =SUM(F2:F51)
L2  Total cost    M2  =SUMPRODUCT(C2:C51,D2:D51)
L3  Total P&L     M3  =M1-M2
L4  P&L %         M4  =IF(M2=0,"",M3/M2)

Here's one row worked through. Say you bought 0.25 BTC at an average of $62,000, so your cost basis is $15,500. If COINTABLE returns 100,000 (example value, October 2026), Value is $25,000, P&L is $9,500 and P&L % is 61.3%. Same math on every row, and the summary adds them up.

The template prices everything in US dollars. If you think in euros, use =COINTABLE(B2&"/EUR")Copy and =COINTABLE(B2&"/EUR", "changepct")Copy and enter your average cost in euros too. Mixing a dollar cost with a euro price gives you a P&L that's off by the exchange rate, and it won't look obviously wrong. CoinTable takes 63 currencies, BTC and ETH among them, in case you'd rather measure everything in bitcoin.

The DCA tab: dollar-cost averaging in Google Sheets

Dollar-cost averaging means buying a fixed amount on a schedule, say $200 of BTC on the 1st of every month, whatever the price. The question it raises is always the same: what did I actually pay per coin? An exchange app will tell you, sort of, if you only ever bought on that one exchange. The DCA tab tells you exactly.

One coin per DCA tab. Duplicate the tab (right-click the tab name, then Duplicate) for each coin you stack.

Row 2 formulas, filled down to row 500 so a new purchase calculates as soon as you type it:

D2  Coins bought   =IF(C2="","",B2/C2)
E2  Running coins  =IF(D2="","",SUM(D$2:D2))
F2  Running cost   =IF(D2="","",SUM(B$2:B2))
G2  Avg cost       =IF(D2="","",F2/E2)

And a summary block in I1:J8. You type the ticker in J1; everything else is a formula:

I1  Ticker         J1  BTC
I2  Current price  J2  =COINTABLE(J1)
I3  Coins held     J3  =SUM(D2:D500)
I4  Total spent    J4  =SUM(B2:B500)
I5  Avg cost       J5  =IF(J3=0,"",J4/J3)
I6  Value now      J6  =J2*J3
I7  P&L            J7  =J6-J4
I8  P&L %          J8  =IF(J4=0,"",J7/J4)

Here's what three monthly buys look like (example purchases, not real quotes):

The average of the three prices is $101,667, but your average cost is $98,361. That gap is the whole point of DCA: the same $200 bought more coins in the cheap month. The tab makes it visible instead of something you take on faith.

Two pieces of friction. First, Coins bought assumes the fee came out of the amount you spent. If your exchange took its fee in coins instead, type the coins you actually received over the formula in column D; the running totals pick it up either way. Second, the tab is buy-only. Selling part of a stack changes your average cost in ways that depend on the accounting method, so keep sells out of this tab and in your exchange records.

Want the Portfolio tab to follow your DCA stack automatically? On BTC's Portfolio row, set Quantity to =DCA!J3Copy and Avg cost to =DCA!J5Copy. Swap DCA for whatever you named the tab.

Build it yourself from a blank sheet

Some people don't copy templates from strangers. Fair. Here's the same tracker from scratch, in about 10 minutes.

1. Install CoinTable. Open the CoinTable listing on Google Workspace Marketplace, click Install and pick your Google account. About 30 seconds, assuming the permissions window doesn't open behind your browser (it sometimes does).
2. Create a new sheet and rename the first tab to Portfolio.
3. Type the headers in A1:J1: Coin, Ticker, Quantity, Avg cost, Price, Value, P&L, P&L %, 24h change, Allocation %.
4. Enter your holdings in columns A to D, one coin per row. Use the ticker your exchange shows: BTC, ETH, SOL. CoinTable covers 10,000+ coins. When two coins share a ticker it picks the larger one, so sanity-check the price of anything obscure.
5. Add the price formulas. In E2 type =COINTABLE(B2)Copy and in I2 =COINTABLE(B2, "changepct")Copy. A refresh updates every CoinTable formula, including ones wrapped in IFERROR or multiplied, like =C2*COINTABLE(B2)Copy.
6. Add the math columns F2, G2, H2 and J2 from the Portfolio formulas above.
7. Fill down. Select E2:J2 and drag the small square at the corner down to your last coin. Only fill rows that have a ticker.
8. Add the summary in L1:M4.
9. Format the columns. Currency for D through G, Percent for H and J. For column I, go to Format → Number → Custom number format and enter 0.00"%".
10. Color the gains. Select G2:G51 and I2:I51, then Format → Conditional formatting: one rule "Greater than 0" with green text, one "Less than 0" with red. Google's conditional formatting help covers the other rule types.
11. Add the DCA tab with the + at the bottom left, name it DCA, and copy in the headers and formulas from the DCA section.
12. Freeze row 1 on both tabs (View → Freeze → 1 row) so headers stay put while you scroll.

Every COINTABLE attribute, with arguments and edge cases, is in the COINTABLE reference.

Refreshing prices, and what it costs

You can update prices any time with Refresh data from the CoinTable menu under Extensions. On Plus, CoinTable also refreshes them when you open the spreadsheet (at most once every 5 minutes, up to 200 cells).

An update is one coin in one currency. Every field of that coin in that currency counts once, and the same coin again within 6 hours is free. The template's five sample coins with price and 24h change come to 5 updates per refresh, and the DCA tab's BTC price rides along with the BTC row. A 10-coin portfolio uses 10.

The Free plan gives you 300 updates a month, reset on the 1st (UTC). That covers one refresh a day of a 10-coin tracker for a month, and a second refresh within 6 hours is free. Fine for a morning check. Tight if you check it morning and night. Plus has unlimited updates for normal use, and the limits page spells out what counts. Errors use no updates.

Tracking stocks and crypto in the same sheet

Easy, and you don't need a second add-on. Use Google's built-in GOOGLEFINANCE for stocks and ETFs and COINTABLE for crypto, in the same Price column:

E2  =COINTABLE(B2)                   (BTC row)
E7  =GOOGLEFINANCE("NASDAQ:AAPL")    (Apple row)
E8  =GOOGLEFINANCE("NYSEARCA:VOO")   (S&P 500 ETF row)

Keep the stock rows on GOOGLEFINANCE. COINTABLE reads every ticker as a coin, and some stock and index tickers are also coins: TSLA, META, SPX and GOLD.

Everything to the right (Value, P&L, Allocation) works the same, because it only reads column E. Google says its quotes may be delayed up to 20 minutes, which is fine for a portfolio sheet.

Why not use GOOGLEFINANCE for the crypto rows too? We tested it on October 15, 2026: =GOOGLEFINANCE("CURRENCY:<ticker>USD")Copy returned a price for 17 of the top 30 coins and #N/A for the other 13, including DOGE, LINK, AVAX and USDT. The full results are in why GOOGLEFINANCE crypto formulas fail. If your old tracker was built on the retired CRYPTOFINANCE add-on, the CRYPTOFINANCE to CoinTable migration guide maps each formula.

Exporting your sheet for a tax tool

Most crypto tax tools import a CSV transaction log. To get one out of the template, open the DCA tab and choose File → Download → Comma-separated values (.csv). Google downloads only the tab you're on, so repeat it per tab (Google's download help).

Three things to know before you upload it anywhere:

• The tax tool wants transactions (date, asset, amount, price, fee), and that's close to what the DCA tab holds. The Portfolio tab is a snapshot of today and is mostly useless to it.
• Price paid has to come from your exchange receipts. On Plus, =COINTABLE(J1, "price", A2)Copy shows the closing price on the date in A2, within the last year, which is close to what you paid but not the same.
• Your exchange's own CSV export is usually the more complete record, sells included. Use the sheet to check it, not to replace it.

What you owe, and which cost-basis method applies to you, is a question for your tax software or an accountant. The sheet doesn't apply any method. It adds and divides.

Tweaks worth adding

A watchlist tab. For coins you're watching but don't own, put a ticker in column A and these across the row: =COINTABLE(A2, "marketcap")Copy, =COINTABLE(A2, "volume")Copy, =COINTABLE(A2, "rank")Copy, =COINTABLE(A2, "ath")Copy and =COINTABLE(A2, "athchangepct")Copy, the last showing how far the coin sits below its all-time high. All five columns of a coin share one update, so add the ones you'd actually look at.

A second currency. Add a column next to Price with =COINTABLE(B2&"/EUR")Copy if you report in euros but think in dollars. Each coin in euros is one more update.

A value log. Make a tab called Log. Once a week, copy M1 (total value) and Paste special → Values only into a new row next to the date. After a few months you can chart it. Sheets won't keep past values for you.

Staking. Put rewards in a Staked column (K) and change Value to =(C2+K2)*E2Copy. P&L still subtracts only C2*D2, so rewards count at zero cost, which is what you paid for them.

Lump sum vs DCA. On the DCA tab, =IF(J4=0,"",J4/C2*J2)Copy shows what your total spend would be worth had you bought everything on day one at row 2's price. In a market that only went up, lump sum wins. Most markets don't only go up.

Spreadsheet or portfolio app?

The honest answer: an app looks nicer on a phone and syncs exchanges for you. A spreadsheet wins when you want your own math, your own columns, and holdings that stay in your Google account. If you've read this far, you probably already know which camp you're in.