Break-Even Graph in Excel: Build and Read One

Charts & pivot tables · Updated November 18, 2026

To make a break-even graph in Excel, set up columns for units sold, revenue, and total costs, then plot them in a scatter chart with straight lines. The point where revenue meets total costs is the break-even point; with the example below, it is 600 units, or $30,000 in sales.

What a break-even graph shows

A break-even graph compares sales revenue with total costs as the number of units sold changes. Revenue usually starts at zero and rises with each sale. Total costs start at the fixed-cost amount and rise as variable costs accumulate. Where the two lines cross, revenue equals costs and the operation has neither a profit nor a loss under the assumptions in the model.

This is a simplified planning tool, not a prediction. It assumes a constant selling price per unit, a constant variable cost per unit, and fixed costs that do not change across the range shown. Real businesses may have discounts, multiple products, step costs, capacity limits, returns, or changing input prices. Those conditions can make actual results differ from a straight-line chart.

Break-even analysis is useful for questions such as how many units must be sold to cover a launch budget, how a price change shifts the target, or whether a proposed cost reduction matters. The graph makes the relationship visible, while the underlying formulas let you inspect the assumptions and calculate a precise threshold.

Set up the assumptions in Excel

In a blank worksheet, enter the example assumptions in these cells: A2 is Selling price per unit and B2 is 50; A3 is Variable cost per unit and B3 is 30; A4 is Fixed costs and B4 is 12000. These are illustrative dollar amounts, not a recommendation or a claim about any particular business. Use the same currency and time period throughout: for example, do not compare monthly fixed costs with annual sales.

In A5, enter Break-even units. With selling price in B2, variable cost per unit in B3, and fixed costs in B4, enter this formula in B5:

=B4/(B2-B3)

The result is 600 units because each unit contributes $20 toward fixed costs ($50 minus $30), and $12,000 divided by $20 is 600. The difference between selling price and variable cost is called the contribution margin per unit. If selling price is equal to or below variable cost, this calculation has no positive break-even quantity: each additional unit fails to contribute enough to cover fixed costs. Check those inputs rather than interpreting a negative or invalid result as a useful sales target.

Build the chart data table

Starting in row 8, create three headings: A8 Units, B8 Revenue, and C8 Total costs. In A9, type 0. In A10, enter the next formula and fill it down to create the sales levels you want to display:

=A9+100

This produces 100 in A10, then 200 in the next row when copied down. A step of 100 is convenient for this example, but choose a smaller step if you need a more detailed view or if the break-even point falls between the increments. In B9, with the units count in A9 and selling price in B2, enter the revenue formula:

=A9*$B$2

In C9, with fixed costs in B4, variable cost per unit in B3, and units in A9, enter the total-cost formula:

=$B$4+A9*$B$3

Fill B9 and C9 down alongside the units column. The dollar signs keep the assumption references fixed when formulas are copied; the A9 reference changes to match each row. At zero units, revenue is $0 and total costs are $12,000. At 600 units, revenue is $30,000 and total costs are also $30,000. That equality is the break-even point.

For this particular example, fill the units column through 1,000 so the chart shows a useful range below and above break-even. If you change the assumptions, extend the table far enough to include the new crossing point. You can type the first few unit values manually or use the increment formula and fill handle.

Insert a break-even scatter chart

A scatter chart is suitable because the horizontal axis represents a numeric quantity of units, rather than categories. In Excel for Microsoft 365 on Windows, select the populated range A8:C19 if your table runs from 0 to 1,000 in steps of 100, then choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter with Straight Lines. Excel should use column A for the horizontal values and columns B and C for the two vertical series.

Check the chart before relying on it. The horizontal axis should show units, and the vertical axis should show dollar amounts. The legend should identify Revenue and Total costs. If Excel has treated the units as a separate plotted series or used row numbers on the horizontal axis, right-click the chart and choose Select Data. Edit each series so its X values point to the units cells and its Y values point to the corresponding revenue or total-cost cells.

Add a clear title such as Break-Even Analysis, and label the axes with units and dollars. In Excel, select the chart and use Chart Design > Add Chart Element to add axis titles or gridlines. The precise appearance can vary slightly with chart selection and window size. On Excel for Mac, the chart type is also available from the Insert tab, though the ribbon layout can differ. Excel for the web offers chart insertion too, but some formatting controls may be arranged differently from the Windows desktop app.

Keep both series as lines, and avoid a 3-D chart: a flat scatter plot makes it easier to see where the values meet. You can format the revenue and cost lines in contrasting colors. If the chart is crowded, show fewer unit increments in the data table or simplify gridlines rather than removing labels that explain what the axes mean.

Find and label the exact break-even point

The visual crossing helps readers understand the result, but a chart based on increments may not contain a data point at the exact break-even quantity. Here, the 100-unit steps happen to include 600. If the formula gives, for example, 637.5 units, the lines may cross between plotted points. The chart can still show the general location, but calculate the result from the assumptions rather than estimating it by eye.

For the example, the break-even sales amount is break-even units multiplied by selling price. With the result in B5 and price in B2, enter this formula in B6:

=B5*B2

It returns $30,000. For a fractional unit result, explain the distinction between the mathematical threshold and an achievable whole-unit target. A business cannot sell half of a physical item in many contexts, so it may need to round the unit target upward; the appropriate treatment depends on what counts as a sale and how the business records it.

To make the crossing visible on the graph, add a row for the break-even quantity to the data table, then calculate revenue and total costs for that row using the same formulas. Alternatively, place a labeled note beside the chart. Do not imply that the chart itself proves profitability: it visualizes the inputs and arithmetic entered in the worksheet.

Interpret the result and test changes

Below 600 units in this example, total costs exceed revenue, so the model shows a loss. At 600, the two are equal. Above 600, revenue exceeds total costs, so the model shows an operating surplus before any items excluded from the assumptions. That last qualification matters: taxes, financing costs, owner compensation, or other expenses are not included unless they have been built into the cost inputs.

A practical way to use the worksheet is to change one assumption at a time and observe the effect. A higher fixed-cost amount raises the break-even quantity. A higher variable cost reduces the contribution margin and also raises the quantity needed. A higher selling price generally lowers the quantity needed, provided it does not also reduce demand or cause other changes that the simple model does not capture.

  • Price decision: Compare plausible prices and note how many units the model requires at each price.
  • Cost control: Separate fixed costs from per-unit costs so a proposed saving is entered in the right place.
  • Sales planning: Compare the break-even target with a realistic sales range, not just the maximum shown on the chart.
  • Scenario review: Keep a record of the assumptions behind each scenario so changes are explainable later.

These are model comparisons, not financial or business advice. Real prices, demand, contracts, and cost behavior can change the outcome. Confirm actual terms and accounting treatment with the relevant records or a qualified professional when the result will inform a consequential decision.

Common chart and formula problems

If the graph appears to start both lines at zero, check the total-cost formula. It needs to include fixed costs; otherwise the chart does not show the cost base that must be recovered. If the cost line slopes downward or the result is unexpectedly large, check that selling price exceeds variable cost and that the inputs use consistent units and periods.

If the lines do not cross within the chart, the plotted unit range may be too short, or the assumptions may imply no break-even point at all. Extend the unit table when the crossing is simply outside the visible range. If variable cost per unit is at least as high as selling price, extending the axis will not create a positive break-even quantity under the model.

Another frequent issue is using a Line chart with unit values treated as text categories. The points may look evenly spaced even when the unit intervals are not. A scatter chart uses numeric X values and is the better choice when spacing represents quantity. Also inspect number formatting: currency symbols and thousands separators improve readability, but formatting a cell as currency does not change the underlying arithmetic.

Finally, verify the workbook after changing inputs. Confirm the break-even formula, check a row below and above the crossing, and make sure the chart references the complete data range. A useful check is to compare revenue and costs at the calculated quantity: they should be equal apart from any rounding in displayed values.

Frequently Asked Questions

What chart type should I use for break-even analysis in Excel?
Use a scatter chart with straight lines. Plot units sold as the X values, then plot revenue and total costs as separate Y series. This keeps the horizontal axis numeric and shows where the two lines intersect.

How do I calculate the break-even point in Excel?
Divide fixed costs by contribution margin per unit: fixed costs divided by selling price minus variable cost per unit. In the example, that is $12,000 divided by ($50 minus $30), which equals 600 units.

Why does my break-even graph not show an intersection?
The unit range may stop before the crossing, or the model may have no positive break-even point. Check the chart data range and confirm that selling price is greater than variable cost per unit.

Can Excel show the break-even point automatically?
The chart displays the two series, but Excel does not automatically identify their crossing as a labeled break-even result in this basic setup. Calculate the quantity with a worksheet formula, then add a data row or a label to make the point clear.

Should fixed costs be included in the revenue line?
No. Revenue is units sold multiplied by selling price. Fixed costs belong in total costs, added to the variable costs that increase with units sold.

Can I use this graph for several products?
You can, but one price and one variable cost per unit may not represent a product mix. A multi-product model needs assumptions about each product’s contribution margin and the mix of units sold; a single-product chart can be misleading if that mix changes.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.