Open In App

Creating Box Plots in Excel

Last Updated : 29 Jun, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

Box plot is a statistical plot that helps in data visualization. It is used to show the distribution of numerical data using various quartiles. They are as follows :

  1. Lower Extreme: It is the minimum value in the data set which is at the end of the whisker.
  2. First Quartile: It is also known as lower quartile where 25% of the scores fall below it.
  3. Median: It is basically the mid-point which divides the box into two equal halves. It is also known as Second Quartile.
  4. Third Quartile: It is also known as the Upper quartile in which 25% of the data is above it and the rest 75% falls below it.
  5. Interquartile Range: It is showing the middle part of the box plot which is 50% of the scores. It is abbreviated as IQR.
  6. Upper Extreme: It is the maximum value in the dataset which is at the end of the whisker.
  7. Whisker: The two whiskers at upper and lower basically denote the value outside the IQR range or 50% of the scores.
  8. Outliers: The points in the box plot which lie outside the whiskers.

Some important links to get more insights about box plots :

  1. Box Plot
  2. Box Plot using Python
  3. Box Plot in R
  4.  What is Box plot and the condition of outliers?
  5. Understanding different Box Plot with visualization

Structure of Box Plot

In this article, we are going to see how to create box plots and also how to find the important parameters associated with box plots in Excel using a suitable example.

Example: Consider the BMI of ten students from section A-1 and that of section A-2. BMI stands for Body Mass Index which is an important parameter to judge the body fat and health of a person on the basis of height and weight of a person. 

The steps to create a box plot :

  1. Insert the data in the cells as shown above.
  2. Select the data and go to the Insert tab at the top of the Excel window.
  3. Now click on the Statistical Chart menu. A drop-down will occur.
  4. Now select Box and Whisker chart.

Box Plot

The box plot by default will be exclusive of the mean value. In order to make it inclusive of mean :

  1. Select the box plot.
  2. Right-Click and select Format Data Series.
  3. In the Format Data Series dialog box check “Inclusive Mean” in Quartile Calculation.

To format a box plot use the + symbol in the top right corner of the chart as shown below :

Check the Data Labels option to add data labels in the box plots and make the plot more insightful.

You can examine the data labels values using the following section where we are going to discuss how to calculate these parameters using Excel formulas.

Formula to calculate parameters associated with the box plot:

In order to calculate the different quartile values use the formula :

= QUARTILE.INC(Cell_Range, integer) 

Here,

  • Cell range: Range of cells. In our case, it is A2 to A11 for section A-1 and B2 to B11 for section A-2
  • integer : [0,4]
Quartile Values Formula
Lower Extreme =QUARTILE.INC(Cell_Range, 0) 
Q1 =QUARTILE.INC(Cell_Range, 1) 
Median =QUARTILE.INC(Cell_Range, 2) 
Q3 =QUARTILE.INC(Cell_Range, 3) 
Upper Extreme =QUARTILE.INC(Cell_Range, 4) 

Make a helper table in Excel to calculate the above formulas. The helper table can be used to interpret our box plot and the values.

Lower Limit Calculation

Quartile 1 calculation

Similarly, you can calculate all the other parameters for both sections. The final table will look like this:

Some other important parameters in a box plot are (1) Mean (2) Range. The formulas are :

 = AVERAGE(Cell_Range)              
= (Upper Extreme - Lower Extreme) 


Helper Table

Another important parameter in a box plot is an outlier which depends on the value of Interquartile Range (IQR). The formula for IQR is :

IQR = Quartile_3 - Quartile_1

In our example, the value of IQR is 6.6 which you can calculate from the helper table. Now, a point is an outlier if the value is :

below (Quartile_1 - IQR*1.5) and  
above (Quartile_3 + IQR*1.5)

In the given example for section A-1 we have an outlier at value 50 which is the maximum value of BMI. After calculation the value will be :

IQR * 1.5=9.9
Q3 + IQR * 1.5 = 34.5

Since, 50 > 34.5 so it is in the outlier of the box plot.

Similarly, you can calculate the above parameters for the second box plot, and you can observe that all the five parameters are within the range and hence there are no outliers.

In order to remove the outlier in Box plot-1, you have to modify the maximum value from 50 to any value less or equal to 34.5.

Outlier Removed


Similar Reads

How To Create Dot Plots In Excel?
A Dot plot is a type of chart used in statistics for representing relatively small data sets where the values are uniquely categorized. A dot plot is also known as dot chart or strip chart. A dot plot and a bar graph are similar in the sense that the height of each “bar” of dots is equal to the number of items in that specific category. These are u
2 min read
Creating Infographics with Excel
Infographics are the best option to capture user attention and effectively convey the information. We explain step by step how to create a simple infographic for displaying sales of Apples and Oranges with a background Grey image. Implementation: Follow the below steps to implement the infographics of the sales data: Step 1: Open Excel. Step 2: Go
2 min read
Creating a Power Pivot Chart in Excel
Power Pivot is an Excel add-in that is available for Excel 2021, Excel 2019, Excel 2016, and Excel 2013. Power Pivot is used to create data models and performs high-level data analysis to establish relationships and give results easily. Power pivot can analyze large data rapidly and creates data models by building extensive relationships and calcul
3 min read
Creating Actual Vs Target Chart In Excel With Floating Markers
Excel is known to be a powerful tool for data visualization, comparison, storage, and management which can handle large amounts of data. We know that data can be visualized and compared using different kinds of plots and charts in Excel such as line charts, bar charts, etc. The tool is used to get insights from data using formulas and functions. It
5 min read
Creating a Gantt Chart With Milestones Using a Stacked Bar Chart In Excel
One of the most common and effective methods of displaying activities (tasks or events) plotted against time is a Gantt chart, which is frequently used in project management. On the left side of the chart is a list of the activities, and at the top is a suitable time scale. A bar is used to symbolize each activity, and the location and length of th
3 min read
Creating a Data Model in Excel
In Excel, a data model is a form of the data table in which two or more tables are linked by a common or many data series. Tables and data from many other sheets or sources are combined in the data model to generate a single table that can access data from all tables. A Data Model enables you to combine data from different tables, thereby creating
5 min read
Dynamic Excel Filter Search Box
Filters are the most commonly used functionalities for filtering out any particular result in a large data set. Dynamic filters searches are used by large companies like Google, Amazon, Youtube, Flipkart, etc. where we just type a single character and it starts showing the recommended result. In this article, we will learn how to create a dynamic e
4 min read
How to Mask Password in VBA Excel Input Box?
A combination of integers, strings of characters, or other symbols is used to verify the identity of a user during the authentication process so, that they can access the system. But a password should always remain in a hidden format to prevent it from shoulder spying. Shoulder spying is a practice where a person collects our personal data by spyin
2 min read
How to Create an Input Box With Multiple Inputs in Excel Using VBA?
The Input Box is a dialogue box that helps users to take a value and do later computations according to the entered value. The input box is similar to the message box, but the message box is used to display the data while the input box is used to enter the data. By default, a message box displays only the Ok button in its conversation box, whereas
10 min read
Editing Excel Macros in Excel
Excel Macro is a set of actions that can be recorded, saved, used multiple times. This feature saves us a lot of time when dealing with repetitive tasks and huge data sets. We can always make changes to an existing Macro in Excel. There are two ways to record and run macros: Excel Commands Excel Visual Basic for Applications(VBA).1. Copying a Macro
1 min read
Article Tags :