Creating a box plot in Google Sheets is an easy way to visualize the distribution of your data. With just a few simple steps, you can make a box plot in Google Sheets to compare datasets and identify outliers.
In this beginner’s guide, we will walk through the process of making a box plot in Google Sheets.
For starters, a box plot (also known as a box and whisker plot) shows the minimum, first quartile, median, third quartile, and maximum of a dataset. This allows you to see the spread of your data at a glance.
Although Google Sheets doesn’t have a built-in box plot tool, we can create one by calculating the five summary values and plotting them on a chart.
By following this simple tutorial, you’ll learn how to make a box plot in Google Sheets to understand the distribution of any dataset.
We’ll go through the process step-by-step, using easy-to-follow examples.
But before we get ahead of ourselves, let’s start by understanding what a box plot is used for.
What is a Google Sheets Box Plot Used For?
A box plot in Google Sheets is a great way to understand and compare your data. It shows you the range of your data and where most data points are concentrated.
With a box plot, you can see at a glance:
- The highest and lowest values in your data
- The middle value (called the median)
- The values at the 25% and 75% marks (called quartiles)
This allows you to easily see where your data is clustered together and if there are any data points that are unusually high or low (called outliers).
Some examples of when box plots are helpful:
- Comparing test scores from different classes
- Seeing how data changes before and after an experiment
- Checking if two datasets have a similar range and concentration of data points
So, to put things simply, box plots give you a quick visual snapshot of your data. You can use them while exploring your data to understand the shape and distribution.
They also allow you to compare multiple datasets side-by-side visually. This makes box plots in Google Sheets a handy tool.
Copy Sample Sheet
If you’d like to follow along with today’s tutorial on how to make a box plot in Google Sheets, feel free to copy our sample data using the link below.
Creating a Simple Box and Whisker Plot Using Google Sheets
Even though Google Sheets doesn’t offer a built-in tool specifically for box and whisker plots, there’s a workaround. To serve our needs, we can adapt a candlestick chart, typically used for financial data like stock prices.
We chose to work with a candlestick chart because it is excellent for displaying the minimum, maximum, and quartile values of a dataset, similar to what a box and whisker plot does.
We will guide you step-by-step on how to convert a candlestick chart into something that resembles a box and whisker plot, minus the median line, in Google Sheets.
To demonstrate how to make a box plot in Google Sheets, we will use the following sample data:
Now that we have our sample data nicely put together. Let’s show you the steps to make a box plot in Google Sheets.
Creating a Five-Number Summary for Your Box and Whisker Plot in Google Sheets
To make a box and whisker plot in Google Sheets, you’ll need to gather five critical statistics from your data. These are:
- The Minimum Value
- The First Quartile (Lower Quartile)
- The Median (Middle Value)
- The Third Quartile (Upper Quartile)
- The Maximum Value
These numbers are crucial and should be listed in this order for your plot.
To start, we’ll input specific formulas in Google Sheets to calculate each of these values in our dataset.
Alternatively, if these statistics are already calculated in another sheet, you can bring them over (transpose) into your current sheet. Let’s break down how to find each of these values step by step.
Locating the Smallest Number (Min value) in Your Data
The smallest number in your dataset is known as the Minimum Value. You can use the MIN function to identify this number in Google Sheets. Here’s how you do it:
Step 1: Choose an Empty Cell
Under the ‘Minimum Value’ column, select the cell where you want the minimum value to appear. For this example, we will go with cell B2.
Step 2: Enter Formula
Having selected where you want the result of the Minimum value displayed, navigate to the formula bar and enter the following formula.
MIN(A2:A10)
This formula will calculate the smallest number from the range of cells you specify, in this case, from A2 to A10. If your data is in a different range, adjust the cell references accordingly.
Step 3: Press Enter
Once you input the formula like we showed you in Step 2, simply press Enter on your keyboard. The Minimum value should instantly show up in your selected cell.
Here is what ours looks like:
Determining the Lower Quartile
Having figured out our minimum value, let’s go ahead and figure out our lower quartile.
For starters, the Lower Quartile, also known as the first quarter middle value, represents the middle number of the first half of your dataset. To calculate this in Google Sheets, you’ll use the QUARTILE function. This function requires you to specify which quartile you’re interested in – in this case, the first quartile.
Here’s how to do it:
Step 1: Choose a Blank Cell
Navigate to the column where you’d like to display the Lower Quartile. For this example, that would be column C. Like we did with the minimum value, we will choose cell C2 as the area where we want our lower quartile to be displayed.
Step 2: Enter the Formula
Having selected the area where you want the lower quartile displayed, head over to the formula bar and type the following formula:
=QUARTILE(A2:A10,1)
This formula will compute the value at the center of the first quarter of your numerical data range, specified here as A2 to A10. If your data is in a different range, be sure to adjust the cell references in the formula.
Step 3: Press Enter
To generate the result for the lower quartile, simply press the Enter button on your keyboard. You should see the result in the selected cell.
Here is what we got:
Finding the Median
In the last part, we explained how to find the lower quartile, which was quite simple. Now, we’re going to teach you how to find the median.
Even though the median isn’t needed to make a box plot, it’s a crucial part of the five-number summary. So, we have to figure it out.
The median is a unique number in the middle of your data.
To find this middle number, we’re going to use something called the Median function. We’ll guide you through how to do this step by step.
Step 1: Choose an Empty Cell
As we did for the other summaries, we need to choose an empty cell in our spreadsheet. This is where we want the median result generated.
For this example, we will go with cell D2.
Step 2: Enter the Median Formula
Done selecting where you want the median result to be? Great. Now, navigate to the formula menu and type in the following formula:
=MIN(A2:A10)
Step 3: Press Enter
To get the median result, all you need to do is press the Enter button on your keyboard. Google Sheets should instantly generate the median result in the selected cell.
Check out what ours looks like:
Having found the mediaN result, we will figure out the Upper quartile in the next section.
Finding the Upper Quartile
The upper quartile represents the value at the last quarter of your numerical data, and since we need this to make a box plot in Google Sheets, we need to figure it out.
To determine the upper quartile, we’ll use the Quartile function. However, we’ll do something different this time from when we found the lower quartile. We’ll adjust the Quartile function’s second parameter to 3.
This adjustment is crucial because our goal is to locate the third quartile, which is another name for the upper quartile.
Keep reading as we walk you through how to find the upper quartile.
Step 1: Choose an Empty Cell
Start by choosing an area in your spreadsheet where you want the result of the upper quartile to be. For this particular example, we will go with cell E2, the column for the upper quartile in our spreadsheet.
Step 2: Type in the Quartile formula
Now, head over to the formula bar and type in the following formula:
=QUARTILE(A2:A10,3)
Step 3: Press Enter
Having entered the Quartile formula like we showed you in step 2, simply press the Enter button on your keyboard. The upper quartile result should be instantly generated in the empty cell you selected.
Here is what we have.
Now that we are done finding the lower quartile, we are getting close to our objective – making a box plot in Google Sheets.
The next section will show you the steps to find the maximum value in our data set.
Find the Maximum Value
The maximum value is the highest number in your data set. To identify this value, we’ll utilize the MAX function.
We’ll walk you through the process step by step to ensure you have a clear understanding.
Step 1: Choose an Empty Cell
Head over to your spreadsheet and choose an empty cell. This is where you want the result for the maximum value to be.
For this guide, we will select cell F2.
Step 2: Type the MAX Formula
To find the Max value in your data, go to the formula bar and type the following formula:
=MAX(A2:A10)
Step 3: Press Enter
With the MAX formula entered like we showed you in the previous step, go ahead and press the Enter button on your keyboard.
This action will generate the maximum result in the selected cell.
Here is what we got using our own data set.
Making a Box Plot In Google Sheets
Having reviewed all the steps to find the five-number summary, it’s time to make our box plot. Read on as we go over the step-by-step process together.
Step 1: Insert a New Column
To make a box plot in Google Sheets, we need to insert a new column in our spreadsheet. Here is how to go about that.
Right-click on the column header of column B, then select ‘Insert 1 column left’ to add a new column to the left of column B.
Step 2: Add Box Plot Title
After adding the new column to your spreadsheet, let’s go ahead and add a title for the column. Here is how to do that.
In the cell directly to the left of where your first number summary value will be, enter the title for your box plot.
For our data, we will type Data in cell B2.
Step 3: Select Cells for the Box Plot
Now, let’s get to the exciting part of creating a box plot in Google Sheets. We want to kick things off by selecting the cells housing the five summary values. Not only that, we will also select the cell that contains the box plot title.
So, for our example, we will choose cells B2:G2.
Step 4: Create the Chart
Having selected all the five summary values along with the new column we created, it is time to create the chart.
To do that, navigate to the Insert menu and select the option for Chart.
If you followed the steps exactly as we showed you, Google Sheets should create a chart that looks like this:
However, this chart doesn’t look anything like a box plot, so we need to choose the correct chart type, and we will show you how to do that in the next step.
Step 5: Choose Chart Type
Once you’ve inserted a chart into your spreadsheet, the chart editor will appear on the far right side of your screen. This is where we’ll transform the default chart type into a Box Plot.
To do this, look for the “Chart type” dropdown menu. Clicking on this will display a variety of chart options. Among these, select the “Candlestick chart,” which we’ll use to represent our Box Plot.
After choosing the option for Candle chart, your spreadsheet should instantly update to show a Box plot.
Here is what ours looks like:
Now, that’s how to make a box plot in Google Sheets. Pretty easy, right? We guess so, too.
Final Thoughts
Overall, making a box plot in Google Sheets is a straightforward process that anyone can do. By following the step-by-step instructions in this guide, you now know how to make a box plot in Google Sheets.
The key steps we covered were:
- Calculating the five-number summary (minimum, lower quartile, median, upper quartile, maximum) using Google Sheets functions like MIN, QUARTILE, MEDIAN, and MAX.
- Inserting a new column in your Google Sheets spreadsheet for the box plot.
- Selecting the cells with the five-number summary and box plot title.
- Creating a chart from those cells.
- Changing the chart type to “Candlestick chart” to make it resemble a box plot.
That’s really all there is to it. Now, you can make a box plot in Google Sheets for any dataset you want.
Remember, box plots are great for visualizing the distribution of data and spotting outliers.
We encourage you to practice making a box plot in Google Sheets with some of your own data.
Feel free to refer to this guide if you need a refresher on creating a box plot in Google Sheets.