How to Do Vertical Analysis in Excel

Vertical analysis is a simple yet powerful technique for understanding your company's financial health by comparing every line item to a single, significant base figure. This guide provides a straightforward, step-by-step process for performing vertical analysis in Excel on your income statement and balance sheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Vertical Analysis?

Often called common-size analysis, vertical analysis is a method of financial statement analysis where each line item is listed as a percentage of a base figure within that statement. By converting raw dollar amounts into relative percentages, you standardize the financial statement, making it far easier to analyze and compare.

Here’s how it works for the two most common financial statements:

• For an Income Statement: The base figure is typically Total Revenue or Net Sales. Every other item on the income statement - from Cost of Goods Sold (COGS) to net income - is then calculated as a percentage of that total revenue. This answers questions like, "What percentage of our revenue did we spend on marketing?" or "How much of each sales dollar is left after paying for our products?"
• For a Balance Sheet: The analysis is split. For the assets section, the base figure is Total Assets. For the liabilities and equity section, the base is Total Liabilities & Shareholder's Equity (these two base figures must be equal). This helps answer questions like, "What percentage of our assets is tied up in inventory?" or "How much of our company is financed by debt versus equity?"

The primary benefit of this approach is that it strips away the impact of company size. A large corporation with billions in revenue can be compared directly to a small startup. You're not comparing dollar to dollar, but rather the relative structure and efficiency of the businesses.

Getting Your Data Ready in Excel

Before you can begin, you need to structure your data correctly in an Excel sheet. The goal is to create a clean, organized table that makes it easy to apply formulas. Start with your standard income statement or balance sheet.

Here's a simple and effective layout to use:

1. Name your line items in Column A (e.g., Revenue, COGS, Gross Profit).
2. Put the dollar amounts for the period you're analyzing in Column B.
3. Leave Column C empty. This is where your vertical analysis percentages will go.
4. If you want to compare multiple periods (which is highly recommended!), you can add the dollar amounts for the second period in Column D and leave Column E for its vertical analysis.

Your setup for an income statement might look something like this:

(Example Spreadsheet Layout)

A: Item | B: Amount ($) | C: % of Revenue Revenue | $500,000 | COGS | $200,000 | Gross Profit | $300,000 | Marketing | $50,000 |

A clean setup like this prevents errors and makes the entire process smoother.

Step-by-Step: Vertical Analysis of an Income Statement

Let's use a sample income statement to walk through the process. The core of this analysis in Excel is understanding absolute references.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Identify Your Base Figure

For an income statement, the base figure is always Total Revenue (or Net Sales). This line item will be your 100% mark. Let's assume your Total Revenue figure of $500,000 is in cell B2.

Step 2: Create the Formula for the Base Figure

In the adjacent cell (C2), you'll write a simple formula to calculate the base percentage. Naturally, the percentage for your base will be 100%. The formula is:

=B2/$B$2

Pay close attention to the $B$2 part. The dollar signs create an absolute reference. This "locks" the formula to cell B2. Why is this important? Because when you drag the formula down to apply it to other cells, you want every line item to be divided by the value in B2, not a cell relative to it.

Step 3: Drag the Formula Down

Click on cell C2. You’ll see a small green square at the bottom-right corner. Click and hold this square, then drag it down to cover all the cells corresponding to the items on your income statement. When you release the mouse, Excel will automatically calculate the percentage for each line item, always dividing by the value in your locked cell, $B$2.

Step 4: Format as Percentages

Right now, your results are probably decimal numbers (e.g., 0.40 for COGS). To make them readable, highlight all the cells in Column C with your calculations. Right-click, select Format Cells, and choose Percentage from the list. Adjust to one or two decimal places for clarity.

Your completed table should feel instantly more insightful:

(Example Completed Table)

A: Item | B: Amount ($) | C: % of Revenue Revenue | $500,000 | 100.0% COGS | $200,000 | 40.0% Gross Profit | $300,000 | 60.0% Marketing | $50,000 | 10.0% Net Income | $100,000 | 20.0%

Step-by-Step: Vertical Analysis of a Balance Sheet

The process for a balance sheet is nearly identical, but with one key difference: you have two separate base figures. You'll apply one formula for all assets and another for all liabilities and equity.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Analyze Total Assets

First, find your Total Assets line item. Let's say it's in cell B15 with a value of $800,000. This is the base for every asset item above it (Cash, Inventory, etc.). In the adjacent percentage column (C), use a formula similar to the one for the income statement. For your first asset item in B2, the formula in C2 would be:

=B2/$B$15

Again, use the dollar signs to lock the formula to the Total Assets cell. Drag this formula down for every asset, and format the column as a percentage. The "Total Assets" line will equal 100%.

Step 2: Analyze Total Liabilities & Equity

Next, find your Total Liabilities & Shareholder's Equity line. For a balanced sheet, this number will be identical to Total Assets. Let's assume it's in cell B30 ($800,000). For your liability and equity items, you will reference this new base. For the first liability item in cell B18, your formula in C18 would be:

=B18/$B$30

Drag this formula down for all liabilities and equity line items. After formatting as a percentage, the "Total Liabilities & Shareholder's Equity" line will also show 100%.

Now, you have a common-size balance sheet that clearly shows what percentage of your assets are in cash vs. inventory, and how your operations are funded through debt vs. shareholder equity.

Putting the Numbers Into Context: Interpreting the Analysis

Creating the percentages is just half the battle, the real value comes from interpreting them. Now that you have a common-size statement, what should you look for?

On the Income Statement:

• A high and rising COGS percentage might indicate declining pricing power or rising input costs, both of which squeeze your gross margin.
• Your Gross Profit percentage (or Gross Margin) is a core indicator of profitability. Is it stable, growing, or shrinking over time?
• Comparing your Marketing & Sales percentage to your revenue growth rate can help assess efficiency. A high percentage isn't necessarily bad if it’s driving top-line growth.
• The final Net Profit Margin percentage tells you how many cents of profit you generate for every dollar of sales. This is one of the most-watched metrics for overall business health.

On the Balance Sheet:

• A high Inventory percentage of total assets could signal that you have too much cash tied up in slow-moving stock.
• A high Accounts Receivable percentage might mean your business is struggling to collect payments from customers in a timely manner.
• Looking at the Debt to Asset ratio (Total Liabilities / Total Assets) reveals how leveraged your company is. A higher percentage means the business relies more on debt to finance its assets.

The goal is to move from "what" the numbers are to "why" they are what they are. Vertical analysis gives you the signal, your job is to investigate the story behind it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Visualizing Your Vertical Analysis in Excel

To make your insights even easier to understand and share, you should visualize them. Excel has simple tools to turn your new percentage data into professional charts quickly.

Pie Charts for Single-Period Composition

A pie chart is perfect for showing the makeup of a category. For instance, you could visualize the breakdown of your Operating Expenses.

1. Highlight the labels (e.g., Marketing, Salaries, Rent) and their corresponding percentage values.
2. Go to the Insert tab on the Ribbon, click on the Pie Chart icon, and select your preferred style.
3. The chart will instantly show how your expenses are structured.

Stacked Column Charts for Comparing Periods

To see how your company's financial structure has changed over time, a stacked column chart is ideal. This is where creating common-size statements for multiple periods pays off.

1. Organize your data with periods as columns (e.g., Q1, Q2, Q3) and categories (e.g., COGS, Gross Profit, Operating Expenses) as rows.
2. Select your entire data set.
3. Go to Insert > Chart > 2-D Column > Stacked Column.

This will create a chart where each column represents a period, adding up to 100%. You can visually track whether your cost of goods sold is taking up a bigger piece of the revenue pie over time, a trend that is much harder to spot in a sea of raw numbers.

Common Pitfalls and Pro Tips

Vertical analysis in Excel is straightforward, but a few common mistakes can trip you up. Here's how to avoid them.

• Forgetting Absolute References ($): This is the number one issue. If you use =B2/B2 and drag it down, the formula becomes =B3/B3, =B4/B4, etc., which isn't what you want. Double-check that your base figure is locked with dollar signs, like $B$15.
• Using the Wrong Base: Always confirm you’re dividing income statement items by Total Revenue and balance sheet items by their respective totals (Total Assets or Total Liabilities & Equity).
• Formatting Errors: Make sure your column is formatted as a percentage. It makes the data far easier to read and interpret.
• Pro Tip - Use IFERROR: Sometimes, your formula might return a #DIV/0! error, especially if your base number cell is blank or zero. To keep your report clean, wrap your formula in an IFERROR function. This will return a zero or a blank cell if an error occurs.

=IFERROR(B2/$B$2, 0)

Final Thoughts

By transforming raw financial statement figures into common-size percentages, vertical analysis gives you a powerful new perspective on your business. This method helps you immediately understand the financial structure of your company, track critical changes over time, and compare your performance to others, all with a few simple formulas in Excel.

While performing vertical analysis in Excel is a fundamental skill, the process of manually exporting data, building spreadsheets, and updating them every month can be time-consuming. We built Graphed to automate this entire process. You can connect your financial data sources in seconds, and our AI can instantly create common-size financial statements and dashboards, allowing you to get to insights without the spreadsheet wrangling.