Free Microsoft Excel 2013 Quick Reference

Kaplan-Meier Plot


I am trying to figure out how to do a Kaplan-Meier Plot on Microsoft Excel. It's a type of plot used to look at survival statistics. Is anybody familiar with this or know a place on the internet where it describes how to make them?

I have already searched the excel forums and the internet and have been unable to find anything. Any help at all would be greatly greatly appreciated.

Thanks so much,


Hi, I am making a sheet which allows the user to run various models based on whether a checkbox is ticked or not. If it is ticked then it sets another variable to true which then determines if certain modules are run or not.
I have a couple of questions. First, I need to set all the variables to true and put a tick in the relevant checkboxes when the sheet is opened. Secondly I cant get the checkbox to actually make a difference to what modules are run. I have tried various different ways:

     'check for a tick in the Kaplan Meier checkbox'
     'if there is one set the boolean variable to true, otherwise false
    If chbK_M_estimator.Value Then 
        runKaplan_Meier = True 
        runKaplan_Meier = False 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried changing the first line to 'If chbK_M_estimator.Value = True', 'If chbK_M_estimator.Value = 1', and 'If chbK_M_estimator.Value = 0'. However, regardless of what I do the module doesnt run.

Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat-life.

I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. So far I have the code to get most of the distribution functions. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this.

Hi, a few days ago I asked on here if anyone knew how to draw a kaplan-meier survival (step) chart in excel. I was very kindly given this link by a kind member which outlines exactly how to do it.

Now to my question, can anyone help me to construct this chart using VBA? I am finding it a lot tougher than a regular chart. I have included an example of the table (which is produced using other code from a reliability dataset) and an example of the chart I would like to produce from it.

Any help would be most gratefully received. Many thanks for your time in advance.

Hi all,

I'm new to data analysis and statistics in general, and was looking for advice on what sort of analysis I require (I'm currently working in excel/SPSS):

My population is made up of 200 cases (each representing a surgical operation). More than one operation may have been carried out on the same patient (a patient may have had between 1 and 10 operations).

I want to determine the effect of smoking as a risk factor for requiring a further operation. For each operation, I have data on the "time to next event", and whether that "event" was another operation or the study end-date.

I had planned to use Kaplan-Meier survival analysis, but was told recently that this wouldn't be correct (due to the fact that several of the operations may have been in a single patient, leading to confounding factors...).

Is there another type of analysis I can carry out, that will give me what I need? Or, can anyone point me to a more appropriate forum where I can post this query?

Thanks in advance,


Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat Pro. I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this.

Hi! I have a large matrix of data. A second sheet reorganizes this data, interpolated, and plots it. Some fields on the original sheet are blank, leading to a 0 on the 2nd sheet which screws up the interpolation and plots. How can I tell the interpolation and plot to ignore any values in the column that are not valid? Or, is there an easy way to remove the invalid links?

I am trying to write a macro that makes a plot for each cons dash in my spreadsheet and then adds a 6th order polynomial trendline and then cuts the equation from the trendline and pastes it to a cell, then the "X" are replaced with my X data and I generate my model to compare for my analysis. I have it where it works for one cons dash but I don't know how to write it where it will select the correct number of cells to plot because each plot is different. I wrote in formulas in excel to make blank cells but for some reason when recording my macro it does not notice them. I attached my data to see if anybody has another approach for it,due to the limit on the file size on this forum I just posted a piece of the data and my code is longer than the alotted size, so any help will be appreciated.
Thanks in advance

Any advice on how to create a plot chart.. i think the actual term is called a Probability Plot chart as in the attachments.
Many thanks Ashprobplotrchart.jpg

I have a VBA script that parses a bunch of data and creates a scatter plot chart for each subset/group of the data (22 of them). The charts are all created the same size and in the same worksheet. Once they are all created I find the maximum value and then loop thru each chart and set the y-axis to that maximum.

The problem is that every once in a while the data, lines, chart points, everything, is scrunched up above the plot area of one of the charts, and I can't do anything to the data (reformat, change max/min/auto/etc). It typically only happens to one of the 22 charts when it happens, but never the same chart. I know the code is working fine because it works for the other 21 charts.

The only thing I can do is recreate that specific chart when it happens. But that breaks the flow of the script as all the charts are then copied to powerpoint automatically.

Anyone seen anything like this?

Hello Everyone
I need a help for plotting a chart with VBA for each row depending on the selection of that row. In each row the x-axis values will be taken from same row but with different columns( i.e If I want to plot for a Row 5 data, I have x-axis values in column A, E,I and so on ,and Y-axis values will be in Row 6 but with different columns like B,F,M and so on). The problem is that for plotting the graph when I select the different data in columns for x-axis and y-axis it shows error. I am not selecting continous range may be this can be error ,How to handle this.please reply me. Below is the code for only one row,


Sub Macro26()
' Macro26 Macro
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).Name = "=""Carefull"""

'in the below line I am getting error it is not taking the data into account
ActiveChart.SeriesCollection(1).XValues = _

'in the below line I am getting error it is not taking the data into account

ActiveChart.SeriesCollection(1).Values = _
ActiveChart.SeriesCollection(2).Name = "=""Confident"""
ActiveChart.SeriesCollection(2).XValues = _
ActiveChart.SeriesCollection(2).Values = _
End Sub

please help me guys.I will be very thankful to you



I am trying to resize the plot area of my bar/line graph to have half plot area, half x axis (text). Excel will not let me do so without also automatically resizing the entire chart (axis included)! I try to drag the borders and/or corner handles but, again, it resizes the whole chart. Is the plot area too complex? Is there any way around this?

Thanks in advance for any help!


I’m writing a macro for Excel 2010 that automatically creates and formats scatterplots. Even in this most recent version of Excel, the choices for plot markers are a bit stingy, so I’d like to augment the options for markers by utilizing Excel shapes. I’ve included a file which contains a sample graph and a bit of code that demonstrates how I’ve gone about trying to do this.

    Chart2.Shapes.AddShape msoShapeMathMultiply, 0, 0, 15, 15 
    With Chart2.Shapes(Chart2.Shapes.Count) 
        .Fill.ForeColor.RGB = RGB(0, 112, 192) 
        .Line.ForeColor.RGB = RGB(0, 0, 0) 
        .Line.Weight = 2 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If you open up the attached file and run the macro, you’ll see that it draws an Excel shape (the multiply sign), formats it, and then replaces the markers for one of the data series with the newly drawn shape. However, if you zoom in (go to 400%) and compare the resolution of the shape itself vs. the new plot markers you’ll see that the markers are a little pixelated around the edges. While the difference may seem subtle, for my purposes this decline in resolution is not acceptable. I’ve tried utilizing different options for the .CopyPicure command (e.g., xlScreen vs. xlPrinter), but this doesn’t make any difference.

Is there another way to programmatically use Excel shapes as custom plot markers without losing resolution?

Hello Friends!

I am somewhat new to scientific charting) more accustomed to working in Excel, but I think I have run into its limitation as regards to charting…I am not sure if it is possibel to do the following:

1) Take the time series data in excel, each data series will have three describers (first three rows of each column) – COLOUR, TYPE and THICKNESS 2) plots each time series data in such a way as: a) colors the time series according to a time series criterion (eg. 1 for red, 2 for black) b) allows to adjust the thinness of the plotted series based on another criterion (e.g. 10 very thick, 2- close to hairline) c)Sets the type of the trendline to dashed/solid (based on the Type criterion)

I can send an example worksheet if necessary..

Thanks for your time!) I will be glad to hear any opinion.


I am trying to create a program that does similar things to another program. One thing I need to do is to plot digital data (binary 1s and 0s). Please see the attached samples. The word document shows a sample plot from the program I am trying to imitate. The spreadsheet has all the sample data used to come up with the plot in the word document. In the plot, when the lines are bold, the digital element is picked up (binary 1), and when they are thin, the digital element is not picked up (binary 0). Ideally, the plot that I create in Excel with VBA will look at least somewhat like the sample plot I have provided. Thanks in advance for your help!

I posted this on and got a response on how to do it manually. Does anybody have other options, in particular with VBA? I am new to VBA and on a deadline to get this done, otherwise I would try to dig deeper into it myself. One other thing: the digitals to be plotted will come from a listbox where the user picks which digitals to plot (there could be tons of them). I had gotten help on to plot voltage and current waveforms from a listbox, so I figured that my answer to plotting the binary numbers is somewhere in between the two posts. Please see the links to my posts for the sample documents. Thanks in advance for your help!

Hi all
I am trying to plot multiple layers where I color between min and max. I was able to plot the lines but
I wasn't able to separate the color block. Thank you.
-Winston Kyu

Hello Everyone,

I need a help from you guyz. Actually I want to plot a chart (XY scattering) .I have many rows In that I need to select a particular row. In that particular row I need to selact values from different columns both for x-axis and y-axis.Can anyone please tell me how to do that. Problem is I can't select different columns for plotting ,It shows error if I select data from different column...Please please help me in this..I will be very thankfull to you guys....



I am really new to using macros in excel. I have a file with 1000's of column and I need to plot as shown in the picture.
Here I have just shown few plots of how it looks. I believe writing a macro will definitely save me loads of time. Can anyone help me? ( x and y values varies a lot and I need to plot by switching 2 columns at once).

1 more question: Is there a way to eliminate E factor in the graph? ( 1.2 * 10^5 instead of 1.2E5 )

Thanks a lot,
SantoshFile.xlsxIV curve.jpg

I am often working with large populations (of 40,000+) but only want to plot a random sample of 2000 on an xy scatter plot. I can do this manually by using this formula =if(A1=x,1,A1+1) where x is the number you need to divide the total population by to get the desired sample size. I then filter on a value and copy & paste that selection onto a new sheet and use that sample for the plot.

I would like to tell excel, "here is my population. Plot 2000 of those onto a scatter chart."



I need to create a macro that would plot a simple xy-scatter graph for me. In the attached file the colored section is the data that needs to be plotted. The graph plotted is an example of what I need. The number of data varies from one file to another but not by much. All the files that I need to work on come in the exact same format as the one in the attachment, only the number of data under V and I may vary a little.

I am a newbie, so detailed answers will be great.

Thanks in advance!


I've got a problem of plotting the "scale"-like scale of a person's career. Every job position a person has had has a certain rank (for instance, junior manager is 0, CEO is 21 - the highest position).
So my task is to plot months in a certain rank against the rank, like this:

the problem is that I have a table with the fields like (Position);(Beginning of the job);(End of the job);(Position rank). And I do not know the way how to do it not manually (as I did in the file attached). And there are PLENTY of the people I have to make plots like this...(((
any ideas?

I would like to select the bottom 30 or 50 entries from a column to plot a chart of the data. This is a typical SPC type operation where data is typed into a worksheet and charts updated to show 6 sigma etc. Do I need to copy the selection somewhere else first or can I make the plot directly? Any info would be most welcome.


I am writing code that will take a large amount of data in columns and break them up by varous values. The program takes a chart that has already been created in the workbook and copies it over to a new workbook. The problem I am having is plotting the array in the chart. I want to make a new series of data and plot it. Am I going about this the wrong way? Is it even possible to do it this way? Below is the part of the code that attempts to do what I mentioned before.

ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.SeriesCollection(Counter).XValues = TimeArray() 
ActiveChart.SeriesCollection(Counter).Values = NArray() 
ActiveChart.SeriesCollection(Counter).Name = Name 
 'Timearray is an array of doubles
 'NArray() is an array of doubles
 'Name is a string
 'Counter is just a counter within a while loop that records the number of plots

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have the output of a logger I was to display in different graphs. The data is in the format:
ID, Length, Time, B0 - B7
Depending on the ID I then convert the raw data (B0-B7) into meaningful numbers.
At the minute just using a simple if statement in a new column: if id=x then calc y kind of thing.

All rows are time stamped, I've 7 different IDs which occur at approx 0.02 intervals and another 6 IDs occurring at approx 1.00 intervals.

I'm currently using a macro enabled spreadsheet created by someone else which will plot the data for a given ID by filtering for that single ID and plotting the resulting data. I would like to be able to plot a number of different IDs as different series (?) on a single graph and a number of separate graphs. I don't really understand how the initial spreadsheet was setup to generate the graphs and so far all I've been able to create is a mess - all data points as a single series.

I've linked a cut down example of the current spreadsheet and a newer one with additional IDs that I am working with.

A typical file is: 225k rows of data 93MB file I cut this down because of the size and it takes some crunching when switching to the graphs.

Is this something I can do/do reasonably easily in excel - if anyone can give me any pointers I would be grateful, been banging my head a bit with this one. I can use excel for simple tasks but this is a bit beyond my usual stuff. First spreadsheet with macros Similar but with additional IDs

I am having difficulty getting an area chart, plotted on the same chart as a stacked column chart, to display as I would like (see image).


I want the area charts (blue and yellow) to ignore the zero values at point 22 and start (in mid-air!) at point 23.
I have seen posts from Jon Peltier, Andy Pope and Tushar Mehta relating to this but have been unable to apply their knowledge (such as including a secondary axis formatted as dates, with the data to be plotted rescaled to this new axis) to my situation. I think I need an extreme degree of hand-holding here ie very detailed instructions!