Percentage Formulas in Excel for Accountants (and the 0.45-Instead-of-45% Trap)

How-to · Updated July 2026 · ~12 min read

You divide payroll by revenue, expecting 14%, and Excel hands you 0.14. So you multiply by 100, get 14, format the cell as a percentage — and now it reads 1400%. Every accountant has been on one side of this or the other, usually in front of someone. The cause is a single design decision that Excel never explains on screen, and once you know it, every percentage formula in this post becomes a one-liner.

This post covers the trap first, then the five percentage calculations that carry most of an accountant's day: percent of a total (the common-size P&L), percent change between periods, margin percentages, adjusting a figure by a percentage, and VAT. Every example uses ledger-shaped numbers, because a formula demonstrated on exam scores tells you nothing about what happens when the denominator is a negative prior-year loss.

The trap: percentages in Excel are a costume, not a calculation

Excel stores a percentage as a plain decimal. 45% is the number 0.45 wearing a costume — the percent format multiplies the display by 100 and appends the % sign, while the stored value stays 0.45. Nothing in your formula should ever multiply by 100. That habit comes from calculators and other software; in Excel it is the direct cause of the 4500% cell.

The two failure modes are mirror images:

To apply the format: select the cells, then Home tab > Number group > % button (Percent Style), or Ctrl+Shift+%. Both apply percentage format with zero decimal places, so 0.4567 displays as 46% — click Increase Decimal (same group) or open Format Cells (Ctrl+1) > Number tab > Percentage and set the decimals you want. For a common-size P&L, one decimal place is the usual convention.

Three entry behaviours worth knowing cold, because they explain most "Excel changed my number" complaints:

1. Type 45% into any cell and Excel stores 0.45 and applies percentage format. Typing the % sign is the cleanest way to enter a rate.
2. Type 45 into a cell already formatted as Percentage and Excel reads it as 45%, storing 0.45. This is why pre-formatting an assumptions column before entry is good practice.
3. Type 45 into a General cell, then apply percentage format afterwards and you get 4500% — the stored value is 45, and the costume multiplies the display. This is the inherited-spreadsheet classic: a colleague's sheet full of =A2/B2*100 columns looks fine as plain numbers, then detonates the moment someone applies percent formatting.

For that inherited sheet, you have two honest fixes: strip the 100 from the formulas and format properly, or — if the workbook is too tangled to rewire — apply the custom number format 0.0"%" (Ctrl+1 > Custom), which appends a literal % sign without* rescaling the display. Use the second sparingly and say so in a comment: the cell still contains 45, not 0.45, and any downstream formula multiplying by it will be out by a factor of 100.

One more consequence of the costume principle, in the other direction: you can type a percent sign directly inside a formula. =A25% is exactly =A20.05, and it reads better in a fee or VAT calculation than the bare decimal.

Percent of a total: the common-size P&L

The workhorse. Every line as a percentage of revenue turns a P&L you can read into a P&L you can compare — across months, entities or against sector benchmarks. The formula is a division with one absolute reference:

=B3/$B$2

Suppose revenue sits in B2 and the lines below it in B3:B10:

Put =B2/$B$2 in C2, fill down to C10, format column C as Percentage with one decimal. The dollar signs lock the denominator to revenue while the numerator walks down the column — press F4 after clicking B2 in the formula bar to add them. Forget the dollars and C3 becomes =B3/B3 on fill-down: a column of reassuring, meaningless 100%s.

If your figures live in a transaction list rather than a summary, compute the percent of total per category with SUMIFS over the raw data — amounts in C, category in B:

=SUMIFS($C$2:$C$500,$B$2:$B$500,"Payroll")/SUM($C$2:$C$500)

That is the whole common-size mechanism; building the statement around it — layout, subtotals, a Transactions sheet feeding the summary — is covered in building a P&L in Excel.

Two defensive notes. A zero denominator returns #DIV/0!, which in a monthly pack usually means a month with no revenue booked yet — guard with =IF($B$2=0,"",B3/$B$2) rather than a blanket IFERROR, which would also hide a genuinely broken reference. And keep the denominator honest: percent of revenue and percent of total costs are both legitimate common-size bases, but a schedule that silently mixes them is worse than no percentages at all. Label the column header with the base: "% of revenue".

Percent change: this month against last

The second workhorse — one subtraction, one division, and the order matters:

=(C2-B2)/B2

New minus old, divided by old. January revenue of 68,400 (B2) against February's 71,820 (C2) gives 3,420/68,400 = 5.0%. Dividing by the new figure instead is the commonest wrong version, and it distorts in both directions: a fall from 100 to 80 is −20% against the old base but −25% against the new one, overstating the decline, while a rise from 80 to 100 shrinks from +25% to +20%, understating the growth. The base is always the period you are measuring from.

The same formula is your budget variance percentage — =(Actual-Budget)/Budget — where an actual spend of 118,000 against a 110,000 budget is +7.3% over. Sign conventions (when is a positive variance favourable?) and the layout of a full variance report are their own topic: see variance analysis in Excel.

Two bases break the formula, and both appear in real ledgers:

• Zero base. Prior period nil, current period anything: division by zero. There is no percentage change from a zero base — show "n/m" (not meaningful): =IF(B2=0,"n/m",(C2-B2)/B2).
• Negative base. Prior-year result −10,000, current year +5,000: the formula returns −150%, which reads as a deterioration when the business swung to profit. A common patch is dividing by ABS(B2) — =(C2-B2)/ABS(B2) gives +150% — but the honest answer in a board pack is again "n/m" with the absolute movement alongside.

Month-on-month is one link in a longer chain — annualising a monthly rate, comparing like months a year apart, CAGR across several years — and that depth belongs in its own post rather than here: MoM, QoQ, YoY, CAGR and the reason a plain AVERAGE of growth rates reads high are worked end to end in how to calculate growth rate in Excel. Compounding those rates forward into a forecast is a modelling job — see building a financial model in Excel.

Margin percentages — and why margin is not markup

Gross margin, with revenue in B2 and cost of sales in B3:

=(B2-B3)/B2

Revenue 800,000 less cost of sales 488,000 is 312,000; over revenue, 39.0%. Net margin is the same shape with profit after all costs in the numerator. The denominator is always revenue — that is the entire difference between margin and markup, and confusing them misprices product:

• Margin = profit / selling price
• Markup = profit / cost

A product costing 100 and selling at 150 carries a 50% markup but a 33.3% margin. Quote "we price at 50%" without saying which, and two people will book two different prices. The conversions:

Margin from markup:  =D2/(1+D2)
Markup from margin:  =E2/(1-E2)

The second formula also gives you cost-plus pricing from a target margin: to achieve a 40% margin, divide cost by (1-40%) — a 60 cost prices at 100, not at 60*1.4 = 84, which yields only a 28.6% margin. Multiplying cost by the target margin percentage is the single most expensive percentage error in small-business pricing.

Increase or decrease a figure by a percentage

Uplifting a fee schedule by 5%, or applying a 10% discount:

Increase:  =B2*(1+5%)     or  =B2*105%
Decrease:  =B2*(1-10%)    or  =B2*90%

The error to avoid is =B2+5%, which adds 0.05 — five pence, not five percent — because 5% is just the number 0.05. Excel will not warn you; a 1,200.00 fee becomes 1,200.05 and the schedule looks plausible at a glance.

The reverse question — "prices went up 5%, what was the old price?" — is a division, not a 5% deduction: =B2/1.05. Deducting 5% from the increased figure lands below the original (1,050 × 95% = 997.50, not 1,000), for the same reason the VAT shortcut below fails.

VAT: adding is multiplication, extracting is not

Adding VAT to a net figure is the easy direction — at the UAE's 5% standard rate (rates and rules change, so confirm the current rate for your jurisdiction before it goes on an invoice):

Gross from net:  =B2*1.05

Net 1,000 grosses to 1,050. At UK's 20%, =B2*1.2.

Extracting VAT from a VAT-inclusive figure is where the intuitive formula is wrong. The VAT inside a 1,050 gross is not =10505% (52.50) — the 5% was charged on the net*, and the gross already contains it. Divide back to the net first:

Net from gross:   =G2/1.05
VAT from gross:   =G2-G2/1.05      or equivalently  =G2*5%/105%

From 1,050: net 1,000, VAT 50. The shortcut overstates by 2.50 on every line — small enough to survive review, large enough to misstate a VAT return once it compounds across a quarter's expense claims. At 20% the error is uglier: the VAT in a 1,200 gross is 200 (=G2/6 is the old hand's shortcut, since 20/120 reduces to 1/6), while 1200*20% claims 240.

The general form, worth keeping in a named cell so one rate change reprices the workbook: with the rate in $B$1, gross =net(1+$B$1), net =gross/(1+$B$1), VAT =gross$B$1/(1+$B$1).

Rounding: why the column sums to 100.1%

Format a common-size column to one decimal and the display rounds while the stored values keep full precision. Total the displayed figures by eye — 61.0 + 14.0 + 3.0 + 5.0 + 7.0 — and sooner or later the printed column foots to 100.1% or 99.9% while Excel's own SUM insists on 100.0%. Neither is wrong; they are answering different questions.

If the pack must foot visibly, round the stored values, not just the display:

=ROUND(B3/$B$2,3)

Three decimals of the stored decimal equals one displayed decimal of percentage (0.6104 → 0.610 → 61.0%). The residual rounding difference — usually ±0.1% — conventionally lands on the largest line or a footnote, exactly as it does on a statutory cash flow.

What not to do: File > Options > Advanced > "Set precision as displayed". It permanently truncates every stored value in the workbook to its displayed digits, it applies workbook-wide, and there is no undo after save. It is a machete where you need a scalpel; ROUND on the cells that matter does the job without collateral damage.

For labels and narrative cells, TEXT does the formatting inline: ="Gross margin: "&TEXT(C4,"0.0%") produces "Gross margin: 39.0%" — the TEXT result is a string, so keep it out of anything downstream that calculates.

How HISAB 360 helps

Percentage formulas are quick to write and slow to write correctly across a whole schedule — the absolute reference on the denominator, the zero-base guards, the SUMIFS per category. HISAB 360 is a paid Excel add-in with an AI chat panel docked inside Excel that writes these formulas into the live workbook from a plain-English request: ask for "each expense category as a percent of revenue, one decimal, dash where revenue is nil" and it builds the SUMIFS and the formatting rather than handing you text to paste. Ask for a common-size P&L and it constructs the report in the workbook from your data.

Because it also connects to six accounting systems — QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting — the figures under those percentages can come straight from the ledger: it pulls invoices, bills and GL activity into sheets, so the margin column is computed on current numbers rather than last week's export.

Honest limits: it is Windows desktop Excel only (Microsoft 365 / 2016+), not Mac and not Excel on the web, and it is paid — the trial is 15 days with 50 AI credits and no card required. And a generated formula deserves the same review you would give a junior's: check the base, check a couple of rows by hand.

The formulas on one page

Fluent percentages are one of the habits that separate a competent Excel accountant from a fast one — the rest of that list is in Excel skills for accountants.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.