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

Why Track Crypto in Google Sheets?

Dedicated portfolio apps like CoinStats or Delta work fine — until you want to customize a column, run your own calculations, or keep your holdings data private. Google Sheets gives you full control: your data stays in your Google account, every formula is transparent, and the whole thing is free.

CoinTable, a Google Sheets add-on for cryptocurrency prices, connects your spreadsheet to live market data with simple formulas like =CT_PRICE("BTC"). No API keys, no JSON parsing, no scripting. If you've never used CoinTable, start with the complete guide to getting crypto prices in Google Sheets.

Sheets vs. Dedicated Apps

The tradeoff: apps have nicer UIs out of the box. Sheets requires 10 minutes of setup but gives you permanent flexibility.

Quick Start: 3-Minute Portfolio Tracker

Already familiar with Google Sheets? Here's the fastest path:

A1: Coin       B1: Qty       C1: Avg Cost    D1: Price           E1: Value           F1: P/L
A2: BTC        B2: 0.5       C2: 62000       D2: =CT_PRICE(A2)   E2: =D2*B2          F2: =E2-C2*B2
A3: ETH        B3: 10        C3: 2800        D3: =CT_PRICE(A3)   E3: =D3*B3          F3: =E3-C3*B3
A4: SOL        B4: 100       C4: 95          D4: =CT_PRICE(A4)   E4: =D4*B4          F4: =E4-C4*B4

That's it — a working portfolio tracker. The rest of this article builds a more complete version with 24h changes, allocation percentages, market context, and conditional formatting.

Build From Scratch: Complete Tracker

Step 1 — Set Up Your Coin List

Create a new Google Sheet. In column A, list every coin you hold by its ticker symbol. CoinTable supports 10,000+ coins — use the same ticker you see on CoinGecko or CoinMarketCap.

A1: Ticker
A2: BTC
A3: ETH
A4: SOL
A5: ADA
A6: AVAX
A7: DOT
A8: MATIC
A9: LINK
A10: UNI

Step 2 — Pull Live Prices

In column B, add the CT_PRICE formula referencing each ticker:

B1: Price
B2: =CT_PRICE(A2)
B3: =CT_PRICE(A3)

Drag the formula down for all rows. Every cell now shows the coin's current USD price. Need prices in EUR or GBP? Use the second argument: =CT_PRICE(A2, "EUR").

Step 3 — Add 24h Price Change

Column C shows the percentage change over the last 24 hours:

C1: 24h Change %
C2: =CT_CHANGE(A2)
C3: =CT_CHANGE(A3)

Format these cells as percentages. Values above 0 mean the price went up; below 0, it dropped.

Step 4 — Calculate Holdings Value

You need two manual input columns — quantity held and average cost per coin — then one calculated column for current value:

D1: Qty           E1: Avg Cost ($)     F1: Current Value ($)
D2: 0.5           E2: 62000            F2: =D2 * CT_PRICE(A2)
D3: 10            E3: 2800             F3: =D3 * CT_PRICE(A3)

Alternatively, reference the Price column you already created: =D2 * B2. Both work; referencing column B avoids duplicate API calls.

Step 5 — Calculate Profit & Loss

P&L is straightforward:

G1: Cost Basis ($)    H1: P/L ($)          I1: P/L %
G2: =D2 * E2          H2: =F2 - G2         I2: =H2 / G2
G3: =D3 * E3          H3: =F3 - G3         I3: =H3 / G3

• Cost Basis = quantity × average purchase price
• P/L ($) = current value − cost basis
• P/L % = dollar P/L ÷ cost basis

Step 6 — Add Market Context

Two extra columns give each coin broader market context:

J1: Market Cap          K1: Rank
J2: =CT_MARKETCAP(A2)   K2: =CT_RANK(A2)
J3: =CT_MARKETCAP(A3)   K3: =CT_RANK(A3)

=CT_MARKETCAP("BTC") returns the total market capitalization. =CT_RANK("BTC") returns the coin's CoinGecko rank (1 for BTC, 2 for ETH, etc.). These help you gauge whether a coin is large-cap, mid-cap, or small-cap.

Step 7 — Allocation Percentage

See how much of your portfolio each coin represents:

L1: Allocation %
L2: =F2 / SUM(F$2:F$20)
L3: =F3 / SUM(F$2:F$20)

The $ signs lock the SUM range so you can drag the formula down without it shifting.

Complete Tracker Layout

Here's the full structure with all columns:

Sample Data

Advanced Features

Conditional Formatting for Price Alerts

Highlight cells based on P/L or 24h change:

1. Select the 24h % column (C2:C100)
2. Format → Conditional formatting
3. Rule 1: "Greater than" → 0 → green background
4. Rule 2: "Less than" → 0 → red background

Apply the same logic to the P/L columns. This gives you an instant visual read on what's up and what's down.

Portfolio Summary Row

Add a summary section above the main table (or in a separate "Dashboard" tab):

Total Value:        =SUM(F2:F20)
Total Cost Basis:   =SUM(G2:G20)
Total P/L ($):      =SUM(H2:H20)
Total P/L (%):      =SUM(H2:H20) / SUM(G2:G20)
Number of Coins:    =COUNTA(A2:A20)
Best Performer:     =INDEX(A2:A20, MATCH(MAX(I2:I20), I2:I20, 0))
Worst Performer:    =INDEX(A2:A20, MATCH(MIN(I2:I20), I2:I20, 0))

Auto-Refresh

CoinTable's free tier updates prices when you open the sheet or manually recalculate. On the Plus plan ($5/month), prices auto-refresh on a schedule — useful if you keep the sheet open as a live dashboard.

Historical Snapshots

Google Sheets doesn't natively store historical snapshots, but you can work around this:

1. Create a "History" tab
2. Each day (or week), copy-paste the current values as Paste special → Values only
3. Add the date in column A
4. Over time you build a manual history of your portfolio value

This is useful for charting portfolio growth. Pair it with a line chart on the History tab.

Common Customizations

Track staking rewards: Add a "Staked" column next to Qty. Use =D2 + M2 (held + staked) as your total quantity for value calculations.

Multi-currency view: Change =CT_PRICE(A2) to =CT_PRICE(A2, "EUR") to see everything in euros. You can even have parallel columns for USD and EUR.

DCA tracking: For a deeper DCA setup, see the dedicated DCA calculator for crypto in Google Sheets. It tracks each individual purchase and calculates your true average cost over time.

Tax prep: When it's time to report capital gains, the crypto tax tracker for Google Sheets template adds buy/sell dates and realized vs. unrealized gains.

Tips for Keeping Your Tracker Clean

1. Use named ranges: Name the ticker column tickers and the quantity column qty for more readable formulas
2. Freeze the header row: View → Freeze → 1 row, so headers stay visible when scrolling
3. Sort by value: Use Data → Sort range to keep your largest positions at the top
4. Color-code by rank: Use conditional formatting to shade large-cap coins (rank < 20) differently from small-caps
5. Number formatting: Set Value and Cost Basis columns to Currency, P/L % to Percentage with 1 decimal

Formulas Reference

Need a quick lookup for all available CoinTable formulas? The crypto formulas cheat sheet has every function with examples and sample output.

What's Next

This tracker covers the essentials: live prices, P/L, allocation, and market context. Once it's set up, you spend zero time on manual updates. Open the sheet, and the numbers are current.

If you want ready-made layouts instead of building from scratch, check out the best crypto Google Sheets templates — including a DCA calculator, watchlist, and tax helper.