How to make a box plot in Excel

What is a boxplot?

For descriptive statistics, boxplots are one of the best ways to show how data is distributed. It displays numbers in quartiles, highlighting averages and outliers. Boxplots are used extensively in statistical analysis, from comparing medical trial results to comparing the test scores of different teachers.

The basis of the boxplot is to display data based on five-digit summaries. This means showing:

• Minimum value: The lowest data point in the dataset, excluding any outliers.
• Maximum value: The highest data point in the data set, excluding outliers.
• Median : The middle value in the data set
• First or lower quartile : This is the lower half of the data set The median of the values.
• Third or upper quartile: The median of the upper half of the data set values

Sometimes, box plots will show vertical upward or Line segments extending downward to show how data varies outside the upper and lower quartiles. These are called "whiskers," and the chart itself is sometimes called a box-and-whisker plot.

How to make a boxplot in Excel for Microsoft 365

In past versions of Excel, there was no chart template specific to boxplots. While it is still possible to create it, it requires a lot of work. Office 365 does now include box plots as an option, but it's somewhat hidden in the Insert tab.

The following guides and screenshots are based on using the Microsoft 365 version of Excel. The steps provided here are designed for users using Mac computers. However, we will provide instructions accordingly as the steps may be different on a Windows computer.

First, of course, you need your data. Once you've finished typing, you can create and style the box plot.

Create a box plot in Excel:

1. Select your data—a single or multiple data series—in an Excel workbook.
2. On the ribbon bar, click the Insert tab.
3. On Windows, click Insert > Insert Chart > Box Whiskers.
4. On macOS, click the Statistical Chart icon and select Box and Whisker.

#This will give you a very basic box plot, complete with whiskers. Next, you can modify its options to see it the way you want.

Formatting Box and Whisker Plots in Excel

After you create your box and whisker plot, it’s time to spruce it up. The first thing you should do is give your chart a descriptive title. To do this, click on the existing title and you can select the text and make changes.

From the Design and Format tabs on the ribbon, you can modify Excel to style the box plot. Here you can choose which theme style to use, change the fill color of the box, apply a WordArt style, and more. These options are common to almost all charts and graphs you might create in Excel.

If you want to change options specific to Box and Whisper charts, click Format Pane. Here you can change how the chart represents your data. For example:

• Show interior points: If checked, your boxplot will also show individual values ​​within the first and third quartiles. Excel represents each value with a point, rather than one point for each record in the data set.
  
• Show Outliers: When this feature is enabled, your chart will include markers showing data points outside the whiskers. In other words, this will be a data point that is unusually far away from other values ​​in the data set.
  
• Show average markers: This option turns on or off the average markers in each box.
  
• Show average line: If checked, draws a line connecting the average of each data set.
  
• Quartile Calculation: When you have an odd number of data points, you can calculate your quartiles with or without the median. In general, exclusive interquartile ranges are more suitable for large sample data. Smaller samples can benefit from the inclusive median method because it tends to be more representative of a narrower range of data points.

A welcome addition to statistical analysis

It's not always easy to figure out how to create a box plot in Excel. Creating charts in previous versions of spreadsheet software required manual calculation of the various quartiles. You can then create a bar chart to approximate the boxplot. Microsoft's addition of this chart type in Office 365 and Microsoft 365 is very popular.

The above is the detailed content of How to make a box plot in Excel. For more information, please follow other related articles on the PHP Chinese website!