How to Make a Break-Even Chart in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Break-even analysis is very important for a business or a company. In the pre-production period, it is necessary to calculate the break-even point of the product or service. And, from a break-even chart, we can easily get those values and draw conclusions. In this article, we’ll demonstrate 6 easy and quick steps of how to make a break-even chart in Excel. So, let’s go through the article entirely to understand the topic properly.

How to Make a Break-Even Chart in Excel: 6 Steps

For ease of understanding, we are going to use a report on the Cost of Goods of ABC Company. This data set includes the Sales Quantity in units, the Unit Selling Price, the components of Fixed Cost, and the components of Variable Cost.

how to make a break even chart in excel

Now, we will make a break-even chart for this business using this dataset in Excel. So, let’s explore the method step by step.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Step 01: Calculate Different Cost Components

At the very beginning, we’ll sum up all the cost components. It’s simple and easy. Just follow along.

📌 Steps:

  • First of all, select cell C7 and start writing the following.

Instantly, Excel will show suggestions of functions related to this word.

  • Then, double-click on the SUM function to select it. Also, you could choose it by tapping the TAB key.

Calculate Different Cost Components

  • After that, go to the sheet Dataset and select cells in the C8:C12 range. Actually, those are the components of the fixed cost.

using SUM function to calculate break even point

Finally, the formula for cell C7 looks like the following.

  • Lastly, press ENTER.

Total Fixed Cost

Similarly, the formula in cell C8 to compute the Total Variable Cost is the following.


Total Variable Cost

Moreover, we can find out the Average Variable Cost which is the mean of the variable cost for each unit of product. Simply, we have to divide the total variable cost by the total production unit. The formula is also as simple as the following.


calculating average variable cost to make break even chart

We are done calculating the costs. Thus, let’s get to the next part.

Read More: How to Calculate Break Even Analysis with Formula in Excel

Step 02: Compute Break-Even Point of Sales

In this step, we will compute the break-even point for sales. Generally speaking, this break-even point consists of two elements. One is the Break-Even Sales Quantity and the other is the Break-Even Sales Amount. So, let’s begin.

📌 Steps:

  • At first, select cell C10 and enter the formula below.
  • Then, press ENTER.

Compute Break-Even Point of Sales

  • After that, go to cell C11 and insert the following formula.
  • As usual, tap the ENTER button.

Compute Break-Even Point of Sales to make chart

So, after selling 6071 units, the company will reach its break-even point. And then the sales amount will be $30,357.

Read More: How to Calculate Break-Even Sales with Formula in Excel

Step 03: Construct a Table of Costs, Revenue, and Profit

At this time, we’ll construct a table consisting of Units, different types of Costs, and their corresponding Revenue and Profit also. This table is crucial to plot the chart. So, without further delay let’s dive in.

📌 Steps:

  • At first, create the basic outline of the table in the B4:G14 range.

Construct a Table of Costs, Revenue, and Profit

  • Following this, give some units in ascending order in the Unit column.

  • Then, go to cell C5 and enter the following formula.
='Break Even'!$C$7
  • As always, hit ENTER.

Entering Fixed Costs

  • Now, bring the cursor to the right-bottom corner of cell C5 and it’ll look like a plus (+) sign. Actually,  it’s the Fill Handle tool.
  • After that, double-click on it.

using fill handle tool to make break even chart in excel

Instantly, all the remaining cells get the output automatically.

At this time, select cell D5 and write down the formula below.

=B5*'Break Even'!$C$9

In fact, we multiplied the number of Units by the Average Variable Cost. Here, we used an absolute cell reference, as the cell reference of cell C9 doesn’t get changed while using the Fill Handle tool.

Fetching Variable cost

Afterward, add the variable cost to the fixed cost to get the Total Cost in cell E5.


Calculating Total Costs to make break even chart in Excel

To get the Revenue, multiply the Units in cell B5 by the Unit Selling Price in cell C5 in the Break Even worksheet.

  • So, go to cell F5 and paste the formula below.
=B5*'Break Even'!$C$5
  • Afterward, press ENTER.

We all know how to get a profit. Just make the revenue greater than the cost. So, in Excel,

  • Navigate to cell G5 and put down the following formula.
  • Later, press ENTER.

Computing Profit

That’s how we got cost and revenue for various numbers of units of the product.

Step 04: Insert Break-Even Chart

Now, come to the main topic, how to make a break-even chart in Excel. It’s the most important part of this article. So, don’t miss it a bit. Let’s follow along.

📌 Steps:

  • Firstly, select the whole table without the Profit column only.
  • Then, proceed to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chart drop-down icon.
  • After that, choose Scatter with Smooth Lines and Markers type plot.

Insert Break-Even Chart

Immediately, you can see a chart in the sheet that looks like the following.

Break-Even Chart in Excel

  • Now, right-click on the marker on the line of any series just like in the image below.

As a result, the context menu will appear before us.

  • Here, just click on the Format Data Series… option.

Marker of break even chart

In the Format Data Series task pane,

  • At first, click on the Fill & Line icon.
  • Then, select the Marker tab.
  • After that, expand the Marker options and select None.
  • Later, expand the Fill section and choose No fill from the available options.

Format Data Series Task Pane

Do the same for other series as well. Our chart looks like the following now: neat and clean.

Minimal outlook of the break even chart

Currently, we’ll distinguish between the line of fixed cost and variable cost.

  • Firstly, right-click on the line of fixed cost.
  • From the context menu, select Format Data Series….

On the Format Data Series task pane,

  • Again, click on the Fill & Line icon.
  • In the Line section, set the Dash type as Long Dash Dot.

differentiate between two lines in the chart in excel

Now, look at the chart. It looks even more beautiful.

Inserting Break-Even Chart in Excel

Step 05: Determine Break-Even Point in the Chart

In this step, our task is to anticipate the position of the break-even point in the chart. Let’s see the process in detail.

📌 Steps:

  • First and foremost, right-click anywhere inside the plot area.
  • From the context menu, Choose the Select Data… option.

Determine Break-Even Point in the Chart

Select Data Source dialog box

Suddenly, the Edit Series input box appears.

  • Firstly, write BEP in the Series name box.
  • Secondly, go to the Break Even worksheet and give the references of cells C10 and C11 in the Series X values and Series Y values box respectively.
  • Thirdly, click OK.

Working on Edit Series dialog box

You can see the newly added series in the dialog box also. Our work is done here.

  • So, click OK.

  • Similarly, open the Format Data Series task pane for the newly created series.
  • Then, go to the Marker options like before.
  • Here, select the Built-in option and choose the circle marker in the Type list.
  • Additionally, set the marker Size at 8.

Changing marker size

Look, how nicely our break-even point accommodated itself in the chart.

Determining Break-Even Point in the Chart

Step 06: Introduce Break-Even Line

Now, we’ll relate the axis values to the BEP. to do this, follow the steps below.

📌 Steps:

  • Initially, click on the plus-shaped Chart Element icon.
  • Then, click on the arrowhead at the right of Error Bars.
  • Hereafter, select More Options….

Introduce Break-Even Line

Suddenly, it opens the Format Error Bars task pane.

  • Presently, advance to the Error Bar Options tab icon.
  • Then, select Minus in the Direction section and No Cap in the End Style section.
  • In the Error Amount region, select Percentage and set it as 100%.

  • Thenceforth, click on the drop-down arrow beside Error Bar Options.
  • From the list, select Series “BEP” Y Error Bars and do the same for the Vertical Error Bar.

Format Error Bar in Break Even Chart in Excel

Lastly, our chart gets a final look.

After a bit of furnishing and formatting, the break-even chart looks like the below one.

Introducing Break-Even Line

Calculating Selling Price at BEP (Break-Even Point) Using Goal Seek Tool in Excel

Previously, we have shown how we can create a break-even chart in Excel. Now, we’ll exhibit how we can find out the selling price of a product at the break-even point using the Goal Seek feature of Excel. Let’s see it in action.

📌 Steps:

  • To calculate the Revenue, we inserted the following formula in cell C9.

Calculating Selling Price at Break Even Point Using Goal Seek Tool

And, the formula to determine the Profit in cell C10 is given below also.


What’s the concept of the break-even point? It’s the condition just before a company or business starts to gain profit. So, at the BEP, the profit should be zero. We’ll use this concept in the following part.

  • Primarily, jump to the Data tab.
  • Secondarily, click on the What-If-Analysis drop-down icon on the Forecast group of commands.
  • Then, select Goal Seek from the list.

Using Goal seek form What if analysis

  • In the Goal Seek dialog box, give the references like the following image and click OK.

Here, we are setting the value of cell C10 as 0 by changing the value of cell C5.
Within a second, it gives us the result of 3.90 as the Unit Selling Price.

Calculating Selling Price at BEP Using Goal Seek Tool in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


This article explains how to make a break-even chart in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF