Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
Changing Any Element in a Chart in Excel
In this tutorial I am going to go through the Layout tab in more detail and show you how to build up chart layouts ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...

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

Hi guys, I am doing a breakeven analysis and would like to paint certain areas of my chart. I want the profit area in blue and the loss area in red (loss area is the area to the left of the breakeven intersection; profit area is the area to the right of the breakeven intersection). Could you let me know how I could do this? I have attached the spreadsheet I am working on.

I have the Excel 2007 version but saved as and uploaded a 2003 version for those who don't have the latest one.

Thank you very much for your help!



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 :]

Is it possible to show only selected data points in an Excal chart's data table?

I have a chart with three series of data which appear on the chart but I want to show only one set of values on the data table.

For instance:
Chart values of 2, 6, 8 for period 1
Graph all three values in period 1
Only show value of 6 in the data table

Hope that makes sense.


CODE JAN FEB MAR 3456 23 43 12 6654 54 32 52 5432 76 23 54 6543 54 23 54 ITEM JAN FEB MARCH SALES COSTS 1560 2598 1200 FIXED COSTS 187 897 120 ACCOUNTING COSTS 45 12 15

Hi here is a basic version of what I need to do. I need to allocate costs for each code by percentage of sales for each month into a file in the format below.

Eg for the highlited item:


For any item where the ammount = 0 I dont want an entry made.

Can anyone help? thanks
CODE MONTH ITEM AMMOUNT 3456 01 SALES COSTS 173.33333 3456 01 FIXED COSTS 20.777778 3456 01 ACCOUNTING COSTS 5 3456 02 SALES COSTS 923.2562 3456 02 FIXED COSTS 318.7686 3456 02 ACCOUNTING COSTS 4.2644628 3456 03 SALES COSTS 83.72093 3456 03 FIXED COSTS 8.372093 3456 03 ACCOUNTING COSTS 1.0465116

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)


Policy A: After exceeding 2000 units, Rs. 1 is charged as tax
Policy B: After exceeding 3000 Unitis, Rs. 1.2 is Charged as tax
Variable cost: Rs. 20
Fixed costs: 2000
Seling price: Rs.10

What is breakeven sales volume for each of policy? Sales volume at which both profits are equal?

Can this be done through SOLVER under Data tab? If not what is the easy way to do it

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, I have one table where the data are produced by formulas. I want to make stake column chart using this data. I have attached the sample worksheet here which includes data as well as final chart also. which I made from the manual data. the scale of the chart is start from 8/1/2010. Please help me to find out what I am doing wrong making this chart.

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

Cant paste the chart.. wont let me.

I have a chart running from Jan 09 to Dec 09

The lines on the chart Start on the left.. but because there is no data in febuary yet.. the line goes straight to the bottom of the chart.

My question is: Is there a way, that until there is a number in the table for febuary.. it just stays up the top without it putting a line that goes from half way up the table.. to all the way to the bottom to zero?


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.



I have built a chart and am displaying the data table that is associated with that chart automatically.

I would like to show the Totals for each column in the data table, but not show them graphed. Obviously that distorts the graph. Is there a way to manipulate that linked data table to show info, but not show the same cells on the graph? I've tried alot from the camera tool and everything but am at a lost.

Thanks all!


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.