Table of Contents
A survival curve is a graphical representation that shows the probability of an event or outcome occurring over a specific period of time. In order to create a survival curve in Excel, you will need to have data on the time of occurrence of the event and the number of participants or subjects at risk. Once you have this data, you can use the built-in functions and tools in Excel to plot a survival curve. This involves using the “Insert” tab to create a scatter plot, adding a trendline, and adjusting the settings to display the survival curve. It is important to properly label and format the graph to clearly communicate the data. Overall, creating a survival curve in Excel requires a basic understanding of the software’s tools and functions, as well as knowledge of the data and its significance.
Create a Survival Curve in Excel
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).
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.
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.
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.
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.
Step 3: Create a list of values in column L as shown below, then sort from smallest to largest values in 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:
