Percentages in Excel: Margin, Markup, Percent Change and the 36,863 Hidden in a Column That Says 30%

The pricing workbook has a margin column. Every cell in it reads 23.08%.

Nobody has ever queried it, because the price list was built to hold 30% and 23 is not far from 30 — it reads like the kind of gap that opens up between a policy and the real world, the sort of thing you explain with discounts and mix and a couple of legacy accounts.

It is not that. There are no discounts in this sheet. Every list price is exactly its cost times 1.3, and 23.08% is not an approximation of anything — it is 0.30 / 1.30, exact to as many decimal places as you care to show, on every row, forever. The person who built the column added 30% to cost and called the result a 30% margin. Those are two different quantities and only one of them is what the finance pack means by margin.

Across a year of trading, the difference is 36,863.23.

This article is about the percentage arithmetic that sits underneath that, and underneath a dozen other quiet spreadsheet errors: what Excel is really storing when a cell shows 23.08%, when to divide rather than multiply, why a column of percentages must almost never be averaged, and why twelve shares of one total can add up to 99.8%.

What you need. Nothing exotic. SUM, SUMIF, SUMPRODUCT, ROUND, IF, IFERROR and ABS work in every version of Excel and in Google Sheets. RRI needs Excel 2013 or later, and exists in Sheets. The percent number format and the % operator are as old as Excel itself.

1) The Column That Says 30% and Means 23.08%

🎯 Scenario: You have been asked why gross margin came in at 23% when the pricing policy is 30%. The obvious suspects — discounting, freight, a bad month — all turn out to be innocent. The answer is in the price list itself, and it has been there since the list was written.

Twelve products, with cost in C2:C13, list price in D2:D13, and this year's and last year's unit volumes in E2:E13 and F2:F13.

Put a margin column beside it:

=(D2-C2)/D2

Fill it down and every row returns 23.0769%. Not roughly — identically. That is the tell. Real margins vary; a column of identical margins means the prices were generated by a formula, and you can work backwards to find which one.

=D2/C2        1.3

Every price is its cost times 1.3. Somebody was told "we make 30% on this stuff" and typed =C2*1.3, which adds 30% to cost. Margin is measured against the price. The 30 that went on the top of the cost is now sitting in the bottom of the margin as well:

margin = 0.30 / 1.30 = 0.230769…

There is nothing to fix in the margin formula. The margin formula is right. It is faithfully reporting a price list that was built to the wrong target, and it will keep reporting 23.08% until somebody changes the prices.

What it costs is the rest of this section. Multiply cost by units and price by units:

=SUMPRODUCT(C2:C13, E2:E13)     286,714.00
=SUMPRODUCT(D2:D13, E2:E13)     372,728.20

So 372,728.20 of revenue on 286,714.00 of cost — 86,014.20 of gross profit at 23.08%. At a genuine 30% margin, the same twelve products on the same twelve volumes would have billed:

286,714.00 / 0.7 = 409,591.43

36,863.23 more, all of it gross profit, none of it requiring a single extra unit to be sold. Section 6 turns that into a repriced column.

2) What the % Button Actually Does

Before any of the arithmetic, the thing that causes more percentage bugs than every formula in this article combined: the percent format changes what a cell looks like, not what it holds.

A cell showing 23.08% contains 0.230769230769231. The format multiplies by 100 for display and appends a sign. Nothing else happens. Which means:

• =A1*B1 where B1 shows 23.08% multiplies by 0.2308, correctly. You do not divide by 100 anywhere.
• Typing 23.08 into a cell already formatted as percent gives you 23.08% — Excel notices the format and divides by 100 for you. Typing 23.08 into a General cell and then applying the percent format gives you 2308.00%. Same keystrokes, two-order-of-magnitude difference, decided entirely by which order you did it in.
• That helpfulness is a setting: File ▸ Options ▸ Advanced ▸ Enable automatic percent entry. It is on by default. If a colleague's sheet behaves differently from yours, this is usually why.

The % sign is also a genuine operator. 20% in a formula is a literal worth 0.2, exactly as =A1*20% and =A1*0.2 are the same calculation. That makes =C2*(1+30%) a legitimate and much more readable way to write =C2*1.3 — the intent is visible, which in this particular sheet would have been worth 36,863.23.

Two formatting habits are worth adopting now:

0.0%;[Red]-0.0%;"–"

as a custom format (Ctrl+1 ▸ Custom) shows one decimal, paints negatives red, and prints a dash instead of 0.0% for a genuine zero, so an empty result stops masquerading as no change. And remember that Increase Decimal never changes a value — if you need the stored number rounded, that is ROUND, and section 13 is about the difference.

3) Percent Change: (New − Old) / Old

The single most-typed percentage formula, and the one people most often write backwards. Year-on-year unit growth, in G2:

=(E2-F2)/F2

or, identically and with one fewer reference to get wrong:

=E2/F2-1

The denominator is always the old value. That is not a convention you can flip: the whole meaning of "grew 14%" is relative to where it started. Filled down, the column reads:

Three things about that column that are not obvious:

It is not symmetric. Oat Barista went 14,200 → 18,600, up 30.99%. Reverse it and 18,600 → 14,200 is down 23.66%, not down 30.99%. The base changed, so the percentage changed. This is why "we lost the 30% we gained" is almost always wrong.

A zero base has no answer. =(E2-F2)/F2 with F2 = 0 returns #DIV/0!, and it is right to. A product that sold nothing last year and 610 units this year has not grown by any percentage — it is new. Section 12 covers what to write in the cell instead.

A negative base gives you the sign backwards. Section 12 again, because it deserves its own explanation.

4) Share of Total, and the Dollars That Stop the Denominator Moving

🎯 Scenario: The category review wants revenue split by category as a percentage of the year. You have a revenue-per-line column and a category column, and about two minutes.

Revenue per line in H2, filled down:

=D2*E2

Share of total in I2:

=H2/$H$14

with the year total =SUM(H2:H13) in H14. The dollar signs are the entire point of this section. Without them, I3 becomes =H3/H15, I4 becomes =H4/H16, and the column silently divides each line by whatever happens to sit a few rows below it — usually blanks, which means #DIV/0! if you are lucky and a wrong number if you are not. Anchor the denominator, always: F4 on the reference, or type the dollars yourself.

If you would rather not keep a total cell at all, put the SUM inline and anchor its range:

=H2/SUM($H$2:$H$13)

By category, with SUMIF:

=SUMIF($B$2:$B$13, B2, $H$2:$H$13)/SUM($H$2:$H$13)

or against a list of unique categories in K2:K6:

=SUMIF($B$2:$B$13, K2, $H$2:$H$13)/SUM($H$2:$H$13)

Always sum the share column. It is the cheapest audit in Excel: if it does not come to 100%, either the denominator is wrong, a row is excluded, or a category is spelled two different ways. SUMIF matching is not case-sensitive but it is very much space-sensitive, and "Tea " with a trailing space is a sixth category as far as it is concerned.

5) Margin Against Markup

These are the two percentages people mean when they say "we make 30% on it", and they are computed from different denominators. Learn the pair once and the rest of this article is bookkeeping.

markup = (price - cost) / cost        =(D2-C2)/C2
margin = (price - cost) / price       =(D2-C2)/D2

Same numerator, different bottom. Markup is profit measured against what you paid; margin is profit measured against what you charged. Since price is bigger than cost, markup is always the larger number — which is exactly why sales quotes drift towards it and finance packs stay on margin.

On this price list, every row is a 30% markup:

=(16.12-12.40)/12.40      30.00%      markup
=(16.12-12.40)/16.12      23.08%      margin

The conversions, which are worth writing on a sticky note:

margin from markup    =markup/(1+markup)      =30%/(1+30%)   → 23.08%
markup from margin    =margin/(1-margin)      =30%/(1-30%)   → 42.86%

A useful table for the pricing conversation:

Two rows there settle most arguments. A 50% markup is a 33% margin, and doubling your money is a 50% margin, not 100% — a margin can never reach 100%, because that would mean the goods were free.

The trap is that the two words are close enough to be used interchangeably in a meeting and far enough apart to matter in a sheet. If a column is called "Margin %", check the denominator before you trust it. It takes one cell:

=D2/C2

1.30 means someone applied a markup. 1.4286 means someone priced to a margin.

6) Pricing to a Target Margin: Divide, Do Not Add

🎯 Scenario: The policy is a 30% gross margin. You need a corrected price column that actually delivers it, and a number to put in front of the person who signs off pricing.

You cannot get there by adding a percentage to cost, because the percentage you would need to add depends on the margin you want in a way that is not linear. You get there by dividing:

=C2/(1-30%)

or, with the target margin parked in $K$1 where it can be changed once and re-flow the column:

=ROUND(C2/(1-$K$1), 2)

That is the whole fix. Cost divided by one minus the margin is the price that produces that margin, and it works for any target: 40% margin is /0.6, 15% is /0.85.

Multiply the new column by the same volumes:

=SUMPRODUCT(J2:J13, E2:E13)      409,693.30

Against 372,728.20 today — 36,965.10 more gross profit, on identical volumes and identical costs.

That figure is 101.87 above the 36,863.23 quoted at the top, and the discrepancy is worth understanding rather than papering over. 36,863.23 is the exact arithmetic target, 286,714 / 0.7. 409,693.30 is what you actually bill once every price has been rounded to the penny with ROUND(…, 2), and rounding to two decimals happened to round up more often than down. The blended margin on the rounded column is 30.02%, not 30.00%. That is fine, and it is the right way round — but it is the reason a repricing exercise never lands exactly on its own business case, and knowing which of your two numbers is the model and which is the invoice saves an afternoon.

7) Taking a Percentage Off — and Putting It Back On

🎯 Scenario: The invoice total is 447,273.84 including 20% VAT. You need the net figure for the revenue line.

The wrong answer is the one that feels symmetrical:

=447273.84*0.8         357,819.07      wrong
=447273.84/1.2         372,728.20      right

A gap of 14,909.13 on one number, and both results look like plausible revenue, which is what makes it survive review.

The reasoning is the same shape as margin against markup. The VAT was added to the net figure, so the gross is net × 1.2. Recovering the net means undoing that multiplication — dividing by 1.2 — not applying a different percentage to a different base. 20% of the net is 16.67% of the gross, and × 0.8 removes 20% of the gross.

The VAT amount itself:

=447273.84/1.2*0.2         74,545.64
=447273.84/6               74,545.64

The /6 shortcut is the one bookkeepers use and it is exact, because 0.2/1.2 is 1/6. For a 5% rate the divisor is 21, for 19% it is 1.19/0.19, and the general form is:

=gross/(1+rate)*rate

The same logic covers every "remove a percentage" question. To reverse a 15% discount and recover the list price, divide by 0.85 — do not add 15%:

=net/(1-15%)

Adding 15% back to a discounted price recovers 97.75% of where you started, and the missing 2.25% is the thing this article keeps coming back to: a percentage is meaningless without its base, and the base moves.

8) Up 10% Then Down 10% Is Not Where You Started

Percentages compound. Two changes applied in sequence multiply their factors, they do not add their rates:

=100*(1+10%)*(1-10%)       99

Up 10%, then down 10%, leaves you 1% down — and the order does not matter, because multiplication commutes. 1.1 × 0.9 is 0.99 either way.

This is the arithmetic behind stacked discounts, which is where it costs money. A 15% trade discount plus a 2.5% early-payment discount is not 17.5% off:

=(1-15%)*(1-2.5%)          0.82875       →  17.125% off

The 2.5% is taken from the already-discounted figure, so the effective discount is 17.125% and the difference on 372,728.20 is 1,398. Chain three of them and the gap widens. The rule for reading a stacked deal:

effective discount   =1-PRODUCT(1-D2, 1-D3, 1-D4)

The same trap runs the other way through price rises. Two 5% increases in a year are not 10%:

=(1+5%)*(1+5%)-1           10.25%

And a cell that has been marked up and then discounted is only back where it started if the two factors multiply to 1, which for a 30% markup means a 23.08% discount, not a 30% one.

When you need to walk a number backwards through a change, always divide by the factor:

=after/(1+change)

=18600/(1+30.99%) returns 14,200 — last year's oat volume, recovered exactly.

9) Percentage Points Are Not Percent

Two different units, one word, endless confusion — and both of them are correct, so this is about writing down which you mean.

Margin moving from 23.08% to 30.00% is:

=30%-23.08%                6.92 percentage points     (a subtraction)
=(30%-23.08%)/23.08%       30.00% higher              (a percent change)

Both describe the same repricing. The first is the difference; the second is the relative improvement. Note the coincidence — the relative improvement is exactly 30%, because 0.30 / (0.30/1.3) is 1.3. Coincidences like that are worth checking rather than trusting, which is what the second formula is for.

The gap gets wide at small numbers, which is exactly where reports live. A payment-processing fee going from 1.0% to 2.0% is:

• up one percentage point, and
• up 100%,

and those are both true. If your commentary says "fees up 100%" and the reader hears "one point", you have not lied but you have not communicated either. Two habits fix it permanently:

1. Write pp or points in the header of any column that holds a difference between two percentages, and format it as a plain number with one decimal — not as a percent, because 6.92 percentage points stored in a percent-formatted cell will render as 692.00%.
2. Never subtract two percentages and format the result as a percentage. It is a different unit and Excel will not stop you.

10) Never Average a Column of Percentages

🎯 Scenario: The board deck wants one number for unit growth. There is a percent-change column with twelve rows in it and an obvious formula to put underneath.

=AVERAGE(G2:G13)          10.64%

The business actually grew 12.92%:

=(SUM(E2:E13)-SUM(F2:F13))/SUM(F2:F13)      12.92%

Both are arithmetically valid and only one answers the question. AVERAGE treats all twelve rates as equally important, so Chai Concentrate's spectacular 45.45% on 1,540 units carries exactly the same weight as Oat Barista's 30.99% on 14,200. Oat Barista is twelve times the volume. The deck is being asked about the business, not about the average product.

The general fix is to weight each rate by the base it was measured against:

=SUMPRODUCT(G2:G13, F2:F13)/SUM(F2:F13)     12.92%

which returns the totals figure exactly, because multiplying each rate back by its own base rebuilds each row's unit change before summing.

The same rule governs every percentage you might be tempted to average:

• Weighted margin is =SUMPRODUCT(margin, revenue)/SUM(revenue), or more simply just =(SUM(revenue)-SUM(cost))/SUM(revenue) — total gross profit over total revenue.
• Weighted conversion rate is total conversions over total sessions, never the mean of the daily rates.
• Weighted interest is total interest over total principal.

Three tests tell you when AVERAGE is defensible. Are the bases all the same size? Is each row a genuinely equal unit of interest — twelve stores, twelve months, twelve people? Are you being asked about the typical row rather than the whole? If any answer is no, weight it. And if a colleague hands you an average of percentages, ask what the denominators were; the question is usually enough.

11) Growth Over Several Periods: CAGR

One more place the arithmetic mean lies. The catalogue did 268,400 four years ago and 372,728.20 this year. The compound annual growth rate:

=(372728.2/268400)^(1/4)-1        8.56%
=RRI(4, 268400, 372728.2)         8.56%

RRI — "rate of return on investment" — is the same calculation with the parentheses already balanced: periods, start value, end value. It arrived in Excel 2013 and exists in Google Sheets.

The exponent is 1/periods, and periods means gaps, not data points. Five annual figures are four years of growth. Getting this wrong by one is the most common CAGR error and it always flatters or deflates by roughly a quarter of the rate.

Why not average the yearly growth rates? Because growth multiplies. A year of +40% followed by a year of −20% averages to +10%, and actually leaves you at:

=(1+40%)*(1-20%)-1         12%       over two years
=(1.12)^(1/2)-1            5.83%     per year

5.83%, not 10%. The arithmetic mean of growth rates is always at least the geometric mean and is strictly greater whenever the rates differ at all, so it systematically overstates. The geometric mean is what RRI computes.

Two conditions before you quote a CAGR. Both endpoints must be representative — a smoothed rate anchored on a freak final year is a straight line drawn through one lucky point. And the sign must not change: a series that runs from a loss to a profit has no meaningful compound rate, because you cannot take a fractional root of a negative ratio. RRI returns #NUM! and the exponent form returns #NUM! too, and both are more honest than a number would have been.

12) When a Percentage Has No Answer

Two cases, both of which arrive the moment real data does.

A zero base. Add a thirteenth line — a matcha base launched in March, 610 units this year, nothing last year — and =(E14-F14)/F14 returns #DIV/0!. That error is correct. Growth from zero is not 100%, not infinite, and not 0%; it is undefined, because there is no base to be relative to. What you want is a cell that says so:

=IF(F14=0, "new", (E14-F14)/F14)

Reach for IFERROR here only if you have already decided every possible error in the cell means the same thing:

=IFERROR((E14-F14)/F14, "new")

That also relabels a #VALUE! from a text entry in the volume column as "new", which is how a data-entry mistake becomes a product launch. The IF tests the actual condition; the IFERROR tests whether anything at all went wrong.

Blank is not zero. =(E14-F14)/F14 with F14 empty also returns #DIV/0!, because Excel reads a blank as 0 in arithmetic — but a blank usually means we do not have last year's figure, which is a different statement from last year was zero. Split them:

=IF(ISBLANK(F14), "n/a", IF(F14=0, "new", E14/F14-1))

A negative base. Suppose the Supplies category lost 4,200 last year and made 1,800 this year. The standard formula returns:

=(1800--4200)/-4200        -142.86%

A large negative percentage describing a clear improvement, because dividing by a negative number flips the sign. There is no universally agreed fix, and that is the point — the honest options are to use the absolute value of the base, which at least gets the direction right:

=(1800--4200)/ABS(-4200)   142.86%

or to refuse the percentage and report the movement in currency: from −4,200 to 1,800, a swing of 6,000. When the base can go negative, a percent-change column is a liability, and a difference column is a report. Say the number, not the ratio.

13) Why the Shares Add Up to 99.8%

Take the twelve share-of-total percentages from section 4 and format them to one decimal place. Sum what you can see:

But =SUM(I2:I13) in the sheet returns exactly 100.0%, because the cells still hold their full precision and only the display was rounded. Twelve small downward roundings, none bigger than 0.05 points, add up to two tenths.

Neither number is wrong. They answer different questions — one is the total of the values, the other is the total of the labels — and the fault is only ever in claiming they are the same. You have three honest ways out:

Round the values, and accept the total. =ROUND(H2/$H$14, 3) changes the stored numbers, so SUM and the eye agree, at 99.8%. A footnote saying "shares may not total 100% due to rounding" is the standard, respectable answer.

Show more decimals. Two decimals here happen to land on 100.00%, which is luck rather than a method — go to three and it may drift again.

Force it to balance. Compute eleven rounded shares and derive the twelfth as =1-SUM(I2:I12). The total is exactly 100% and one row silently absorbs the entire error. Legitimate for a pie chart, dishonest for a table people will read line by line.

The general principle is worth more than the fix: formatting is a lie you tell the reader, and ROUND is a change you make to the data. Decide which one the report needs before you print it, because a total built from formatted cells and a total built from stored ones will disagree eventually, and always in front of an audience.

14) Common Mistakes

1. Calling a markup a margin. =C2*1.3 is a 30% markup and a 23.08% margin. The whole subject of this article, and worth 36,863.23 on twelve products.
2. Adding a percentage to hit a target margin. The target margin is =cost/(1-margin), a division. No amount added to cost will do it.
3. Multiplying by 0.8 to strip 20% VAT. Divide by 1.2. On 447,273.84 the mistake costs 14,909.13, and both figures look like revenue.
4. Averaging a percentage column. 10.64% against a real 12.92% here. Weight by the base — =SUMPRODUCT(rate, base)/SUM(base) — or recompute from the totals.
5. Relative references in the denominator of a share. =H2/H14 filled down divides each row by a different cell. Anchor it: $H$14.
6. Assuming percentages reverse. Up 30.99% and down 30.99% are different journeys. To undo a change, divide by (1+change).
7. Adding stacked discounts. 15% and 2.5% is 17.125%, not 17.5%. Multiply the factors: =1-(1-15%)*(1-2.5%).
8. Confusing percentage points with percent. 23.08% to 30% is 6.92 points or a 30% improvement. Label the unit, and never format a points difference as a percentage.
9. Typing a number into a cell and then applying percent format. 30 becomes 3000%. Format first, or type 30%.
10. Treating Increase Decimal as rounding. It changes the display only. SUM reads the stored value, which is why a column of visible numbers refuses to add up.
11. Suppressing #DIV/0! with IFERROR. A zero base means "new", not "0% growth". Test the condition with IF; save IFERROR for when every error genuinely means the same thing.
12. Percent change on a negative base. =(1800--4200)/-4200 reads −142.86% for a genuine recovery. Use ABS on the denominator, or report the swing in currency instead.
13. CAGR over the wrong number of periods. Five annual figures are four periods of growth. And RRI returns #NUM! if the sign flips between the endpoints — believe it.
14. Google Sheets differences. There are effectively none for this article. SUM, SUMIF, SUMPRODUCT, ROUND, IF, IFERROR, ABS and RRI all exist and behave identically, the % operator works the same way, and Format ▸ Number ▸ Percent is the same format. Sheets also divides by 100 when you type a number into a percent-formatted cell, but it has no equivalent of Excel's Advanced option to switch that off.

Conclusion

Nothing in this sheet was broken. The margin formula was right, the prices were entered exactly as intended, the totals footed, and the workbook has been reconciling to the ledger for years. What went wrong is that one word was used for two quantities, and the spreadsheet had no way to notice.

That is the shape of nearly every percentage bug. A percentage is a ratio, and a ratio is only as meaningful as its denominator — so the questions worth asking are always the same two: a percentage of what, and measured from where? Cost or price. Old or new. Gross or net. Weighted by volume or by row. Every mistake in this article is a case of the right numerator meeting the wrong bottom half.

The tell in this workbook was a column of identical values. Real margins vary, because real costs and real prices move independently, so twelve rows reading 23.08% to four decimal places meant the column was reporting a formula rather than a business. That is a useful instinct to build: a percentage column that is suspiciously uniform, suspiciously round, or suspiciously symmetric is usually describing the sheet rather than the world.

And the fix, once it was found, was one character wide. =C2/(1-30%) instead of =C2*1.3 — a division where there was a multiplication — worth 36,965.10 of gross profit on volumes that were already sold, costs that were already paid, and a price list nobody had questioned in years.

If you want to drill margin, markup, share-of-total and weighted averages against real business data, the exercises in the app are built on exactly this kind of sheet — and a wrong denominator there costs nothing but another go.