Google Sheets has three genuine advantages over Excel for a trading journal. It’s free with no software install required. It syncs across devices automatically, so the journal is accessible from any machine with a browser. And it has the QUERY function, a single formula that produces a complete setup performance table (trade count, win rate, average R-multiple, profit factor per setup) that would require a pivot table and half a dozen SUMIFS to replicate in Excel.
That last point is the reason this guide exists as a separate article rather than a footnote on the Excel version. The sheet architecture is nearly identical. The column structure is the same. The core formulas (AVERAGEIF for expectancy, SUMIF for profit factor) transfer directly. But QUERY and ARRAYFORMULA are Sheets-native features that change how the journal is built and maintained in ways worth covering specifically.
This guide assumes no prior experience with Google Sheets beyond knowing how to open a spreadsheet. The formulas are exact and usable. The structure produces the same five diagnostic metrics covered in the trading journal template guide: expectancy, profit factor by setup, win rate, rule compliance rate, and drawdown by session.
Sheet Structure: Four Tabs and What Each One Does
Create a new Google Sheets workbook and set up four sheets using the tabs at the bottom.
Trades is the main log. Every trade gets one row. This is the only sheet where data is entered manually. All other sheets pull from it using formulas.
Dashboard displays performance metrics and charts. Nothing is typed here. If a number looks wrong on the Dashboard, the fix happens in the Trades sheet. Keeping data entry and data display on separate sheets prevents the most common form of journal corruption: accidentally overwriting a formula cell while entering a trade.
Lists stores the dropdown options used in the Trades log. Setup tags, market condition categories, direction, rule compliance reasons. Storing these here means updating a dropdown requires one change in one place. Sheets propagates it everywhere the dropdown is used automatically.
Broker Export is optional but worth setting up if the broker provides downloadable CSV trade data. Paste or import raw broker data here and use IMPORTRANGE or direct cell references to pull specific fields into the Trades log. This keeps raw import data separate from the clean, manually structured log and prevents formatting conflicts.
The Trades Log: 21 Columns With Sheets-Specific Setup Notes
The column structure mirrors the Excel guide exactly. The setup process differs in a few places.
Column A: Trade # In Sheets, use =ARRAYFORMULA(IF(B2:B<>"",ROW(B2:B)-1,"")) in cell A2. This auto-numbers every row that has a date in Column B without needing to drag the formula down. The ARRAYFORMULA handles the entire column from a single cell.
Column B: Date Format via Format, Number, Date. Google Sheets date formatting uses the same DD/MM/YYYY or MM/DD/YYYY options as Excel. Pick one format and apply it to the entire column at once by selecting the column header before formatting.
Column C: Time Format as Time via Format, Number, Time. Entry time in HH:MM. Required for session-based analysis. Without it, identifying whether losses cluster at the open, midday, or close is not possible.
Column D: Instrument Plain text. No formatting needed.
Column E: Direction Dropdown validation in Sheets works via Data, Data Validation, Dropdown (from a range). Set the source range to the Direction column on the Lists sheet. Long and Short are the two options. Do not type them directly into the validation dialog. Reference the Lists sheet so changes propagate automatically.
Column F: Setup Tag Dropdown validation from the Lists sheet, same method as Direction. This is the most analytically important field in the log. The setup names should reflect actual setups being traded. Generic categories like “Breakout” are less useful than specific ones like “Opening Range Breakout” or “VWAP Reclaim”, because the QUERY formula later will group by exactly these values, and vague categories produce vague output.
Column G: Entry Price Format as currency. In Sheets: Format, Number, Currency.
Column H: Initial Stop Currency format, same as Entry Price. Log this at the moment of entry. A stop level estimated or adjusted after the trade closes is not reliable data. It drifts toward wherever price actually went.
Column I: Position Size Number format, no currency. Shares, contracts, or lots depending on instrument.
Column J: Market Condition Dropdown from Lists sheet. Four options: Trending, Ranging, Choppy, News-Driven. More than four and consistency breaks down across months of logging.
Column K: Pre-Trade Confidence Number format, values 1 through 5. Use data validation to restrict to integers in that range: Data, Data Validation, Number, Between, 1, 5.
Column L: Exit Price Currency format. Left blank for open positions.
Column M: Exit Date Date format. Left blank for open positions.
Column N: Gross P&L This is where ARRAYFORMULA earns its place. In cell N2, enter:
=ARRAYFORMULA(IF(L2:L<>"",IF(E2:E="Long",(L2:L-G2:G)*I2:I,(G2:G-L2:L)*I2:I),""))
This single formula calculates Gross P&L for every row in the sheet automatically. No dragging required. New trades added to the Trades sheet get their P&L calculated immediately without touching the formula.
Column O: Commission Currency format. Logged manually at trade close. Total round-trip commission per trade.
Column P: Net P&L =ARRAYFORMULA(IF(N2:N<>"",N2:N-O2:O,""))
Column Q: Initial Risk (R) =ARRAYFORMULA(IF(H2:H<>"",ABS(G2:G-H2:H)*I2:I,""))
Column R: R-Multiple =ARRAYFORMULA(IF(Q2:Q<>"",N2:N/Q2:Q,""))
This is the most important calculated field in the log. Every trade outcome expressed as a multiple of initial risk. A 2R trade returned twice what was risked. A -0.5R trade lost half the initial risk before the stop was hit. R-multiples are what expectancy is calculated from.
Column S: Rule Compliant Dropdown from Lists sheet. Yes or No.
Column T: Rule Broken Dropdown from Lists sheet. Entry Criteria, Position Sizing, Stop Placement, Trade Management, Early Exit. Only populated when Column S is No.
Column U: Notes Free text. The only column in the log where free text is appropriate, because qualitative observations don’t need to aggregate across trades. They need to be readable in context.
The ARRAYFORMULA entries in Columns A, N, P, Q, and R mean the calculated columns are fully automated. After initial setup, the only manual inputs per trade are Columns B through M (at entry and exit) and Columns S, T, and U.
The Formulas That Matter: QUERY First
The core metric formulas (expectancy, profit factor, win rate, rule compliance) work identically to the Excel versions. The one that doesn’t exist in Excel is QUERY, and it’s the reason Sheets produces setup-level analysis faster than any other spreadsheet tool.
QUERY: The Setup Performance Table in One Formula
Place this on the Dashboard sheet, referencing the Trades sheet:
=QUERY(Trades!E:R,"SELECT E, COUNT(E), COUNTIF(N, '>'&0)/COUNT(E), AVG(R), SUMIF(N,'>'&0,N)/ABS(SUMIF(N,'<'&0,N)) WHERE E <> 'Direction' AND L <> '' GROUP BY E LABEL E 'Setup', COUNT(E) 'Trades', COUNTIF(N,'>'&0)/COUNT(E) 'Win Rate', AVG(R) 'Avg R', SUMIF(N,'>'&0,N)/ABS(SUMIF(N,'<'&0,N)) 'Profit Factor'")
This single formula produces a table with one row per setup showing trade count, win rate, average R-multiple, and profit factor, updated automatically every time a new trade is added to the Trades sheet. In Excel, producing the same output requires a pivot table and a separate SUMIFS formula for every metric in every setup row.
The WHERE clause filters out the header row and excludes open trades (those with no exit date). Adjust the column references if the column layout differs from the structure above.
Expectancy
=AVERAGE(Trades!R2:R1000)
Filters blank cells automatically in Sheets. A positive result confirms the strategy has an edge. Above 0.3R per trade is meaningful. Below 0.1R and the strategy is marginal.
Per-setup expectancy:
=AVERAGEIF(Trades!F2:F1000,"Opening Range Breakout",Trades!R2:R1000)
Replace the setup name with a cell reference to make this reusable across a table of setups.
Profit Factor
=SUMIF(Trades!N2:N1000,">"&0,Trades!N2:N1000)/ABS(SUMIF(Trades!N2:N1000,"<"&0,Trades!N2:N1000))
Works identically to the Excel version. Above 1.5 is a workable edge. Above 2.0 is strong. The QUERY formula above calculates profit factor per setup automatically, so this account-level version sits in the summary metrics block on the Dashboard alongside overall expectancy and win rate.
Win Rate
=COUNTIF(Trades!N2:N1000,">"&0)/COUNTA(Trades!N2:N1000)
Format as percentage.
Rule Compliance Rate
=COUNTIF(Trades!S2:S1000,"Yes")/COUNTA(Trades!S2:S1000)
Format as percentage. The most behaviorally important metric in the journal. A compliance rate below 80% during a losing streak points to execution problems, not strategy problems. The response to each is completely different.
The Dashboard Tab
The Dashboard has three components: a summary metrics block, an equity curve, and the QUERY-driven setup performance table.
The summary metrics block sits at the top. Five cells pulling from the Trades sheet via the formulas above: overall expectancy, profit factor, win rate, total net P&L, and rule compliance rate. Label each cell clearly. Format expectancy to two decimal places, win rate and compliance as percentages, P&L as currency.
The equity curve uses a running cumulative sum of Net P&L. On the Trades sheet, add a helper column (Column V) with:
=ARRAYFORMULA(IF(P2:P<>"",MMULT(IF(ROW(P2:P)>=TRANSPOSE(ROW(P2:P)),1,0),IF(P2:P<>"",P2:P,0)),""))
This produces a running total without needing a separate formula in each row. On the Dashboard, create a line chart using Column B (dates) as the X axis and Column V as the Y axis. The result is an equity curve that updates automatically as trades are added.
For a more compact option, the SPARKLINE function embeds a miniature equity curve directly inside a Dashboard cell:
=SPARKLINE(Trades!V2:V1000,{"charttype","line";"color","#2563eb"})
One cell, no chart setup required. Useful for a summary view where space is limited.
The setup performance table is produced by the QUERY formula above. Place it below the metrics block. It updates automatically. No maintenance required after initial setup.
Skip pie charts showing trade distribution by setup. They have no diagnostic value. The QUERY table already shows which setups are profitable and by exactly how much. A pie chart showing 35% of trades were Opening Range Breakouts adds nothing to that information.
ARRAYFORMULA: What It Does and Where to Use It
ARRAYFORMULA is a Sheets-native function with no Excel equivalent. It applies a formula to an entire range of cells from a single entry point, rather than requiring the formula to be copied into each row individually.
In a trading journal, this matters for one practical reason: the journal should never require maintenance. New trades should be added to the Trades sheet and have their calculated fields populated automatically, without the trader needing to drag formulas down after each entry. ARRAYFORMULA makes that possible.
The columns that benefit from ARRAYFORMULA are all the calculated ones: Trade # (Column A), Gross P&L (N), Net P&L (P), Initial Risk (Q), and R-Multiple (R). The formulas for these are written once in row 2 of each column and left alone permanently.
The columns that should not use ARRAYFORMULA are the manually entered ones. Applying ARRAYFORMULA to a column where data is typed will cause conflicts. The formula output will overwrite or block manual entries. Keep ARRAYFORMULA strictly to calculated columns.
One limitation: ARRAYFORMULA formulas are somewhat harder to read and debug than standard row-level formulas. If a calculation looks wrong, check the ARRAYFORMULA in the header row of the affected column rather than looking at individual cells. The error, if there is one, will be in the formula itself, not in the data.
Sharing, Collaboration, and Version History
These three features are where Sheets has a genuine structural advantage over Excel for trading journals.
Sharing a view-only link lets a trading mentor, coach, or accountability partner see the journal in real time without being able to edit it. In Excel, this requires exporting a file and emailing it. In Sheets, it’s one click: Share, change access to “Anyone with the link,” set role to Viewer. The link can be revoked at any time.
Real-time collaboration means two traders can review the journal simultaneously, one navigating the Dashboard and one looking at the Trades log, with changes visible to both instantly. This is genuinely useful for traders who work with a coach who wants to annotate trades or add comments directly in the journal.
Version history is automatic and unlimited. Every change to the Sheets workbook is recorded. If a formula gets accidentally overwritten or a week of trades gets deleted, the full history is accessible via File, Version History, See Version History. Restoring a previous version takes seconds. Excel requires manual backups to achieve anything close to this.
When to Leave Sheets Behind
The threshold for moving to dedicated software is the same as for Excel: broker auto-import availability and trade frequency above 30 per month.
At that volume, manual data entry in Sheets takes 90 minutes per month at minimum. More importantly, the friction of manual entry is what causes journaling habits to degrade. Entries get delayed, notes get abbreviated, and the rule compliance flag gets skipped because filling in the full row after a long session feels like too much work.
TraderSync and TradeZella both support broad broker auto-import and handle the quantitative fields automatically. The trader’s manual input after auto-import is limited to setup tag, market condition, confidence rating, and notes: the fields that require judgment rather than data entry. Edgewonk has the most flexible custom field system for traders who need setup-level analysis beyond what standard platforms provide. A full comparison across platforms is at the best trading journals page.
For traders not yet at that threshold, a Sheets journal built to the spec in this guide is a legitimate long-term tool. The trading journal Excel template guide covers the same structure for traders who prefer local storage and the Excel formula ecosystem. The trading journal template guide explains the field logic and asset-class-specific column variations in more depth.
FAQ
Do the formulas in this guide work on mobile Google Sheets?
The formulas work, but the data entry experience on mobile is poor enough to be a practical problem. Dropdown validation fields are harder to navigate on a touchscreen, and ARRAYFORMULA outputs occasionally display incorrectly on mobile before refreshing. The recommended approach is to log trades on desktop or laptop and use mobile access for read-only Dashboard review between sessions.
How is the QUERY formula adjusted for a different column layout?
The column letters in the QUERY formula must match the actual column positions in the Trades sheet. If Setup Tag is in Column F and R-Multiple is in Column R, the formula references F and R. If the layout differs, adjust the SELECT and WHERE clauses accordingly. The easiest way to verify: check the column letter at the top of each column in the Trades sheet and map it to the corresponding field name before writing the QUERY.
Can IMPORTRANGE pull trade data directly from a broker export sheet?
Yes, with caveats. IMPORTRANGE can pull specific columns from a separate Sheets workbook, which is useful if the broker provides a Google Sheets export link rather than a CSV. The syntax is =IMPORTRANGE("spreadsheet_url","SheetName!A:Z"). The limitation is that broker export formats rarely match the Trades log column structure exactly, so some manual mapping or a helper column with reformatting formulas is usually required. For most traders, pasting broker CSV data into the Broker Export tab and manually copying the relevant fields into the Trades log is simpler and less error-prone.
What happens to ARRAYFORMULA columns when rows are sorted or filtered?
Sorting the Trades sheet by a column (date, setup type, or P&L) can disrupt ARRAYFORMULA outputs because the formula anchors to specific row positions. The safest approach: never sort the Trades sheet directly. Instead, use the QUERY formula on the Dashboard or a separate Analysis sheet to produce sorted or filtered views of the data. The Trades sheet stays in chronological entry order, and all sorting happens in read-only formula outputs elsewhere.
Is there a limit to how many trades the journal can handle?
Google Sheets handles up to 10 million cells per workbook. At 21 columns per trade, that’s roughly 475,000 rows of trade data, more than any active trader will accumulate. Performance does slow with very large datasets, particularly for QUERY formulas operating over thousands of rows. At above 5,000 trades, consider splitting historical data into an archive sheet and keeping the active Trades sheet to the current year only.
Should the journal be shared with a broker or third party for tax purposes?
No. The trading journal is a performance analysis tool, not a tax document. Brokerage statements and official trade confirmations are the appropriate records for tax purposes. The journal figures will differ from brokerage records in some cases (different commission calculations, open trade inclusion) and should not be used as a substitute for official documentation. For tax record-keeping, the broker’s annual statement is the authoritative source.