There are many different ways to create data visualizations in Google Sheets depending on what elements of your data you want to view.
One great way to help summarize your data visually is with a box plot.
In this tutorial, I will show you how to create a box plot in Google Sheets.
Table of Contents
What is a Box Plot?
A box plot (also called a box and whisker plot) is a type of graph that displays the five-number summary of a dataset. It gives you a good idea of how the data is spread.
The five-number summary that is displayed in a box plot includes:
- minimum value
- first quartile
- median value
- third quartile
- maximum value
This gives you a quick summary of the distribution of data.
Here is an example of a box plot:
Here is how to read this box plot:
- The top of the blue line (top whisker) is the maximum value
- The top of the blue box is the third quartile
- The bottom of the blue box is the first quartile
- The bottom of the blue line (bottom whisker) is the minimum value
The whiskers in the plot (blue lines) help you identify outliers in your dataset. You can see how far away the maximum and minimum values are from the median which can help you identify outliers that could skew your distribution.
Calculating Five Number Summary
Before we can create our box plot, we need to first calculate the five-number summary of our dataset.
I will be calculating the five-number summary of this data:
To calculate the five-number summary, we will need to use four functions:
- MIN
- QUARTILE
- MEDIAN
- MAX
Here is the syntax you will need to use with each function to calculate each point in our five-number summary:
- Minimum – =MIN(data_range)
- First Quartile – =QUARTILE(data_range,1)
- Median – =MEDIAN(data_range)
- Third Quartile – =QUARTILE(data_range,3)
- Maximum – =MAX(data_range)
Here is how each formula looks after I have set them up in my spreadsheet:
Creating a Box Plot
Once you have your five-number summary for your dataset, you are ready to create the box plot.
Here are the steps:
1. First, highlight the five-number summary in your spreadsheet
2. Then in the top menu select the Insert chart button. You can also select Insert>Chart
3. In the Chart editor on the right side of your screen, change the Chart type to Candlestick chart
4. This will have created a box plot that displays the data you highlighted in step 1
Editing Your Box Plot
To edit your box plot either double-click on it or select the three-dot menu in the top right corner.
Then, in the Chart editor, there are two tabs where you can make changes to your graph: the Setup tab and the Customize tab
In each of these tabs, you will find settings that can change the layout and appearance of your chart.
You can edit things like:
- Which data is displayed
- How data is displayed
- Headers and labels
- Fonts and font styles
- Axis Titles
- Colors
- Gridlines and ticks
Closing Thoughts
Viewing the five-number summary in graph form via a box plot is a useful way to create a visualization that will show you the distribution of a dataset.
The key is to create your five-number summary for your data, and then to create the graph on those data points.
We recommend you take some time to try out each of the functions that I showed for getting the five-number summary (MIN, QUARTILE, MEDIAN, MAX).
Once you have those down, the rest is easy.
More Google Sheets Tutorials:
How to Create a Candlestick Chart
How to Make a Scatter Plot
How to Make a Dot Plot
How to Make a Bubble Chart