Google Sheets Crypto Formulas — Complete Cheat Sheet (2026)

CoinTable Formula Reference

CoinTable, a Google Sheets add-on for cryptocurrency prices, gives you 10 formulas that cover every common crypto data point. Install it from the Google Workspace Marketplace, then use any formula below — no API key, no setup.

For a full installation walkthrough, see how to get crypto prices in Google Sheets.

All Formulas at a Glance

Every formula accepts a coin ticker as the first argument. CT_PRICE also accepts an optional second argument for the fiat currency (USD, EUR, GBP, JPY, AUD, CHF, CAD, and more).

Formula Details

CT_PRICE — Current Price

=CT_PRICE("BTC")            → 94250
=CT_PRICE("ETH")            → 3680
=CT_PRICE("SOL")            → 142
=CT_PRICE("BTC", "EUR")     → 86500
=CT_PRICE("BTC", "GBP")     → 74200
=CT_PRICE("BTC", "JPY")     → 14100000

Returns the current spot price. Default currency is USD. Use the second argument for any supported fiat currency.

With cell references (recommended for portfolio sheets):

=CT_PRICE(A2)               → price of whatever ticker is in A2
=CT_PRICE(A2, "EUR")        → same, in euros

CT_CHANGE — 24h Price Change

=CT_CHANGE("BTC")           → 2.4
=CT_CHANGE("ETH")           → 1.8
=CT_CHANGE("DOGE")          → -3.2

Returns the percentage change over the last 24 hours as a number. Positive = price went up, negative = price went down. Format the cell as a percentage or append % in a label column.

CT_MARKETCAP — Market Capitalization

=CT_MARKETCAP("BTC")        → 1850000000000
=CT_MARKETCAP("ETH")        → 442000000000
=CT_MARKETCAP("SOL")        → 62000000000

Returns the fully diluted market cap. Format cells with Format → Number → Custom → #,##0,,,"T" for trillions, or #,##0,,"B" for billions.

CT_VOLUME — 24h Trading Volume

=CT_VOLUME("BTC")           → 28500000000
=CT_VOLUME("ETH")           → 12400000000
=CT_VOLUME("SOL")           → 2100000000

Returns the total trading volume across all exchanges over the past 24 hours (in USD).

CT_RANK — Market Cap Rank

=CT_RANK("BTC")             → 1
=CT_RANK("ETH")             → 2
=CT_RANK("SOL")             → 5
=CT_RANK("DOGE")            → 8

Returns the CoinGecko market cap ranking. Useful for filtering or sorting coins by market position.

CT_SUPPLY — Circulating Supply

=CT_SUPPLY("BTC")           → 19800000
=CT_SUPPLY("ETH")           → 120500000
=CT_SUPPLY("SOL")           → 436000000

Returns the circulating supply — the number of coins currently in circulation (not max supply).

CT_ATH — All-Time High

=CT_ATH("BTC")              → 108786
=CT_ATH("ETH")              → 4878
=CT_ATH("SOL")              → 294

Returns the all-time high price in USD. Compare it with the current price to see how far a coin is from its peak:

=1 - CT_PRICE("BTC") / CT_ATH("BTC")    → % below ATH

CT_HIGH and CT_LOW — 24h Range

=CT_HIGH("BTC")             → 95100
=CT_LOW("BTC")              → 93200
=CT_HIGH("ETH")             → 3720
=CT_LOW("ETH")              → 3640

Return the highest and lowest price in the last 24 hours. Useful for gauging intraday volatility:

=(CT_HIGH("BTC") - CT_LOW("BTC")) / CT_LOW("BTC")    → 24h range as %

GOOGLEFINANCE Crypto Formulas (for Comparison)

Google Sheets has a built-in GOOGLEFINANCE function that supports a few cryptocurrencies:

=GOOGLEFINANCE("BTCUSD")        → Bitcoin price
=GOOGLEFINANCE("ETHUSD")        → Ethereum price

GOOGLEFINANCE Limitations

GOOGLEFINANCE is fine for a quick BTC or ETH price check. For anything beyond that, you need an add-on. For a deeper breakdown, see GOOGLEFINANCE crypto limitations.

Common Formula Patterns

Portfolio Value

Track the value of your holdings when the ticker is in column A and quantity in column B:

=CT_PRICE(A2) * B2

Profit & Loss

With cost basis in column C (average purchase price per coin):

P/L ($):   =CT_PRICE(A2) * B2 - C2 * B2
P/L (%):   =(CT_PRICE(A2) - C2) / C2

Portfolio Allocation

With current value in column D:

=D2 / SUM(D$2:D$20)

Distance From All-Time High

=(CT_ATH(A2) - CT_PRICE(A2)) / CT_ATH(A2)

Returns how far the coin is below its ATH as a decimal (e.g., 0.13 = 13% below ATH).

24h Volatility

=(CT_HIGH(A2) - CT_LOW(A2)) / CT_LOW(A2)

Multi-Currency Price Table

Show the same coin in multiple currencies across columns:

B2: =CT_PRICE(A2, "USD")
C2: =CT_PRICE(A2, "EUR")
D2: =CT_PRICE(A2, "GBP")
E2: =CT_PRICE(A2, "JPY")

Conditional Formatting Recipes

Green/Red Based on 24h Change

1. Select the 24h change column
2. Format → Conditional formatting
3. Rule 1: "Greater than" 0 → green background, white text
4. Rule 2: "Less than" 0 → red background, white text

Highlight Large-Cap Coins

1. Select the rank column
2. Rule: "Less than or equal to" 20 → blue background

Highlight Coins Near ATH

1. Create a column with =(CT_ATH(A2) - CT_PRICE(A2)) / CT_ATH(A2)
2. Rule: "Less than" 0.1 → gold background (within 10% of ATH)

Quick Copy-Paste Template

Set up a complete watchlist in seconds. Paste this structure into row 1, then fill tickers starting at A2:

Drag row 2 formulas down for every coin in your list. For a full portfolio tracker with P&L calculations, see the crypto portfolio tracker template.

Tips

• Cell references over hardcoded tickers: Use =CT_PRICE(A2) instead of =CT_PRICE("BTC"). This way you can add coins by editing column A without touching formulas.
• Number formatting matters: Market cap and volume return raw numbers. Use custom number formats like #,##0,,\"B\" to display them as billions.
• Free tier limits: CoinTable's free tier gives you 300 requests/month. Each formula call is one request. A sheet with 10 coins and 8 formulas per coin uses 80 requests per refresh.
• Reduce API calls: Reference the Price column instead of calling CT_PRICE multiple times for the same coin. One call per coin per data point.

For ready-made layouts that use these formulas, browse the best crypto Google Sheets templates.