Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
You can build a stock heat map in Google Sheets with GOOGLEFINANCE and conditional formatting: put each ticker on a row, calculate its daily percentage change, then color that column from red through neutral to green. For a more visual market map, you can also use a Treemap chart, with rectangles sized by market capitalization. The table is easier to compare precisely; the treemap is better for showing relative size.
Google Sheets does not have a dedicated stock-heat-map command. These methods combine its stock-data functions, formatting rules, and chart tools. Google Finance quotes may be delayed by up to 20 minutes, and some exchanges, securities, or attributes may not be supported, so treat the result as a dashboard—not a trading feed.
Choose a colored table or a treemap
A stock heat map can mean two different things:
- Conditional-formatting table: Each stock remains a row in a normal table, and a numeric column—usually daily percentage change—is shaded according to its value. This is the best starting point for a watchlist because labels and exact numbers stay easy to read.
- Treemap: Stocks appear as nested rectangles, often grouped by sector. Rectangle size represents market capitalization or position value; color represents performance. This is compact and visually striking, but precise comparisons are harder.
Use percentage change for color rather than share price. A $500 stock is not necessarily performing better than a $10 stock; percent change makes the size of their moves comparable. Google lists conditional-formatting color scales and Treemap among Sheets’ supported features (conditional formatting; chart types).
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Build a stock data table
Start with a sheet containing one stock per row. For example:
#1 Best Overall
| Ticker | Company | Sector | Price | Change % | Market cap |
|---|---|---|---|---|---|
| NASDAQ:GOOG | Alphabet | Communication Services | Formula | Formula | Formula |
| NASDAQ:AAPL | Apple | Technology | Formula | Formula | Formula |
Use an exchange-qualified ticker where possible, such as NASDAQ:AAPL, rather than relying on a bare symbol. Exchange and ticker conventions differ, and qualification helps avoid ambiguous matches. Enter company and sector labels yourself if needed; do not assume every desired detail is available from the quote function.
Pull quotes with GOOGLEFINANCE
In D2, retrieve the price:
=IFERROR(GOOGLEFINANCE(A2,"price"),"")In E2, retrieve daily percentage change:
=IFERROR(GOOGLEFINANCE(A2,"changepct")/100,"")Format column E as a percentage, then fill both formulas down your list. The division by 100 normalizes a result such as 1.25 to 0.0125, which percentage formatting displays as 1.25%. Check the raw result in your sheet before relying on this conversion: locale and cell formatting can affect what you see, and applying the conversion or percentage format twice can produce an incorrect display.
In F2, retrieve market capitalization if available:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →=IFERROR(GOOGLEFINANCE(A2,"marketcap"),"")Fill down. Attributes are not available for every symbol. Other documented attributes include previous-day close, absolute change, opening price, volume, and P/E ratio. For example:
Rank #2
- Comes with secure packaging
- Easy to read text
- It can be a gift option
=IFERROR(GOOGLEFINANCE(A2,"volume"),"")See Google’s GOOGLEFINANCE documentation for syntax, available attributes, coverage limitations, and data qualifications.
Create the color-scale heat map
- Select the percentage-change cells, such as
E2:E100. Do not apply this gradient across the whole table: ticker names, prices, and market caps are different kinds of values and would produce misleading colors. - Choose Format → Conditional formatting.
- In the side panel, choose Color scale.
- Set the minimum color to red, the midpoint to white or pale gray, and the maximum to green.
- Set the midpoint value to
0, then click Done.
A zero midpoint makes unchanged stocks neutral and separates gains from losses. A scale whose midpoint is based on the list’s average can make a flat stock look positive or negative just because the rest of the list moved. Google’s conditional-formatting instructions explain color-scale settings and formula-based rules.
Keep the percentage visible next to its color. Color alone is difficult to compare and may be inaccessible to readers who have trouble distinguishing red and green. Use a legend, consider a muted or color-blind-friendlier diverging palette, and check that the table still makes sense in grayscale if it may be printed.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Optionally highlight entire rows
A gradient in the change column conveys both direction and magnitude. If you also want a simple positive-or-negative cue across each row, select A2:F100, open Format → Conditional formatting, and add a custom-formula rule:
=$E2<0Choose a pale red fill. Add a second rule with:
=$E2>0Choose a pale green fill. The dollar sign fixes the reference to column E while the row number changes for each row. Keep these fills subtle: a binary row highlight should supplement, not replace, the change-column gradient.
Make the table easier to read
- Freeze the header row and format the price as currency.
- Format percentage change as a percentage and market capitalization consistently, for example in millions or billions.
- Sort by sector, market capitalization, or percentage change, depending on what you are trying to see.
- Add a clear legend, such as “red: down; neutral: near 0%; green: up.”
- Keep the underlying numbers visible so a color is never the only way to understand a value.
- Consider fixed scale limits if one unusually large move makes all other cells look nearly identical. For example, a range from -10% to +10% can make ordinary moves easier to distinguish, but values beyond those limits will share the extreme colors.
Build a market-cap treemap
For a market-map-style dashboard, create a separate chart-ready table with a parent category, stock label, size value, and performance value:
| Sector | Stock | Market cap | Change % |
|---|---|---|---|
| Technology | Apple | Formula | Formula |
| Technology | Microsoft | Formula | Formula |
| Communication Services | Alphabet | Formula | Formula |
- Select the chart-ready data.
- Choose Insert → Chart.
- In the Chart editor, choose Treemap as the chart type.
- Use sector and stock as the hierarchy, with market capitalization as the size measure.
- If the editor offers a separate color-data setting, assign percentage change to it and set a diverging scale with zero as the neutral point.
- Add a descriptive title and explain what the color scale means.
Sheets supports Treemap charts, and its chart model describes separate size data, color data, and a color scale. However, the user-facing editor may not expose all those controls uniformly. If you cannot assign percentage change separately from size, use the conditional-formatting table for a reliable performance heat map instead. Google’s general workflow for adding a chart is select data, then choose Insert → Chart.
Free tools Windows power users keep installed
One-click scans. No signup required.
A market-cap treemap shows the relative size of companies, not the importance of each holding in your own account. Large companies dominate the visual area, and small ones may become too tiny to read. For a portfolio map, use position value instead of total company market capitalization if your goal is to show how your own money is allocated.
Rank #4
Use historical returns instead of today’s move
To examine a past period, request a date range. For example, this formula retrieves daily prices for a ticker over roughly the past 30 days:
=GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-30,TODAY(),"DAILY")Historical queries return an expanded array with headers, rather than a single price cell. Calculate a period return from the start and end prices, for example (LatestPrice-StartPrice)/StartPrice, and use that return as the heat-map value. Label the dates clearly; a historical map is not a picture of today’s market. Google also documents limitations on accessing historical GOOGLEFINANCE data through the Sheets API or Apps Script.
Fix common problems
GOOGLEFINANCE returns #N/A or a blank
Check that the ticker is spelled correctly and includes an exchange, then test a known symbol and the simpler "price" attribute. Some exchanges, securities, or attributes are unsupported; data can also be temporarily unavailable or absent for a non-trading day. IFERROR hides the visible error but does not fix missing data. If the instrument is not covered, use a suitable external source or enter a fixed snapshot manually.
The percentage looks 100 times too large
Inspect the raw changepct result in an unformatted cell. If it is supplied as a number such as 1.25, dividing by 100 and then applying percentage formatting should display 1.25%. Normalize it once and format it once; do not apply both a second division and a second percentage conversion.
Best Value
Market cap is missing or treemap boxes look wrong
Some symbols have no market-cap result. Check for blanks or zeroes in the size column and remove or replace those entries before charting. A blank or zero size can make a rectangle disappear or distort the map. If the size data is incomplete, the table remains usable without a treemap.
All the colors look almost the same
An extreme outlier can stretch an automatic scale. Set fixed minimum and maximum values, use separate scales for distinct groups, or keep the full numeric return beside the color. Fixed limits improve comparison within a chosen range but can make unusually large moves appear no more extreme than the limit color.
The market is closed or the data seems stale
Daily change is measured against the previous trading day’s close. On weekends and market holidays, values may reflect the last completed session or be unavailable. Google says quotes may be delayed by up to 20 minutes; a formula-driven sheet should not be treated as an instant update or execution signal. Add a “data as of” note where timing matters.
International stocks do not work
Google documents incomplete market coverage and says GOOGLEFINANCE is available in English and does not support most international exchanges. Do not assume that every global listing or attribute will resolve. Test the exact symbol, and use another appropriate data source for unsupported securities.
Know what the heat map can—and cannot—tell you
A daily-change heat map answers a narrow question: which listed stocks rose or fell most over the period represented by the data? It does not tell you which company is undervalued, which holding contributes most to portfolio risk, whether a move reflects a corporate action or bad data, or what to buy. A performance color is a visualization, not investment analysis.
Use a dedicated market-data or charting service if you need guaranteed exchange coverage, tick-level or exchange-grade real-time quotes, intraday alerts, adjusted historical data, or professional portfolio accounting. For a personal watchlist, Sheets is useful when you accept its data limits and verify important figures through an appropriate source. Google describes GOOGLEFINANCE data as informational and not intended as trading advice; verify prices, corporate actions, coverage, and fundamentals before making financial decisions.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
