FOREST PLOT IN EXCEL

Creating a forest plot is useful in visually presenting differences in effect sizes and confidence intervals across studies or across moderators within a study. Using Excel may be easier for some than a statistical package. One you have obtained your Effect Sizes and Confidence Intervals, use the following directions to plot your data visually. (Directions, sample and example provided by Heather Hatton, October, 2011)

Data Entry
  • Columns A, B, & C will contain the series number
  • Column D contains the study label
  • Column E contains the lower limit of the confidence interval
  • Column F contains the effect size
  • Column G contains the upper limit of the confidence interval
  1. Label all columns
  2. Cut & paste or enter data for columns D, E, F, and G
  3. Number the each row in column A that contains data sequentially, by hand or using the fill series command
  4. Copy the row numbering from column A into columns B & C

Manipulating Data
  • Sort only the data in columns D, E, F, and G
  • Copy the Sheet to allow for multiple sorts
  • Highlight all the data entered in columns D, E, F, and G.
  • Use the custom sort function to sort the data according to specific criterion

Creating a Scatterplot
  1. Click on Insert>Charts>Scatter>Scatter with Straight Lines and Markers
  2. With the chart area outlined, right click on the border of the chart
  3. Click on Move Chart
  4. Select New sheet and hit OK (a new tab labeled Chart 1 will appear to the left of the tab for Sheet 1)
  5. Click on Design>Select Data
  6. Click on the cell selection icon at the end of the entry location for Chart data range
  7. Highlight the data entered in columns E through G
  8. Click Edit
  9. Click on the cell selection icon for Series Name
  10. Click on Sheet 1
  11. Click on cell D2
  12. Click on the cell selection icon
  13. Highlight the text in the box Series X values
  14. Click on the cell selection icon next to Series X values
  15. Click on Sheet 1
  16. Highlight the data in cells E2:G2
  17. Click on the cell selection icon
  18. Highlight the text in the box Series Y values
  19. Click on the cell selection icon next to Series Y values
  20. Click on Sheet 1
  21. Highlight data in cells A2:C2
  22. Click on cell selection icon
  23. Repeat steps 8 – 24 for each series adjusting the cell selection to match the series
  24. Click on Chart Tools>Layout>Axes>Primary Vertical Axis>More Primary Vertical Axis Options
  25. Set the following axis options as fixed:
    1. Minimum: 0
    2. Maximum: number of studies
    3. Major Unit: 1
  26. Click Close
  27. Click on Axes>Primary Vertical Axis>None
  28. Click on Axes>Primary Horizontal Axis>More Primary Horizontal Axis Options
  29. Set the following axis options as fixed:
    1. Minimum: 0
    2. Maximum: 1
    3. Major Unit: 0.1
  30. Click on Legend>None
  31. Repeat as needed for multiple graphs reflecting different sorting criterion

Formatting the Data Series
  1. Click on Insert>Shapes>Line
  2. Draw a short straight vertical line (hold down the shift key while drawing) somewhere on the chart
  3. Right click on the line and select format object
  4. Select line color and choose solid line, set the color to black
  5. Select line style and set width to 2 pt
  6. Select size and enter height 0.25”
  7. Click Close
  8. Select the line and copy (Ctrl+C or Click Home>Cut)
  9. Select Series 1 by clicking on the line connecting the data points
  10. Paste (Ctrl+V or Click Home>Paste)
  11. A line should replace all data markers
  12. Repeat for each series
  13. Select Series 1 by clicking on the line connecting the data points
  14. Right click and select Format Data Series
  15. Select Line Color and choose Solid line, set color to black
  16. Select Line Style and set Dash type to dash
  17. Click Close
  18. Repeat for each series
  19. Select only the middle data point for Series 1, click on the data point to highlight the series, then click again on the single point
  20. Right click and select Format Data Point
  21. Select Marker Options and choose Built-in, select the diamond, set size to 10
  22. Select Marker Fill and choose Solid fill, set fill color to black
  23. Click close
  24. Repeat for each series
  25. Select data series 1
  26. Click on Chart Tools>Layout>Data Labels>More Data Label Options
  27. Select Label Options
  28. Under Label Contains, select X value and deselect Y value
  29. Under Label Position, select Above
  30. Click Close
  31. Repeat for each series
  32. Delete the line drawn to create end markers
  33. Add Labels to the Y Axis • Excel graphs the first study on the bottom and proceeds upwards (which is opposite of the listing on the spreadsheet or the legend)
  34. Click on the Chart
  35. Copy the Chart (Ctrl+C or Home>Cut)
  36. Open a Word Document
  37. Paste the Chart as a JPEG (Home>Paste>Paste Special>Picture (JPEG)
  38. Click on JPEG
  39. Adjust size to fit page (width one inch less than page width after margins; position in upper left hand corner)
  40. Go to Sheet 1 in Excel
  41. Select the data in columns C & D
  42. Copy and paste to a series of empty cells
  43. Highlight the data pasted
  44. Click Home>Sort>Largest to Smallest
  45. Highlight only the study names in the new order
  46. Copy the study names (Ctrl+C or Home>Cut)
  47. Go to Word Document
  48. Click Home>Insert>Text Box
  49. Create a Text Box on the right side of the document
  50. Click Home>Paste>Keep Text Only
  51. Resize Text Box and adjust font to align labels with the data series