How to Make a Break Even Chart in Excel

Knowing your break-even point isn't just an accounting exercise - it's the financial baseline that tells you exactly what it takes to keep your business running. Creating a break-even chart in Excel turns that number into a powerful visual story. This guide will walk you through setting up your data, calculating the core components, and building a professional break-even chart step-by-step.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is Break-Even Analysis?

A break-even analysis identifies the point at which your total revenue exactly equals your total costs. At this point, you aren't making a profit, but you also aren't losing money - you're "breaking even." Anything below this point is a loss, and everything above it is profit. Understanding this is crucial for making smart business decisions about pricing, costs, and sales targets. The analysis is built on three core components.

The Key Components of a Break-Even Analysis

To do a proper analysis, you first need to categorize your costs correctly. Every business expense falls into one of two buckets: fixed or variable.

• Fixed Costs: These are expenses that don't change regardless of how many products you sell or services you deliver. Think of things like rent for your office, monthly software subscriptions, employee salaries, and insurance payments. Whether you sell one item or one thousand, these costs remain the same.
• Variable Costs: These costs are directly tied to the number of units you sell. If you sell more, these costs go up, if you sell less, they go down. Examples include raw materials, shipping costs, sales commissions, and transaction fees.
• Selling Price per Unit: This is the price at which you sell a single unit of your product or service.

By understanding how these three elements interact, you can calculate your break-even point and visualize the path to profitability.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Setting Up Your Break-Even Data in Excel

Before you can build a chart, you need a clean data setup in your spreadsheet, making your formulas easier to manage and update. Let's use the example of a small business that sells custom coffee mugs.

Step 1: Input Your Core Assumptions

First, create a small table for your main variables. Putting these in one place makes it easy to change your assumptions later without having to rewrite every formula.

In a blank sheet, set up cells for your inputs like this:

• Fixed Costs: $5,000 per month (this includes rent, salaries, etc.)
• Variable Cost per Unit: $5 (the cost of the plain mug, printing, and packaging)
• Selling Price per Unit: $15

Step 2: Calculate Your Contribution Margin

The contribution margin is a key metric. It tells you how much money from each sale is left over to cover your fixed costs. The formula is straightforward:

Contribution Margin per Unit = Selling Price per Unit - Variable Cost per Unit

Using our example, that’s $15 - $5 = $10. This means every mug you sell contributes $10 towards paying off your $5,000 in fixed costs.

Step 3: Calculate Your Break-Even Point in Units

Now you can calculate the exact number of units you need to sell to break even. This is where your fixed costs and contribution margin come together.

Break-Even Point (Units) = Total Fixed Costs / Contribution Margin per Unit

For our mug business, that’s $5,000 / $10 = 500 units. You need to sell 500 mugs per month to cover all your expenses.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Creating the Data Table for Your Chart

The calculation above gives you a single number, but a chart needs a range of data to visualize the relationship between costs and revenue at different sales volumes. We'll create a data table that shows what happens at different levels of units sold.

Step 1: Set Up Your Table Columns

In a new section of your spreadsheet, create the following headers:

• Units Sold
• Fixed Costs
• Total Variable Costs
• Total Costs
• Total Revenue

Step 2: Populate the "Units Sold" Column

Fill this column with a range of quantities, starting from 0. It's a good practice to include quantities both below and above your calculated break-even point (500 units). For example, list units in increments of 100:

• 0
• 100
• 200
• 300
• 400
• 500 (your break-even point)
• 600
• 700
• ...and so on.

Step 3: Add the Formulas for Each Column

Now, use formulas to fill in the rest of the table. To make this easier, use absolute cell references (with $) for your main inputs so you can drag formulas down without changing references. Assume your initial inputs are in cells B2, B3, and B4.

Suppose your "Units Sold" data starts in cell A8.

• Fixed Costs (cell B8): =$B$2 (drag down for all rows)
• Total Variable Costs (cell C8): =A8*$B$3 (drag down)
• Total Costs (cell D8): =B8+C8 (drag down)
• Total Revenue (cell E8): =A8*$B$4 (drag down)

Drag the Formula to Populate Your Table

After setting these formulas in the first data row, drag them down to fill the entire range. This will generate the data points showing how costs and revenue change with units sold.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Visualizing Your Data with a Break-Even Chart in Excel

With your table data set up, you can create the visualization. It’s a straightforward process once the data is ready.

Step 1: Select Your Data

Highlight the columns: "Units Sold", "Total Revenue", and "Total Costs". Do not include "Fixed Costs" or "Total Variable Costs" separately.

Step 2: Insert the Chart

Go to the Insert tab in Excel’s ribbon. In the Chart section, choose a Scatter with Straight Lines chart. This type displays the relationship between units sold and the revenue/cost lines effectively.

Step 3: Clean Up and Format Your Chart

Excel will generate the chart immediately. Final formatting tips:

• Add Chart Title: e.g., "Break-Even Analysis"
• Axis Titles: Add labels like "Units Sold" (horizontal axis) and "Dollars ($)" (vertical axis)
• Legend: Ensure it clearly labels the lines as "Total Revenue" and "Total Costs"

When done, you'll see the revenue and cost lines intersect at the break-even point. The point where they cross is your break-even volume, and the space after that indicates profit.

Final Thoughts

Building a break-even chart in Excel transforms static numbers into a dynamic visual tool. It helps you understand how variations in sales volume impact profitability and guides decision-making for pricing, costs, and sales strategies. Mastering this analysis offers valuable insights for steering your business toward sustainable growth.