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

Creating An Overlapping Chart

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

I have attached a spreadsheet as an example.

I have attached the images
Image "Overlap" shows an overlaped chart (which I want to be able to do)
Image "12" shows a line runningn along the peak of each bar (also want to do this).

The Problem
I have temprature 2 readings for each day. I want to show that information in a chart but I'm having a couple of problems.

1. I want the two readings for each day to be overlapped (shown behind one behind the other)

2. I would like to have a line tracking the high point of each of the high temps and the same for each of the low temps.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Add Background Photos / Images to Comments in Excel
- Add background images and photos to your comments in Excel with this macro. This macro uses a really cool feature in Ex
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
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
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
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

Similar Topics

My delima: I generate a line chart that shows the DC voltage of 30 batteries, with DC voltage readings taken once per Quarter for each battery. Each Quarters readings are placed in a separate column on the spreadsheet with the date taken on top. I now create a line chart by selecting Source Data and the Series String with the NAME being the Date. I repeat this process for each of the 4 quarters of data using different colors to identify different quarters data. Works great. The problem is that in the 5th quarter I add another column of data. How do I get the chart to automatically display the last 4 quarters of readings without manually selecting the Source Data and Series for the new column while REMOVING the 1st Quarters string from the Chart. I would like to find a way to automate this process. Also I always show the most recent Quarters data in the Chart with a "RED" color. I have to manually change this every time also. Is there anyway to automate this process? Thanks...


I created a chart on an excel spreadsheet, and a macro allowing me to show on this chart data coming from another worksheet and to change the data by pressing some keys.

I put on this chart a few boxes containing text, showing some relevant parameter and results from computation. The value on these box is copied from a cell that changes according to the data shown.

The problem is that one, just one of the box doesn't show up when I scroll the data. I already tried to use the "bring to front" on this box, and the "bring to back" on the chart. What is puzzling me is that other boxes show up correctly. However, as soon as the chart is selected, one box, containing a very important data, disappear.

The point is that this box is not "attached" to the chart. I mean that if I move the chart, all the other boxes move as well, except this one. So, if I could "attach" the box to the chart, I could probably sort out the problem...

Any idea? Thanks very much for any help and suggestion

Hi all,

I am a diabetic. My doctor asks that I maintain a list of glucose readings and take the last 15 days as a line chart with a trend line. Sounds simple? I thought so. And it is, as long as I manually change the data range in the chart after every new entry.

In my 'Readings' sheet, I have column B containg a DateTime value. I have a random number of readings per day, usually between 1 and 4. Column C contains the glucose reading, an integer value.
To determine the last date, I use the formula =LOOKUP(2,1/(B2:B9999<>""),B$2:B$9999). 15 days prior to the last date is =LOOKUP(2,1/(B2:B9999<>""),B$2:B$9999)-15. To get the range, I determine the last row to be =LOOKUP(2,1/(B2:B9999<>""),ROW(B$2:B$9999)). To determine the first row in the range, I use the formula =MATCH(LOOKUP(2,1/(B:B<>""),B:B)-15, B:B). How do I use the rows found in (3) and (4) in the select data dialog on the chart?

Thanks greatly for any help!


[See attached image]
I am trying to group a textbox and a pie chart using VBA without luck.

The textbox (shown in the lower right corner of the attached image) has been named "DelChart" in the VBA code and the chart is the only chart on the worksheet.

I've limited VBA knowledge (I tend to create macros and try figure out what's going on then modify them as best I can) so would appreciate if someone could show me the appropriate code required.

Thanks in advance for any help or guidance.

MODS: If this properly belongs in another forum feel free to move.

I am trying to creat a number line chart in Excel using 7 numbers. The chart need to be able to plot changing information. I am trying to place a low number, a current number, a range within a high and low, an average and a high.

I have attached a picture of the kind of chart I am trying to produce.

I am specifying a series in a chart by picking the cells.
Is there a limit to the number characters, as I get so far and I just get a beep when I pick the next cell?

='Regular Readings'!$C$1:$E$1,'Regular Readings'!$F$1:$H$1,'Regular Readings'!$I$1:$K$1,'Regular Readings'!$L$1:$N$1,'Regular Readings'!$O$1:$Q$1,'Regular Readings'!$R$1:$T$1,'Regular Readings'!$U$1:$W$1,'Regular Readings'!$X$1:$Z$1

If so, is there a work around?
Maybe I need to have the cells adjacent.

I need to do a chart for work. My boss wants it to show the trend of the daily activities for several pieces of equipment with 1 or more gauges on each piece. The chart needs to have the dates as well. I can make the regular charts but I cannot seem to figure this out. I have attached the read out sheet the information is to come from. I only have readings entered so far for 1 day. I'll enter the rest when we figure out the chart. Can anyone help me please? I feel really useless about right now. I'd appreciate it.

We can define a shape to Line chart series.
Exm: The attached file: I defined "up and down arrow" for line chart.
We need to do the equivalent in the "column chart". But I couldn't find a solution.
Chart should be in bmp format, sample attached.

How can I do?


Hi I wonder if anyone could help me with this one:

I have a table representing a bar chart :

Open High Low Close BarType Zone1 Zone2 Zone3 Zone4... Zone 10
10 45 15 50 Type1 15 30 40 50 100
34 60 45 69 Type5 25 40 45 65 150
76 80 10 56 Type2 10 15 25 75 100
45 87 09 27 Type1 45 60 70 90 200

Basically i am trying to get some statistics for each "bar type" : I am trying to find out how many zones are reached or crossed by the high and the low of each "bar type"

So for example the first line shows that the bar "Type1" has a "High" value that has reached "zone3" and a low that has reached "zone1"

I would like to do that for each bar type and then produce some stats that shows in average how many zones the high and the low for each bar type are reached.

The output would be something like that for each bar type:

Type1 Zone1 Zone2 Zone3 Zone.... Zone10
High 65% 89% 78% .....
Low 45% 18% 90% ....

Any help would be much apreciated!
Thanks in advance

Hello all,

I am wondering if anyone has encountered this problem. I have data containing checkboxes (produced from the "forms" toolbar, not the "controls" toolbar) in a simple chart. When I sort the data using the drop-down auto-filters, the last row of checkboxes from my original data appears as a row of "ghost" checkboxes along with the desired filtered data.

The attached jpeg's do a better job of explaining my problem. The "unfiltered" attachment shows my original data. The "filtered" attachment shows the data with "car 2" selected. If each check box has been assigned to an individual cell link, shouldn't the check boxes for "car 2" be the only ones that appear when I filter the data?

unfiltered chart.JPG
filtered chart.JPG

Furthermore, the "cut and paste" attachment show a similar weird occurrence when I simply cut the 4th row and paste it to the 9th row. It takes the check boxes from the 3rd row along with it! This makes no sense to me.

cut and paste.JPG

Anyone know of any solutions to this? I will be implementing this format to an equipment start-up matrix for a high-rise hotel that contains ~1500 rows, so I want to avoid these "ghost" data if at all possible.

Thanks in advance,



I am trying to do a combo scatter and line chart. The scatter portion of the chart is going to show the # of tickets we have completed since each day 2/15/10. The line portion is going to show the ticket capacity depending on how many people we have on the help desk. The info is as follows:

Scatter Chart

# of Tickets

Line Chart

Tier 1 Capacity

I have attached an image (I think) that shows what I want. The line is just drawn on top of the chart. you can see the problem with 2 tickets that should be above the line are below it. The Tier 1 capacity means if all 20 agents are on Tier 1 the max tickets we can handle is 405. If 19 are on Tier 1 and 1 is on tier 2 the max # is 383 and so on.

Any thoughts?



I normally find excel help just by googling my problem, but for some reason it seems nobody else has this problem.

I have a stacked area chart using dynamic named ranges that is currently giving me problems. When I first plot the chart, the the fill is below the line, but at the end of the range, the line connects back to the beginning of the range, messing up my fill area. (see first image)

In my effort to fix the problem, I toggled one of the data series to use a secondary axis, and all of a sudden it is filling in the area between the line and the top of the chart. (see second image)

But if I switch back so both lines are on the primary axis, this line still fills in above the line all the way to the top of the chart. (see third image)

I have tried redoing the chart, and the same things happen every time. I just need the chart to display like a normal stacked area chart.

What am I doing wrong? Is it how I have selected my data?

In case you need it, see the fourth image to see what my data looks like.

Hi, I was wondering if someone could help. I am creating weather charts showing average low and high temperatures for different areas. I created the first chart on the 1st page, copied the data for the 2nd page, etc and just amended the target areas.

The problem I am having is that on some of the pages the difference in temperatures shows as a huge gap and on others its where it should be. I have attached the file so you can see what I am talking about, for example if you check the tab for Sri Lanka and compare with the tab for Maldives. The Sri Lanka page shows the information they way I want it to be shown.

Does anyone have any ideas how this can be solved??


I am trying to set up a dynamic chart to only chart the last 25 readings in a column without growing my chart. Can someone help? I have checked out the peltrain site and they are pretty complex and I am just a beginner. Is there a simple vba code I can assign to my chart?


I'm new to this forum

I would like to make a chart in Excel 2007 that looks like the image mockup in the attachment. It did work very awkwardly for me so I would like to hear some advice how to make this properly.

So, the point is to have information copied from my online bank account statement and to "tag" each income/expense with a colored text. Only the payments with same color "tags" should be seen in the chart with the according color. By looking at the attached image you surely know what I mean.

Do you think this is possible?

Or if you happen to know different kinds of Excel solutions for this kind of problem?

Thank you very much.

(I tried to do the search for this problem but didn't get anything relevant back)

I have a basic stacked column chart -- the kind which shows percentages, so it's always 100% "high". I would like it to look like a barrel which is full of different liquids.

I've tried this with the cylinder autoshape, with no fill, but it's not visible when I put it over the chart.

If an autoshape doesn't work, is there another way? Perhaps doctoring a barrel image?

I'd be grateful for any advice.

This one should be easy, but I can't fix it and am asking for help.

I have a client that wants to be able to publish *.pdf versions of select sheets within the workbook. Two worksheets have overlapping charts on them. The charts, when I push to *.pdf, do not line up. (In fact, they look odd, with axis, labels, etc. out of place)

I can push a chart to a Chart1 page, but I have overlapping charts (I show an overlay on an earned value progression chart).

Here's the simple code:

msg = "Publish to PDF format?"
Dialogstyle = vbQuestion + vbYesNo
Title = "Save *.pdf Report"
RESPONSE = MsgBox(msg, Dialogstyle, Title)
If RESPONSE = vbYes Then
FileName1 = InputBox("Please input filename", "Filename", ProjectName & Month(Date) & "-" & Day(Date) & ".pdf")
' I was able to export, as an image, the single chart. It looks okay as an image
ActiveChart.Export "test.png"
' This doesn't work, and I know why:
'ActiveSheet.Export "test2.png"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FileName1 _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End If
'This seems extra:
If RESPONSE = vbNo Then
End If


The attached file has 2 sheets.
The first one ("Example") shows a chart report for each worker based on row data.

The second sheet ("Example 2") shows what I want to chart.

1. Some workers have two rows of data (desktop and mobile) that I Want to show in the same chart.
2. Charts must assume automatically the adding of new workers and new month data.

Are these changings possible to do?

Thanks in advance

And second problem I have is, that chart leaves gaps if there is empty cells.
I need these cell to be ignored instead. That chart don't show information from them. For example if there is no information about this year, then just ignore it, and show only years where is information.

Data for example:
971 349 188..........................953 285 324
611 323 736..........................612 510 025
747 189 300..........................703 691 200

How to ignore "200804" instead of gap?

As I know, it work's if there is only line chart, but i have chart with 2 Y axes, 1st presents column chart, 2nd - line chart.

Thx in advance.

This is my first attempt at creating a chart but I'm needing help.

I'm wanting to plot measurements taken at various points along a distance of 10 miles. The value of these measurements decreases as the distance increases. I want the measurements to appear on the y axis and distance on the x axis.

And here's the tricky part, at least for me. I want the distance scale (x) to run from "0" to "10", and graduated every mile. Zero distance would be at the y axis. The first tick mark would be labeled "1" (mile), the second tick mark, "2" and so on up to "10" at the far right side of the chart.

Tricky Part (cont.): I won't necessarily end up with ten mearsurements to coincide with the ten 1-mile distance points; there may be five, eight, or even 20 measurements taken within this ten mile distance. But of course, I want the measurement points I do end up with plotted at their respective distance point along the distance scale.

I think I'd even like to see a vertical line coming down from each measurement point that hits the distance scale at the distance that mesurement was taken (e.g. 3.4 miles).

This would be a line line chart, where the line would start high on the y axis and slope down to the right as the distance increased.

I'd appreciate any help.

I'm creating a chart that shows a distribution of values, comparing them month by month (see attached spreadsheet). In addition to showing the actual values on the chart, I'd like to show the percentage for that month. For example, in my spreadsheet, in month '1', there are 11 values. 4 of them are labeled as '14.3'. I'd like to show the 14.3 column as being 4 high, but also, I'd like to show that it's 36% (4 divided by 11) of the total numbers gathered for that month. Is there a way to do this?


I posted this the other day, but I realize now that it was in the wrong part of the forum.

OK, what I'm trying to do is set up a sheet that will generate a QR code from Google's API. I have the sheet set up so it will create a URL and want to insert the image that URL creates on a separate sheet. In theory, this would create images for each row in the inventory.

I have it set up so that these columns:

2711-P431209D2 | 1238470234 | 293847123

are fed into this formula:


Which leads to this:

And from that I can insert this image:

Google Chart

What I'd like to be able to do is create a sheet of QR codes that I could print on some Avery labels to label inventory.

Basically, it is the same as telling Excel via VB to insert a pre-made image, but the address of the image is created in sheet1.

I just have no idea how to get VB to look at the cell with the address, and insert that image on to sheet2.

Any idea how I'd go about this?

Ok so ive got a worksheet, that shows how much work I've done monthly.

There are 12 tabs (jan - Dec) as well as a "Totals" tab.

The totals is where im keeping a running total of work ive done, including this chart.

I cant get a chart setup to show the month along the bottom, and the numbers along the side.

The side numbers would have to go to about 100.

Each of my tabs has a Totals row, that shows how many items ive produced each month.

How do i apply this to a chart? When i create a chart, and select each of the "Totals" rows each month it tells me the data is invalid.

I just want a basic line chart.

I'd like to be able to to adjust bar widths on an open-high-low-close stock chart (AKA: candle chart). This is possible to do on a column chart, but I'm not able to find a way of doing it on this particular chart.

To bring this chart up, first paste the following example data into your spreadsheet and select all of it:

111.20 111.74 109.09 109.21
113.92 114.27 111.56 111.70
114.28 114.45 112.98 113.89
113.62 115.13 113.59 115.06
114.73 114.84 113.20 113.64
114.49 115.14 114.42 114.93
113.95 114.94 113.37 114.62
113.97 114.21 113.22 113.66
115.08 115.13 114.24 114.73


insert -> other charts -> all charts -> stock chart -> select second one from the left.
it should read "open-high-low-close" when you hover over it's icon.

What I'd like to do is make the bars 50% wider or so. If it is not possible to do it with standard excel commands, is there a way to program this with basic?

Thank you.

I am working on a plumbing fixture spreadsheet for an architecture firm that first shows an image of the fixture; then the name; and then lists the manufacturer, style, color, and contact information. Is there a way to make this series of lines a "block" that will automatically insert another "block" when all of the information is full? Also, as you can see from the attached file, I am inserting images. Is there a way to program either the cell or the images so that they will automatically scale to fit inside the appropriate cell?