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

Combined Split Bar And Line Chart

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


I'm using Excel 2002 on Window XP. I have managed to create a combined line - column chart on 2 axis as featured in the Chart Wizard - Custom Types folder. However, I would like to change the bar chart aspect of the chart to display each bar in a split format.

Currently, it displays one colour showing the total value of the bar. However, I would like each bar to be split into 4 different colours representing 4 different rows of data that make up the total value.

Here are the data values:

Month (x axis) Dec-05 Jan-06 Feb-06 Mar-06 Apr-06

Line 50% 50% 46% 49% 53%

Bar - split 1 0 0 0 0 0
Bar - split 2 0 0 0 0 0
Bar - split 3 0 0 0 0 0
Bar - split 4 0 0 0 0 0

View Answers     

Similar Excel Tutorials

Update, Change, and Manage the Data Used in a Chart in Excel
In this tutorial I am going to show you how to update, change and manage the data used by charts in Excel. This tut ...
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 ...

Helpful Excel Macros

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

Can anyone help me please?

I have a large data set (hourly data for a year).
I usually present this as a line chart which then looks cramped.

My idea is to create a radar chart but split into sections (imagine a pie chart with 12 equal slices). I then want the data (split into months) to be graphed around each section.

Does anyone know if this is possible or exists? It will effectively have 12 x-axis' albeit all identical.

Many thanks,


I am trying to create a comparative bar chart with year-on-year quarterly
sales figures for three different branches of a company. There are three
divisional figures and a subsequent total for each quarter of each of the
three years. The separate series need to be the years but each quarter total
needs to be visually split by the three divisions (sales quarters along the
x-axis, £ along the y-axis, 3 separate bars for each quarter each
representing a year and each split into 3 sections by division)

I can't work out how to do it or even if it is possible to split the series
in this way?


I have a column chart type. It is split up into years on the x-axis and amounts on the y-axis. On the x-axis it is also split up into seperate locations. Is it also possible to split the date further to include another data series? What I want to show is complaints by location, year, and type of complaint. Three groups of info.

Hi All

I need to make a bar graph for the data below, now the tricky part is , the total has to be one bar and split one and split two will be one bar ( values combined). Is there a way to do this.

Total Split 1 Split 2
Jan 453 100 353
Feb 543 200 343
Mar 543 200 343

Anything to get me started would be much appreciated.

thanks heaps


I am trying to split a single sheet into two, so I can have a chart in the "new sheet" below the first sheet containing data. So it is possible to quickly see the impact.

I have tried the normal "split" function, but it splits the same sheet into two views. That does not allow me to change the column width. Should I put summarizing data and chart into new sheet and show it below the other by split? and how?

I have core data that has 3 columns - Col A is Month Jan to Dec .. Col B is Total units sold 0 to 100.

Col C is the average deal closure time 04:00:00 to 16:45:00 representing hours.

I have created a line chart for Month v Total sold easily enough, however trying to create a Month v Deal Hours is proving troublesome. Whenever I set range the Month on X axis reverts to only one month ???

What should I be using as my Max / Min split in order for chart to make sense. And should I be fixing cells formats to 00:00:00 etc before starting?


I'm using a pivot table and then a pivot chart to display a large amount of data to do with certain incidents

In the pivot table I have dropped "username" in the row field and then in the joining line between row and data "date" and "severity" and then "severity" again in the data space.

I then use the table to plot a bar chart which works fine but for one thing.

I know have 3 sorts of label on the x axis.

username which is then split into month which is further split into severity.

All the labels become overlapped and squashed and I can make out anything. I have already tried to vertically align the labels however all this does is cause the 1st labels to do this (severity) the others remain horizontal. I can't find another way to make the label readable!

Using Excel 2002

Can provide example if needed.
Any help would be great!


I have a stacked bar chart that shows total time to sell, broken out in 3 categories (Time to make + time to ship + time to sell in store), split out into different segments (Toys, Tools, etc).

So, my stacked bar chart has 7 different bars (one for each segment), and each bar is split out into 3 colors, relating to the time to make, time to ship, and time to sell in store. The total length of the bar represents the total time to sell. This chart works perfectly.

Ideally, what I'd like to do is have a separate bar going from right to left on the chart that shows Total # of sales by segment.

Or, if I can't do that, I'd be happy with a second bar underneath the stacked bar showing Total # of sales by segment, which I can probably figure out how to do, but not without having two labels for the same category on the axis.

Preferably, I'd like to do this without using VB? Any thoughts?


Hi folks,

I've got three worksheets. Total, A and B. On the first sheet, I enter the total, then there's a table where I enter the split of that total for 'A' and 'B'. So say the total is 457, I would then enter a rough split of 227 and 229 for 'A' and 'B' respectively. On the second worksheet for 'A', I just want the total for 'A' on that date to be filled in automatically with what's on the split table. And the same for the 'B' worksheet.

And this needs to be on a new line following the date. I've attached a file with the barebones tables: one main sheet where I'd enter the total for the day, and the split, and then a second sheet (A), and a third sheet (B), which would need to be filled automatically. totalSplit.xlsx

So, as I enter the total number, and then split it manually, I need that split value to be entered on a new line for every day.

Does anyone know how to do this!

I've asked a few regular excel users about this but so far no one has had an answer:
When making a simple line graph (using the curved lines with data point markers "chart type") is it possible to put a split/gap on the y-axis scale? For instance, if I have all my data points btw 0-50 and one point at 500 can the scale concentrate on the 0-50 and then thru a dash/gap/split in the scale show that there's one outlying point at 500? (I know I can put it on a secondary axis but sometimes it's not as effective)

Thanks a lot. Ciao.
-- Joe

I am trying to put the following data into a bar chart, but I want the first two rows to be on the same bar but split. Any ideas what chart type I should use and how it should be set up. When I use the split bar all data does together but I need the third row to be seperate.

Sep-07 Oct-07 Nov-07 Dec-07
65 78 58 52
2 43 7 7
4 30 27 13

Thanks in advance!

I am trying to put the following data into a bar chart, but I want the first two rows to be on the same bar but split. Any ideas what chart type I should use and how it should be set up. When I use the split bar all data does together but I need the third row to be seperate.

Sep-07 Oct-07 Nov-07 Dec-07
65 78 58 52
2 43 7 7
4 30 27 13

Thanks in advance!

I'm trying what appears to be a complicated bar chart and I'm struggling to get any decent results... Can anyone help?

I have six variables (V1, V2, V3, V4, V5, V6) which represent groups of people. Each of these groups have a male/female split, and so I am representing each variable's male/female split as a percentage.
V1: 52.9% Male; 47.1% Female
V2: 52.7% Male; 47.3% Female
V3: 46.2% Male; 53.8% Female etc...

That has produced a very straightforward bar chart where each bar adds-up to 100%. Simples.

However, I also want to add, below each of the variable's (V1, V2, etc...) bars, another bar that splits each male/female group into three further sub-categories (X1, X2, & X3). So, of the 52.9% who are males within V1, X1=61.84%, X2=3.52%, and X3=34.64%; and of the 47.1% who are females witin V1, X1=19.28%, X2=26.65%, and X3=54.06%.
V1: 52.9% Male 47.1% Female
V1': 61.84 X1, 3.52% X2, 34.64 X3; 19.28% X1, 26.65% X2, 54.06% X3

The problem is that Excel doesn't seem to like the fact that the bars representing variables V1-6 are split into two groups (male/female) whereas the bars representing the three further sub-categories are split into six (X1,X2,X3,X1,X2,X3) groups.

Have I made any sense there? These things are always quite tricky to explain...

Any help would be very appreciated!


Hello, I have seen many threads about the stop watch but have problem to figure out which one will be best for me. I would like to create the stopwatch in excel where I can time single athlete (unlimited number) and check their splits/laps as well to get their total time. I would like to be able to take as many splits I want it. I need this to work in seconds.

Athlete Split 1 Split 2 Split 3 Split 4 Split 7 Split 8 Split 9 Total Michal Greg

Appreciate your help.


My existing file has a macro that puts all the necessary numbers and data into a table. To easier create a chart that is linked to these data, I split the existing table into different tables. Every week, I have to make an update with new data. Thus, each line from the existing line has to be split into one of my new tables. How would the macro look like? Additionaly, the macro should cancel the oldest data from my tables, as I do not want the chart to show more than ten weeks. My chart is linked to the fields C4:H10. Thus the macro should for example delete field C4:H4 and then move the lines up that the new data would be set into C10:H10.

Hi, I have 2 lots of data that I'm trying to show simultaniously in a pie chart. Firstly I have the work done by my team split in to various different job codes. I then also need to show (on the same chart) the percentage of these individual jobs that have been outsourced. Please have a look at the example data below and let me know if this is possible:

Job Code, % of workload, % of this job that is outsourced

1, 30, 10
2, 25, 15
3, 45, 0

So the pie chart should be initially split in to 3 to show the jobs as a percentage of our total workload. I then need to separate these 3 slices in to 2, showing the percentage of them completed in house and those outsourced. Hope this makes some sense.


If I split a string using

split(split(Cells(3, 6), "-")(0), "-")

I can manually look at which part of the split to pull out. I know the 0 can be changed to pull out the split sections, but can it be reversed. The split string I need is the second to last string all the time, but the string before that can always vary in number.
I tried using -2, thinking that using the "-" might think count backwards in the split sections but alas an error.

I have a line chart that, because of the values, is difficult to read. While several series values are under 75 consistently (anywhere from 0 to 75), one value is consistently between 200 to 300. This creates several series values at the bottom of the chart compressed together and one series value at the top of the chart.

Is there a way to split the value axis, so that the lower series values are more readable? The other problem is that I need to have three separate charts on one page, each it's own separate category.

Split an Excel Worksheet. Question:
Lets say you put your split on line 75 and entered some data on that line. If your using the scroll bar in the upper portion of the worksheet the data you entered on line 75 will appear on the upper portion if you reach line 75 and appear on line 75 on the lower. I don't know if I'm wording this well. My supervisor feels that the data from where the split starts would never appear on the upper display. I've tried everything to do this nothing works. Is she correct or am I.

I'm looking to create a pie chart which has a segment of the chart much bigger than the rest, and separated from the chart.

Example, the total value of something is 100,000; split between a cell value of 75,000 and another of 25,000.

I want the pie chart to go 75% of the way around like a regular pie chart, and then have the 25% separated, but much larger in scale than the rest of the pie chart. You should still see that it is part of the same story, but that it is sticks out to the viewer.

Is this possible in Excel?

Many thanks.

Split(MyStr, " ")(0)
I've been playing with variations on the above line to try and split a string up from the right hand side not the left as it does above.

Below, is an example of the string
"Bedfordshire 2,735 178 1,354 1,203 2,554 213 883 1,458"

Want i want to achieve is the above string split so that each number or name ends up in a cell of its own.
So the code below works to a point. Code:

For X = 0 To 8
    For Incr = 1 To Erow - 1
        MyStr = Range("A" & Incr).Value
        Range("K" & Incr).Offset(0, X).Value = Split(MyStr, " ")(0 + X)Next Incr
Next X

It splits all the elements up ok. But, some of the strings have a longer name than Bedforfshire, e.g. East Sussex
When it encounters a name witrh more than 1 word, it overwirtes what its already split up. This is because it loops 9 times, which is the common number of spaces which the code uses to identify the split point.
So I think it would work if it started looking for the split from the other end but i cant find a way to make the initial line of code start from the right rather than the left.

Any ideas would be really helpful

When a worksheet window is split and a comment shape overlaps the split bar (with or without Freeze Panes), the comment appears behind the split bar instead of in front of it. Is there a way to have comments always in front of window split bars?


So I have a typical line chart with 4 series going across it. The X axis shows the value in milions and the y axis is showing the months of the year.

In each month obviously each point is the value spent in each month. Is there anyway to get the chart to show the total amount spent in the year and display in millions.

January = 10M
February = 30M
March = 13M

Total spent is 53M, can this be displayed as a value on each line or even in the chart legend in some way.

Figures display in M but are in normal format ie 13,000,051.00

Ok, my data looks like this:

01/01/2013 00:00 12345.78
01/01/2013 01:00 98765.43

... and so on, for about 20.000 lines (is 3 years and a half of logging every hour, but I will can split it into years).

I want to plot this data in a chart with double X axis, one for the month and one of the week number.
I achieved something quite close with a line chart and the x-axis formatted for date, with units of one month, but then it plots a line for every day, while I want one for every hour.

Also, I don't know how to plot the week number on an extra axis.
I don't know anything about Visual Basic, so I would like if someone could explain me how to achieve that through the menus.

Thank you

I have a colleague looking to plot one line on a 6 data set Bar Chart.

The issue appears that when plotting on a Bar/Line chart the data set is split in two so that three sets are Bar Plots and three are Line Plots.

How would you go about plotting one line representing one data set across the 5 data set Bar?

Thanks in advance for the help.