Format a P&L in Excel So the Numbers Read
By Mark Fulton · 2026-09-22 · 11 min read
To format a P&L in Excel, apply five conventions: indent line items one level under their section so hierarchy is visible without reading, put a single accounting underline on the last figure above each subtotal and a double accounting underline under net profit only, show negatives in parentheses with the code #,##0_);(#,##0);"-"_), state the units once in the heading (for example "$ thousands") instead of repeating a currency symbol in every cell, and bold only the profit lines a reader is hunting for. None of it changes a single number. All of it changes whether someone can read the statement in thirty seconds.
A profit and loss statement carries more formatting convention per square inch than any other business document. The indent tells you what belongs to what. The rule tells you a figure is derived. The parentheses tell you it gets subtracted. You can learn the whole set in ten minutes.
Templates bake these conventions into someone else's file. This post applies them to yours: the P&L your accounting software exported, or the one you built yourself.
What do the indents in a P&L mean?
Indentation is the P&L's table of contents. Each level answers "what is this a part of?" so the reader never has to work it out from the labels.
A small business P&L needs three levels at most:
- Level 0: section headings (Revenue, Operating expenses) and the profit lines (Gross profit, Operating profit, Net profit). These sit flush left.
- Level 1: line items inside a section (Product sales, Salaries, Rent).
- Level 2: occasionally, a breakdown under a line item. Anything deeper belongs on a supporting schedule.
Apply indents with Home → Alignment → Increase Indent, or set an exact level in the Format Cells dialog (Ctrl+1 on Windows, ⌘+1 on Mac) under Alignment → Indent. Never indent by typing spaces into the label. Spaces break lookups and any SUMIFS that matches on the account name.
The subtle rule is that a subtotal sits at the level of its section, not its items. "Total revenue" lines up with "Revenue", one step left of "Product sales". That outdent is how the eye knows the list has ended.
Where do single and double rules belong?
Rules on a P&L are not decoration and not gridlines. They are arithmetic notation, and there are exactly two of them:
- A single rule goes under the last figure before a subtotal. It means "the numbers above this line add up to the number below it".
- A double rule goes under the final result, net profit, and nowhere else. It means "this is the bottom line", literally.
The rule goes under the figures, not under the labels. A border across the whole row, label column included, reads as a table divider. A rule only in the number columns reads as a sum.
Use accounting underlines rather than cell borders for the rules. Excel has four underline styles, and two of them were built for this job. Microsoft describes the single and double accounting underline as an underline that fills the width of a cell, set from the Font dialog launcher on the Home tab, in the Underline box. Because it is font formatting, it travels with Format Painter and cell styles along with the number. In practice the line also stops just short of the cell edges, so two adjacent period columns get two separate rules instead of one line running across the page, which is exactly how a printed statement looks. A bottom border works too, and it is the portable choice if the file will also be opened in Google Sheets.
A comparison column gets the same rules on the same rows, because the rules belong to the row's arithmetic.
Why are negatives shown in parentheses?
Because a minus sign is the smallest character in the column and the most important one. Parentheses wrap the whole number, so a negative is visible even when you are scanning a column from arm's length.
The code for a P&L body column is:
#,##0_);(#,##0);"-"_)
Reading it section by section: positives get a thousands separator and no decimals, followed by _), which reserves a space the width of a closing parenthesis. Negatives are wrapped in parentheses. Zeros print as a dash. Microsoft's guidelines for customizing a number format spell out both halves: a format has up to four sections separated by semicolons (positive, negative, zero, text), and an underscore followed by a right parenthesis makes positive numbers line up with negative numbers enclosed in parentheses. Without that _), every positive figure sits one character to the right of the negatives and the column looks ragged. The same guidelines note that a color such as [Red] must be the first item in its section, if you want one.
Google Sheets reads the same four-part structure and supports _ as a space the width of the next character, per Google's number format help. Enter the code at Format → Number → Custom number format.
Parentheses only work if you pick one sign convention and hold it. There are two respectable options:
- Signed. Costs are stored as negative numbers and display in parentheses. Every subtotal is a plain SUM of the lines above it, and the statement adds straight down. This is the convention I use in the example below, because it makes the formulas impossible to get backwards.
- Labelled. Costs are stored as positive numbers under a heading that says they are costs, and the subtotal formula subtracts them. Parentheses are then reserved for results that are genuinely negative, like an operating loss.
Both are fine. Mixing them is the mistake, because then parentheses might mean "a cost" or "something went wrong". The syntax behind all four sections, and what the built-in Accounting format adds, is in the Excel number formatting guide.
How should the units be stated once?
A P&L opens with a three-line heading: who, what, and when.
Example Trading Co.
Profit and loss statement
Year ended 31 December 2026 ($ thousands)
The units live in that heading, or in the column header ("2026 $000"), once. A dollar sign on every line is a dozen identical glyphs competing with the digits.
The printed-statement convention allows one exception: a currency symbol on the first figure in the column and on the final total. That tells a reader who lands in the middle of the page what they are looking at. Give those two cells $#,##0_);($#,##0);"-"_) and everything else the plain code.
To show thousands without dividing your data, use the scaling comma. A comma that follows a digit placeholder scales the number by 1,000, per the same Microsoft guidelines, so #,##0,_);(#,##0,);"-"_) displays 812,400 as 812 while the cell still holds 812,400. The full treatment of scaling is in the number formatting guide. The catch is that scaled figures are rounded for display, so they will not always appear to add up. Two lines of 1,499 each display as 1 and 1, and their total of 2,998 displays as 3. Your formulas are right. If someone will check the addition by eye, either round the source figures with ROUND before they reach the P&L, or add a note that figures may not sum due to rounding, as published statements do.
The worked example: twelve lines, before and after
Here is a twelve-line P&L as it often arrives: numbers in whole dollars, General format, minus signs, every label flush left. The figures are illustrative round numbers.
Before
| A | B | |
|---|---|---|
| 1 | Product sales | 800000 |
| 2 | Services | 200000 |
| 3 | Total revenue | 1000000 |
| 4 | Cost of sales | -400000 |
| 5 | Gross profit | 600000 |
| 6 | Salaries | -250000 |
| 7 | Rent | -60000 |
| 8 | Marketing | -140000 |
| 9 | Total operating expenses | -450000 |
| 10 | Operating profit | 150000 |
| 11 | Income tax | -30000 |
| 12 | Net profit | 120000 |
Everything is correct and nothing is readable. You cannot see where revenue ends, which lines are totals, or which figure is the answer without reading every label.
After (in $ thousands, signed convention)
| Label (indent level) | 2026 $000 | Convention applied |
|---|---|---|
| Revenue (0) | Section heading, bold, no figure | |
| Product sales (1) | $800 | Indent 1; currency symbol on first figure only |
| Services (1) | 200 | Indent 1; single accounting underline under the figure |
| Total revenue (0) | 1,000 | Outdented to section level, plain weight |
| Cost of sales (1) | (400) | Parentheses from the format code; single underline |
| Gross profit (0) | 600 | Bold: a profit line |
| Operating expenses (0) | Section heading, bold, no figure | |
| Salaries (1) | (250) | Indent 1 |
| Rent (1) | (60) | Indent 1 |
| Marketing (1) | (140) | Indent 1; single underline |
| Total operating expenses (0) | (450) | Plain weight; single underline |
| Operating profit (0) | 150 | Bold: a profit line |
| Income tax (1) | (30) | Single underline |
| Net profit (0) | $120 | Bold, currency symbol, double accounting underline |
Check the arithmetic, because a formatted statement that doesn't foot is worse than an ugly one that does: 800 + 200 = 1,000. 1,000 − 400 = 600. 250 + 60 + 140 = 450. 600 − 450 = 150. 150 − 30 = 120. In the signed convention every one of those is a SUM: Gross profit is =SUM(Total revenue, Cost of sales), Operating profit is =SUM(Gross profit, Total operating expenses), and so on.
The two section headings add rows but no figures. The formula cells never changed.
Which lines get bold, and which get nothing?
Bold is a pointer. It says "if you read one number, read this one". Use it on everything and it points nowhere.
On a P&L the lines a reader hunts for are the profit lines: gross profit, operating profit, net profit. Those get bold. Section headings can take bold too, since they carry no figures and only mark where a section starts.
Everything else gets nothing: no bold, no italics, no fill. That includes subtotals like "Total revenue", which are already marked by the rule and the outdent. Bolding them too puts them at the same weight as net profit.
If you have a column of the same figures expressed as a percentage of revenue, it follows the row, not its own rules. Gross margin sits next to gross profit in bold, and the rest stay plain. Percent columns take 0.0%;(0.0%);"-".
Blue-for-inputs colour coding stops here too. It describes how a model is built, and a P&L is output for people who won't edit it, so it stays black. The reasoning is in financial model formatting conventions.
How do you keep the format when you add a period?
A P&L grows sideways: next month, next quarter, a prior-year comparison. Three habits keep the formatting from falling apart.
Insert the new column inside or next to the existing block, not in empty space. When you insert a column in Excel, the Insert Options button offers Format Same As Left or Format Same As Right, so the new period inherits every number format, indent and underline in one click. Typing into a blank column to the right gets you General format and no rules.
Decide the column order once. Monthly packs usually run oldest to newest, left to right. Annual statements usually put the current year first. Either works; switching halfway through a workbook does not.
Save the treatments as cell styles. Create three styles (P&L line, P&L subtotal with single underline, P&L net with double underline) in Home → Cell Styles → New Cell Style. Applying a style is faster than Format Painter and cannot pick up a stray border.
Then check the formulas copied too: a gross profit row pointing at the wrong column still looks perfect. For totals that survive added rows, the budget formatting guide covers why SUBTOTAL beats SUM for nested totals.
Letting the file do the mechanical part
SuperSheet handles the pure mechanics. Drop in the .xlsx or .csv your accounting software exported and it reads the file in your browser without uploading it, applies real number formats (including the accounting-style code that puts negatives in parentheses), adds a totals row that stays a live formula, freezes the header and turns gridlines off. The Financial theme follows accounting conventions: green accents, red negatives and tight rules. It is one of the Pro themes; the free tier formats the same file with the Minimal or Executive theme and still gives you the downloadable .xlsx.
It will not choose your indent levels or sign convention, or know that "Rent" belongs under operating expenses. It formats one table per tab and does not restructure a statement, because guessing at a P&L's hierarchy produces a confidently wrong file. The structure is your call. The formats are worth handing off.
FAQ
Should P&L negatives be red or parentheses?
Parentheses first, red optional. Parentheses carry the meaning on their own, survive black-and-white printing and work for readers who can't distinguish red. If you want colour as well, put [Red] at the start of the negative section: #,##0_);[Red](#,##0);"-"_). On a signed P&L, though, every cost is negative, so red turns half the statement red. Red earns its place on variance columns, not on the statement itself.
How do I show figures in thousands on a P&L?
Add a trailing comma to the format code: #,##0,_);(#,##0,);"-"_) displays 812,400 as 812 without changing the stored value. State "$ thousands" once in the heading or column header. Expect small rounding differences between displayed lines and displayed totals, and either round the source figures or add a note.
Which P&L lines should be bold?
The profit lines (gross profit, operating profit, net profit) and, if you like, section headings with no figures. Line items and subtotals such as total revenue stay plain, because the rule and the outdent already mark them. More than three or four bold figures and bold stops meaning anything.
Does a P&L need gridlines?
No. Turn them off (View → Gridlines in Excel) and let the accounting underlines do the work. Gridlines put a line under every figure, which drowns out the two rules that actually mean something. The rest of the presentation pass, from column widths to freeze panes, is in how to make a spreadsheet look professional.