Microsoft Excel - Forest Plots (Odds Ratios and Confidence Intervals)
Hi everyone my name is. Erica and today I'm going to show you how to create the fourth plot that plots odds ratios and confidence intervals by using Microsoft Excel 2007 and this method of creating plots was done by John healthier and a thank you to him for sharing method online and for my tutorial I'll be using data from a study that looked at the risk factors for prolonged opioid use after major surgery and I'll be plotting specifically the patient characteristics so Ardea is going to be split up into two different sections so the first section is where we're going to keep our results and the second section is where we're going to use error bars to create our confidence intervals. We're going to start with the first section here and you can see that we have our factors are lower and upper confidence intervals as well as our odds ratios and these are just taken straight from the study in this set of business column. We're going to call it factors and we're just pretty much going to copy and paste what we had in the first column but we're going to make sure to leave an empty cell between the title as well as our factors factor in labels in our last column. I'm going to call it the Cal Cal column and in this section is going to account the total number of factors that we have and count it down so what I'm going to do is copy and paste this equation into the cell and the cells that you use in the equation may differ from what's in this equation depending on where you put your data in the Excel workbook as well as depending on the number of factors that you have so I copied and pasted that that and press. Enter we have a total of 8 factors and I'm just going to hide and expand and it counted down so we're done with the first section. Let's go ahead to the second section you can see that we have our factors listed in the next column. I'm going to call it the lower column and this is where we're going to calculate just the difference between the odds ratios as well as the lower limit so double click equals C 4 minus B 4.
I'm sure I'm going to be highlight. That and expand and it calculates this for the rest of the factor the next column. I'm going to call it the upper column and we're this time we're going to take the upper confidence interval - the odds ratios. I'm going to fit equals c4 - z4. Enter and extend so our data is all set up and ready to go. We can now go ahead and make our 4 spot. I'm going to highlight these factors here and I'm going to do insert and I'm going to select a tab bar chart and looking at our chart you can see that. None of our factors are listed along the y axis so to fix that. We're under the design tab we're just going to hit switch row and column and you can now see that at least one of the factors is listed along the y axis - so we need to add the rest of the label so what. I'm going to do is select the y axis right. Click select data and we're going to edit series number one for the series name we're going to select factors and for the series values. We're going to use our countdown column and just make sure to you. Select the empty cell. That's above and below the values. We're also going to adjust the horizontal axis labels. So let's go ahead and hit edit and then we're going to select the empty cell that's above and below our labels here and now you can see that we have all our labels listed but it's in the wrong order from what we have so let's right. Click format access and hit categories in reverse order so now you can see that as a woman factors through at the top now so now we can go ahead and import our odds ratios. Let's go ahead and right. Click select data and what we're going to do is add a sort of placeholder for the odds ratios because pretty much anything that we add into the chart. It's going to add it as a bar chart which we don't want so let's hit add series name we're going to call it off and then hit. OK and we want to change these red bars into a scatter plot so select that change chart type and hit scatter okay.
Let's go ahead. And add in our author our other odds ratios values right. Click select data for the odds. We're going to go ahead and edit that our series values will be our odds values for our series. Y values we're going to select the count down home again but this time leave out the empty cells above and below the Calvin box values and hit. Okay so now our odds ratios are plotted into the chart. Now looking a lot better before we go ahead and add in our lower and upper confidence intervals. Let's clean up our chart a bit so this secondary vertical axis we won't need so select and delete this like this legend. I'm going to get rid of delete and you can see that our labels are not perfectly aligned with its specific odds ratios so we're going to select back our y-axis right click format axis and we're going to position the axis on the tick marks so that fix is not there for x-axis the if it's up at the top here we don't want it at the top we want it at the bottom we're going to select our y-axis again right. Click format axis and we're going to put our horizontal axis at the maximum category close and you can now see that it drops it up down to the bottom these these blue bars we don't need for our chart so I'm going to select it. Make sure that they're all selected right. Click format data series for the fill. We're going to do no fill for the border color. We're going to do no line and you can now see that the bars disappear for our x-axis we don't need the values to go from 0 to 10. We don't need it that wide. Let's go ahead and adjust that. I'm going to right. Click the x-axis format axis for a minimal fixed value. I'm going to put it as 0.5 for our maximum fix value. I'm going to put it as 2.5. And our major unit I'm going to put up point five so it's looking a lot better now. We're ready to go ahead and add in our error bars we're going to click the odds ratio values. Go into the. Layout tab hit error bars and more error bars options and you can see here that Excel automatically adds in the horizontal axis bars as well as the vertical error bars and we only need the horizontal error bars.
So we're going to select the vertical error. Bars hit delete. And that's get back gets rid of those. We're going to go ahead and adjust our horizontal error. Error bars. Make sure that they're selected right. Click format error bars. We want the bars to go in both directions. We want it to there to be a cap at the end. And for the error amount we're going to customize it and specify its value the positive error values are going to be the upper values that we calculated earlier and the negative error crops values will be the lower values we calculated and hit okay so we have our odds ratios as well as confidence. Intervals plotted now we can make some further adjustments for example these odds ratios we have them s circles before so we're going to select that to format data series marker options. We're going to hit the circle for the marker fill. Let's do a solo fill. You're going to leave that black and for the marker line color let's leave s go away and to add a bar at the top we're going to click inside our charts here. I'm going to right. Click format plot area. And then we're going to add a border to the whole chart so border color. We want it as a solid line. And let's make the width at one point. So let's go ahead and also add a title to our x-axis we're going to select that axis title and let's call it alterations with the discussion. Okay everyone so that's pretty much how to make a fourth spot in. Microsoft Excel if you feel free to leave any questions or comments on this page here and I hope you found this video useful and thank you so much for watching.
I'm sure I'm going to be highlight. That and expand and it calculates this for the rest of the factor the next column. I'm going to call it the upper column and we're this time we're going to take the upper confidence interval - the odds ratios. I'm going to fit equals c4 - z4. Enter and extend so our data is all set up and ready to go. We can now go ahead and make our 4 spot. I'm going to highlight these factors here and I'm going to do insert and I'm going to select a tab bar chart and looking at our chart you can see that. None of our factors are listed along the y axis so to fix that. We're under the design tab we're just going to hit switch row and column and you can now see that at least one of the factors is listed along the y axis - so we need to add the rest of the label so what. I'm going to do is select the y axis right. Click select data and we're going to edit series number one for the series name we're going to select factors and for the series values. We're going to use our countdown column and just make sure to you. Select the empty cell. That's above and below the values. We're also going to adjust the horizontal axis labels. So let's go ahead and hit edit and then we're going to select the empty cell that's above and below our labels here and now you can see that we have all our labels listed but it's in the wrong order from what we have so let's right. Click format access and hit categories in reverse order so now you can see that as a woman factors through at the top now so now we can go ahead and import our odds ratios. Let's go ahead and right. Click select data and what we're going to do is add a sort of placeholder for the odds ratios because pretty much anything that we add into the chart. It's going to add it as a bar chart which we don't want so let's hit add series name we're going to call it off and then hit. OK and we want to change these red bars into a scatter plot so select that change chart type and hit scatter okay.
Let's go ahead. And add in our author our other odds ratios values right. Click select data for the odds. We're going to go ahead and edit that our series values will be our odds values for our series. Y values we're going to select the count down home again but this time leave out the empty cells above and below the Calvin box values and hit. Okay so now our odds ratios are plotted into the chart. Now looking a lot better before we go ahead and add in our lower and upper confidence intervals. Let's clean up our chart a bit so this secondary vertical axis we won't need so select and delete this like this legend. I'm going to get rid of delete and you can see that our labels are not perfectly aligned with its specific odds ratios so we're going to select back our y-axis right click format axis and we're going to position the axis on the tick marks so that fix is not there for x-axis the if it's up at the top here we don't want it at the top we want it at the bottom we're going to select our y-axis again right. Click format axis and we're going to put our horizontal axis at the maximum category close and you can now see that it drops it up down to the bottom these these blue bars we don't need for our chart so I'm going to select it. Make sure that they're all selected right. Click format data series for the fill. We're going to do no fill for the border color. We're going to do no line and you can now see that the bars disappear for our x-axis we don't need the values to go from 0 to 10. We don't need it that wide. Let's go ahead and adjust that. I'm going to right. Click the x-axis format axis for a minimal fixed value. I'm going to put it as 0.5 for our maximum fix value. I'm going to put it as 2.5. And our major unit I'm going to put up point five so it's looking a lot better now. We're ready to go ahead and add in our error bars we're going to click the odds ratio values. Go into the. Layout tab hit error bars and more error bars options and you can see here that Excel automatically adds in the horizontal axis bars as well as the vertical error bars and we only need the horizontal error bars.
So we're going to select the vertical error. Bars hit delete. And that's get back gets rid of those. We're going to go ahead and adjust our horizontal error. Error bars. Make sure that they're selected right. Click format error bars. We want the bars to go in both directions. We want it to there to be a cap at the end. And for the error amount we're going to customize it and specify its value the positive error values are going to be the upper values that we calculated earlier and the negative error crops values will be the lower values we calculated and hit okay so we have our odds ratios as well as confidence. Intervals plotted now we can make some further adjustments for example these odds ratios we have them s circles before so we're going to select that to format data series marker options. We're going to hit the circle for the marker fill. Let's do a solo fill. You're going to leave that black and for the marker line color let's leave s go away and to add a bar at the top we're going to click inside our charts here. I'm going to right. Click format plot area. And then we're going to add a border to the whole chart so border color. We want it as a solid line. And let's make the width at one point. So let's go ahead and also add a title to our x-axis we're going to select that axis title and let's call it alterations with the discussion. Okay everyone so that's pretty much how to make a fourth spot in. Microsoft Excel if you feel free to leave any questions or comments on this page here and I hope you found this video useful and thank you so much for watching.