How to Create a Box Plot in Microsoft Excel
Statistical analysis has never been easier
Microsoft Excel makes it easy for you to organize, present, and analyze data using various charts. A particularly powerful chart is the box and whisker plot (also known as a box plot), designed to help display the distribution of values in a data set.
In this article, we’ll cover how you can create a box plot in Microsoft Excel, covering both Excel 365 and older versions for those yet to upgrade.
Box Plots: What Are They?
A box plot is a method of displaying data that helps visualize statistical qualities like the spread and variability of the data. It shows a single bar (the box) split in two, with lines (the whiskers) extending to either side of the box.
Each of these elements visualizes the five-number summary of a set of numerical data. They look like this and can be displayed horizontally or vertically:
To understand the five-number summary, let’s take a look at a sample data set.
25, 26, 28, 30, 32, 34, 37, 38
How to Create a Box Plot in Excel 365
InOffice 365, Microsoft Excel includes box plots as a chart template, making it easy to create a visual plot for your data. If you aren’t sure how to use Excel,learn the basics first.
To create a box plot:
- ClickInsert Statistic ChartthenBox and Whisker.
Note:If you insert an empty chart, you can input your data set by selecting theChart Designtab and clickingSelect Data.
Excel will now create a bare-bones box and whisker chart. However, you can customize this Excel chart further to display your statistical data exactly how you’d like it.
How to Format a Box Plot in Excel 365
Excel allows you to style the box plot chart design in many ways, from adding a title to changing the key data points that are displayed.
TheChart Designtab allows you to add chart elements (like chart titles, gridlines, and labels), change the layout or chart type, and change the color of the box and whiskers using built-in chart style templates.
TheFormattab allows you to fine-tune your color selections, add text, and add effects to your chart elements.
To add further display elements, right-click the box and whisker plot and selectFormat Data Seriesfrom the drop-down menu.
The options include:
How to Create a Box and Whisker Plot in Older Versions of Excel
As olderversions of Excel(including Excel 2013 and Excel 2016) don’t include a template for the box and whisker chart, creating one is much more difficult.
First, calculate your quartile values using the following formulae and create a table:
Note:For cell range, drag and select your data set(s).
Next, calculate the quartile differences in a separate table (these relate to the box heights):
You can then create a chart using these values:
To add the top whisker:
To add the bottom whisker:
You now have a basic box and whisker plot for your data set. You can customize this further by adding a mean line or dot, changing the colors, and altering the chart style.
Statistical Analysis Has Never Been Easier
Luckily, with newer, more powerful versions of the program, visualizing and analyzing data has become much simpler. With this tutorial, you should have a firm grasp of how a box and whisker plot is used and how you can set one up in an Excel workbook.
Jake Harfield is an Australian freelance writer whose passion is finding out how different technologies work. He has written for several online publications, focusing on explaining what he has learned to help others with their tech problems. He’s an avid hiker and birder, and in his spare time you’ll find him in the Aussie bush listening to the birdsong.Read Jake’s Full Bio
Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.
HomeAbout UsEditorial StandardsContact UsTerms of Use
Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved
