How to Make a Box Plot Excel Chart? 2 Easy Ways
Note: This tutorial on how to make a box plot Excel chart is suitable for all Excel versions including Office 365.
In this guide, you will learn how to insert a box plot in Excel the easy way.
I’ll cover:
- What is a Box Plot Excel Chart?
- How to Insert a box plot in Excel 2016 and 2019?
- How to Insert a box plot in Excel 2013?
Related:
Easily Make a Bullet Chart in Excel—2 Examples
Creating a Dynamic Pivot Chart Title Based on Slicer(6 Easy Steps)
How to Make a Line Graph in Excel? 4 Best Line Graph Examples
What Is a Box Plot Excel Chart?
A box plot helps you visualize the data distribution by displaying the minimum and maximum values along with their median and quartiles.
Use them to :
- Find the average, median, and quartiles of the data
- Identify outliers in the data set
- Determine if the data is symmetric or skewed
They can be plotted both horizontally and vertically.
How to Insert a Box Plot in Excel 2016 and Excel 2019?
If you are using Excel versions 2016 or later, inserting a box plot is pretty straightforward.
All you have to do is follow these simple steps:
- Arrange your dataset in a column. You can arrange it in descending order for your convenience and understanding.
- Select your data range and go to the Insert tab and click on the Insert Statistics chart icon under the Charts Group.
- Click on the Box and Whisker option.
After that, your Excel box plot is ready. The middle line in the box plot is nothing but the median of the data set, which divides the data into two halves. Also, the ‘x’ mark points to the average of the data set.
The bottom and the top parts of the box chart represent the 1st and 3rd quartiles respectively. Similarly, the vertical lines (whiskers) extend towards the bottom and top extreme values.
All outliers will lie beyond these whiskers. Outliers are nothing but data points that appear beyond a distance of 1.5 times the interquartile range before or after the first or the third quartiles (Q1&Q3).
Also Read:
Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)
How to Make a Scatter Plot in Excel? 4 Easy Steps
How to Add Error Bars in Excel? 7 Best Methods
How to Make a Box Plot in Excel 2013?
It is still possible to insert a box plot in Excel 2013 and older versions. Follow these steps to do this easily.
- Calculate the minimum, first quartile(Q1), median, last quartile(Q3), and maximum values of your data set. Do this using the MIN, QUARTILE, MEDIAN, QUARTILE, and MAX functions respectively.
- In a separate column, calculate the length of the boxes as shown below:
- Hidden Box = Q1
- Lower Box = Median – Q1
- Upper Box = Q3 – Median
- Top Whisker = Max – Q3
- Bottom Whisker = Q1 – Min
- Now, select the calculated values of the first three boxes (hidden, lower and upper) and click on the Stacked Column chart option under the Charts group of the Insert tab.
- Click on the bottom-most column and set the fill to No Fill. Similarly, set the border to no border in the Format Data Series pane. Finally, remove the legend and other unnecessary chart elements.
- To add the top whisker, click on the topmost box and go to the Chart Elements menu and choose Error bars. Under the Error bars options set the direction to ‘Plus’ and click on the Custom option to specify a value. Now, select the cell which contains the value of the top whisker.
- Follow the same procedure to add the bottom whisker but click on the hidden box instead and select the cell which contains the value of the bottom whisker in the specify value menu.
- Adjust the fill colours and formatting of the stacked column chart to closely resemble a box plot.
- You can add an average marker to the box plot by pasting the average value as a new data series to the same stacked column chart. Just remember to format it as a Line with Markers and later change the line’s fill to No fill.
Follow these steps:
1. Copy the calculated average value, click on the box plot Excel chart and click on the Paste as special button in the Home tab.
2. In the Paste Special dialogue box, tick ‘New Series‘, ‘Series Name in First Column‘ and Plot values (Y) in rows and click OK.
3. Now, the average value will be plotted as another stacked column. You need to change this to a marker instead. To do this, right-click on it and choose the Change series chart type option. Then, in the Change chart type menu, change the chart type of the average to Line with Markers. Now, the average of the data series will appear inside the Excel box plot. In case, a line appears double click on the line and set its fill to No Fill in the format data series pane.
Suggested Reads:
How to Add a Watermark in Excel? 2 Easy Methods
How to Remove Hyperlinks in Excel? 3 Easy Methods
How to Use the Format Painter Excel Feature? — 3 Bonus Tips
Closing Thoughts
That’s all folks. In this guide, I have shown you the two easiest ways to make a box plot Excel chart. Try them out now in a practice Excel sheet to understand the concept better.
If you have any questions about this or any other Excel feature please let us know in the comments section. We’re always happy to help.
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
