How to Show Profit and Loss in Excel Chart

A profit and loss (P&L) statement can tell you if your business is making or losing money, but a wall of numbers in a spreadsheet doesn't always show you the full story at a glance. Transforming that data into an Excel chart makes your financial performance immediately obvious. This guide will walk you through setting up your data and creating a clear, professional P&L chart - specifically using Excel’s powerful waterfall chart feature.

Why Bother Charting Your Profit and Loss?

Before diving into the steps, it’s worth understanding why this is such a useful skill. A standard P&L statement requires you to read and mentally process each line item. A P&L chart, however, gives you an instant visual summary.

Here’s what you gain by visualizing your P&L:

• Instant Comprehension: You can see the relationship between revenue and expenses immediately. Are your marketing costs a small dip or a massive chunk of your revenue? A chart tells you instantly.

• Easy Trend Spotting: When you place P&L charts for different months side-by-side, you can quickly spot emerging trends. Is the cost of goods sold (COGS) creeping up? Is a new marketing channel actually driving down relative ad spend? Visuals make these patterns jump out.

• Better Communication: Sharing financial data with your team, investors, or stakeholders can be challenging. A chart is a universal language that simplifies complex information, ensuring everyone gets the main points without needing to be a finance expert.

Step 1: Get Your Data Ready

The single most important step in creating any chart is structuring your data correctly. Excel is powerful, but it can't read your mind. For a P&L chart, you need a simple, clean table that outlines your income and expenses.

Let's use a sample P&L for a small e-commerce business for the month of April. Your data should be organized in two columns: Category and Amount.

Here’s the key rule: Revenue is a positive number, and all expenses are negative numbers. This is critical for showing how costs eat into your income.

Create a simple table in Excel like this:

• A1: Category | B1: Amount

• Sales Revenue | 15,000

• COGS | -4,500

• Marketing | -2,000

• Salaries | -3,500

• Software Subscriptions | -500

• Office Rent | -1,200

Once your primary line items are listed, you’ll want to calculate key subtotals. The most common are "Gross Profit" and "Net Profit." However, for a standard waterfall chart, you only need to calculate the final "Net Profit" right in your table. This will serve as our final anchor point.

Add a "Net Profit" row at the bottom and use the SUM formula to calculate the total.

• A8: Net Profit | B8: =SUM(B2:B7)

Your finished table should look something like this:

Step 2: Create a Waterfall Chart in Excel

While a simple column chart can show positive and negative values, it doesn't illustrate the flow from a starting value (revenue) to an ending value (profit). The perfect tool for this is the Waterfall chart, which is built into modern versions of Excel (Excel 2016 and newer).

A waterfall chart shows a running total as values are added and subtracted. It's designed to visualize financial statements like a P&L, making it incredibly easy for anyone to understand how you arrived at your net profit.

How to Build It, Step-by-Step

Using the data table we just prepared, creating the chart is surprisingly simple.

1. Select Your Data: Click and drag to highlight your entire data range, including the headers and the "Net Profit" row (in our example, A1 to B8).

2. Insert the Chart: Navigate to the Insert tab on the Excel ribbon. Click the icon for "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart." From the dropdown menu, select Waterfall.

Excel will immediately generate a chart. It will look pretty good, but you'll notice that "Sales Revenue" and "Net Profit" are treated like all the other values - they just float there. We need to tell Excel that these are totals, not step-changes.

Designating the Totals

This is the magic step that makes a waterfall chart work. You need to "set" your starting and ending columns as totals, so they are anchored to the baseline and show the cumulative result.

1. Set the first column as total: Double-click on the very first bar in your chart ("Sales Revenue"). This will select just that single bar. Now, right-click on it and choose Format Data Point from the menu. A pane will appear on the right. Just check the box that says "Set as total." You'll see the bar immediately turn grey (by default) and anchor itself to the horizontal axis.

2. Set the last column as total: Next, do the exact same thing for the "Net Profit" bar. Double-click to select only that bar, right-click, choose Format Data Point, and check the box for "Set as total." This bar will also anchor itself to the axis, showing the final landing point after all expenses are subtracted.

Step 3: Polish and Finalize Your P&L Chart

Your chart is now functionally correct, but a bit of formatting will make it much easier to read and present.

Customize Colors for Clarity

By default, Excel colors increase green, and decrease orange or blue. These colors work, but let's make it even more intuitive.

• Change 'Decrease' color to Red: You are likely used to "red" meaning loss or an expense. Double-click one of the expense bars (like COGS or Marketing). Then, in the Format Data Point pane, go to the "Fill & Line" section (the paint bucket icon). Under "Fill," you can change the color for all "Decrease" bars to a shade of red.

• Recolor your Totals: You can also set a custom color for your totals. Some people like using a neutral color like grey or a strong blue to make the start and endpoints stand out. Click the "Net Profit" bar, go to the Fill menu, and pick your color.

Add a Meaningful Title and Data Labels

Finally, some small touches will elevate your chart from good to great.

• Add a Descriptive Title: Click on "Chart Title" and change it to something clear, like "April Profit & Loss Statement" or "Monthly P&L for Q2."

• Add Data Labels: Click on the chart, then click the green '+' sign that appears in the top-right corner. Check the box for Data Labels. This will add the corresponding values to each bar, so your audience doesn't have to guess or refer back to the y-axis.

• Remove Chart Clutter: For a P&L chart, the colored bars (called connectors) show the flow, so you don't really need the vertical gridlines or even the y-axis labels if you have data labels. You can click on any of these elements and press the Delete key to remove them for a cleaner look.

Following these steps will give you a polished, insightful waterfall chart that clearly communicates your business's financial story - transforming a boring table of numbers into a powerful decision-making tool.

Final Thoughts

Visualizing your profit and loss in Excel, especially with a waterfall chart, elevates a standard financial report into an intuitive story. It turns abstract numbers into a clear, visual journey from revenue to profit, allowing anyone on your team to understand the financial health of your business at a single glance.

While Excel is a fantastic tool, you still have the manual upkeep of pulling data from Shopify, QuickBooks, Google Ads, and other platforms to build your reports. At Graphed, we created a way to skip the spreadsheet wrangling entirely. We allow you to connect all your data sources in one place, so you can just ask in plain English - "show me a P&L report for last quarter" - and instantly get a real-time dashboard that updates automatically, saving you hours of manual work.