A trading journal template is not a pretty spreadsheet. It is a controlled input form. The version that survives has four tabs, 23 columns, 13 formulas and a two-minute entry routine. Build it in Google Sheets if you want free access from any device; use Excel if you need Power Query or offline work. Here is the exact layout, the formulas and the point where an automated journal becomes the better tool, with no add-ons required.
Five things to remember
- ▸One row per closed trade, with a stable convention for partial exits.
- ▸Initial risk and R multiple are mandatory; without them, the sheet is just a list of wins and losses.
- ▸Dropdown lists beat free text: a setup must have one name only.
- ▸Statistics belong in a separate tab, never inside the data rows.
- ▸A spreadsheet becomes limiting beyond 20 to 30 trades per week, or with several accounts.
What a template can and cannot do
A good template does three jobs. It forces complete entry while the information is fresh, it calculates the core metrics without arithmetic mistakes, and it lets you filter trades by setup, session or instrument. It does not replace chart reading or the decision to change a strategy.
It does not sync with your broker either. Every trade must be entered or imported, and open positions are not updated in real time. That limitation is exactly why many traders move to a connected journal after a few months.
Treat the spreadsheet as a ritual, not a project. Spending three weekends polishing charts produces no extra information. Filling the same fields after every trade, even imperfectly, produces a comparable series. Consistent entry matters more than an elegant layout.
Step 1: workbook structure
| Tab | Purpose | Content |
|---|---|---|
| Trades | Raw data | One row per trade, no summary formulas |
| Dashboard | Summary | Win rate, expectancy, profit factor, drawdown, plan adherence |
| Lists | Reference values | Setups, sessions, directions, emotions, accounts |
| Review | Decision log | Five weekly questions and one action for next week |
Separation matters. If you mix statistics and trades on the same tab, inserting one row shifts the formulas. Keep raw data in one range, then let the Dashboard read that range.
Step 2: the exact columns
Here is the recommended layout, from A to W. Columns U to W are calculation helpers: hide them if you like, but do not delete them.
| Column | Name | Type and example |
|---|---|---|
| A | Trade ID | Text, 2026-001 |
| B | Entry | Date and time, 2026-10-12 09:32 |
| C | Exit | Date and time, 2026-10-12 11:05 |
| D | Instrument | EUR/USD, MNQ, BTC/USDT |
| E | Direction | Long or Short |
| F | Setup | Closed list, Pullback |
| G | Session | Asia, London, New York |
| H | Entry price | Number, 1.0920 |
| I | Initial stop | Number, 1.0890 |
| J | Exit price | Number, 1.0975 |
| K | Size | 0.50 lot or 2 contracts |
| L | Risk | Currency amount, 200 |
| M | Fees | Commission and swap, 4.20 |
| N | Gross P&L | Before fees, 360 |
| O | Net P&L | After fees, 355.80 |
| P | R | Net P&L divided by risk, +1.78 |
| Q | Plan followed | Yes, No, Partial |
| R | Emotion | 1 to 5 before entry |
| S | Notes | One sentence, 140 characters max |
| T | Screenshot | Link to the image |
| U | Equity | Calculated, starting balance plus cumulative net P&L |
| V | Peak | Calculated, highest equity reached |
| W | Drawdown | Calculated, equity / peak - 1 |
If a column has not supported a decision for a month, delete it. A 20-column template filled completely is better than a 35-column template filled at 40%.
Step 3: formulas that do the work
These formulas use English syntax and commas as separators. If your Excel uses a different locale, replace commas with semicolons and adjust function names. Put the first formula in row 2, then fill down.
| Cell | Formula | Result |
|---|---|---|
| O2 | =N2-M2 | Net P&L |
| P2 | =IFERROR(O2/L2,"") | R multiple |
| U2 | =Dashboard!$B$2+SUM($O$2:O2) | Cumulative equity |
| V2 | =MAX($U$2:U2) | Equity peak |
| W2 | =IFERROR(U2/V2-1,"") | Current drawdown |
| Dashboard B3 | =COUNTA(Trades!A2:A1000) | Trade count |
| Dashboard B4 | =IFERROR(COUNTIF(Trades!O2:O1000,">0")/COUNTA(Trades!O2:O1000),"") | Win rate |
| Dashboard B5 | =SUMIF(Trades!O2:O1000,">0") | Gross profit |
| Dashboard B6 | =ABS(SUMIF(Trades!O2:O1000,"<0")) | Gross loss |
| Dashboard B7 | =IFERROR(B5/B6,"") | Profit factor |
| Dashboard B8 | =IFERROR(AVERAGE(Trades!P2:P1000),"") | Expectancy in R |
| Dashboard B9 | =IFERROR(MIN(Trades!W2:W1000),"") | Maximum drawdown |
| Dashboard B10 | =IFERROR(COUNTIF(Trades!Q2:Q1000,"Yes")/COUNTA(Trades!Q2:Q1000),"") | Plan adherence |
Dashboard cell B2 holds your starting capital, for example 10,000. Column U adds cumulative net P&L to that balance. Column W gives current drawdown as a percentage of the peak; the MIN formula finds the lowest point, which is maximum drawdown.
Step 4: data validation and conventions
Journal quality is decided before analysis. A poorly built dropdown creates invisible variants: "Pullback", "pull-back" and "PB" become three different setups. Create the lists in the Lists tab, then use Data > Data validation to apply them to columns E, F, G, Q and R.
- ▸Direction: two values only, Long and Short.
- ▸Setup: a closed list of 8 to 12 names, never free text.
- ▸Session: Asia, London, New York, London-New York overlap.
- ▸Plan followed: Yes, No, Partial. When in doubt, use Partial, not Yes.
- ▸Emotion: whole number from 1 to 5, with each level defined in Lists.
- ▸Dates: YYYY-MM-DD format, to avoid ambiguity between 03/04 and 04/03.
- ▸Instrument: one ticker per market, without spaces or broker names.
Add simple conditional formatting: R below -1 in red, plan not followed in orange, winning trades in light green. You will spot anomalies without filtering.
The most common validation mistake is allowing a blank setup. A blank cell is not neutral: it silently removes the trade from every setup comparison. Make the field required, and add a final check in the Review tab so missing categories are found before the weekly analysis, not three months later.
Step 5: a trading journal example in four rows
| Date | Instrument | Direction | Setup | Risk | Net P&L | R | Plan |
|---|---|---|---|---|---|---|---|
| 2026-10-12 | EUR/USD | Long | Pullback | $200 | +$360 | +1.80 | Yes |
| 2026-10-13 | MNQ | Short | Breakout | $150 | -$165 | -1.10 | No |
| 2026-10-14 | EUR/USD | Long | Pullback | $200 | -$200 | -1.00 | Yes |
| 2026-10-15 | MNQ | Long | Range | $150 | +$240 | +1.60 | Yes |
Total: +$235 net across four trades, two of them winners. Win rate is 50%, profit factor is 600 / 365 = 1.64 and expectancy is $58.75 per trade, or +0.325R. The numbers are deliberately small: they show that a spreadsheet calculates quickly, but not whether the trades followed the plan. That is column Q's job.
Do not draw conclusions from that total. Four trades cannot validate a setup, let alone a strategy. The table is there to check that formulas work and categories are consistent. Real analysis starts after several dozen rows, not after a successful test import.
Step 6: pivot tables
A pivot table turns rows into an answer. In Google Sheets, select Trades!A1:W1000, then Insert > Pivot table. Put Setup in rows, then add three values: COUNT of Trade ID, AVERAGE of R and SUM of Net P&L. Add Session as a filter to compare London and New York.
In Excel, select the range, then Insert > PivotTable. The principle is the same. Refresh after each import; if you use a structured table, the range expands automatically. Do not build one pivot per question: start with Setup, then add Instrument or Session only if the first split reveals a difference.
A segment with fewer than 30 trades is a lead, not a conclusion. Always show trade count next to the average: +0.8R over 6 trades is not worth +0.2R over 90 trades.
The limits of Excel and Google Sheets
- ▸Manual entry takes time and introduces typos.
- ▸Formulas shift when a column is inserted or a row is deleted.
- ▸Open positions are not tracked, so current risk stays invisible.
- ▸Screenshots often live elsewhere, with links that break.
- ▸Reconciliation with the broker statement is manual.
- ▸Sharing a file across several people creates competing versions.
- ▸Fees, swaps and currency conversion require constant attention.
- ▸A spreadsheet sends no alert when a risk limit is close.
These limits do not make spreadsheets useless. They simply mark the point where maintenance cost exceeds the benefit. If you spend more time fixing formulas than analyzing trades, the template is no longer the solution.
A simple test: try to answer one specific question, such as the expectancy of your London pullback trades, in under a minute. If it takes three filters, a broken link and a manual count, the tool is already costing you more than it returns. That is the signal to migrate, not the number of rows alone.
Migrating to an automated journal
Migration starts with a CSV export from your broker or platform. Most platforms let you export closed position history; start with the last 50 trades to check column mapping. Import, compare ten rows against the official statement, then archive the spreadsheet as read-only.
Mapping is the fragile step. Platforms do not use the same names for entry price, commission or position direction. Write the mapping in a separate file and check the sign of short positions: an inverted sign turns a winning strategy into a statistical disaster.
The free trading journal keeps the essential columns and calculates win rate, profit factor, expectancy, drawdown and results by setup automatically. CSV import is free. If you trade on MetaTrader 5, the MT5 connector is also free and syncs closed positions in read-only mode. For cTrader, the plugin is distributed through the cTrader Store and may be a paid add-on depending on the offer: check the price on the Store listing.
Keep a notes column and a screenshot link anyway. Automation is excellent with numbers, not with context. An imported trade without a comment remains an anonymous trade, and the analysis loses half its value.
Sources
- ▸Google Workspace, create a pivot table in Google Sheets.
- ▸Microsoft Support, create a PivotTable in Excel.
- ▸Microsoft Support, SUMIF function for conditional sums.
- ▸MetaQuotes, MetaTrader 5 Help: trade history and reports.
- ▸Spotware, cTrader Open API: official connector documentation.
Links: Google Sheets pivot tables, Excel pivot tables, SUMIF, MetaTrader 5 Help, cTrader Open API.
FAQ
What does a trading journal example look like? One row per trade with the date, instrument, direction, setup, risk, net P&L, R multiple and plan adherence. The example above shows four trades; your template should above all keep the same columns for every row.
Google Sheets or Excel? Both work. Google Sheets is free, accessible from your phone and easy to share. Excel is stronger for large histories, Power Query and offline work. Choose based on how you work, not on the number of available functions.
How many columns do I need? About twenty is enough. Columns A to T cover entry and context; U to W calculate drawdown. Add a column only when it answers a question you actually ask.
How should partial exits be handled? Two conventions work: one row per execution, or one row per position using an average exit price. The second is simpler for statistics, the first is more faithful to execution. Pick one and document it in the Lists tab.
Should R use the initial stop or the moved stop? The initial stop. It represents the risk actually accepted when the decision was made. If you moved the stop, record that in the notes: it is a management decision, not a redefinition of risk.
When should I leave the spreadsheet? When entry takes more than 30 to 40 minutes per week, when you track several accounts, or when you fix more formulas than you analyze trades. Moving to a connected journal then saves time rather than adding a gadget.
Test the template: copy the columns, enter your last ten trades, then compare the total with your statement. If the difference is zero, the template is ready. If not, fix it before adding statistics. To start without a spreadsheet, open the free journal; for the next step, read our guide to trading performance analysis.