How to Make a Breakeven Chart in Excel

A break-even analysis is one of the most fundamental tools for any business, providing a clear picture of the sales volume you need to achieve to cover all your costs. While the formula itself is straightforward, visualizing it in a chart makes the concept instantly understandable for you, your team, and any stakeholders. This tutorial will walk you through, step by step, how to create a dynamic and professional-looking break-even chart in Excel.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, What Are We Actually Building?

Before diving into Excel, let's quickly review the core components of a break-even analysis. The break-even point is where your Total Revenue exactly equals your Total Costs. In other words, you're not making a profit, but you're not losing money either. To find it, you need three key pieces of information:

• Fixed Costs: These are expenses that don't change regardless of how many units you sell. Think of things like rent, salaries, and insurance. They are the baseline costs you incur just for being in business.
• Variable Costs: These are costs directly related to producing each unit. This includes raw materials, production labor, and sales commissions. If you sell more, your variable costs go up.
• Selling Price Per Unit: This is simply how much you charge a customer for one unit of your product or service.

The chart we'll create will show two lines: one for Total Costs and one for Total Revenue. Where these two lines intersect - that's your break-even point.

Step 1: Set Up Your Initial Data in Excel

The foundation of your chart is a small, clearly organized table for your core assumptions. This setup makes it easy to update your analysis later without having to change any complex formulas.

Open a new Excel worksheet and set up a table like this:

1. In cell A1, type Inputs.
2. In cell A2, type Fixed Costs. In B2, enter your value (e.g., 10000). Format this cell as currency.
3. In cell A3, type Variable Cost Per Unit. In B3, enter your value (e.g., 12).
4. In cell A4, type Selling Price Per Unit. In B4, enter your value (e.g., 30).

Your sheet should look clean and simple, with your three critical variables clearly laid out. Keeping these inputs separate makes "what-if" analysis incredibly easy, as we'll see later.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Calculate Your Break-Even Point

With our inputs defined, we can calculate the break-even point in terms of units sold. The formula is:

Break-Even Point (Units) = Fixed Costs / (Selling Price Per Unit - Variable Cost Per Unit)

The parenthesis part, (Selling Price Per Unit - Variable Cost Per Unit), is also known as the Contribution Margin Per Unit. It's the amount of money from each sale that is left over to contribute towards covering your fixed costs.

Let's add this to our Excel sheet:

1. In cell A6, type Break-Even Point (Units).
2. In cell B6, enter the following formula:

=B2/(B4-B3)

Using our example numbers, Excel will calculate the result as 555.56. This means you need to sell approximately 556 units to cover all your costs.

Step 3: Build the Data Table for the Chart

A chart needs data presented over a range, not just a single point. To visualize the path toward breaking even, we need to create a table that calculates revenue and costs at different sales volumes.

Choose an empty area of your spreadsheet (e.g., starting in column D) and create the following headers in row 1:

Units Sold | Fixed Costs | Variable Costs | Total Costs | Total Revenue | Profit/Loss

Now, let's populate the data for each column step by step.

Column 1: Units Sold

This column represents different sales scenarios. Start from 0 and go up to a number well past your calculated break-even point. This ensures your chart clearly shows a profit zone.

If our break-even is around 556 units, going up to 1,000 or 1,200 units would be a good range.

1. In cell D2, type 0.
2. In cell D3, type 100.
3. Select both cells D2 and D3. Click the small square (the fill handle) at the bottom-right corner of your selection and drag it down until you reach 1200. Excel will automatically fill the series for you in increments of 100.

Column 2: Fixed Costs

This is the easy part. Fixed costs remain the same at every level of production. But here's a critical Excel tip: use an absolute cell reference.

1. In cell E2, type the formula:

=$B$2

The dollar signs ($) lock the reference to cell B2. This tells Excel, "no matter where you copy this formula, always refer back to cell B2." Now, double-click the fill handle in cell E2 to copy this formula all the way down. The value $10,000 should appear in every row.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Column 3: Variable Costs

Variable costs are the cost per unit multiplied by the number of units sold.

1. In cell F2, type the formula:

=D2*$B$3

Notice we use a relative reference for D2 (the units sold in this row) and an absolute reference for $B$3 (our constant variable cost per unit). Copy this formula down the column.

Column 4: Total Costs

Total Costs are simply your fixed costs plus your variable costs for each sales level.

1. In cell G2, type the formula:

=E2+F2

Since both references are relative, you can just drag this formula down the column without needing any dollar signs.

Column 5: Total Revenue

Total Revenue is the selling price per unit multiplied by the number of units sold.

1. In cell H2, type the formula:

=D2*$B$4

You'll use an absolute reference for the selling price ($B$4). Double-click the fill handle to copy it down.

Column 6: Profit/Loss

This column isn't required for the core break-even chart, but it's great for confirming your results and seeing profitability grow.

1. In cell I2, type the formula:

=H2-G2

Drag the formula down. You should see negative numbers (losses) at the beginning, turning into positive numbers (profits) right after the break-even point you calculated earlier.

Your data table is now complete and should look clean and orderly, with all calculations linked and ready for plotting.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Create the Break-Even Graph

Now for the fun part - turning that table of data into a clear, insightful visual.

1. Select the data for Units Sold, Total Costs, and Total Revenue. The easiest way to do this is to click and drag to highlight the range D1:D14, then hold down the Ctrl key (or Cmd on Mac) and select the ranges G1:H14.
2. Go to the Insert tab on Excel's ribbon.
3. In the Charts group, click on Insert Scatter (X, Y) or Bubble Chart.
4. From the dropdown menu, choose Scatter with Straight Lines. Excel will instantly generate a chart.

You'll see two lines on your chart. The point where they cross is your break-even point. Now, let's clean it up to make it more professional.

Customize Your Chart for Clarity

A default chart is a starting point, not a finished product. Let's add some essential elements.

• Give it a Title: Click on "Chart Title" and change it to something meaningful, like "Break-Even Analysis for [Your Product]".
• Add Axis Titles: With the chart selected, a green "+" icon should appear to the right. Click it, check the box for "Axis Titles."
• Make an Optional Tweak - Add the Fixed Cost Line: For bonus clarity, you can add a horizontal line representing your Fixed Costs to show how Total Costs are built.

A new, perfectly flat line will appear on your chart, visually separating your fixed and variable costs.

Reading the Finished Chart

Your finalized chart now tells a powerful story:

• The intersection of the Total Revenue line and the Total Costs line is your break-even point.
• The space between those two lines to the left of the intersection is your loss zone. Here, costs are higher than revenue.
• The space to the right of the intersection is your profit zone. Here, revenue has surpassed costs and continues to climb, widening your profit margin.

Step 5: Perform What-If Analysis

The real magic of setting up your spreadsheet this way is how dynamic it is. Go back to your Inputs table in cells B2:B4 and change a number.

• What happens if you increase the Selling Price Per Unit to $35? The Total Revenue line will get steeper, and the break-even point will shift to the left, meaning you need to sell fewer units to become profitable.
• What happens if your Fixed Costs increase to $15,000? Both the Fixed Cost and Total Costs lines will jump up, shifting the break-even point to the right, meaning you now have to sell more to cover your expenses.

The chart updates instantly with every change, making it a fantastic tool for planning, budgeting, and making strategic decisions.

Final Thoughts

Building a break-even chart in Excel is a brilliant way to transform raw numbers into an actionable business planning tool. This visual approach clearly communicates financial targets and helps you test different strategies to see how they impact your profitability and sales goals.

As insightful as this analysis is, we know that getting all your data into one spreadsheet can be a job in itself, especially when revenue comes from Shopify and costs live in accounting software. We actually built Graphed to solve this problem. You can connect your various data sources directly and ask questions in plain English - like "create a break-even chart using revenue from Shopify and expenses from last month" - to get a live, automated dashboard in seconds, without any manual data entry or formula-building.