How to Do Financial Analysis in Excel

Excel is the go-to tool for financial analysis for a reason - it’s powerful, flexible, and already on your computer. This guide will walk you through the essential techniques for conducting thorough financial analysis in Excel, from organizing your data to building insightful dashboards.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Setting Up Your Excel Workbook for Analysis

Before you can analyze anything, you need a solid foundation. A well-organized workbook is the difference between clear insights and a confusing mess. The best practice is to separate your raw data, calculations, and final presentation onto different sheets.

Start by creating three core tabs in your workbook:

• Data Entry Sheet: This sheet holds your raw financial data, typically exported from your accounting software (like QuickBooks) or bank. You might have separate tabs for your Income Statement, Balance Sheet, and Cash Flow Statement. The key is to keep this data clean and untouched - think of it as your single source of truth.
• Analysis Sheet: This is where you'll perform your calculations. You'll reference cells from your data sheets to calculate ratios, growth rates, and other metrics. This separation prevents you from accidentally overwriting a critical formula or a raw data point.
• Dashboard Sheet: This is your summary and visualization layer. It will feature key charts, tables, and metrics pulled from your Analysis sheet, giving you and your stakeholders a high-level overview of financial health at a glance.

Keeping these elements separate makes your analysis easier to audit, update, and understand. When new data for the month comes in, you simply add it to the data sheet, and your formulas and charts on the other sheets will update automatically.

Essential Excel Functions for Financial Analysis

Excel has hundreds of functions, but a handful do most of the heavy lifting in finance. Mastering these will give you the ability to work through almost any analytical task.

Core Functions for Everyday Operations

• SUMIFS/COUNTIFS/AVERAGEIFS: These are the intelligent cousins of the basic SUM, COUNT, and AVERAGE functions. They allow you to add, count, or average cells that meet specific criteria. For example, you can calculate total revenue from a specific product line or average transaction value for a particular region. =SUMIFS(sum_range, criteria_range1, criteria1)
• IF Statements: The IF function is fundamental for creating logic in your models. It allows Excel to return one value if a condition is true and another if it's false. You can use it to flag accounts that are over budget or categorize transactions based on their value. =IF(logical_test, [value_if_true], [value_if_false])
• XLOOKUP / VLOOKUP: These functions are used to find things in a table or a range by row. For example, if you have a list of employee IDs and want to pull their corresponding department from another table, LOOKUP functions are what you'll use. While VLOOKUP is a classic, the newer XLOOKUP is more flexible and powerful, allowing you to search in any direction. =XLOOKUP(lookup_value, lookup_array, return_array)

Key Financial Functions

• NPV (Net Present Value): Use this to analyze the profitability of a projected investment or project. It calculates the present value of a series of future cash flows, helping you determine if a future stream of revenue is worth the initial investment today. =NPV(rate, value1, [value2], ...)
• IRR (Internal Rate of Return): This function finds the interest rate at which the net present value of cash flows (both positive and negative) equals zero. It's often used to compare the profitability of different investments. A higher IRR is generally better.
• PMT (Payment): This function calculates the payment for a loan based on a constant interest rate and constant payments. It’s perfect for calculating monthly mortgage or car loan payments.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Core Financial Analysis Techniques in Excel

With your data organized and key functions in hand, you can start performing common types of analysis to uncover trends and insights about your business.

1. Horizontal Analysis (Trend Analysis)

Horizontal analysis compares financial data over several periods to see how figures have changed over time. For example, you might look at your revenue growth from quarter-to-quarter or year-over-year. It helps you spot growth trends, seasonality, or potential red flags.

To perform it in Excel:

1. Set up your income statement data with periods in columns (e.g., 2022, 2023, 2024).
2. In a new column, calculate the change in value with the formula: =Current_Period - Previous_Period.
3. In another column, calculate the percentage change: =(Current_Period - Previous_Period) / Previous_Period. Be sure to format this cell as a percentage.

Doing this for each line item (Revenue, COGS, Operating Expenses) tells a powerful story about where your business is growing or shrinking.

2. Vertical Analysis (Common-Size Analysis)

Vertical analysis shows the relationship between items on a single financial statement by displaying each item as a percentage of a base figure. On an income statement, every line item is typically shown as a percentage of total revenue. On a balance sheet, it's typically a percentage of total assets.

This method allows you to understand the cost structure and profitability of your business and compare your company to others in your industry, regardless of size.

To implement it in Excel:

1. On your income statement, dedicate a new column next to your revenue figures.
2. Use a formula to divide each line item by the total revenue for that period. Use absolute references (like $B$10) for the total revenue cell so you can easily drag the formula down. =A2/$A$10

Now you can easily see that, for instance, your marketing spend was 15% of your revenue in Q1 but grew to 20% in Q2.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Financial Ratio Analysis

Ratios are a popular way to evaluate a company's performance and financial health. Calculating these in Excel is a matter of simple arithmetic once your financial statements are correctly laid out.

Profitability Ratios

• Gross Profit Margin: Measures how much profit is made on each dollar of sales after accounting for the cost of goods sold. Formula: (Revenue - COGS) / Revenue
• Net Profit Margin: Shows the percentage of revenue left after all expenses, including taxes and interest, have been deducted. Formula: =Net_Income / Revenue

Liquidity Ratios

• Current Ratio: Assesses a company’s ability to pay off its short-term liabilities with its short-term assets. A ratio above 1 is generally considered good. Formula: =Current_Assets / Current_Liabilities

Leverage Ratios

• Debt-to-Equity Ratio: Indicates how much debt a company is using to finance its assets relative to the value of shareholder equity. Formula: =Total_Debt / Shareholder_Equity

On your 'Analysis' sheet, create a section for these ratios and have them pull directly from your data-entry sheets. This way, they update automatically with new information.

Visualizing Your Analysis with a Financial Dashboard

Numbers and ratios are great, but visualizing them helps communicate insights quickly and effectively. A dashboard is a single sheet that summarizes your most important financial KPIs.

Using PivotTables to Summarize Data

PivotTables are one of Excel’s most powerful features for financial analysis. They let you summarize large datasets in seconds. For example, you could take a list of all your sales transactions for a year and quickly create a summary table showing total sales by region, product, and month - all without a single formula.

Select your raw data table, go to Insert > PivotTable, and drag and drop fields into the Rows, Columns, and Values panes to explore your data interactively.

Creating Essential Charts

Once you have summary tables (from a PivotTable or a custom calculation), you can create charts to visualize trends:

• Line Charts: Perfect for showing trends over time, like your monthly recurring revenue (MRR) or website traffic over the last year.
• Column/Bar Charts: Ideal for comparing different categories, such as sales performance by employee or marketing expenses by channel.
• Waterfall Charts: An excellent tool for showing how an initial value is affected by a series of positive and negative changes. Use a waterfall chart to visualize your net income, starting with total revenue and subtracting various costs one by one.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Adding Interactivity with Slicers

Slicers are interactive filters for your PivotTables and charts. After creating a PivotChart, you can add a slicer (go to PivotChart Analyze > Insert Slicer) for a field like "Year" or "Product Category." Now, you can click buttons on the slicer to filter your entire dashboard dynamically, allowing users to drill down into the data that matters most to them.

Advanced Tips for Power Users

• Conditional Formatting: Use this feature to automatically highlight cells based on their value. For instance, you can make all negative numbers automatically turn red or apply a color scale to show a range of values. This makes large tables easier to read.
• Data Tables: Found under the "What-If Analysis" menu, Data Tables allow you to see how changing one or two variables in your model affects your final output. You can use this for scenario analysis to see how changes in interest rates or sales growth would impact your profitability.

Final Thoughts

Excel remains an invaluable tool for conducting deep financial analysis. By systematically organizing your data, mastering essential functions, performing trend and ratio analysis, and visualizing your findings, you can turn a simple spreadsheet into a powerful engine for business insights and decision-making.

While Excel is fantastic, the repetitive process of downloading records, cleaning data, and ensuring all your formulas are referencing the right cells can consume entire days. We've built Graphed because we believe your time is better spent on strategy, not spreadsheet management. Graphed connects directly to financial platforms like QuickBooks and Stripe, automating the data pipeline and creating live, interactive dashboards instantly. You can ask complex questions in plain English - like "what's our net profit margin this quarter compared to last?" - and get answers and visualizations in seconds, freeing you to focus on growing the business.