# How to Make a Box Plot in Google Sheets (The Best Guide in 2024)

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.

Copy Sheet

## 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

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:

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:

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:

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:

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

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.