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

Break Single Large Value To Make Bar Chart More Useable?

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

I need to disply a bar chart where most of the values range between 1 and 300, but then 1 figure is 1577.

I think I've seen a thread on here before (although I can't find it now) that I can reduce the height of the singe tall bar and show a break in it to illustrate the jump from, say 450 to 1450?

Is this possible, and if so how do I do it?

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 ...
How to Create and Manage a Chart in Excel
In this tutorial I am going to introduce you to creating and managing charts in Excel. Before you create a chart yo ...
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 ...

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

Similar Topics

sorry, rather jumbled title. i am trying to ask how to put a break in the values on my chart that run up the Y axis. Most of the data i have is in the $0 - $40m range but we then have a huge jump up to $100m or so. How do i make my chart detailed at the bottom, and then to jump to the larger figure.

as an example, i was thinking of $5m increments up to $40m, and then restarting the count at, say, $100m - on the Y axis.

can anyone assist?

Hi, I want to draw a column chart where the range varies widely. The values are 10, 8 ,7 5 and 80. When I use the graphing wizard, it draws the correct graph, but because of the big difference in the largest value (80) vs the rest, I have a tall column and 4 short columns which doesnt really look good. Can anyone teach me how I can draw this chart with a break in the Y-axis, please? Thank you.


I have two pie-charts I'm trying to resize and then place into a PowerPoint presentation.

Can someone guide me to or write some basic VBA code that I can modify to achieve the following:

Reduce the size of the chart object but NOT change the size of the pie-chart itself inside the object (i.e. reduce the white surrounding area).

I'm happy to use something along the lines of (pysuedo code, not vba):

For each Chart in activesheet
  ChartObject.Height = a
  ChartObject.Width = b
  Chart.Height = c
  Chart.Width = d

Where I consider ChartObject to be the overall frame the pie-chart is inside and Chart the pie-chart itself.

Values for a,b,c,d I'll find by trial and error


Hi all,

I can't figure out how to set or find the height and width of a chart that's in a chart sheet.

For a chart in a worksheet I do this:
height = ActiveChart.Parent.height

A chart sheet doesn't seem to be in a ChartObject, but I can't see what it's in.
I tried ActiveChart.height, but that didn't work either.

The height property must exist, because I can go into a chart sheet and resize the chart with the mouse.


I have a question for you. I want to break the Y axis of my chart because I have lots of small data and 3, 4 are much larger. If I do nothing my chart will take me two pages and I would like to fit on one page. Is there any way to break the lines? I found a link that explains how to internet but with two large data. I need months more than 3 data and I do not understand its purpose formula can help me on this form or explain an easier way. Thank you to all

I have a single worksheet with a hard page break about 2/3 of the way down, so that it prints rows 1 thru 47 on the first sheet of paper and rows 48 thru 76 on the second sheet. The problem is that I have "wrap text" on the first section, and sometimes it will increase the height of that section so as not to be able to print on only one sheet. I've tried the following "Page Setup" options with the following results:

"Fit 1 wide by 2 tall" gives a soft page break a couple of lines from the bottom of 1st page, ignores the hard page break, and prints the bottom of page 1 and all of page 2 on the second sheet.

"Fit 1 wide by blank tall" gives a soft page break a couple of lines from the bottom of 1st page, a hard page break at the bottom of page 1, and prints on 3 sheets.

Could someone please tell me the setting to get page 1 and 2 to print on sheets 1 and 2 respectively, regardless of the height of either page?

Hi guys,

As you can see in my file below, I want to make bar chart with a break on the Y axis and make that refflected on my bars as well.

My problem is that my sales are really higher than my others values.

Thanks you verry much.

I have a bar chart that displays monthly techincal problem duration. We have a weekly ceiling of 350 hours of outages that we shouldn't break. Occaisionally, it is broken for example here are the actual hours since last December:
Dec - 401
Jan - 760
Feb - 686
Mar - 531
Apr - 775
May - 602
Jun - 1606
Jul - 2187
Aug - 4992
Sep - 994
I'm using a bar chart to show these values. I want to show how the actual amount of outage duration compared with the ceiling by putting a line in the chart for each month.
I figured out a way to accurately show the allowed ceiling for each month (350/7) * (number of days in the month), so for months with 30 days the ceiling is 1500 and months with 31 days, it's 1550 (Feb is 1450). I tried a line chart but that's one continuous, unbroken line. I want to see a line for each month at the point of the ceiling for that month. Thanks for any help


can somebody help me create a break even chart in excel 2003 from scratch i have no idea where to start but need to know how, you a'll can make up figures if need be but please help me

thanks in advance

Hello, I am trying to print multiple charts on one worksheet that span down several pages. Each chart has different headers however. I want to automatically (based on the chart) print the chart heading on to each page. I can easily do this if they all had the same headings. I was thinking of making a break like in Microsoft word but i am only able to insert a page break and not a formatting break. Is there a way to do this? Thank you


I am trying to produce a column chart in excel with 15 x-axis categories with values ranging from -50 to 210, I would like to put a break on the y-axis at 60 and resume again at 180. I have tried using the tutorials by Jon Peltier and Andy Pope, but these dont seem to work when the y-axis goes below zero, is it possible to produce a break on such a graph?

many thanks in advance for any help,

Best wishes,

I am trying to chart a series of 'horizontal' lines overlaid across some other linear functions. the horizontal lines are noted by a column of data scaling from 0, then to a certain 'jump value' (e.g. 1/3), then back to zero, then later on to another jump value (1/6), such as follows (column A is the 'x-axis' on the chart, column B is intended to be the 'y-value' of the function).

0 0
1 0
2 0
3 0.3
4 0.3
5 0.3
6 0.3
7 0.3
8 0
9 0
10 0
20 0
21 0.2
22 0.2
23 0.2
24 0
30 0
31 0
32 0.1
33 0.1
34 0.1
35 0

etcetera. essentially this results in a horizontal line at y-value of zero, interrupted by horizontal lines at varying y-values, depending on the x-value (that is, the Column A value).

the problem, of course, is that if I do a line chart excel plots any 'jump' between zero and another value as a vertical line leaping straight up, which I do not want to show. I'm not sure if there is another chart option available to suppress this. I know I could choose 'none' for the line in chart option, and just have markers (the data set is large enough that visually a lot of small points would appear as a line), but I can't get the markers small enough to make this appear correctly.

Does anyone have a suggestion for this, say another type of chart to use or an option to suppress these jumps but still graph my lines?

Many thanks.

I have a master workbook which does a series of calculations, then produces an embedded chart on a worksheet

I then copy this worksheet to a new workbook using Edit / MoveCopy sheet. The chart appears OK and correct.

When I run another study in my master workbook, and copy the latest chart to a new worksheet in the same new workbook, this apprears OK, but the original chart in the new workbook changes , to show the latest master chart - ie all the charts copied to the new workbook show the same, which is the latest chart on the mater workbook.

Can anyone please advise how I can break the links after copying the chart from the master?

I am trying to chart 3 time series by date and wondering if anyone might have some suggestions, please. This is what the data would look like:

Date Start End Duration Task
1/1/2009 8:00 AM 5:00 PM 9.00 Shopping
1/1/2009 12:00 PM 1:00 PM 1.00 Break
1/1/2009 8:15 AM 5:05 PM 8.50 Shopping Audit
1/2/2009 11:30 AM 1:05 PM 1.35 Break
1/2/2009 8:02 AM 5:12 PM 9.10 Shopping
1/2/2009 8:04 AM 4:12 PM 8.08 Shopping Audit
1/3/2009 8:00 AM 5:00 PM 9.00 Shopping
1/3/2009 11:45 AM 12:48 PM 1.03 Break
1/3/2009 8:17 AM 4:59 PM 8.42 Shopping Audit
1/4/2009 12:00 PM 1:00 PM 1.00 Break
1/4/2009 8:15 AM 5:05 PM 8.50 Shopping Audit
1/4/2009 8:00 AM 5:00 PM 9.00 Shopping
1/5/2009 11:30 AM 1:05 PM 1.35 Break
1/5/2009 8:02 AM 5:12 PM 9.10 Shopping Audit
1/5/2009 8:00 AM 5:00 PM 9.00 Shopping

What I want to show is a chart with times on the Y axis like from 7:30 AM to 5:30 PM. Then have the dates along the X axis. Each date would have a floating bar for the shopping in one color, a floating bar for the break in another color and a floating bar for the shopping audit in a third color. The point would be to show a pattern of the time of day that these events happen in relation to each other day by day. Any suggestions would be great. There may be a better way entirely to chart this data out. I am all ears.


Hi, everyone.

Let us assume a1= "hello " and b1= "world". I would like to concatenate both of these cells with a line break between then. Is that possible?

To illustrate my question, if I make c1= a1 & b1 I get:

hello world


I want:

in a single cell.

Thanks in advance,



I am doing break even analysis in excel and i create a line chart on the basis of data. So now i want to show the intersection point in that chart with label. So any one can help me in this.

Please find the attachment....


Thanks in advance

To fellow excel users,

I woudl like to break a link from a dynamic chart to its source data in another excel file, however when i break the links, everything goes to "REF#".

I think it is because it is a named range? Also, I tried pressing f9 on the formula bar on the series, but it exceeded 8192 characters.

Any suggestions or help? I am not that good with VBA coding.

Thank you.

Hi, I need some guidance in creating a column chart where a column could be break and shorten when find an outlier.
For Example: we have sales data for months JAN, FEB, MAR, APR, MAY and JUN
Sales ($mm)
JAN 10
FEB 12
MAR 11
APR 15
MAY 16
JUN 90

if I create a simple column chart, all columns will look very small in respect to JUN column. I want to show JUN column just a bit large in size rather outsizing all others columns (labels on top to show the data). I noticed one chart where the column of largest data was cut from the top in two and with a gap, data label was showing the exact value. Accordingly the index was also arranged. I believe I have stated the problem clearly. If not please let me know I will try to explain it further.

Thanks in advance,

How do you get the chart to show the axis at 0. I have values above and below 0 in my chart and I want to illustrate that the horizontal axis (hope I'm saying this correctly) in the middle of my chart is 0.

If someone knows how to do this it would be greatly appreciated. I know its simple, but I'm not use to 2007.

I think it is the Vertical (Value) Axis I need to edit. However I don't know how to add that for my chart.

I am trying to make a dynamic chart so that future zero values (months with no data yet) do not show up on the chart.

With my spreadsheet, I have the values for the chart being referenced from other cells. Having several months that have zero values (which cannot be deleted), how can I have a dynamic chart to not display the zero values?

The dynamic charts work (from my understanding) if the zero values are actually BLANK values.

Using =NA# causes the same issue and has the extra months plotted which I don't want. Is there a way to make a cell that is equal to a zero value in another cell be blank/empty?

I've been working on this for hours and cannot find a solution.

I followed the steps from this thread:

I have a spreadsheet that shows employee name, shift start time, am break start time, am break end time, lunch start time, lunch end time, pm break start time, pm break end time, and shift end time.


Tammy 8:00 9:30 9:45 12:30 13:30 15:00 15:00 17:00

I want to chart this so that it looks like a floating or Gantt chart. I know I could manually "color" in the blocks but I was hoping there was a way to have excel create the chart in case schedules change. After I get the hours charted I want to show a trendline that shows call volume by time ....

this is so a customer service helpdesk can evaluate that they have enough people on the phones during peak call time.

Does anyone have any ideas? I have read a lot of articles about Gantt charts and floating charts here and on other boards but they all are project based and deal with dates - not times. My shifts are 24 hour days too so I have folks in at midnight etc.

Thanks in advance!

Hi again

I'm trying to make a page break after a chart. But I can't do this it seems, the page break can only be inserted into a cell/row.

Any idea how to determine the cell that the corner of an object is in? I want to take my object, find what call it is above, and break based on that cell.


Hello all:
I am trying to figure out, within excel, how to chart a range of values. For instance, I have 5 levels of compensation for a given job and am trying to figure out how I could chart this.

Level 1 - Min: $13.00 Median: $13.50 Max: $14.00
Level 2 - Min: $14.00 Median: $14.00 Max: $15.00
Level 3 - Min: $15.00 Median: $15.50 Max: $16.00

This seems like it should be simple enough to do, but all the charts I try to insert start at $0 or don't show the range of values. I'd like them to show Level 1-5 vertically with the corresponding ranges to the right on the chart. I apologize if this is a real beginner question, but I can't figure it out for the life of me.

Thanks in advance for any direction/advice!

I need some help here. I'm trying to make a pie chart that will get a list of items and depending on what category they are in it will show the proper percentage in the pie chart.


Value - Category
120 - 1
50 - 2
541 - 1
444- 3
4445- 2

then the pie chart would show the % of all the values added up in the categoryes 1,2,3 instead of showing each single value in the pie chart. So it would show category 1 - 20%, category 2 - 50%, category 3 - 30%.

Does anyone know how to do something like this?

In Excel 2003, I have 2 worksheets. One has charts. The other does not. I am able to break links with the data source on the sheet without a chart. The "Links" button is gray on the worksheet with the charts. Therefore, I can't break the link to the data source. Since I have never used VBA, is there an easy way, without using VBA code to break the link? Thanks, Kenny