Analyzing data is an important part of any business operation, and one of the best ways to do so is to create a box plot. Box plots, also known as box and whisker diagrams, are an effective way to visualize and summarize large datasets. Ultimately, they provide an easy-to-understand visual representation of data distributions, which can be useful when making decisions. In this blog post, we’ll discuss how to create a box plot in Excel, the popular spreadsheet software. We’ll go over the basic idea of a box plot and provide step-by-step instructions on how to make one in Excel. By the end of the post, you’ll have a better understanding of box plots and will be able to create your own. Let’s get started!
- Step 1: Calculate the quartile values.
- Step 2: Calculate quartile differences.
- Step 3: Create a stacked column chart.
- Step 4: Convert the stacked column chart to the box plot style. Hide the bottom data series. Create whiskers for the box plot. Color the middle areas.
Why use a box plot?
Box plots can be used to compare various categorical values, visualize the distribution of values, and spot outliers. These illustrations are useful in many statistical applications, but take the situation where a teacher makes a box plot as an illustration. They might input test score data from three different teachers. They might assist the teacher with their lesson plans if they observe that a significant portion of Teacher As test scores fall within the first quartile range. The box plot might also reveal exceptionally high outlier scores and inspire teachers to assign students with more difficult work.
What is a box plot?
The distribution of numerical values in a data set is graphically summarized in a box plot, also known as a box and whisker plot. All of the data in the second quartile are represented by the rectangular component of a box plot. Additionally, a line through the center of this component denotes the median value of the data set.
All of the numbers in the first and third quartiles are represented by lines extending from the top and bottom of the rectangle. Each of these lines has a “whisker” at the end that represents the highest and lowest values, respectively. To show outliers, box plots may also have dots outside of the whiskers.
How to make a box plot in Excel
Heres how to make a box plot in Excel:
1. Enter data into the spreadsheet
Open a spreadsheet and enter your data. Although you can also type each value in a separate adjacent cell, most people copy and paste values from another document. It doesn’t matter whether you arrange the data in rows or columns when creating box plots.
2. Select your data
Highlight the data you entered. By clicking on a cell and dragging your cursor across other cells, you can select a range of cells. Press the “Ctrl” key while making a separate selection if you want to select more information that is not close to your current selection.
3. Create a box plot
Go to the “Insert” tab. Click the “Insert Statistic Chart” icon in the “Groups” by placing your cursor over it. Click “Box and Whisker. “.
4. Edit the box plot
The box plot can be customized to better convey your data. To change the name, think about double-clicking the title or the x- and y-axis fields. Additionally, you can modify the colors and other stylistic components by clicking the paintbrush icon.
How to make a box plot in Excel manually
Box plots can be quickly created using Excel’s “Box and Whisker” feature, but earlier versions of the program might not have this feature. Heres how to make a box plot in Excel manually:
1. Set up your spreadsheet
By pasting values into adjacent cells or typing values there, you can enter data into a spreadsheet. Leave space for the “Minimum,” “Q1,” “Median,” “Q3,” and “Maximum” values to be entered. Consider the example below as you go over these steps. A small set of data is in column A, and the values that column B indicates are to be entered in column C:
ABCD110Minimum
214Q1
315Median
416Q3
520Maximum
625
2. Calculate the 5 values
Calculate the minimum value by typing “=QUARTILE. INC” into C1 and pressing “Enter. Choose the data set, which in this example is A1 through A6. If you want to determine the minimum value, enter “0” as the second argument after a comma. Type a closing parenthesis and press “Enter. “.
Use the formulas below as a guide to repeat this procedure for the other four values. Recognize that the data set is always the first argument and that the second argument is always a number between 0 and 4 indicating the percentile or value to be calculated. The second argument is “1” in Q1, “2” in Median, “3” in Q3, and “4” in Maximum. “.
3. Calculate two differences
Calculating the difference between the Q1 and minimum values is the next step. Next to the cell that contains the minimum value, enter the equation. Select the cell that contains the Q1 value and enter the equal sign in this cell. Enter the minus sign, choose the cell that has the lowest value, and then click on it. These actions would cause cell D1 in the example above to contain the following formula:
=C2-C1
Calculate the difference between the maximum and Q3 values using a similar process. Select the cell with the maximum value, enter “=”, and then select the cell next to it. Enter the Q3 value, type a minus sign, and then select the cell. These actions would cause cell D5 in the example above to contain the following formula:
=C5-C4
4. Insert a column chart
Choose the Q1, Median, and Q3 fields, along with the corresponding values. In the example above, you would select B2:C4. Go to the “Insert” tab and view the “Charts” group. Select “Clustered Column” under “2-D Column” in the “Column” drop-down menu. “.
5. Set up the chart
Right-click the charts legend and select “Select Data. ” Click “Switch Row/Column” and select “OK. To format data series, right-click any of the bars and choose “Format”. Drag the slider in the “Series Overlap” section so that the series is completely overlapping. Click “Close. “.
There are a few things you can do to make the chart appear more organized. Think about clicking the x-axis label at the bottom and pressing the “Delete” key on your keyboard. Alternatively, you can right-click the graph and choose “Format gridlines.” In the “Line Color” tab, select the radio button next to “No line,” then click “Close. “.
6. Create the rectangular component of the box plot
Click “Select Data” by right-clicking the single rectangle in the diagram. Use the up and down arrows to reorder the entries in the “Legend Entries” section so that they appear in the following order:
Click “OK” to finalize the changes. Right-click the bottom rectangle and select “Format Data Series. Select “Solid fill” from the “Fill” tab and change the color to white. Click “Close. “.
7. Add the whiskers
Select the chart. In the ribbon at the top of the spreadsheet, select the “Layout” tab. In the “Analysis” group, select “Error Bars. ” Click “More Error Bars Options” in the drop-down menu. Select “Q1” and click “OK. Select “Cap” as the “End Style” and “Minus” under “Vertical Error Bars. Click “Specify Value” after selecting the “Custom” radio button next to “Error Amount.” Select the cell that contains the difference between the Q1 and minimum values, highlight the “Negative Error Value” field, and then click “OK. “.
Repeat this process to add the top whisker. Choose “Q3” rather than “Q1” after clicking the “More Error Bars Options” button. ” Click “OK,” select “Plus” and choose “Cap. Select the “Custom” radio button, then “Specify Value” and then “Positive Error Value” to highlight the field. The cell with the difference between the maximum and Q3 values should be chosen. Click “OK. “.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
How To Create A Box Plot In Excel (Including Outliers)
FAQ
How do you make a Boxplot in Excel 2019?
- On the Insert tab, in the Illustrations group, click Chart.
- Click Box & Whisker in the Insert Chart dialog box’s All Charts tab.
Does Excel have box plots?
Excel 2013 doesn’t have a Box Plot chart type, but you can make your own by using a Stacked Column chart type with error bars and adding calculations to the worksheet.
How do I make a box plot?
Use a rectangular box and a horizontal or vertical number line to create a box plot. The axis’ endpoints are indicated by the smallest and largest data values. One end of the box is marked by the first quartile, and the other end is marked by the third quartile.
