How Do You Do Vertical Analysis in Excel?

How Do You Do Vertical Analysis in Excel?

Vertical analysis is one of those techniques that sounds like it needs an accounting qualification and turns out to need one formula. You pick a base figure, divide every other line in the statement by it, and format the result as a percentage. That is the whole method.

What makes it worth learning is not the arithmetic but what the arithmetic exposes. Raw financial statements are built from numbers that all move together, so a column of dollars tells you very little about whether anything has actually changed. Restate the same column as percentages and the structure of the business becomes visible: how much of every sales dollar goes to materials, how much survives to gross profit, how much of the balance sheet is tied up in stock that has not sold.

This guide covers the formula, the absolute-reference trick that makes it fill correctly, how to read the output, and the places where the technique quietly misleads you.

How do you do vertical analysis in Excel?

Divide each line item by the base figure for its statement, with the base locked as an absolute reference, then format the column as a percentage.

For an income statement the base is total revenue. If revenue sits in cell B2 and your line items run down column B, the formula in the first analysis cell is:

=B2/$B$2

Fill that down the column. The $B$2 stays pinned to revenue while B2 advances to B3, B4 and so on, so every row is measured against the same denominator. Select the results and apply the percentage format with Ctrl+Shift+%.

For a balance sheet the base is total assets, and the same formula applies with the total-assets cell in place of revenue. Liabilities and equity are conventionally measured against total assets as well, which is what makes a common-size balance sheet add to 100% on both sides.

Numbers only help when you can see the structure behind them. Get a free SEO audit and find out what is actually driving your traffic.

That is the mechanical answer. The rest of this guide is about doing it in a way that survives contact with a real set of books.

What is vertical analysis actually telling you?

Vertical analysis converts a statement into a set of proportions. Every figure answers the same question: what share of the base does this line represent?

Why proportions beat raw figures

A company whose revenue grew 40% and whose cost of goods sold grew 55% looks like a success story in dollars and a problem in percentages. The dollar column shows two numbers going up. The percentage column shows margin going down, which is the thing that will eventually matter.

This is the core value of the technique. It strips out scale. A $2m business and a $200m business can be laid side by side and compared directly, because both have been restated against their own base.

What the income statement version shows

On an income statement, vertical analysis is usually called margin analysis even when nobody uses that phrase. Cost of goods sold as a percentage of revenue gives you gross margin by subtraction. Operating expenses as a percentage of revenue show how much of the top line administration consumes. Net income as a percentage of revenue is net margin.

Read down the column and you have a cost structure. Read the same column across several periods and you have the direction that cost structure is heading.

What the balance sheet version shows

On a balance sheet, the proportions describe where capital is sitting and who has a claim on it. Inventory at 8% of total assets is a different business from inventory at 34% of total assets, and the second one has considerably more money parked in things it has not sold yet.

The liabilities side tells you about funding. Total debt as a share of total assets is a leverage reading, arrived at without needing to calculate a ratio separately.

How do you build it step by step?

The steps below assume a statement already typed or pasted into Excel with labels in column A and figures in column B.

1. Put your line-item labels in column A and the period's figures in column B, with the base figure (total revenue, or total assets) on its own clearly labelled row.
2. Make sure the base row really is a total. If your sheet has subtotals that double-count, vertical analysis will happily divide by the wrong number and give you percentages that look plausible and are wrong.
3. In cell C2, enter =B2/$B$2, replacing $B$2 with the actual address of your base figure.
4. Press Enter, then select C2 and double-click the fill handle at the bottom-right of the cell to copy the formula down the full height of your data.
5. With the column still selected, apply the percentage format using Ctrl+Shift+%, then add one decimal place if your line items are small enough that whole percentages hide the detail.
6. Check that the base row itself now reads 100.0%. If it does not, the absolute reference is pointing at the wrong cell.
7. Label column C with the period, for example FY2026 %, so the sheet still makes sense when you add a second period beside it.

Adding more periods

Vertical analysis becomes genuinely useful at two or more periods, because that is when the trend appears. Put the second period's figures in column D and its percentages in column E, with the formula =D2/$D$2. Each period is measured against its own base, never against the earlier one.

A common error here is to lock the first period's base and reuse it for every column. That produces something that is neither vertical analysis nor horizontal analysis, and the numbers are not interpretable.

Handling blank rows and subtotals

Real statements have spacer rows and subtotal rows. The formula will return 0.0% on a blank row and a large percentage on a subtotal, both of which clutter the column. Wrap the formula to suppress the empty ones:

=IF(B2="","",B2/$B$2)

Leave the subtotals in place and formatted in bold. They are informative — gross profit as a percentage of revenue is one of the most useful cells on the sheet.

Stop guessing which numbers matter. Get a free SEO audit and see where the real opportunities are.

Making negatives behave

Expenses are sometimes entered as negative figures. Dividing a negative expense by positive revenue gives a negative percentage, which is internally consistent but awkward to read in a column where everything else is positive. Either enter expenses as positive numbers and rely on the labels, or wrap the formula in ABS(), which is honest as long as you do it for the whole column and not selectively.

When should you use vertical analysis?

The technique earns its place in four situations.

• Comparing companies of different sizes. This is the classic use. Two competitors with very different revenues become directly comparable once both statements are restated as percentages.
• Spotting cost creep inside your own business. A line item that drifts from 4% of revenue to 7% over six quarters is a problem that the dollar figures will not show you, because revenue was growing the whole time.
• Sanity-checking a budget or forecast. If next year's plan has marketing at 19% of revenue when it has never exceeded 11%, the plan is making an assumption nobody has stated out loud.
• Explaining financials to people who do not read financials. Percentages of revenue are intuitive in a way that dollar columns are not. "Thirty-one cents of every sales dollar goes to staff" lands; "$4.2m in payroll" does not.

It is also the right first move on an unfamiliar set of accounts. Before calculating any ratio, restate the statement as percentages and read down the column. The unusual lines announce themselves.

Where does vertical analysis fall short?

It is a descriptive tool, and it has real blind spots.

• It cannot see growth. A business whose every line stays at exactly the same percentage of revenue looks completely static in vertical analysis, whether revenue tripled or halved. The proportions are identical. You need horizontal analysis, or simply the dollar column, to see the direction of travel.
• It is only as good as the base figure. If total revenue includes a one-off item — an asset sale, a settlement, a grant — then every percentage in the column is distorted by it, and the distortion is invisible once the dollars are gone.
• Fixed costs behave oddly. Rent does not scale with revenue, so rent as a percentage of revenue falls in good quarters and rises in bad ones without anybody having changed anything. A rising percentage is not automatically a cost problem; it is sometimes a revenue problem wearing a cost costume.
• Different accounting policies break comparability. Two companies that capitalise versus expense the same type of spending will show structurally different percentages for reasons that have nothing to do with how they operate.
• It flattens seasonality. A single quarter restated as percentages can look alarming purely because that quarter is always like that. Compare like periods, or use trailing twelve months.

None of these are reasons to skip the technique. They are reasons to treat the percentage column as the beginning of the question rather than the answer.

Good analysis starts with clean data. Get a free SEO audit and find out what your site is really doing.

How does it compare to horizontal and ratio analysis?

The three techniques answer different questions, and the usual mistake is picking one and stopping.

Vertical analysis asks what share of the whole each line represents, within a single period. It is a structural reading. Its output is a cost structure or a capital structure.

Horizontal analysis asks how much each line changed from one period to the next, usually as a percentage change. In Excel that is =(C2-B2)/B2, filled down the column. It is a directional reading, and it is the thing vertical analysis cannot do.

Ratio analysis asks about relationships between lines that are not part of the same whole — current assets against current liabilities, net income against equity, revenue against total assets. Vertical analysis gives you some ratios for free, but not the ones that cross between statements.

Used together the sequence is straightforward. Vertical analysis to see the structure, horizontal analysis to see the movement, ratios to test specific questions the first two raised. All three are a single-formula exercise in Excel, and the hard part in every case is the interpretation rather than the spreadsheet.

A note on automating it

Once you have a vertical analysis column working, it is tempting to build a template that takes any pasted statement and restates it. This works, with one caveat: the base-figure row has to be found rather than assumed, because every export puts total revenue on a different line. A MATCH() on the label is more reliable than a hardcoded cell address:

=B2/INDEX($B:$B,MATCH("Total revenue",$A:$A,0))

That survives a statement gaining or losing rows, which a hardcoded $B$2 does not.

Most sites are leaving easy wins on the table. Get a free SEO audit and see which ones are yours.

Final Thoughts

Vertical analysis is a one-formula technique that changes what you can see in a set of accounts. Lock the base figure with an absolute reference, divide every line by it, format as a percentage, and check that the base row reads 100%. That is the build.

The judgement is in the reading. A percentage that moved is a question, not a conclusion — it might be a cost that grew, or a revenue base that shrank, or a fixed cost behaving exactly as fixed costs do. Ask which before acting on it.

If you want another Excel technique that works by restating figures as shares of a total, our guide to ABC analysis of inventory in Excel applies the same cumulative-percentage thinking to stock rather than to statements.

The habit worth building is reaching for the percentage column first. It takes thirty seconds and it tells you where to look.