How to Make Box Plots in Excel

1. Open the Excel file that contains the data you want to represent as a box plot.
2. Scroll to the bottom of the data set and type in five new row headers on the left-hand side of the screen. These headers, from top to bottom, are: “First Quartile,” “Minimum,” “Median,” “Maximum” and “Third Quartile.”
3. Select the cell to the right of “First Quartile.” Type in “=Percentile (XXX, 0.25),” where XXX is the field of data for that sample. Write out the field by typing in the column and row of the first cell in the field, then add a colon, and then type in the column and row of the last cell in the field.
4. Type in “=min (XXX)” next to the “Minimum” cell. Then type in “=median (XXX)” next to the median cell and “=max (XXX)” next to the maximum cell. Finally type in “=percentile (XXX, 0.75)” next to the “Third Quartile” cell. The “XXX” in all of these will be the same data field.
5. Copy and paste these formulas to the cells to their right, if you need to create a box plot for more than one data sample. If your second sample is located in the cells directly to the right of your first sample, Excel will automatically change the formulas to reflect this.
6. Select all of the cells in the chart you just made, including the row headers in the first column. Click “Insert” on the top of the window, then click the “Line” button in the “Charts” area. Choose the “Line with Markers” button, and your chart will appear.
7. Click the “Switch Row/Column” button at the top of the screen.
8. Right-click on any of the data points on the chart. Select “Format Data Series” from the menu that pops up. Then choose “Line color” on the left side of the new window, and click the radial button next to “no line” before clicking “Close.” Repeat this for the other four data lines.
9. Click the Layout tab at the top of the screen, then click the “Analysis” button. Choose “Lines” from the menu and finally click on “High-Low Lines.” Then click “Analysis” and “Lines” again, but this time select “Up/Down Bars” to reveal a menu, and select the “Up/Down Bars” button from this new menu.