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

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

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 ...
Insert and Manage Page Breaks in Excel
How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and confusing but this tutori ...
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 ...
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 ...
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 ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...

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?

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

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!


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 am trying to figure out if I can break an axis so that my data on the high end and data on the low end can occupy a greater portion of the chart. I'd like to eliminate the empty middle space.

I have a scatter plot that shows data on the X axis from 0-12 million and the same for the Y axis. I'd like to be able to break this up into 4 equal quadrants on the chart, but the majority of my data lies at the bottom of the range.

How can I create a break in the chart so that 0-2 million takes up the first half of the X-axis and 2-12 fills the second half? TIA

hi everyone, was wondering if you could help me

at the moment on my works schedules we only have the hours they are scheduled to work, what i was hoping to do was introduce a Break colunm to work out there breaks, but i was hoping that there would be a single forumla that would work out there break?

for example if someone works 4 hours they wont recieve a break, if they work over 4 hours but up to 7 they get 20 mins break and if they work 8 hours they get 30min break.

i hope this makes sense???

thanks in advance


Is there a way I can insert a section break without having the page break?

I need to insert a section break so that my footer can be different on the second page. But I want the page to break in the natural place (I'm doing a mail merge and some letters are different lengths, so there's nowhere I can force a page break and make it look right on all letters).

Thanks for any assistance you can offer!

I'm currently creating a PivotTable chart, with multiple columns of data. Here's an example of the data column names.

14.1, 14.2, 14.3, 15.5, 15.6, 17.1, 17.2, and so on.

What I'd really like to show is just 3 columns: 14, 15 and 17. However, within each column, I'd like to break out the value of the smaller pieces. So, if 14.1 had a value of 2, 14.2 had a value of 5, and 14.3 had a value of 4, column 14 should now have a value of 11 (2 + 5 + 4). However, I'd like to delineate each of those 3 values within that column. Can that be done?


PS I've attached a spreadsheet that should hopefully illustrate the problem.

I have a very large XLS file (150MB) that I use to populate a lot of pivot tables and charts on various worksheets for different things. I would like to export a single sheet which contains one pivot table and one pivot table chart, save the file, then distribute it to the users so that 1. The size is small. 2. All my crazy ways of populating data remain my secret

The problem I am having is after I copy the worksheet itself to a new workbook whenever I change selections in the pivot table the chart no longer updates. The pivot table does.

Does anyone know how I can preserve the pivot table chart as well? If it doesnt update its worthless. I can break down my scenerio to a 400KB file if anyone wants to take a look at it.

Version: Excel 2007.


I need to simply drop large sets of data on a sheet, and then chart it out by month. I avoid using filters because it needs to be pasted (interval data - and a lot of it)

I have been using scatter charts, and usually I am able to see the "source data data range" outlined in a thin blue line when I click on the entire chart. Typically, I make a copy of the chart, click on the new chart which then shows the data range outlined in blue, and simply relocate the blue lines (the series are shown in red ) over the new set of data. (I'm looking at interval data from several months and years)

This is a real time save for analysis of large sets of data I look at.

In my new charts, I have been forect to break out the data to other worksheets using "if" formulas (I am trying to avoid using filtering if I can because it isn't effective when it comes time to pasting large sets of data, and thea amount of time spend re-graphing is great).

In these new charts, the blue outline for adjusting the data set is no longer visible. When I select the data range Data Source Data range, the data is outline in dashed lines.

Any help would be greatly appreciated

Hi all,

This VBA creates a chart on each worksheet in the workbook, can anyone please tell me how to change the background of the chart from gray to white and the color of series 2 from pink (the default for a second series I presume) to green? Also is there a list anywhere of all chart properties that you can change using .chart?


Sub ChartYearComparison()
Dim WS As Worksheet
Dim Rng As Range
Dim Rng1 As Range
For Each WS In Worksheets
        Set Rng = WS.Range("A200:C213")
        Set Rng1 = WS.Range("A11:L21")
    With WS.ChartObjects.Add _
            (Left:=Rng1.Left, Width:=Rng1.Width, Top:=Rng1.Top, Height:=Rng1.Height)
        .Chart.SetSourceData Source:=WS.Range("A200:C213")
        .Chart.ChartType = xlColumnClustered
        .Chart.HasTitle = True
        .Chart.ChartTitle.Characters.Text = WS.Range("A2") & " Occurences"
        .Chart.SeriesCollection(2).ChartType = xlLine
    End With
Next WS
End Sub


I have a bunch of data series that I want to chart using a line graph with $
on the y-axis and time on the x-axis. The trouble is that about half of them
have relatively low y-values (between 0 and $100) whilst the rest have high
values ($600-$1000).

My question is: Does anyone know of any way to manipulate the y axis so that
it ues a scale of, say, $20 for the 0-$100 section, and then "jump" to $600
(with zig-zag or something on the y-axis to tell the user that the graph is
non-continuous) and continue the chart there? Otherwise the scale just
becomes too large or too small to make any sense of trends within these 2

Many thanks for your help in advance!


I can't seem to find this anywhere for some reason but how do you name a chart. When I use the macro recorder I see they get named Chart 1, Chart 2 etc.
I have a macro to create a chart that works the first time around, but will create a new chart each time it is run. I want it to just update, but can't figure out how to get the name of the chart to reference it.


'Make Chart
Set d = .Find(Hours_Chart,  LookIn:???) 
         'If the chart hasn't been created, then create it. 
 If d Is Nothing Then 
           ActiveChart.ChartType = xlColumnClustered
           ActiveChart.ApplyLayout (5)
           ActiveWindow.SmallScroll Down:=0
           ActiveChart.SetSourceData Source:=Range(("O3"), Range _
            Need to name chart as "Hours_Chart" so that below I can refer to it. 
                 'Otherwise if it is present, then update it. 
            'Chart you are updating 
          ' Set range data 
               Set rngChartdata = Sheets("Hours Log").Range(("O3"), Range _
                   ActiveChart.SetSourceData Source:=rngChartdata, _
        End If

1) How do I give the chart a specific name?
2) How do I use .find to see if it is already there.
3)What property defines the charts location on the screen.

Thanks as usual...

If I can't search for it, but can name it I can just select it, delete it and re-make it.

Is it possible to create a bar chart with a broken X-axis? I found Peltier's nifty broken Y-axis instructions and have created a column chart using it. I've seen it posted that the method can be used to create a broken x-axis as well, but have not been able to figure out how.

However, I think a bar chart would be better to display the info, but cannot get the markers that would allow me to break the axis to display on the x-axis. They just scatter straight to the y.

Any advice would be aprpeciated.

Trying to figure out how to make the chart data range selective. I have a single pie chart, yet I have the need to selectively display two different data ranges.
Range 1 is C5:D15
Range 2 is AA50:AB55

For both ranges, the data present is derived from formulas.

I'd like to use a couple option buttons to choose which data actually gets displayed in the chart.

I named the two data ranges, and grouped the option buttons. But when I tried to use an IF statement in the chart's data selection - I get 'not a valid function'.

Thanks for any help!

Is there a way to "Name" a chart range like you can Name a Range in a Tab? I am trying to make a Dynamic chart so that I can define the chart range in VBA. My problem is that I alwsy have to create a chart for proposals I do. Unfortionatley, the date range of the chart is always different depending on the period of performance (AKA Number of Months). I know how to dynamically name a Range in VBA. So "Naming" Ranges is not my hurdle. I need to know if there is a way to use that Named range to create a chart vs resetting the range for each proposal I do.

Any chart experts out there???

Thanks for the help!

So I have a breakdown of major sectors I am showing in a traditional pie chart


Easy enough.

This is where I am getting messed up. Now in a new chart I want to show that same chart but with the slices of the pie broken out into subsectors:

So that the Equity portion will show a bar chart of the different types of equity investments. So 10% Large Cap, 5% Mid, 5% Small, 10 Intl. for instance. Ideally it'd be great to make those percentages show as total percentage of the 30% if that makes any sense. I hope this makes some sense. Thanks in advance for any help.

so you know how in excel when you do the subtotals wizard you can insert a page break after each change in a field...I basically want to do that in an access report (access 2007). In the detail section I have a field for "Stores" which can have anywhere from 2 to 50 records per store. I want a page break inserted after each change in store. I am hoping this is simple, I just can not find a thread that quite addresses this issue and can't figure it out on my own.

I'm trying to print a holiday chart onto one page, this chart was done
manually rather than using the 'chart' option. I have fiddled around with the
wide and tall and percentage on page setup but the best I can do is a
printoff of a very small version of the chart that you almost need a
magnifying glass for.


I have done this a thousand times in the past but can't seem to get this one to work. I have a workbook which contains links to other files and I would like to break these links. However, I can't seem to get one of the links to break. I don't get an error message or anything. I have actually attempted to delete the pages containing links and re-insert them. Yet it will still not allow me to break these.

Any ideas on how to break these?


I've got a horizontal clustered bar chart, but the height of each of the bars is too large to see all of the bars while viewing at 100% on my screen. If I could somehow change the height of the bars this would fix things...any suggestions?



We are performing a Physical Inventory and have about 5,000 unique inventory locations. I apologize, as this is similar to my other post, but I failed to realize that our counters would want unique sheets for every location. I think I came close to the correct code myself, but could not quite get there.

Basically I would need to create page break after every unique identifier; see below:
<Page Break To be inserted>

Thank you for your assistance, and let me know if I can provide any additional information...I could not find a thread that answered this question; I apologize if one already exists.