Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Breakeven Chart - Wont Start At Zero

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi guys.
Desperate for some help in producing a breakeven chart for my assignment. I have a data table using the values given but the chart won't start at zero ie when sales are zero, variable costs are zero but fixed costs are 17500.

I'm not very good at Excel - having been largely self-taught - so I would appreciate very simple instructions if anyone can help!!

I hope I have attached a copy of the data table and graph to show what I've done.

Many many thanks for anyone who can help.

View Answers     

Similar Excel Tutorials

Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Combine Multiple Chart Types in Excel to Make Powerful Charts
In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...
Prevent Charts from Printing in Excel
This is how you prevent a chart from appearing when you print from Excel. This is a great feature when you want to ...
Make a Transparent Chart in Excel
How to make a Chart transparent so that it blends in with its background and surroundings in Excel. Hover your mous ...

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics

I need to create a breakeven chart in Excel 2007. I have my total variable costs, total fixed costs and sales price. The chart needs to show how many units I need to break even. My intial data has the sales prices changing over three years. Any help would be appreciated


Hi Excel genuises

I am trying to calculate a breakeven point with total fixed costs and variable number of people. With the detail below how is it possible to calculate a breakeven number of people and the value and then the number of people and value to show 15% margin?

REVENUE Package price $ 6.99 per person Our revenue $ 3.50 per person COST OF SALES Cost $ 1.75 per person Net Revenue $ 1.75 per person Total cost Cost 1 (2084.00) Cost 2 (54.50) TOTAL COS (2140.25) Overhead (4122.00) CAPEX (103.00) TOTAL COSTS (6365.25)

High Guys,

I need some help writing a few formulas in the attached spreadsheet. The Data_Input sheet where all the information will come from I have set-up Name ranges for each category.

I need to write the following formulas in the calculation sheet.

1 Fixed costs per week

2 Variable costs per drink in normal hours

3 Variable Costs per Drink in Overtime

4 The breakeven point in the number of 375ml drinks per week Inoring overtime

5 If the selling price per drinks changes, while all other data remains the same, what is the minimum selling price per drink below which overtime will be required to breakeven. I will need an If statement to test whether overtime is required.

6 A formula that calculates the profit or loss in normal hours

7 Graph_Data sheet will need formulas to allow me to graph it

8 I need to calculate the number of drinks that must be sold to achieve that target profit of $45,000.

9 A formular that will warn me that a target profit I keyed in will require more that 15 hours overtime.

Thank You for Your Held in advance



I am trying to create a stacked bar chart similar to a pie-of-bar chart, but am unable to find a way to do this.

Specifically, this is what I'm looking to do:
1. I have the following data set: Total Fixed Costs=$60 and Total Variable costs=$30. The fixed cost component is made up of: Labor=$20, Maintenance=$30 and Supplies=$10.
2. I want to create a stacked bar chart, where my first stacked bar has the total fixed costs($60) and total variable costs($30) stacked on top of each other. Now I want my second stacked bar to display the details of the fixed costs ( i.e. Labor, Maintenance & Supplies) with lines going from the first bar to the second.

I was able to acheive the above with a pie-of-bar chart, where the pie chart showed the fixed & variable cost breakup and the stacked bar chart showed the breakup of the fixed costs.

Any help is much appreciated.



I have put together some breakeven analysis with a small P&L but as the breakeven number of sales and the P&L is dependant on the inputs the numbers do not remain constant even when formularized.

Is there a way (macro perhaps?) that I can have the breakeven number of sales AND the P&L change depending on the inputs as per below?

Many thanks

Inputs Proposed price $6.99 Revenue share 50% New Revenue $3.50 Revenue share to X 50% X share $1.75 Fixed cost $6,471.00 Variable cost $1 Breakeven P&L REVENUE Package price 60,511.42 Revenue 30,255.71 COST OF SALES Revenue share 15,127.86 Variable cost per sale 8,656.86 Fixed cost 6,471.00 TOTAL COST OF SALES 30,255.71 Margin 0.00 Breakeven % Number of sales 0 8,657 15 9,955


I have a chart that automatically changes when the information changes. However i want to drop a line vertically and horizontally where my breakeven point is.

The only way i how overcome this is by manually drawing the line everytime the info changes.

Is there any possibility that excel can understand my graph and where the lines cross/intercept and automatically draw my vertical and horizontal lines?

Many Thanks

Hey guys this is my first post, found this site and it looks very helpful :D I have a homework assignment due soon. I am stuck on a step in the instructions our teacher gave.

Basically I was asked to create a 3D Column Chart using data that the teacher gives, change up the options/fonts a bit. I am now up to step 8 and it says:
8. Insert a data table without legend keys below the 3-D chart to provide data values so that the reader is not confused about the relative sizes of the different columns. (Hint: Use the Data Table button in the Labels group on the Chart Tools Layout tab.)

I tried just clicking on it, going to the table and selecting "Show Data Table" and nothing happens!

I am so lost right now, could anyone help me out? Thanks for reading :]

I have attached a picture to try an explain what I need to do.

I would like to put a line between the negative a positive values to indicate the breakeven point.

I would this line to apply to the whole column. The table is 30 rows.

I understand that I can use conditional formatting to apply the line but I cannot figure out how to write the statement.

I want to say: =IF(AND(M1<0,N1>0)) then put a line to the left of N to show the breakeven (I know I will do this with borders no problem and there seconf and 3rd Logic is missing in the forumlae)

Am I complicating the issue or have I missed something completely? The statement needs to be applied from B1 to P1 as the value will breakeven at different points.


I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is

But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"

Now I want to offset Costs against Sales, so I am looking for some way to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the results.

Anyone here know how to do that ?
Or, can anyone point me to a site where that is explained.



Hi All,

I've found great explanations regarding Dynamic Charts (e.g. or however all examples refer to data within a table than extends vertically.

I however would like to creat a dynamic chart on a table with data going horizontally. I assume you have to alter the OFFSET formula to Count for the data going horizontally, but not sure how to do this?? For example, please refer to attached Chart sample in 'Sheet 1'.

Does anyone know how to kick start me on this??

Is it also possible to automatically collapse the x range on graph (e.g. data in attached only appears till July, so would like to only show chart till July, rather than Dec. However august data is entered, the chart would extend to show aug)


How do I get a stacked column to go over x axis the way I want it to?

I'm looking to show the breakdown of sales into the costs - cost of goods, shipping, rent, staff costs, etc.. with profit the balance to add upto 100%.
This works fine in a stacked column where there is a profit, however, I have a year when there was a loss, the costs become greater than 100% - but I still want to show 100% above the x axis, with the remainder below.
Using the the data in a stacked chart, the year with a loss shows less than 100% above the x axis.

I've no doubt this has already been answered - but I couldn't find an answer in previous postings (I've previously found waterfall chart help).


Dear experts,
How can I get rid of N/As on the chart?
In the table I have formulas returning NA() if no data is available. The chart treats N/As the way I want it - not as zeros, but it makes the chart ugly.
I need to have data values on the chart (whenever available), and those guys come together with #N/As for other parts of the chart. And I cannot use the line chart (it seems to be doing what I am asking for) instead of the bar chart.
Thanks in advance.


I'm trying to find the beakeven point for variable cash flows.

Cash Flows per year Fixed Cost 1 yr 2 yr 3 yr 4 yr 5 yr 6 yr 7 yr -22,000,000 5,000,000 5,500,000 3,800,000 7,500,000 1,200,000 4,000,000 3,500,000 I know my breakeven point is around 4 years, but is there a formula I can use to calculate this? I will be using this formula in a template where the fixed cost and cash flows will be changing. My goal is to have the breakeven point calculated no mater what the Fixed Cost and Cash Flows display.

Many clients are concerned at what sales price will return a zero profit so that they can cash out and hit the road.

A simple one for you brainiacs. "A" - B - C + D = 0.
This means what sales price "A" minus closing costs and prorated taxes(B and C) plus escrow refunds (D) will equal "zero" . This is a break even formula that will show what price I must sell a house for after I back out expenses but add the escrow in. This is real important for a homeowner who has live in a home for only a couple of year and wants to sell but doesn't want to come to the table w/ 10 grand. Thanks again

Hi all,

I would like to have an Excel chart automatically update itself off of a table of data. Every month new information is added to the excel table that the chart is based off of and I do not want to have to change the source data each time updating the information. There are five metrics in the chart as well as changing dates that I would also like to have automatically update. I would like the chart to show the last 12 months of data. I have attached and example of the excel spreadsheet with the table and chart. I feel like there is macro you can write but I am not very good at VBA.

Thank you in advance for your help!!

bmatthie11Forum Doc.xls

Hi all,

I am currently using Excel 2007.

I have created a stacking bar chart based on sales information for the past 5 years by month. Currently, I have the chart and the data table displayed on the graph (chart layout) with the averages displayed as a second axis and I am wondering if it's possible for it to hightlight the highest sold months in the data table?


-- Sam

Hello All,

I have a table (table A) and a Chart (Chart A) linked to the data in table A (the height of the Chart is about the same number of Rows high as the table).

If I copy the Rows that hold both table A and Chart A and paste them beneath (to become table B and Chart B), I can update table B but Chart B is still referenced to table A.

What the easiest way of getting a Chart to link to the right table (i.e. not hold absolute references)? I don't really want to have to change my source data everytime I create a new table.

Hope someone can help with this. If I try changing the source data references from absolute to relative, they simply revert themselves back to absolute automatically.



Hi guys/gurls I have attached an example to help. What i am trying to achieve is not have space for departments with zero value as it takes up space and from chart it doesnt seem that clear what department it is refering. If anyone could help me on this it would be much appreciated.

I would also like this chart to be replicated on varous others costs with same format e.g. fuel if there is a vba or macro that i can be helped with.


Can I get either the Auto or fixed features in format axis to select the value of a cell content as both the start and end points for the x-axis.

These will be date values separated by 1 (i.e one day)

So I know I can can generate hourly tags by making the the major units =1/24.

My problem is if I use the fixed function on the first graph to set the start and end points on the chart (which eliminates the annoying gap Excel insists on putting in when I chose Auto) then it does just that and if I then copy data to a copy of the chart from a new range, the resulting new chart retains the old x-axis range.

As I say if I just start on Auto Excel insists on extending the X-axis beyond the data range at both ends..

(Just out of interest why does Excel do the above, it has always annoyed me.

I have often wanted only to break the x-axis in a controlled number of tags between the highest and lowest X values or as in this case use a time axis that fills the chart window.
But Excel insists on giving me distance between the lowest x value and the y axis and space between the end of the data and edge of the charted area. . Is there some kind of techncial reason (e.g. possible rounding errors) for this?)

Good Afternoon,

Thank you all for your help in advance.

I would like to create a gantt-like chart in Excel without using any VB or macros.

Specifically, I am having problems trying to figure out how to get a task to appear with two seperate durations.

For example,

Building 1 will have a Start Date of 5/1/10 and End Date of 5/25/10
as well as a Start Date of 7/1/10 and End Date of 7/7/10

Building 2 will have a Start Date of 6/15/10 and End Date of 6/30/10

How can I display these durations graphically on the same line? Is there a graph in Excel that can do this? I have tried to show the 2-D Bar graph particularly the Stack graph, but I have not had any luck in get the data series to display the information correctly on the graph.

I have attached an excel spreadsheet to show an example of what I would like to display on my graphs

Hi everyone!

I have some data on revenue/costs etc for some companies in a table. I am trying to plot charts for each of them but do not want to overcrowd the page.
Is it possible so that when I tick a check box, the chart pops out - without using VBA

I have tried the method where you tick a box so one chart is required for 3 different data, but if I uncheck all the boxes, the chart (without lines but legend) is still there. Is it possible so that it hides the entire chart as I got many tables - no space for charts



I have a worksheet that contains our sales information on a weekly basis.

I would like to create a graph for this sales information but i do not want to include all of the sales data.

Is it possible to create a macro that asks for a start date and end date and then creates a graph based on the sales information for those dates.

ie. StartDate := 01/01/08
EndDate := 28/02/08

This would create the graph for all sales data between these date ranges.

I realise I could just highlight the data and use the Insert|Line Chart option, but I want to be able to create the charts using a command button attached to a macro.

Any help would be greatly appreciated.


Hi All,

I am working with a pivot table and using a pivot chart from the data. The chart I need to use is a horizontal colour coded bar chart. The chart and labels accurately show the data. The problem is with the legend. I will give a simple example.

Chart Legend
A - 30% D
B - 73% C
C - 15% B
D - 29% A

On the example above, treat Chart and Legend as different rows.

The colours on the chart are matched with the colours on the legend correctly, so A would be for example red on both the chart bar and the legend, but it looks confusing as the legend is in an opposite position to the bar it refers to.

I am using excel 2007 and I need to keep the order of the chart bars as they are following legacy printed charts, I just need the legend to work correctly.

Any ideas?

I have a table of numbers related to sizes. I'll call the row and column headers the mesurements of a 2 sided box and the numbers in the table what it costs to make that size.

Obviously, the way it would work is, you tell me what length and width you want the box to be. I would look it up in the chart and tell you the cost.

The problem is, if I want to expand my business to include decimal sizes, I don't want to create a table to for a hundred decimals per size.

Given the chart below, I want to average the costs for the sizes above and below the requested size. In other words, if you wanted a box that is 2.345 x 3.00 , I want to average the costs of the cells C3:D3.

If neither measurements were whole numbers, it would have to average 4 cells.

Any help would be greatly appreciated.

1 2 3 4 5 6 7 8 9 10 1 42.53 42.95 43.37 43.79 44.64 45.48 46.32 48.06 49.78 51.49 2 47.16 48.00 48.85 44.64 45.48 46.32 47.16 48.92 52.35 53.21 3 52.22 53.06 53.90 48.00 48.85 49.69 50.53 52.35 53.21 54.07 4 53.06 53.90 54.74 48.85 50.53 52.22 53.90 55.78 54.07 54.93 5 53.90 54.74 56.43 49.69 50.53 53.90 56.43 56.64 58.36 60.93 6 58.95 63.16 64.01 50.53 51.37 57.27 61.48 66.08 66.94 67.80 7 - 64.01 64.85 53.90 54.74 58.95 64.85 70.37 71.23 72.09 8 - - 67.37 57.27 58.11 64.01 68.22 73.81 74.67 75.52 9 - - - 58.95 59.80 66.53 69.90 79.82 77.24 82.39 10 - - - - - 77.48 78.32 86.68 87.54 88.40


I have a pivot table that is summarised in a nice dynamic chart. I have a range of values in this chart that have a lot of 0's in it. Is it possible for either the pivot table or the graph to exclude these 0's so it only shows the figures greater than 0?

Screen shot attached should help show what I mean. The little circles are the graph are linked to column 'Sum of RS eBay Listings'.

Many thanks,