In this tutorial, you will learn how to make a box and whisker plot in Google Sheets.
How To Make A Box And Whisker Plot In Google Sheets
A Box and Whisker Plot is a graphical tool for depicting variability in a data set.
They’re easy to read and can be a powerful tool for interpreting data. Additionally, Google Sheets makes it simple to add them to your spreadsheet
Data Setup
A five-number summary of the data set is required to generate a Box and Whisker Plot. The five numbers are:
- First quartile
- Third quartile
- Median
- Minimum
- Maximum
The first and third quartiles are used to graph the central box portion of the chart, and the minimum and maximum values to plot the whisker portion or the vertical lines extending above and below the box.
The median is required to plot the centerline for a Box and Whisker Plot, but Google Sheets uses a similar charting method that doesn’t use the median: The Candlestick Chart.
Follow these steps to calculate the four required numbers:
Step 1
It’s good practice to start out by adding headers for your numbers.
Type in the following headers with space beneath them: Minimum, First Quartile, Third Quartile, Maximum
Step 2
Under the Minimum header, type the following formula: “=MIN(A:A)”. Where your data set is stored in column A
Step 3
Under the First Quartile header, type: “=QUARTILE(A:A, 1)”
Step 4
Under the Third Quartile header, type: “=QUARTILE(A:A, 3)”
Step 5
Under the Maximum header, type the following formula: “=MAX(A:A)”
Step 6
If you have more than one data set to plot, repeat Steps 2-5 for each data set, adding new rows beneath the headers for each data set
Step 7
The last step is to add a column of labels.
Add a Labels header beside the existing headers and populate the column with labels for each of your data sets. Do this even for a single data set
Step 8
Your data is now ready to be plotted
Inserting a Candlestick Chart
Google Sheets doesn’t support true Box and Whisker Plots, but Candlestick Charts are almost identical.
Here’s how to insert a Candlestick Chart:
Step 1
Select range containing the four calculations and labels, omitting the headers, then open the Insert menu and select the Chart option
Step 2
In the Chart Editor sidebar that appears, click on the Chart Type dropdown menu and select the Candlestick Chart option
Step 3
Your plot will be inserted into the Google Sheet
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to make a box and whisker plot in Google Sheets. Want more? Check out all the Google Sheets Tutorials.