Comment from Felicity Todd
Time: Tuesday, July 1, 2008, 11:15 pm

Hello, I was excited to find you section on OHLC charts with left and right tick marks. However, when I try to construct one the dates for the XY chart change to 4 years earlier and these dates are not in the data sheet. As a result I can never get the OC series to match up with the HL series.
Can you help.

Many thanks,

Felicity

Comment from Julie Sheehy
Time: Tuesday, July 15, 2008, 3:49 pm

I have been asked to create a chart from a pivot table (actually the chart is linked to the pivot data and not the actual pivot table).
What they want me to do is write some code or macro to automatically have the chart update with the correct number of rows without manually selecting them if they differ from month to month.
For instance this month I have 5 depts and next month there could be 4 or 6 so without manually changing the cell ref for the source data is there a way to do this. I am not a programmer just a data analyst.
Thanks if you can answer this.
Julie

Comment from Leo
Time: Tuesday, August 19, 2008, 9:33 am

This site is an excellent resource. The combination of your scientific background and extensive practical experience makes all the difference. You just solved my clustered stacked column chart problem. Thanks very much for the clever solution.

I have a problem I’ve been struggling with for some time now and would be most grateful if you could help me solve.

I have a chart with 4 series represented as lines. 3 series have their “Y” values on the primary axis and the 4th is on the secondary. The chart has a data table showing the values of the points for all the series.
I would like to put on the chart horizontal lines to represent target values for 2 of the series (one on each axis). The problem is doing it without showing the values for the target lines in the data table. I’ve been overlaying the chart with another transparent one that has the target lines on it. This is very messy as you can imagine, particularly as all the series and their domains are updated monthly by the user running some code I’ve written to calculate quarterly and year to date …etc totals.

Can this it be done at all? i.e., to have some series on a chart without showing their values in data tables or am I asking too much?

Your response would be much appreciated
Regards,

Leo

Comment from Patrick
Time: Tuesday, August 19, 2008, 5:37 pm

Awesome charts!

Jon, I have a stacked bar chart where I have three costs; labor, materials and freight displayed for each month of the year. Cost is the other axis.

I show the cost of each item as a data label but would like to also show the percentage. Is it possible to have TWO data labels for one series?

Let’s say labor is 100, materials 80 and freight 20. I’ve tried many ideas to outsmart excel such as having six items, having labor2, materials2 and freight2 with values so small (.5), you wouldn’t see them on the graph. I then changed the color to no fill and selected data labels, but the 0.5 (value) appeared. What I’d like is 50%, 40% and 10%.

My other option is secondary axis graph, but don’t know how it would be any different than the above, since the labels equate to the series.

Currently I’m using a text box, which I have to re-size every time I refresh the graph.

Thanks for all you do…

Patrick

Comment from renee
Time: Wednesday, August 27, 2008, 1:37 am

hi there, i need to show 2 sets of stacked data (2 data columns – Forecast 1 & 2 and Actual 1 & 2) in the same chart, can you help?
Thanks

Comment from Balaji
Time: Wednesday, September 10, 2008, 9:01 am

I am creating a excel with using java. I have to load three sets of data that are given as input in the jsp page into excel and make charts for them. Upto the point of rendering data in the excel sheet, its fine. But i have no clue as to how to create chart for excel from java.

One method of doing it is VB macro. Is it possible to set macros for excel from java file?

Regards,
Balaji

Comment from Carlos Diezel
Time: Thursday, September 11, 2008, 9:29 am

Hi,

I’m trying to make a normal stacked-column chart, but instead of having in the label the column values, I’d like to show other values (in this example the increase rate for each column along the years).

Do you know how can I do this without needing to input in each label some formel (for example “= $A$2″, “= $A$3″ and so on?

Thanks!

Bye!

Comment from Leo
Time: Tuesday, September 23, 2008, 11:10 am

Hello again Jon,
After I produced a Clustered-stacked chart following your technique, my boss now thinks nothing is impossible.
I have a new requirement to produce a chart with several line graphs ending at the same point (which is the current date , but starting at different times on the x-Axis.
To explain in more in detail, The requirement is to produce a chart with performance indicators for several projects over a period of 12 months.
The last point on each of the line graphs on the Chart represents the current date, however some projects would have started at a later date and therefore there is no data for the period before their start.
By default excel starts charts at the same point and ends them at different points if the x domains are different, can this be reversed?
Your advice would be much appreciated.

Comment from alan grieve
Time: Wednesday, October 1, 2008, 9:17 am

I’m trying to build a simple line graph (dates on X axis, numbe on y axis) where the line changes colour after a specific data. Is this possible?

Comment from Pedro Carrasco
Time: Tuesday, October 7, 2008, 12:14 pm

Hi Jon, after many exercises and with your help, I divide the period and made my chart with 3 sections and now I have a better and smoothing curve.

Sorry for my late response, but my baby girl will be born on this week, and my wife an me are so nervous………you know, this is a nice experience.

Thanks by your help, we will keep in contact.

Pedro Carrasco S.

Comment from Darlene
Time: Friday, October 10, 2008, 2:25 pm

Hi Jon, you really helped me out a few months ago when I was trying to chart information which had mixed data. It came out dynamite and I learned so much. I’m hoping you can help me again. I need to setup an Exit Interview spreadsheet. I want to be able to put a value to the answers so I can chart the information. I just do not know how to begin the process. I have looked all over the place for a template and I cannot find anything. Any ideas, suggestions, places I can find a template.

PS: Was up the mountains last weekend. Took the tram up Mt. Cannon. Mt. Lafayette’s cap was full of snow. It was so cool to see that along with the foilage on the other mountains. Got some great pictures.

Thanks, Darlene

Comment from Darlene
Time: Friday, October 10, 2008, 3:42 pm

Hi Jon, thanks for that information. I will save this for when I chart the information. What I am looking for right now is how to set up an Excel spreadsheet to capture this information. Not even sure if I know what I’m asking for. I think maybe I just have too much information. I have ? like

Reasons for Leaving

1. Why are you leaving your position at the hospital?

Pay __
Hours __
Moving __
Retirement __
Other __

It’s Friday afternoon…………

Darlene

Comment from Darlene
Time: Tuesday, October 14, 2008, 8:45 am

Thanks Jon. I know it is a complicated process. I’m going to just start playing around with a spreadsheet and refine it as I go along. Thanks for helping out. Absolutely love your blog. Your charting is the best!!

Darlene

Comment from Darlene
Time: Friday, October 17, 2008, 8:32 am

Hi Jon, hope you can help me out with this problem. It probably is a relatively very simple thing for you but I am a novice. I am trying to chart two series with one having a total of 11,500 and the other having a total of 79. The 79 does not show up because it is so small in comparison to the 11,500. I have the Y axis set to -500 and it just shows 79. I also have a secondary axis that I am using to chart two line items in correlation to the 79. Can you help me with somehow getting the 79 to show. I have tried playing with the y axis and cannot get the 79 column to show.

Thank you,
Darlene

Comment from Darlene
Time: Friday, October 17, 2008, 12:15 pm

Hi Jon, yes “total” means maximum. I am taking total number of patients for FY which is 11,500 and then I am also taking the monthly average from the totals and trying to chart Total and Average with total having a large number of 11,500 and average having a low number of 79. And I am also using a secondary axis to show total wait times. Hope you can understand what I’m trying to say. And I am so happy to hear from you. I thought maybe you took the day off. Thanks for all your help.

Darlene

Comment from Bruce Jonson
Time: Tuesday, November 4, 2008, 1:08 am

Hi Jon,

I have built a Dynamic chart that grows when I ad data to the range using the technique
The series = to the Name range ChartData which is
=OFFSET(ChartData!$B$1,1,0,COUNTA(ChartData!$B:$B)-1,1)
Now this works great…. Until I want the cells in column B to be referencing a column on another sheet Now my chart shows a bunch of zeros as Column B now shows zeros not blanks.
Any idea how to get around this?

Thanks in advance

Comment from Alyssa
Time: Tuesday, November 4, 2008, 2:43 pm

Hi Jon,
Is there a way to get excel to change the way it orders chart types in a combo chart legend?
I found one of your entries that states that Excel orders chart legend entries by type of chart… I’m trying to create a before /after chart with the ‘before’ a line, and the ‘after’ a column. Each time I try, I can’t get the legend to order the ‘before’ before the ‘after’. I’ve tried creating dummy series entries, but can’t get that to work either!

Comment from James
Time: Thursday, November 20, 2008, 11:05 am

Hi Jon,

Very nice page, lots of great ideas. I am having trouble with pie (of pie) charts. Bascially, I want a series of pivot charts that produces a pie chart, but groups together items below a certain value to avoid clutter. Pie of pie and bar of pie do this, but I don’t want the second chart to be displayed.

Any ideas on how to solve this problem?

Thanks,
James

Comment from J. Susnik
Time: Thursday, November 20, 2008, 11:21 am

Jon,
I have a data series say:

A B C
100 10 1
200 15 2
300 20 3

I am plotting the values in A on the X-axis and the values in B on the y. I want to label the y-values, but using the numbers held in column C.

Do you know any easy way of doing this? I am using Excel 2007 by the way.

Thanks for any advice

Comment from James
Time: Friday, November 21, 2008, 11:32 am

Thanks Jon, that did the trick.

Unfortunately it doesn’t remember the settings when the data is pivoted (well not in the right way – big missing chunks in the main pie!) nor does a chart template solve the problem, but I guess it might be a specific issue relating to how MS have implemented the chart in the first place.

Will have to do things manually when I pivot, or fish around in the MS chart documentation – not sure which is more tedious!

Thanks again,
James

Comment from James
Time: Monday, November 24, 2008, 8:20 am

Hi Jon,

thanks again. I managed to cobble together a bit of code to automate the removal of the second chart.

Public Sub formatOthers()
    ActiveSheet.ChartObjects("Chart 1").Activate
    Dim maxDataPoints As Integer
    maxDataPoints = ActiveChart.SeriesCollection(1).Points.Count
    For pt = 1 To maxDataPoints
        If ActiveChart.SeriesCollection(1).Points(pt).SecondaryPlot Then
            ActiveChart.SeriesCollection(1).Points(pt).Interior.Pattern = xlPatternNone
            If ActiveChart.SeriesCollection(1).Points(pt).HasDataLabel Then
                ActiveChart.SeriesCollection(1).Points(pt).DataLabel.Select
                Selection.Delete
            End If
        End If
    Next pt
End Sub

 
This does the trick, just have to reset the chart at the beginning each time and allow macros to run in your workbook.

Cheers,
James

Comment from James
Time: Monday, November 24, 2008, 9:17 am

Thanks Jon,

I’ve now managed to modify it to restore segments in the main chart after pivoting too. This is important since the chart template seemed to mess up my dynamic title.

Public Sub formatOthers()
    With ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1)
        For pt = 1 To .Points.Count
        If .Points(pt).SecondaryPlot Then
            With .Points(pt).Interior
                .ColorIndex = xlNone
            End With
            If .Points(pt).HasDataLabel Then
                .Points(pt).HasDataLabel = False
            End If
        Else
            With .Points(pt).Interior
                .ColorIndex = xlColorIndexAutomatic
            End With
            .Points(pt).HasDataLabel = True
            With .Points(pt).DataLabel
                .ShowValue = False
                .ShowCategoryName = True
                .ShowPercentage = True
            End With
        End If
        Next pt
    End With
End Sub

Comment from michael lawson
Time: Wednesday, December 31, 2008, 2:38 pm

Jon -

I’m trying to programmatically set the colors (points) of a pie chart using the RGB function. I can set indivdual cells to these RGB values but they appear differently when applying to each point in the series.

the code goes something like this:

range(“a1″).interior.color = rgb(colorArray(1, 1), colorArray(1, 2), colorArray(1, 3)
range(“a2″).interior.color = rgb(colorArray(2, 1), colorArray(2, 2), colorArray(2, 3)
range(“a3″).interior.color = rgb(colorArray(3, 1), colorArray(3, 2), colorArray(3, 3)

…

generates the correct colors, but

for i = 1 to numpoints

set mypoint = mypoints(i)
mypoint.interior.color = rgb(colorArray(i, 1), colorArray(i, 2), colorArray(i, 3))

next i

gives the points very different colors.

Should I be concerned with the mypoint.fill.backcolor.rgb and mypoint.fill.forecolor.rgb?

I get an error when trying to set them:

mypoint.fill.backcolor.rgb = rgb(colorArray(i, 1), colorArray(i, 2), colorArray(i, 3))

gives me the error:

Compile error:

Wrong number of arguments or invalid property assignment

Is this a bug in Excel 2003 or is there something I’m missing?

Please advise.

Thanks!

Best regards and Happy new year,

Michael

Comment from Barbara
Time: Friday, January 9, 2009, 11:36 am

Is there a way to create a waterfall chart which will show a trend up and down at the same time creating a sideways “V” effect.

For example, I start with my original budget, decrease by lost sales, increase by new found unbudgeted sales, and now I have a bunch of entries which can either go up or down from that point on. I want to show the down trend, as well as the up trend, starting from the same point.

Comment from JonC
Time: Thursday, January 29, 2009, 10:23 am

Great post! I’ve tried a couple of solutions available online (amCharts, Emprise, Fly Charts, Open Flash Charts, flot and flotr… among others) and none of them fits me for one reason or another. I would really appreaciatte some hints on where to look further. Just to describe a bit my application here I leave some details and my needs.

Application

Our application will mainly make use of the line chart.
It will display temperature values against a time scale (for the future we would like flexibility in the x-axis data format).
Multiple graphs will be displayed in one canvas (maximum of  50).
Maximum data displayed in each graph of 106.000 points.
Data will be read from an XML file.
It will be displayed in a JavaServer Faces environment and we use rich facelets components.

Needs

Automatic resizing of the canvas and graphs when the browser window changes size.
A primitive firing an event or notification when the zoom is performed, for displaying a sensitive amount of data and not overloading the web application.
A primitive to redraw the data set periodically, for quasi real-time information display since new temperatures will appear every 3 to 5 minutes.
Possibility of having some sort of marks at specific points in time (in order to showcase alarms or information alike).
The x-axis has to be fully customizable (tickers at fixed positions specified by our application instead of automatic calculation).
Compatibility with IE Explorer 6, Firefox 3, (if possible Opera 9.6 and Google Chrome).
Zooming (if possible squared).
Precision of +- 0,01 ºC
Label showing each graphs temperature value.
Discontinuous data handling.
Legend not shown in the displaying area, to avoid visibility problems.

Optional needs.

Printability of the graph in a pdf file or similar.
Day-night background shading.
Activation and deactivation of graphs from the legend or directly.
Mouse-lock for each graph.

Most of these are covered either by one solution or another, but not for all of them. Any ideas?

Thanks!

Comment from JonC
Time: Friday, January 30, 2009, 10:15 am

Thanks for the suggestion, I will get in touch with those people. I have had a look at Tableau and altough it looks really powerful maybe is not the type of solution I am looking for (a charting library which I can utilize for a web application). But again, I appreciate your suggestion.

Comment from Neville Turbit
Time: Wednesday, February 11, 2009, 6:50 pm

I sell project management software which is Access based. For the last two weeks I have been working on a Gantt Chart display backed up with lots of VBA code to do the configuration. As I knew next to nothing about the Chart object I tried to buy a book but could find nothing. I searched the web and found this site which has been fantastic. Without it I would be struggling for another week. Thanks Jon for publishing such great help. The Access function is not quite there yet but I am now tweaking a few last bits.

So when are you going to write a book? Your style of presentation on the web is easy to understand and would be great in a book.

Comment from Darlene
Time: Thursday, February 12, 2009, 8:27 am

Good morning Jon, Hoping you can help me out with figuring out how to chart the following. I am trying to chart 3 series. One has a high number and the other two series are percentages (most being in the 90% and the other ones are like 2.5% etc.). I started out with a column chart and added a secondary axis. The high number is arranged as a column and the percentage series I changed to lines. But it looks weird because the low percentages hug the bottom of the axis and high percentages hug the top of the chart. What I would like to do is keep the high number as a column and cluster the percentages one on the other. I’m having a difficult time because of the mixed value ranges and the high & low percentage numbers.

This is the information I’m charting:

Total Specimens 675

# Successful 97.5%

# Hemolized 2.5%

What would you suggest as the best way to chart this information, panel chart maybe??

Comment from Darlene
Time: Thursday, February 12, 2009, 10:34 am

Thank you I will try your suggestions out. Have a wonderful day!

Comment from Adam
Time: Tuesday, February 17, 2009, 1:12 pm

Hello Jon
Excellent site, great excel tutorial. Congratulations.
Still – I am looking for a solution for broken line chart. Something like you can see on yahoo currency charts
like this (flash interactive 5 days chart)

http://finance.yahoo.com/echarts?s=USDEUR=X#chart2:symbol=usdeur=x;range=5d;indicator=volume;charttype=line;crosshair=on;ohlcvalues=0;logscale=off;source=undefined
Of course I am not looking for interactivity but the broken line. NA() formula does not work because it still plots line between two points. Masking with other series does not look good.
Any ideas?

Comment from Adam
Time: Thursday, February 19, 2009, 5:27 am

:) Thank you Jon
Nice solution but… still not for me, sorry
Saying “not looking for interactivity” I meant about yahoo (or maybe it’s google chart) “time scaling” of a chart.
What I need the chart for is currency exchange rates shown in time. Sometimes there are gaps in series but they are different for different currencies.
I use the same chart for every currency which I choose from dropdown list. The list gives a variable for an OFFSET formula that gives me whole series for a chart. OFFSET looks for a currency and time period in a big table of exrates data and it is shown on the chart. Cannot find solution for “broken lines”.
Thank you, again. Regards.

Comment from Anonymous
Time: Friday, March 6, 2009, 2:32 pm

I keep getting the error message when using the PTS Charts add in – Rin Time Error “1004′. Any suggestions?

Comment from Ifat Royter
Time: Friday, March 20, 2009, 5:10 pm

Hi

I want to do a partialy overlapped bar graph but to a stacked graph type?
Is it possible and if it is how do I do it?

Comment from Liam Morris
Time: Thursday, April 9, 2009, 11:57 am

John,
Your advise last year on the plotting funtion of =iferror([argument],#NA(),[satisfy]) was the most AWESOME tip ever received. It was the perfect answer to my needs.
I am now working year upon year of data. The original comes to me as an output file from another, Non-Windows application, in a *txt format and has to be imported to Excel as fixed with, when in truth, there are no natural points to seperate the data that arrives in 141 coulmns. I’ve rapidly learne how to import and identify the column breaks, but is the way to set an “automatic import” operation into Excel?

Of bigger challenge, with the data available in different folders then files:
*YYYY
***MMM
File Tab Arrangement:
RAW/PIVOT/PIVOT FIX
– (Pivot fix is used to ensure dates line up for pasting to individual tabs)
original UNSORTED spreadsheet
and then each MMM file has 75 tabs for each of the data recorders
8000/8001/8002 etc.

What I would like to know, is there a way in which I can, in as few moves as possible, look at the annual and deeper monthly and still deeper daily data for trend analysis.
Offering an analogy, looking through a paper towel tube to simultaneously look at, not through, the different layers of glass in a triple pane window, to look for similarities deep in the historial data.

Keep up the great work, I enjoy visiting this site and adding to my wealth of knowledge. (And your don’t even tax it:-) )

Thanks
Liam Morris

Comment from Ruben
Time: Monday, April 20, 2009, 11:19 pm

Jon, i would like to know how to make fancy waterfall charts that have negative values (like the example you have with volume, price, mix and other but with one or more of those values not increasing but decreasing) For example a decrease in value can be compesated with an increase in price. Thanks

Comment from Liam Morris
Time: Tuesday, April 21, 2009, 7:25 am

Doug,
I’ve had similar challenges. Rather that fighting it, whai I have done is further expand the scope of my X axis to the LEFT in the data set.
For example: (VALUES MEAN NOTHING)

INSERT GRAPH

If you highlight A1-A5 INSERT/RIGHT, then make a text entry in A2, expand and center that text in A1-A2 so that it is a single cell, or however long the span is.
REPEAT AS NECESARY
When you insert the graph, Start with B1:F5, then EDIT X axis to include A1.

What you end up with is a two line X axis, the near line showing the years, and the lower line grouping the years as desired.

I haven’t figured out how to colorize within the Axis, but I hope this helps

Liam

Good Luck

Comment from Rodolphe
Time: Tuesday, July 14, 2009, 11:18 am

Hi, First thanks for your web site that taught me a lot…
Now, I have created a customised (xlLineMarkers type) chart that :

1) takes its source data from a selected range (by declaring in VBA : SetDatasource=… )

2) calculates (in VBA) additional data from this source, so that at the end I have a Xdata array and Ydata Array that I assign to Values(=Ydata) and Xvalues(=Xdata).
All of this works fine and the chart comes up correctly but…

3) whenever I modify (in Excel) a cell, there is no “update” event generated and my chart is not re-calculated (which is normal since I started doing a ‘SetDatasource=’ and deleted all Series Excel assumed I wanted before starting adding my own series)

The question is : how could I cope with this so that my chart could be re-calculated whenever I change a cell that belong to its Datasource (though this datasource is merely used to generate VBA custom data from which the chart is generated) ?

Comment from Deon
Time: Thursday, August 6, 2009, 7:57 am

Hi Jon,

Firstly, I hope I am placing this question in the correct place.

I am not very good at VBA, I have just been reading suggestions throughout the web.
Finally, I have got my chart working up to a point.

I have a macro which changes the colours of the bars of series 1 and series 4. It works perfectly on Sheet 1. However, if I make a copy of sheet 1, the macro still runs, but it obviously does not have the same Chart number, which then gives me an error. Please could you have a look at the code bellow and let me know how to activate the Chart on the active sheet?

Sub Macro1()
Dim cht As ChartObject
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range

Set rPatterns = Worksheets(“Legend”).Range(“A1:A42″)
For Each cht In ActiveSheet.ChartObjects

With ActiveSheet.ChartObjects(“Chart 43″).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
With ActiveSheet.ChartObjects(“Chart 43″).Chart.SeriesCollection(4)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End With
Next
End Sub

I am suprised I got it work up to this point!!!

Thank you.

Keep well.
Deon

Comment from Deon
Time: Friday, August 7, 2009, 3:47 am

Thanks Jon,

Unfortunately it is now giving me a runtime error ‘91′, object variable or with block variable not set.

Debug highlights the following line:

.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex

Enjoy your day.
D

Comment from malcolm
Time: Sunday, August 9, 2009, 6:05 am

Hello Jon

I am trying to create a chart where I enter values each month and it shows me the trend as I go along the year. However because I have zero values for all the months after this month (they will get values at the end of each month) the graph just shoots down to the X axis after month one. I would like it so that I can have the graph there waiting to add in values each month whilst it still is useful straight away.

I am trying to model weight loss so it needs to be motivating straight away. If you have answered this already sorry

thank you

malcolm

Comment from Louis
Time: Friday, October 30, 2009, 11:29 pm

I want to make a surface chart with black wireframe lines and no band fill in Excel 2007.

I select the label I want to edit in the legend and then select Format Band. I cannot change the Fill to anything but “Automatic”.

I tried to sneak up on it by using the Patterns add-in. Excel says “Cannot apply Pattern Fills to current selection.”

Comment from Ty Elders
Time: Friday, November 20, 2009, 4:18 pm

Jon,

I was wondering if you could help me with something. I’m not even sure if this is possible in excel but I thought if it was you would know how to do it. What I’m trying to do is create a special type of ranked chart. What I have is a list of hospitals and a score for two different time periods for each hospital. What I would like to do or what I have envisioned is two vertical axes with each of these hospitals listed (and ranked based on the two time periods) on each side so the first time period on the left and the second time period on the right. Then I would like a line connecting the hospital from the left to that same hospital on the right. So kind of like what (at least what I did) in grade school where I would draw lines connecting the question to the answer. Is there a way to do this in excel? I have excel 2007.

Comment from Louis Fussell
Time: Tuesday, December 22, 2009, 4:41 pm

Thanks for your help before. I am now stuck and cannot find anything to help me. Here’s my situation: I have a a set of ordered pairs of data (cost, schedule) generated from a Monte Carlo simulation. I use the data to find an empirical joint distribution function and then plot frontier lines using a contour plot. (That’s where you helped me last time.) I also plot an xy-scatter plot from the ordered pairs.

Now, what I need to do is show the contour plot and the xy-scatter plot on the same axis. As I understand it, because they are different flavors of charts, it cannot be done using conventional methods. So, what I do is 1) make sure that the charts have the same scale range, 2) copy the plot area of the contour plot, 3) save it as a picture file, and then 4) set the plot area fill of the xy-scatter plot to the picture of the contour plot.

I don’t always have write access to the customer’s drive, so the macro bombs when I try to create the image file. What I would like to do is copy the picture of the contour plot into the clipboard and then set the plot fill area of the xy-scatter plot with the clipboard image. I can do this using the Excel dialogue box, but cannot figure out how to do it using VBA commands. Recording a macro does not yield useful results.

Comment from Liam Morris
Time: Tuesday, December 22, 2009, 5:03 pm

Louis Fussell :

I recently made two different charts from differently positioned data.
I wanted to have the (2) average %/population superimposed over the (1)Volume/hr.
My solution was to take the overheight the (1) which reflected in hourly BAR volume in each of the 24 hours of each day of the month;
the (2) graph was teaked until I got the data set presented correctly then reduced it’s height, set FORMAT PLOT AREA to NO COLOR, NO LINE. What resulted was essentially a transparent line graph. All axis and GRID LINES became RED so as to stand out. I dragged the LINE chart superimposed onto the BAR chart, and then stretched it horizontally to line up perfetly with the X-axis of the BAR gaph.
Once accomplished, the 2 X-axis were perfectly in sync with eachother, and then I made the text of the x-axis of (2) WHITE.

Comment from joris
Time: Tuesday, December 29, 2009, 5:24 am

Dear Jon,

Your site has been very helpfull to me for creating some interactive waterfall charts. however i now have a question for which i haven’t find the answer just yet. I’m wondering how I could control the lenght of an x-axis, but more specifically, the gap between the different categories. It concerns a simple line chart which I’m trying to use to make a kind of a bump chart. However, lengt of the x-axis and the gap between the categories is determined by xls upon the creation of the chart. I want my lines to become smaller, but if I just make my chart thinner by dragging the plot area, everything is scaled. would you be able to help me with this?

Comment from John
Time: Thursday, January 7, 2010, 12:33 pm

I am trying to do something that you will find to be very simple. I am trying to add data labels to an XY scatter plot. I have tried copying the suggested macro from the Microsoft website

http://support.microsoft.com/?kbid=213750

but I get a run time error 91 message when I try to run it in excel 2002. My X and Y values are valuation metrics for individual stocks and I am trying to display their ticker symbols on the scatter plot rather than the values. Can someone help?

Thanks,

John

Comment from John
Time: Thursday, January 7, 2010, 1:11 pm

a follow up to that is I figured out how to use the microsoft macro for the scatter plot labels but I can only do it for five rows as the macro is limited to B2:C6. Any suggestions on how to do it for a much larger data set that sometimes skips rows?

Thanks,

John

Comment from John
Time: Thursday, January 7, 2010, 6:30 pm

Great, thanks!

Comment from Liam Morris
Time: Wednesday, February 3, 2010, 2:54 pm

In an every increasing effort to make my job more palletable to my customers, I would appreciate any insight into developing a macro or shortcut in order to AUTOMATICALLY pair up the PRIMARY Y axis to be 10% of the SECONDARY Y axis.
The data sets are ALWAY married but the overall range of tables will fluctuate enough that a single scale set for the year would hid significant variations. During the year – the old rising tide raise all boats. AUTO Scale is OK for one axis, but the second axis doesn’t always work out to 10% or x10. if not matched, then PPRIMARY axis lines do not match to auro scale of SECONDARY axis.

Comment from Silvia
Time: Thursday, February 4, 2010, 3:37 am

I learn how to update the axis scale through the page: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html#ixzz0eYM0GsvN

However, if the source data and chart are on separate worksheets (i want the chart to be on a standalone worksheet so that it’s easier to read the chart), how should I adjust the VBA code?

Thank you very much!

Comment from Silvia
Time: Thursday, February 4, 2010, 11:48 pm

Jon,

Thank you very much! It works beautifully in my workbook. May I ask you another VBA question, I want to attached data labels to a XY scatter plot and I found the following VBA code:

Sub AttachLabelsToPoints()

‘Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String

‘ Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

‘Store the formula for the first series in “xVals”.
xVals = ActiveChart.SeriesCollection(1).Formula

‘Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, “,”), xVals, _
Mid(Left(xVals, InStr(xVals, “!”) – 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, “!”), xVals, “,”) – 1)
Do While Left(xVals, 1) = “,”
xVals = Mid(xVals, 2)
Loop

‘Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub

Imagine in my workbook, there are 5 datasheets feeding into 5 charts. This set of VBA codes works very well with the first 3 of the charts but errors come up with the last 2 when the data labels for all 5 charts are identical (different X-Y values).

The error always happen at:

xVals = Mid(xVals, InStr(InStr(xVals, “,”), xVals, _
Mid(Left(xVals, InStr(xVals, “!”) – 1), 9)))

Any idea how to resolve this? Your help is much appreciated!

Silvia

Comment from Philip
Time: Monday, February 15, 2010, 10:38 am

Hi Jon,
It’s my first time on your blog & I must admit it’s really nice !
I would like to know if it is conceivable to create a chart similar to what you call a Whisker Chart with a standard Excel setup ?
What I’m trying to portray is 2 price levels along one vertical “whisker”, which would represent our price level vs the market. So for example, on the left of the whisker you would have the market price and on the right you would have our price, each cursors at different heights of the whisker.
Then, I would just align 10 or so of those whiskers side by side to show our position in several countries.
Is this something that sounds feasible according to a specialist as yourself ?
Tons of thanks in advance.
Philip

Comment from Philip
Time: Tuesday, February 16, 2010, 4:27 am

Thanks a lot for your answer Jon.
Although I must admit I actually don’t even know which type of chart to begin with to form a base. The only chart I see that could possibly work is the stock one or maybe the traditional column chart. Which one should I start with in your opinion as there isn’t exactly a chart called whisker in excel… ?
Tons of thanks in advance.
Philip

Comment from Philip
Time: Wednesday, February 17, 2010, 1:46 pm

Hi Jon,
As per your previous answer, I am struggling to actually put together the “right ticker” and “left ticker”. Is there a special trick to do so, I looked in all the options I could possibly find…or so I believe…and would really appreciate you guiding me through ..

As a reminder –> I am still trying to place along the same whisker a market price curser and another representing our price.
Tons of thanks in advance.
Philip

Comment from Vanna
Time: Wednesday, February 17, 2010, 6:18 pm

This is a fascinating site! I’m trying to use a combination of a stacked column chart out of the available choices in Excel that combines the chart with a data table below the chart. My challenge is that I’d like an extra row and column in the data table to show the totals for each category, but I don’t want the totals to appear in graphic form on the graph.

I can’t get the image uploaded right now, but maybe you can answer my question without seeing the chart. If not, that’s OK too. Thanks in advance.

Comment from Philip
Time: Friday, March 5, 2010, 8:51 am

Hi Jon,
I am trying to plot a scatter graph and have attempted to find the answer to my problem in your notes but didn’t seem to find the solution. Could you please tell me if you have any clue as to how to clean up the x axis of a scatter graph when trying to plug multiple Y values corresponding to the same X axis date. I have attempted to write out my data sheet below, I hope it won’t look all distorted once I submit my comment…it probably will.
A B C D E
Date (x) Project (y name) Competitor 1 (y) Competitor 2 (y) Competitor 2 (y)
Jan 09 pj 1 comp 1 price comp 2 price comp 3 price
Feb 09 pj2 comp 1 price comp 2 price comp 3 price
Feb 09 pj3 comp 1 price comp 2 price comp 3 price
Mar 09 pj4 comp 1 price comp 2 price comp 3 price
Apr 09 pj5 comp 1 price comp 2 price comp 3 price

Comment from Philip
Time: Friday, March 5, 2010, 10:13 am

Hi again Jon,
OK the data I wanted to write out nice and fancy didn’t turn out too fancy after all.
Plus I messed up the column order….no big deal, here’s the summary :
Column A = Project Names
Column B = Months
Column C = Competitor 1 prices
Column D = Competitor 2 prices
Column E = Competitor 3 prices

Not really sure if I should actually use a scatter or a line graph to have the months show up neatly on the X axis considering I have several Projects corresponding to the same month. I know I want to portray the prices with points and have plugged in a macro to portray the project names for column C, but I also can’t figure out a way to tell the macro to write out the project names of colums D & E.
Finally is there a way to make the project name show up in different colors to show it as a won or a lost ?

Well, that’s quite a challenge I’ve set myself but I realize this is beyond my abilities. Does any of what I wrote ring a bell to you ?
Tons of thanks in advance Mr Pelltier !
Philip

ps : here’s my macro that shows column C project names :
Sub AttachLabelsToPoints()

‘Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String, xVals2 As String

‘ Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

‘Store the formula for the first series in “xVals”.
xVals = ActiveChart.SeriesCollection(1).Formula

‘Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, “,”), xVals, _
Mid(Left(xVals, InStr(xVals, “!”) – 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, “!”), xVals, “,”) – 1)
Do While Left(xVals, 1) = “,”
xVals = Mid(xVals, 2)
xVals = Mid(xVals2, 3)
Loop

‘Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub

Comment from Philip
Time: Friday, March 5, 2010, 10:32 am

Hey Jon,
Ok, I looked at your dummy chart and I’m actually only trying to sketch the competitor prices along the correct month on the x axis. The difficulty is that I have several projects corresponding to the same month (for some months, not all). That’s why I wrote out two “Feb” months in my initial msg. And that is what messes up the X axis, it doesn’t portray the months simply from january through december.

The project name column, I realize was confusing, is only there for the macro to portray the name (instead of the value) on the price point.

Ahh, charts… I hope you can understand my problem & have a nifty solution.
Thanks a lot in advance.
Philip

Comment from Philip
Time: Sunday, March 7, 2010, 4:33 pm

Good evening Jon,
Well I did what you suggested, but to be honest that just really seems to complicate the problem. The legends became a real problem as there are as many legend icons as there are points, as opposed to only 1 per competitor.
The x axis was the only problem with my initial data setup, and even in this way it doesn’t seem to want to stay formated in “mm yy”, it somehow remains in “dd.mm.yyyy” format, which doesn’t make much sense.
Is there really no other way to get the x axis to just comport itself normally using my initial format (without the pivot) ?
Additionally, as I mentioned, I am applying a macro to the chart and it is impossible for me to adapt it to the pivot table.
I didn’t think this chart would be this difficult, it sounds really basic.
Please let me know what you think.
Tons of thanks,
Philip

Comment from Philip
Time: Monday, March 8, 2010, 11:49 am

Hey Jon,
I got it !! I finally managed to get the chart looking the way I wanted it to…and as you put it so well, it’s all about data placement.
I have a question now, is it possible to circle the 2 or 3 price points representing the offering price from various competitors for a given project ?
The point is that it gets a bit messy with lots of data and labels all over the place.
I got it pretty clean already, but it would be very nice to be able to only show 1 label beneath a circle (circling the single, 2 or 3 price points for every project).
What do you think ?
Tons of thanks in advance.
Philip

Comment from Philip
Time: Monday, March 8, 2010, 12:49 pm

Thanks a lot Jon,
Sounds good. I looked at the tutorial and it seems as though it is only explaining how to circle single points, whereas I would like to circle a set of points to indicate that they belong to the same project, not to indicate that they are min or max values etc…
Does this sound feasible to you ?
Thanks
Philip

Comment from Philip
Time: Monday, March 8, 2010, 3:00 pm

Jon,
Thanks for the explanation,
But just to make sure we understand eachother.
Can I put 3 points or more into 1 single circle ?
Thanks again (sorry)
Philip

Comment from Philip
Time: Tuesday, March 9, 2010, 3:41 am

Hi Jon,
I like to hear that it’s feasible. But what do you mean by X Y coordinates ? Also, can I get a thin oval marker instead of a round one ?
To set the context, my Y axis is price, and my X axis represents months. I am plotting price points which correspond to the offering price of different competitors (3 max = 3 points). And since I can have more than one project per month, the set of points corresponding to a given project are plotted vertically to the left of the previous set of points and therefore can be quite close to one another. That is why I cannot use a round marker otherwise it will englobe the set of points aligned next to the ones I am actually trying to circle.
So if I have a set of points, 5,6,7 for proj A and 6,7,8 for proj B, both of which are in January, how can I circle 5,6,7 together, and 6,7,8 together by using oval circles for each of them ?
Thanks a ton for your feedback.
Philip

Comment from Philip
Time: Tuesday, March 9, 2010, 9:46 am

Hi Jon,
In fact I created an invisible secondary X axis so there is room to align 3 projects from left to right, knowing that each project may have more than one offering price.
I will try to represent what I see on the graph with 3 projects (A, B, C).
ABC
469
358
247
The question is, is it possible to circle price points from project A (2,3,4) and give it a single label (project A) without touching the other price points from projects B & C.
The circle would need to be an oval. I hope this is a bit more clear.
Thanks a mill,
Philip

Comment from Philip
Time: Wednesday, March 10, 2010, 6:10 am

Hey Jon,
OK, I got it good enough. I gave up on the circling several points together in a single circle part. My graph is looking mighty nice right now… thanks a lot for your assistance.
Have a great day !
Philip from Zurich

Comment from chrisham
Time: Tuesday, March 23, 2010, 11:09 pm

Jon,
Thanks for you many tutorials, recently I have been trying to get around your tutorial on the – Broken Y Axis- http://peltiertech.com/Excel/Charts/BrokenYAxis.html.
Being a not so bright bulb, I am struggling to make sense of the many steps involved here.
Since you have some great Utilities for sale, I was wondering if you could add this Utility on your website for this, since there are many instances when data has to be represented in this fashion, with one value dwarfing the others? Just a suggestion.

Chrisham

Comment from miriam
Time: Wednesday, March 24, 2010, 1:36 am

hi – i am trying to plot data in a pyramid with stacked option but i can’t figure out how to do it.
the plot should look as follows:
to the right of the y-axis there should be a pyramid plot for multiple rows (horizontal bars). Each bar should
have the option to be a stacked output. to the left of the y axis I would like to have the same information
being displayed except that this would correspond to another group.

for instance, the two groups (right and left of the vertical axis) corresponds to female and male respectively.
within each of these groups there are 10 categories (horizontal bars). Within each category there are five
grades.

i look forward to your feedback.

Comment from Michelle
Time: Thursday, March 25, 2010, 2:19 pm

Hello,

I’m hoping you can help me out and that I haven’t overlooked a similar post. I’m looking for a way to create a bar graph (or something similar) that includes the individual points that make up the value for the bar. In other words, I have 6 classes along the x-axis, each with 7 values; the average of these 7 values is the bar value. In order to show variability among classes, I want to be able to plot those 7 values as dots in a straight line on top of each bar. Is there a way to do this?

Thank you in advance.

Comment from Miriam
Time: Thursday, March 25, 2010, 5:32 pm

Hi Jon – your idea of using a 2D stack chart works however, there is still something that it does not get incorporated using this approach.

That is, I want to plot a stack 2D plot where the information to the right of the y-axis pertains to one group and I also would like to do this stack plot for a second group.

Ideally, it would be great to have a stack graph where the values to the right of the y-axis pertains to group 1 and the stack graph for the second group is to the left of the y axis.

This is just like the type of graphs that I have seen done using the pyramid graphs for age where the numbers to the right pertain to say males and those numbers for females are illustrated to the left. However in this type of pyramid plots the stack option has not been explored.

I can’t seem to find anywhere where this is done.

Here I’m sending a link of a plot that describes part of what I need with the only exception that stack bars are now included in the horizontal bars.

http://bm2.genes.nig.ac.jp/RGM2/R_current/library/plotrix/man/images/big_pyramid.plot_001.png

I look forward to our feedback.

thanks

miriam

Comment from Walker
Time: Thursday, April 8, 2010, 10:20 pm

Hi Jon,

Using VBA, I am trying to insert text into a rectangle that resides in an existing template chart.

I originally saved the template chart with text that was associated with a range in the spreadsheet.

When I change the series, categories, and Chart Title, using VBA, these parametres are shown as changed on the template chart.

However, the text that is contained in the rectangle is not changed, and remains the same as the original Template Chart that was saved.

Any help with VBA code that will solve this issue wil be greatly appreciated.

Take care,

Walker

Comment from Walker Smith
Time: Friday, April 9, 2010, 9:08 am

Jon,

Thanks so much for your prompt answer.

Please find below, a snippet of my code below that should answer some of your questions:

++++++++++++++++++++++++++++++++++++++
Option Explicit

Dim Text2 As Range ‘Text2 is given its value in a procedure that calls the
‘procedure below
———————————————————————–
Sub InsertRectangleWithText()

Dim Shp As Shape

Set Shp = ActiveChart.Shapes(msoShapeRectangle) ‘Rectangle that exists on
‘template chart
Shp.Select ‘Brings Rectangle into focus

With Selection

Shp.TextFrame2.TextRange.Select ‘Brings Text area of rectangle into focus

With Selection

Shp.TextFrame2.TextRange.Text = _
“The number of Widgets for” & _
” this period is ” & Text2 ‘ Although the statements above
‘bring the text area into focus, the
‘text in this statement is not inserted
‘into the rectangle

End With

End With

End Sub
++++++++++++++++++++++++++++++++++

I hope this brings clarity to my problem. Obviously, I am doing something wrong.

Thanks and take care,

Walker

Comment from Walker
Time: Friday, April 9, 2010, 4:24 pm

Jon,

Here is some additional information that is associated with my last post.

1) All charts are on a sheet, and are not embedded on a sheet.

2) The procedure in my last post is apparently creating an unformatted rectangle of the same size with the text as indicated by the VBA code, but it is behind the rectangle that was saved as part of the chart template.

To get the text into the saved rectangle that has been formatted, I send the formatted rectangle back, then copy and paste the text from the unformatted one. I then send the unformatted rectangle back, and paste the text into the formatted rectangle.

Next, I have to give the text a color, and format the text.

I would not have thought that the code in my last post would have caused this problem.

Unfortunately, I have no clue on how to replicate what I did manually using VBA code.

There must be an easier way.

TIA for any help.

Take care,

Walker

Comment from Walker
Time: Friday, April 16, 2010, 10:38 am

Jon,

A brief update on the code problem discussed above.

I exported the code to a new module and it works fine.

I do not know why it did not execute in the prior module. Very strange.

I do apologize for taking up your time.

Thanks again.

Take care,

Walker

Comment from Miguel
Time: Saturday, April 24, 2010, 3:10 am

Hi Jon,

It is really agreat site, I have taken many ideas from it. Thank You!

I am trying to reproduce the excel 2003 chart feature where your could move the points in the charts with the mouse, in 2007 it is not possible. To define trends (non regression) or digitize plots was very useful.

I am trying to paint an object in the graph and use its coordinates to calculate the chart values and then use them in the equations but I do not know if this would be the best approach. I am fairly new to VBA and don`t know the most appropate way of writing this sub

Any suggestion?

Thank You in advance

Miguel

Comment from Philip
Time: Friday, June 4, 2010, 11:27 am

Hi Jon,
I have a question regarding your module on Advanced Gantt Charts, I hope you can help me out.

I would like to add a variant type of the cursor that is on the end of each task bar.
The cursor I would like to add would represent the “Actual End Date” as opposed to the “Planned End Date” cursor that is currently in place at the end of each task bar.

This “Actual End Date” cursor would either be invisible or along the same bar but placed at a different date (i.e. : before or after the “Planned End Date”).
It would only be visible if the “Actual End Date” is different than the “Planned End Date” cursor.

I Hope this is clear.
Tons of thanks in advance,
Philip

Comment from Philip
Time: Friday, June 4, 2010, 3:45 pm

Jon,
Yes that is what I am trying to achieve, the problem is that the milestone marker always appears at the same exact spot as the “Finished” and “Unfinished” markers.

Could you please give me an example of how you would set up the data and formulas knowing that I set up my model in the same way you set up yours ?

Many thanks in advance.
Philip

Comment from Philip
Time: Saturday, June 5, 2010, 1:55 am

Hi Jon,
Just to clarify what I was explaining in my previous message, I set up my Advanced Gantt Chart in the exact same way you set up yours in your guide.

In order to add the additional milestone marker that would represent the “Actual End Date”, the only way I know of would be to add a series whose values would by calculated by a formula “=row()-1.5″. But I don’t know how to make this marker appear at a different date along the bar, it always appears on the same exact spot. Could you please try it out on your model and tell me what formula I am supposed to utilize ?

Looking forward to read you soon.
Enjoy your weekend.
Philip

Comment from Philip
Time: Monday, June 7, 2010, 8:13 am

Hi Jon,

I honestly can’t seem to figure out how to get a marker to appear at a different date than the blue “finished” marker as per your example.

I added a stacked bar series representing the difference between actual & planned dates which appears when the difference between the 2 dates is greater than or equal to 1. I was hoping I could get a marker to appear at the end of this bar, but it isn’t happening…

Everytime I try adding a marker representing the actual finished date it appears at the exact same spot as the planned marker (your “finished” or “unfinished” markers).

Can you perhaps think of why ?
On a side note, You should actually think of adding this detail to your guide as only very seldom are planned dates ever respected…

Hoping to hear from you soon.
Tons of thanks in advance.
Philip

Comment from Anonymous
Time: Tuesday, June 8, 2010, 10:39 am

Jon,
I’m really sorry to bug you about this… I realize how absurd these querries must sound to you. But I have tried what you suggested already 2 comments ago over & over.

The thing is, the milestone marker only appears with a value between 0.5 and 4.5 (if I only have 5 tasks), so it won’t appear on the chart if I point to the column with dates.

I’m trying to understand how this marker mysteriously only attaches itself to the end of “Done” or “Not Done” series. How can I get a third marker (not the “Finished” nor “Unfinished” markers) to attach itself to an extra stacked bar ?

As I mentioned, I already added this extra series in the chart in the form of a stacked bar chart representing the difference between “actual end dates” and “planned dates”. But I can’t get the third marker (with values 0.5 to 4.5) to attach itself to this extra series I called “overtime”.
Maybe you could try adding a series to your example so you can understand my roadblock.
Hoping I was able to make myself understood.
Tons of thanks in advance.
Philip

Comment from panda
Time: Monday, July 12, 2010, 5:22 pm

Jon,

I hope I haven’t overlooked this question in the above blogs, but I cannot, for the life of me, figure out how to format the x-axis for dates using a scatter graph. In the old days, I would enter the minimum date (example January 1, 1998) and maximum date (example January 1, 2015) and the axes would just calculate out the value. Now the minimum is zero and the maximum is 50000, which crams my data in the middle of this “graph”. Do you know how to fix this? I have a feeling it’s an easy fix that I am overlooking! – Thanks!

Comment from panda
Time: Tuesday, July 13, 2010, 9:23 am

We haven’t upgraded to 2010, just installed 2007 a few months ago. Sorry for overlooking the office 2007 part in my original request! Seriously, overlooked date and time for XY graphs!

Thanks for the tip.

Comment from Connor
Time: Wednesday, July 14, 2010, 11:41 am

I am using excel 2003 and have a problem with the concept I’m trying to create.
I have a smoothed line chart and added a trendline to it. I was wondering if there was any way I could fill in the area between the trendline and the line chart. Is it possible in excel?

Comment from Casey
Time: Thursday, July 22, 2010, 10:33 pm

Jon,

Let me start by saying “VERY” Awesome Site. I was wondering if you could help me with a an excel chart? A type of line chart or strip chart for showing values changing every 5 minutes over an 10 hour period.

I have DDE data coming from an OPC server in which I want to take the “LIVE” Realtime reading that I have displaying and updating every 5 minutes in Excel.

I would like to record the following item based on a time stamp that comes from the server.

Formulas I use to obtain real-time data into excel fields:
In Excel this is my time stamp from the Server…. =kepdde|_ddedata!_System._Time
this is the number I want to plot… =kepdde|_ddedata!PACKAGING.LINE5.UlmaWastedCycles

Is there a somewhat simple way to have each value as it changes, be recorded to cell after cell or just simply have it plotted on the chart?

Thanks very much for any suggestions you may have.
Casey Swett

Comment from HOO
Time: Thursday, July 22, 2010, 11:16 pm

Dear Jon,

I found a strange behavior in Excel 2007. I have 10 charts embedded in one sheet. If I select 2 charts & run Selection.Count in macro, the result is 10!

Any idea if I just want to loop through SELECTED charts or shapes only to perform some tasks?

Comment from Alan
Time: Thursday, July 29, 2010, 4:05 am

Jon,

A great website and a great resource: thanks for sharing your knowledge and I look forward to many hours perusing the site. I have a question re Excel that I’m hoping you could help me with:

I’m trying to create a basic 3-D graphic of a building showing a basic 3-D block diagram that will show the basic dimensions (height, length, width) and the setback around it. I would like the building shape to change as the user inputs the different dimension variables into specific cells on the spreadsheet. Is this functionality available within Excel (via VBA or whatever) or are there plug-ins that might accomplish this? Any info you might have that would point me in the right direction would be appreciated!

Thanks!

Comment from Udayan
Time: Thursday, July 29, 2010, 1:00 pm

Can you please let me know how a chart such as that at http://krugman.blogs.nytimes.com/2010/07/28/japanese-debt-and-growth/ can be created? Assume you have multiple observations on three variables. The task is to create a scatter diagram from two of the variables, using the remaining variable to label the scatter points.
Please help.

Comment from Udayan
Time: Thursday, July 29, 2010, 1:24 pm

Oops! I just found a solution to my own question! See http://support.microsoft.com/kb/213750 for a solution to Udayan’s question.

Comment from Peter
Time: Sunday, August 8, 2010, 5:18 pm

Jon
This may have already been covered; if so a pointer to where would be appreciated. I need to have a bar chart which, if showing a negative value has a fill colour different to that when the bar has a positive value eg red vs green.
Thanks

Comment from Lynn
Time: Monday, August 23, 2010, 5:30 pm

I was told you are the best excel charter in the universe! Maybe you can help me. It’s been 30 years since I took statistics. What I am trying to do is compare a group of hourly rates (a specific grade with 10 employees) to the recommended min, mid and max. 4 of the employees fall out of the range and the other 6 are in the range. I prepared a chart using the min, mid and max of the range as straight lines and the employee rate as variables points but now my boss said try it with a bell curve using quartiles. I understand quartiles but have no idea how to chart what he is asking. Can you point me in the right direction?

Thanks!

Comment from Lynn
Time: Tuesday, August 24, 2010, 6:30 pm

Thank you – I will go read your post now. I really appreciate you pointing me somewhere, thanks so much for sharing your knowledge!