Free crypto portfolio tracker spreadsheet (Excel and Google Sheets)
A crypto tracker spreadsheet has three jobs: what you hold, what it cost you, what it’s worth. Most free templates handle the last one and get the first two wrong as soon as your coins live on more than one account.
The crypto portfolio tracker spreadsheet most people put together has one row per coin: quantity, average price, today’s price. It works on one exchange. Once your coins move between Coinbase, Kraken and a Ledger, it no longer knows what you hold or what it cost you. This is the template I wish I’d had before I automated my tracking, and where it falls short.
What a crypto tracker spreadsheet needs
A good tracker keeps what happened apart from what’s left of it.
| Tab | What you enter | What it calculates |
|---|---|---|
| Transactions | one row per movement: date, account, coin, type, quantity, amount, fees | what each sale brought in |
| Portfolio | your list of coins and their price | quantity held, average cost, value, unrealized and realized gains, share of the portfolio |
There is one transaction log for all your accounts. With a tab per exchange, a bitcoin sent from Kraken to Coinbase would show up with no purchase price. That’s also why you want one cost basis per coin, across all your exchanges.
The free spreadsheet template, ready to fill in
Google Sheets makes a copy in your Drive. The .xlsx file also opens in LibreOffice.
Yellow cells are for you, grey cells calculate, and a “How to use” sheet explains every formula.
It comes pre-filled with a made-up example of ten movements. To use your own data:
- in Portfolio, type your coins in column A and their price in column B;
- in Transactions, add one row per movement, in chronological order;
- fix anything the Check column doesn’t mark “OK”.
The Transactions tab: one row per movement
Five types cover everything:
| Type | Quantity | Cost |
|---|---|---|
| Buy | goes up | goes up by the amount plus fees |
| Sell | goes down | falls in the same proportion: the average cost doesn’t move |
| Fee | goes down (network fee on a withdrawal) | doesn’t move: the average cost rises slightly |
| Reward | goes up | goes up by the value you choose, 0 or that day’s price |
| Transfer | doesn’t move | doesn’t move |
A crypto-to-crypto trade takes two rows. The example swaps 0.2 ETH for 5 SOL: a Sell of ETH for $600 and a Buy of SOL for $600. Fees paid in BNB go into the buy they belong to, in dollars, and a Sell row of BNB takes out the quantity spent.
The Check column flags an unknown coin or type, an empty cell, a sale larger than your holdings, or a date out of order.
The Portfolio tab: what you hold and what it’s worth
Each row adds up its coin’s movements with SUMIFS. For the bitcoin quantity, on row 9: =SUMIFS(Transactions!$J$5:$J$1004,Transactions!$C$5:$C$1004,A9). The remaining cost works the same way on column K. Then average cost = remaining cost ÷ quantity, value = quantity × price, unrealized gain = value − remaining cost.
Across the three coins, the portfolio is worth $5,675 for a remaining cost of $4,960: an unrealized gain of $715 (+14.4%), plus $59.05 already realized.
Unrealized, realized, and what to divide by to get a profit percentage: see realized vs unrealized crypto gains.
Why average cost isn’t a single SUMIFS
Many templates calculate average cost in one cell: total paid ÷ quantity bought. It goes wrong as soon as a sale comes before another buy.
In the example, the November 2025 sale takes out 0.01 BTC at the average cost of the time, $85,000. The January 2026 buy, at $100,000, then weighs on the 0.025 BTC still held, not on the 0.035 bought in total:
| Calculation | Bitcoin average cost |
|---|---|
| Total paid ÷ quantity bought: (1,800 + 750 + 500) ÷ 0.035 | $87,142.86 |
| Row by row, as the template does | $88,000.00 |
The one-cell version makes the bitcoin sold in November carry part of the January buy. The template instead keeps two grey columns with what was held, and what it cost, before each row. A sale takes its share out of the cost: remaining cost before × quantity sold ÷ held before.
Live prices: GOOGLEFINANCE, or a column to fill in
In Google Sheets, =GOOGLEFINANCE("CURRENCY:BTCUSD") gives bitcoin’s price in dollars, delayed by up to 20 minutes. The major coins (bitcoin, ether, solana, XRP…) have a Google Finance page. Google doesn’t publish its list, and a smaller coin returns an error.
So the template has two price columns: C asks GOOGLEFINANCE, B takes what you type and wins. In Google Sheets, clear column B and the major coins update by themselves. In Excel and LibreOffice, you type the price in. A position without a price is left out of the total value.
Where a spreadsheet hits its limits
For one exchange and a few buys a year, this template is enough, and I recommend it. It breaks as your history grows, and my own numbers show where.
- Transfers. An export shows a withdrawal on one side and a deposit on the other, never a transfer. Three quarters of mine are moves between my own accounts, each to be spotted by hand.
- Crypto-to-crypto trades. 398 of my trades involved no dollar, euro or stablecoin: two rows and a historical price each.
- Fees paid in another coin. I’ve paid fees in at least eight currencies, 144 times in BNB alone.
- Interest. Savings products at Binance, Bitget or Nexo pay interest every day: more than three quarters of my history, eight times what the template holds.
- The exports. Binance’s export columns change with your account’s language, Kraken calls bitcoin XXBT, and OKX’s API only returns 90 days of buys and sells.
- Errors you can’t see. A missing buy gives a plausible average cost, and a wrong one. The Check column can’t see missing rows.
What Crypto Master does instead
Crypto Master keeps these two tabs for you. Your exchanges connect through a read-only API key or their files, your wallets through their public address. Everything is up to date whenever you open it, with nothing to re-type. Average cost is calculated across all your accounts, transfers between them aren’t counted as buys, and daily interest goes into the calculation.
See it in the demo, no sign-up needed. The free plan covers 3 connected accounts and 500 transactions (see pricing); a history of several thousand rows needs a paid plan.
Average cost isn’t a tax calculation
Average cost is a tracking measure. Tax rules differ by country: in the US, for instance, the IRS doesn’t allow average cost for crypto. This spreadsheet doesn’t fill in a tax return.
Crypto Master handles the US version for you: lots tracked account by account, first-in first-out by default, gains split into short and long term, and an export laid out like Form 8949. Filing stays yours.
Frequently asked questions
Is there a free crypto portfolio tracker spreadsheet?
Yes, the template in this article, no sign-up needed. It calculates quantity held, average cost, value and gains.
How do I get live crypto prices in Google Sheets?
With GOOGLEFINANCE("CURRENCY:BTCUSD"), replacing BTC with the ticker you want. Only the major coins are covered.
Can Excel pull crypto prices?
Not in this template: you type prices into a column. The automatic GOOGLEFINANCE column only works in Google Sheets.
Can one spreadsheet track several exchanges?
Yes, in a single transactions tab. Sending coins from one exchange to another is a Transfer.
How do I log a crypto-to-crypto trade?
As two rows with the same dollar amount: a Sell of the coin you gave and a Buy of the coin you received.
Does this spreadsheet calculate my crypto taxes?
No. Tax rules differ by country, and in the US the IRS doesn’t accept average cost for crypto. Crypto Master builds your Form 8949 lots instead, account by account.
See the same numbers calculated automatically on a made-up portfolio, no account needed.
Open the demo
