How to Create a Bland-Altman Plot in Excel?

Bland-Altman plot is not present by default in excel, but it can be easily built with the help of excel. There is software that provides in-built Bland-Altman plots like MedCalc, Analyse-it, etc. In this article, we will learn how to create a Bland-Altman plot in excel. A Bland-Altman plot is used to give agreement between two measured quantities or equipment. This plot is generally used in the medical industry to test the agreement and correlation values between two equipment or units. This plot was introduced in medicine statistics by J. Martin Bland and Douglas G. Altman. Bland and Altman said that in medical statistics, if two types of equipment are highly correlated, then it might not imply good agreement, as there could be widespread sample collection which leads to good correlation. To solve this issue, Bland and Altman introduced this plot, which gives you a qualitative analysis of whether the equipment or units used are highly correlated or not. The plot does not give a probability of error on the variables. 

Parts of a Bland-Altman Plot 

1. The basic construction of a plot is that it contains two variables of which graph is to be plotted. The X-axis contains the mean of the two variables, and the Y-axis contains the difference between the variables. The X and Y values obtained are plotted.
2. An additional factor name CI(confidence interval) is also taken into consideration. The values are plotted for a 95% confidence interval.
3. A bias line is also created. Bias is the mean of the difference of variables. The line is parallel to the X-axis.
4. Line of Agreement(loA) is also created. Two loA are created in the Bland-Altman plot, parallel to the X-axis, which specifies the region of 95% confidence interval. The formula-driven to calculate it is +1.96S.D. and -1.96S.D., where S.D. is the standard deviation of the difference of the variables. 

Creating a Bland-Altman Plot

Bland-Altman plot is created with the help of excel, scatter plot. For example, “Arushi” is a researcher at a university who has developed a new vaccine for the coronavirus. She tested her vaccine and has the results. Now, she asked “Gautam”, who is a data analyst, to make a Bland-Altman plot to check the agreement and correlation with the authorized vaccine “Covishield”. 

Following are the steps: 

Step 1: Make two new columns, Difference and Mean, in the excel sheet. Cell E2 is the Difference, and cell F2 is the Mean. 

Step 2: Difference Stores the differences between the two variables. For example, B3-C3 gives the difference in their values.

Step 3: You can observe -0.2 appears in cell E3. 

Step 4: Currently, the active cell is E3. Keep your cursor at the right-most bottom of the active cell. Mouse click and drag the cursor downward. The same formula gets copied in all the cells. 

Step 5: You can observe the difference of variables stored in range E3:E22. 

Step 6:  Mean stores the mean of the two variables. For example, =average(B3, C3) gives the mean of the two values. 

Step 7: You can observe that 12.4 appears in cell F3. 

Step 8: Currently, the active cell is F3. Keep your cursor at the right-most bottom of the active cell. Mouse click and drag the cursor downward. The same formula gets copied in all the cells. 

Step 9: You can observe the mean of the variables stored in range F3:F22.

Step 10: Now, we have to find the Bias, standard deviation, Upper LoA, and Lower LoA of the difference column. Make 4 columns stating them in cells H2, H3, H4, and H5. 

Step 11: In cell I2, find the average of the Difference column, using the =average(E3:E22) function. 

Step 12: In cell I3, find the standard deviation of the Difference column, using the =STDEV(E3:E22) function. 

Step 13: In cell I4, find the lower line of agreement (LoA) of the Difference column, using the 1.96*I3. 

Step 14: In cell I5, find the upper line of agreement (LoA) of the Difference column, using the -1.96*I3. 

Step 15: The statistical values table of Differences is ready. 

Step 16: Keep your active cell out of the tables. Go to the Insert Tab, under the charts section, and click on the scatter chart. 

Step 17: An empty scatter chart is created. 

Step 18: Go to the Chart Design tab, under the Data section, and click on Select Data. 

Step 19: Data Source dialogue box appears on the screen. Click on the Add button. 

Step 20: Edit Series dialogue box appears on the screen. Add the Series name, Bland-Altman Plot. 

Step 21: Select the series X-values i.e., F3: F22. 

Step 22: Select the series Y-values i.e. E3:E22. Click Ok. 

Step 23: Data Source dialogue box reappears. Click Ok. 

Step 24: A scatter plot is created. 

Step 25: Now, you need to delete the grid lines from the chart. Click on any of the y grid lines. Press Delete. 

Step 26: Click on any of the x grid lines. Press Delete. 

Step 27: The grid lines are deleted from the chart. You can observe that the X-axis lies in the mid of the Y-axis. We need to push our X-axis downward i.e., at the bottom. Observe that the minimum value of y is -1.5. Double click on the Y data labels. 

Step 28: Format Axis dialogue box appears. Under the Axis Value, type -1.5.

Step 29:  Now, your axis has moved to the bottom. 

Step 30: Our next task is to create 3 lines i.e., Bias, Lower LoA, and Upper LoA. To achieve it, we have created a new table with column headings X-Axis, Upper LoA, Lower LoA, and Bias. The X-axis contains the lowest and the highest value in the chart, i.e., 0 and 25. Upper LoA value is provided in cell I4, Lower LoA value is provided in cell I5, and Bias is provided in cell I2. Copy values from there and paste them into the current table as shown below. 

Step 31: Go to the Chart Design tab, under the Data section, and click on Select Data. 

Step 32: Data Source dialogue box appears on the screen. Click on the Add button. 

Step 33: Edit Series dialogue box appears on the screen. Add the Series name, Upper LoA. 

Step 34: Select the series X-values i.e. K3:K4.

Step 35: Select the series Y-values i.e. L3:L4. Click Ok. 

Step 36: Data Source dialogue box reappears. Click Ok. 

Step 37: You will observe two data points of different colors are created. 

Step 38: Go to the Chart Design tab, under the Data section, and click on Select Data. 

Step 39: Data Source dialogue box appears on the screen. Click on the Add button. 

Step 40: Edit Series dialogue box appears on the screen. Add the Series name, Lower LoA.

Step 41: Select the series X-values i.e., K3:K4. 

Step 42: Select the series Y-values i.e., M3:M4. Click Ok. 

Step 43: Data Source dialogue box reappears. Click Ok.

Step 44: You will observe two data points of different colors are created.

Step 45: Go to the Chart Design tab, under the Data section, and click on Select Data.

Step 46: Data Source dialogue box appears on the screen. Click on the Add button.

Step 47: Edit Series dialogue box appears on the screen. Add the Series name, Bias. 

Step 48: Select the series X-values, i.e. K3:K4.

Step 49: Select the series Y-values, i.e., N3:N4. Click Ok.

Step 50: Data Source dialogue box reappears. Click Ok.

Step 51: You will observe two data points of different colors are created.

Step 52: Right-click on the upper LoA points as shown in the image. Click on the Format Data Series. 

Step 53: Format Data Series dialogue box appears on the right-most side of the screen. Under Series Options, select Solid line. Select the Dash type you want to make it into the chart. 

Step 54: A dashed line is created for the upper LoA points. 

Step 55: Right-click on the lower LoA points as shown in the image. Click on the Format Data Series.

Step 56: Format Data Series dialogue box appears on the right-most side of the screen. Under Series Options, select Solid line. Select the Dash type you want to make it into the chart. 

Step 57: A dashed line is created for the lower LoA points. 

Step 58: Right-click on the bias points as shown in the image. Click on the Format Data Series.

Step 59: Format Data Series dialogue box appears on the right-most side of the screen. Under Series Options, select Solid line. 

Step 60: A solid line is created for the bias points. 

Step 61: Now, our task is to add the chart title and axis title. Click on the plus button. Check the box Axis Titles and Chart Title. Add the chart title as “Bland-Altman Plot”, and axis titles as “The median of two values” and “The difference between the two values”. 

Step 62: A Bland-Altman plot is created. By observing the chart’s value, we can see that vaccine works very well, and “Arushi” has done really good work.