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

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


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

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

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

im using excel 2003

i would like to reduce the size of the following formula as t is too big to

=‘\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi Report 2005
Swindon.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi
Report 2005 Hemel.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break
Fix KPi Report 2005 Milton Keynes.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI
Reports\[ Break Fix KPi Report 2005 Pangbourne.xls]WC
180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi Report 2005
Llandarcy.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi
Report 2005 Stanlow.xls]WC 180405'!B3 +’\\stock\uk\Break Fix\KPI Reports\[
Break Fix KPi Report 2005 Avonmouth.xls]WC 180405'!B3+’\\stock\uk\Break
Fix\KPI Reports\[ Break Fix KPi Report 2005 Sunbury.xls]WC
180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi Report 2005
Aberdeen.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi
Report 2005 Sullom Voe.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[
Break Fix KPi Report 2005 Teeside.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI
Reports\[ Break Fix KPi Report 2005 Dimlington.xls]WC
180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix KPi Report 2005
Wytch Farm.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[ Break Fix
KPi Report 2005 Coryton.xls]WC 180405'!B3+’\\stock\uk\Break Fix\KPI Reports\[
Break Fix KPi Report 2005 St James.xls]WC 180405'!B3+’\\stock\uk\Break
Fix\KPI Reports\[ Break Fix KPi Report 2005 Britannic House.xls]WC 180405'!B3

the path is the same for each file...



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.



I have wrote the following formula


to search for everything in IU4:IU435 that matches cell C3 and for everything in D4:IB4 that matches the word Break* (using wildcard) and then sum all of these occurances between D4:IB435.

Unfortunately all I keep getting is the value 00:00 even though values exist within the Pivot sheet. Instead of the word Break I have reffered this to a cell and this works but the problem I have is that I have Break 1, Break 2, Break 3, Break 4, Break 5, etc and rather than writing this separately i need one total figure.

hope that makes sense. thanks in advance.

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,


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.

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:

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


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?

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!

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


Anyone have any ideas how to make a waterfall chart that can break below the
Y-axis? Jon Peltier has a good example of an automatic waterfall chart, but
I can't figure out how to make it go negative.


I have a chart that finds values for various permutations of entries.

Out of the chart, I have M3:M12 finding the top 10 permutations using a LARGE formula.

L M N O P Q R S T 2 Top

Total Starter1 Starter2 Starter3 Starter4 Starter5 Starter6 Starter7 3 1 1462 RB QB WR TE RB WR WR 4 2 1455 RB QB WR RB TE WR WR 5 3 1452 RB QB RB TE WR WR WR 6 4 1450 RB QB RB WR TE WR WR 7 5 1448 RB QB WR TE WR WR RB 8 6 1447 RB QB WR WR RB WR TE 9 7 1446 RB QB WR WR RB TE WR 10 8 1446 RB QB WR WR RB TE WR 11 9 1444 RB RB WR TE QB WR WR 12 10 1444 RB RB WR TE QB WR WR

N3:T12 uses an the formula =INDEX($C$14:$J$856,(MATCH(M3,$J$14:$J$856,0)-1),COLUMN(C:C)-2) to reveal the total's permutation. For instance, N3:T3 looks up the value 1462 in range $C$14:$J$856.

The chart is pretty spiffy, but I've run into a problem when there are two entries of the same value. I need to find the second value for the INDEX MATCH formula. For instance, M11 and M12 both show there are two permutations that find a total of 1444. As you can see, the rows for each are the same, but they shouldn't be.

Any ideas? Thanks!