How to create a box plot in Google Sheets

Box plot in Google Sheets
Reading Time: 5 minutes

In this tutorial, we will learn how to create a box plot in Google Sheets. But before we get into the nuts and bolts of creating a box plot in Google Sheets, it’d be helpful to learn a few essential things about box plot.

What is a box plot?

A box plot is a method to graphically represent the distribution of data based on a five-number dataset: the minimum value, the lower quartile (first quartile), the median, the upper quartile (thir d quartile), and the maximum value. It gives information about outliers in the data and what their values are. It also gives information about symmetry in the dataset, their spread and skewness and how tightly they are grouped.

  • The minimum and the maximum are the lowest and the highest data points, respectively, excluding the outliers.
  • The median is the middle value in the data set.
  • And the first and the third quartiles represent the median of the lower half and the upper half of the data set, respectively.
  • Outliers are values that lie beyond the minimum and the maximum values.
Parts of a box plot in Google Sheets
Different parts of a box plot

Box plots are an excellent tool for conveying location and variation information in data sets, particularly for detecting and illustrating location and variation changes between different groups of data. The box plot can also be used to analyse stock market performance and visualise a summary of returns during a specific period.

How to create a box plot in Google Sheets

Creating a box plot in Google Sheets is not very different from how you’d create a box plot in other spreadsheets, except that in Google Sheets we use a Candlestick chart instead, since Google Sheets does not support box plot natively.

Here’s a step-by-step guide to create a box plot in Google Sheets:

In this tutorial, let us box plot the monthly mean temperature of Delhi for a typical year and analyse the data.

Make a copy of this Google spreadsheet if you want to follow along and practice.

Step 1: Compute the five-number summary for the box plot

In order to create a box plot, we need to compute the following values and place them in the order as given below:

  • Minimum value
  • Lower quartile
  • Median
  • Upper quartile
  • Maximum value

For this, we make headers titled Minimum Value, Lower Quartile, Median, Upper Quartile, and Maximum Value.

Creating headers for the five-number summary in Google Sheets

Finding the Minimum value

To find the minimum value, enter the following formula under the Minimum Value column to find the minimum value:

=MIN(B4:B15)
Formula to find the minimum value

Finding the Lower Quartile value

To find the value of the lower quartile, we enter into the cell underneath the Lower Quartile header the following formula:

=QUARTILE(B4:B15,1)

Here, 1 indicates the quartile number.

Formula for first quartile

Finding the median value

We use the MEDIAN function to find the median value. We enter the formula given below into the cell D18 to compute the median:

=MEDIAN(B4:B15)
Finding the median value in Google Sheets

Finding the Upper Quartile value

In order to compute the upper quartile value, we enter the formula given below in the cell E18.

=QUARTILE(B4:B15,3)

Where 3 signifies third quartile

Finding the value of the upper quartile in Google Sheets

Finding the Maximum value

Entering the formula below in the cell F18 returns the maximum value.

=MAX(B4:B15)

Now that we have found the values required for the box plot, we can proceed with creating a chart to represent the values.

Step 2: Creating a suitable chart for the box plot

As Google Sheets has no direct way to create a box plot, we use the nearest available option, which is the candlestick chart.

Before we go about creating a candlestick chart, we need to give a name to the summary values we have computed above to the left of the values, since Google Sheets requires that the first column for the chart data be text. Let us simply name it “Summary Data”.

Inserting the chart

Select the summary values including the cell with text to the left of the values (“Summary Data”, in this case). Then go to the “Insert” tab and select “Chart”.

Inserting chart in Google Sheets

Selecting the chart

The chart that is inserted by default may not be what we desire. So, we go to

Chart editor > Chart type > Candlestick chart

This will bring up the box plot on the screen.

Selecting box plot chart in Google Sheets

Customising the box plot

We can now customise the box plot to give it more refinement. To do so, we click on the “Customise” option on the Chart editor menu. You can here give the box plot a name, change the background colour, font size and colour, insert gridlines, and so on.

Box plot in Google Sheets

Adding more than one box plot in Google Sheets

We can also add more than one box plot to a single chart. Inserting more box plots is helpful if we want to make a comparison between two or more data sets. Let us add the weather data for Mumbai and compare it with Delhi’s by inserting it in the same chart.

For this, we first add the weather data for Mumbai and find the five-number summary values for Mumbai as we did for Delhi. Then we select the summary data for both Delhi and Mumbai and insert the Candlestick chart. This will show the box plot for both Delhi and Mumbai in a single chart.

Chart with multiple box plots

Conclusion

Box plots help us to easily visualise differences and outliers in a data set, and also compare data for different similar entities. Although box plots are less commonly used than other types of data representations, they offer few advantages such as occupying less space and easy interpretation of the data. Do not, therefore, hesitate to use them when appropriate.

We hope this article has been helpful to you. You can find more articles on Google Sheets here.

See Also

Here are some related articles you might find useful:

Use Waterfall Charts in Google Sheets | Easy 2022 Guide

Use Waterfall Charts in Google Sheets | Easy 2022 Guide

Create Slicers in Google Sheets – 3 Easy Steps

Leave a Reply