How to Create a Survival Curve in Excel

Mood's Median Test in Excel tutorial

A survival curve is a chart that shows the proportion of a population that is still alive after a given age, or at a given time after contracting some type of disease.

This tutorial shows how to create a survival curve in Excel.

Creating a Survival Curve in Excel

Suppose we have the following dataset that shows how long a patient was in a medical trial (column A) and whether or not the patient was still alive at the end of the trial (column B).

Survival curve data in Excel

In order to create a survival curve for this data, we need to first get the data in the correct format, then use the built-in Excel charts to create the curve.

Formatting the Data

Use the following steps to get the data in the correct format.

Step 1: List all of the unique “Years in trial” values in column A in column D:

Note: Always include “0” as the first value.

Survival curve example in Excel


Step 2: Create the values in columns E through H using the formulas shown below.

Note: we removed the value “18” in column D since there were no death values associated with that time.

Kaplan Meier in Excel

Here are the formulas used in the following cells:

E3: =COUNTIFS($A$2:$A$16,D3,$B$2:$B$16,1)
F2: =COUNTIF($A$2:$A$16, “>”&D2-1)
G3: =1-(E3/F3)
H2: =1
H3: =H2*G3

In order to fill in all of the other values in column E, simply highlight the range E4:E13 and press Ctrl-D. Fill in all of the other values in columns F through H using the same trick.

Now we’re ready to create the survival curve.

Creating the Survival Curve

Use the following steps to create the survival curve.

Step 1: Copy the values in columns D and H into the columns J and K.

Excel survival curve tutorial

Step 2: Copy the values in the range J3:J13 to J14:J24. Then copy the values in the range K2:K12 to K14:K24.

Excel tutorial

Step 3: Create a list of values in column L as shown below, then sort from smallest to largest values in column L:

Create list of values in column L
Sort columns J through L smallest to largest based on column L

Step 4: Highlight cells J2:K24, then select “Insert” > “Charts|Scatter” > “Scatter with Straight Lines and Markers” option.

Feel free to modify the title, axes names, and chart colors. The resulting chart will look something like this:

Survival curve end result in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *