How to Do Horizontal Analysis in Excel

Trying to understand how your business’s key numbers have changed over time? Horizontal analysis is the perfect tool for the job. It cuts through the noise of raw data to show you the growth, decline, or stability of your finances from one period to the next. This guide will walk you through exactly how to perform a horizontal analysis in Microsoft Excel, turning your spreadsheets into powerful tools for tracking trends.

What Exactly is Horizontal Analysis?

Horizontal analysis, also known as trend analysis, is a technique used to compare financial information from different time periods. Instead of just looking at a single profit and loss (P&L) statement in isolation, you line up two or more periods side-by-side to see what has changed. For example, you might compare your revenue this quarter to the same quarter last year or last month’s expenses to the previous month.

The beauty of this method is in its simplicity. The analysis calculates two key things:

• The Absolute Variance: The straight dollar amount change between the two periods. For example, "Revenue increased by $50,000."
• The Percentage Variance: The dollar amount change expressed as a percentage of the base period's figure. This gives context to the change. For example, "Revenue increased by 3%."

Doing this for each line item in your financial statements turns a static report into a dynamic story about your business performance.

Why is Horizontal Analysis So Helpful?

Learning how to use horizontal analysis isn’t just another spreadsheet trick. It’s a fundamental way to unlock valuable insights for your business. It can help you:

• Spot Trends and Patterns: Is your revenue consistently growing? Are certain expenses spiraling out of control? Horizontal analysis quickly uncovers these trends, giving you the bigger picture beyond a single month or quarter.
• Measure Performance: Did we perform better this year compared to last year? Horizontal analysis offers a clear, objective answer by quantifying the changes in key metrics like net income or cost of goods sold.
• Identify Anomalies: Did your marketing spend suddenly skyrocket without much return? Did sales take an unexpected nosedive? Large, unexplained fluctuations become immediately apparent and signal that something needs further investigation.
• Inform Strategic Decisions: By understanding historical trends, you can make more informed forecasts and strategic decisions. If you know what costs are creeping up over time, you can actively find ways to control them.

Setting Up Your Data in Excel

Before you can do any calculations, you need to structure your data correctly in Excel. This process is straightforward: a clean, well-organized layout can be the difference between an easy analysis and a frustrating obstacle.

The best way to do this is to list your financial line items in column A, followed by each financial period in its own column. For a year-over-year analysis, your table might look like this:

Sample Excel Layout for Horizontal Analysis:

Ensure each line item (e.g., Revenue, Sales, etc.) is in its own row, and every number is in the right format (not as text). Take a moment to double-check for typos or inconsistencies.

Step-by-Step Guide to Calculating Horizontal Analysis

With your data neatly organized, you’re ready to do the analysis. You’re going to add two more columns: one for the Absolute Change ($ variance) and one for the Percentage Change (% variance).

Step 1: Create Additional Headers

Next to your most recent period data (2023 in our example), add two new column headers:

• $ Variance
• % Variance

Step 2: Calculate the Absolute Change ($ Variance)

The first calculation shows the dollar amount change between the periods. It's a simple subtraction.

Click in the cell under your $ variance header next to your first line item (Revenue). Assuming 2022 Revenue is in cell B2 and 2023 Revenue is in cell C2, enter the following formula:

=C2-B2

Press Enter. Excel will show you the dollar increase or decrease in revenue. To apply this to the rest of your line items, simply click on the cell with the formula, move your mouse to the bottom right corner until it turns into a black cross (+), and then click and drag it down to cover all line items.

Step 3: Calculate the Percentage Variance (% Variance)

The percentage change puts the absolute change into context, showing how big the increase or decrease was in relation to the base period's number.

Click in the cell under your % variance header. Using the same cells as before, enter this formula:

=(C2-B2)/B2

This formula uses the equation: (Current Period - Base Period) divided by the Base Period.

Drag the fill handle down to apply this formula to the rest of your data.

Important Calculation Tip: If you have a line item that has a value of zero (“0”) in the base period, Excel will return an error (“#DIV/0!”) because you’re not allowed to divide by zero. To avoid this, you can wrap your formula in an IFERROR function:

=IFERROR((C2-B2)/B2, "N/A")

This formula tells Excel that if the calculation results in an error, it should instead display “N/A”.

Step 4: Format as Percentage

Right now, your % variance column is probably showing as decimals (e.g., 0.04). To make it easier to read, format it as a percentage.

• Select the entire % variance column.
• Right-click or use the ribbon to find the “Format Cells...” option.
• Choose “Percentage” from the number tab and set the number of decimal places you want.

Here's what your finished table will look like:

Visualizing Your Horizontal Analysis with Excel Charts

Numbers are great, but charts bring these insights to life at a glance. Excel makes it easy to turn your analysis into a simple visualization that highlights the most important changes.

Using a Bar Chart to Show Change

A clustered bar chart is an excellent choice because it clearly shows both positive and negative changes.

• Select the line items and your % variance data.
• On the 'Insert' tab, in the "Charts" section, choose a bar or column chart.
• Customize your chart with a title (e.g., "Year Over Year Variance"), label data values to show the exact percentage, and consider changing colors to make it easier to read.

Using Conditional Formatting for Quick Highlights

Another powerful visual tool in Excel is conditional formatting. This allows you to color code data based on their values, making it easy to instantly see what's up and what's down.

• Select your entire “% variance” column.
• From the “Home” tab, click on “Conditional Formatting” > “Color Scales”.
• Choose a color scale that highlights negative values in red and positive values in green.

Tips for Effective Horizontal Analysis

Doing the math is just half the battle. To get the most value from your analysis, keep these best practices in mind:

• Consistency is Key: Ensure all accounting procedures (e.g., depreciation methods) are consistent across all the periods you’re analyzing. If one period’s data is recorded differently, it’s going to skew the comparison.
• Understand the Context: A single number rarely tells the whole story. A 5% growth in revenue is only impressive if expenses haven’t risen by 7%. Always look at the relationships between line items.
• Look Beyond the Numbers: The most important part is understanding why any major changes happened. Did a marketing campaign drive a spike in sales, or did a new competitor force you to lower prices? This qualitative insight is where the true strategic value lies.

Final Thoughts

Horizontal analysis in Excel is a powerful way to track business performance over time by comparing financial data from one period to the next. By calculating both absolute and percentage changes, you can easily spot trends, assess progress, and unlock critical insights for making smarter decisions.

Building these reports manually can become time-consuming, especially when you need to pull data from your accounting software, your online store, and your POS manager just to get a full picture. That’s why we built Graphed to make this process efficient. Instead of dragging cells and pasting formulas into spreadsheets, you can analyze all your sources in one and simply ask: “Create a month-over-month sales report comparing inventory expenditures for the last six months.” We automate the financial reporting to help you focus on what your insights translate to, not drowning in them.