How to Draw a Box Plot in Excel
A box plot is a hybrid of a line chart and a bar chart, which lets you graph several ranges of numbers using the same axes. It is a useful tool for illustrating survey results, demographic trends and many other business results that cannot easily be explained using a traditional chart. Instead of trying to draw a box plot freehand, let your Excel database software do it for you. Then all you have to do is type in the labels and numbers.
Things You'll Need
- Excel software (98 or newer)
Launch Excel. It should open up to a blank worksheet. Put your mouse in box A1 and type in the word "Statistic." Type "Median" in cell A2, "Q1" in cell A3, "Min" in cell A4, "Max in cell A5 and "Q3" in cell A6. Next click on box B1. Type in "a." Continue entering data moving down the columns for cells B2 through B6, C1 through C6 and D1 through D6. Type the following numbers and letters, one in each cell, in this order: 40, 20, 10, 100, 70, b, 45, 22, 15, 110, 75, c, 50, 30, 18, 90 and 57.
Click on cell A1 and then drag your mouse to cell D6 with your finger still on the button. This will highlight all of the cells you just typed into. Click on the insert tab at the top left of your Excel window. Click on "Other Charts" and then on "Stock." You will see icons for four types of stock charts. Roll over them with your mouse. Click on the icon for the one that says "Volume-Open-High-Low-Close." Then click "Next."
Click on the tab that says "Data Range." Look for a header that says "Series In." In the list below, select "Rows." Click "Next."
Click on the tab that says "Axes." Look for the words "Secondary Axis." Click on the box next to "Value (Y) Axis." Click "Finish" at the bottom right.
Go to your chart, which should have popped up on the screen. You will see both white box columns and colored box columns. Click on a colored column to select it. Go back to the "Chart" tab and select "Line" under chart type to change how the graph is displayed. Click "OK." You now have your box plot.
Tips & Warnings
- Click on your worksheet, which will still be in the background, to replace the sample data with your data. Do the same with the labels. This way you can see your chart change without having to go back and forth.
- Directions may vary slightly between different versions of Excel. If you get stuck, check the Help menu.