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.