Updated April 2023
A box plot is a handy way to visualise and compare data across datasets. But, how do make a box plot in Excel and Google Sheets? While the charts might seem complex, making them isn’t so tough.
What is a Box Plot?
A box plot also known as a box and whiskers plot, all the way to graphically represent data based on four different sections of results. These quartiles, or one-quarter data sections, are represented as a rectangle with two lines coming off of it, one from each short side of the rectangle.
These are some of the terms that come up often with box plots:
- First quartile: The median result from the lower half of the numerical data results.
- Third quartile: The median result from the upper half of the numerical data results.
- Median: The middle value of a dataset.
- Minimum or 0th percentile: The lowest result in a numerical data set.
- Maximum or 100th percentile: The higher result in a numerical data set.
- Interquartile range: The difference between the first and third quartile.
When drawn on a plot, the first and third quartile are the short sides of the rectangle. The median is represented as a line somewhere within the rectangle. Finally, to form the whiskers of the box-and-whisker plot, the minimum and maximum values are plotted, and lines extending from the rectangle to the data points are drawn.
Benefits of Using a Box Plot
Box plots come up a lot in analytics and statistics. Because of this, this particular plot is taught early on in math classes in school. Here are some of the reasons why:
- Visual summary of variation in a dataset: A box plot creates a single visual plot that allows someone to quickly review data and how it is spread out across a range.
- Visual outliers: Unlike other calculation methods, such as means, there are visual outliers on a box and whisker plot.
- Can compare multiple datasets: A box plot makes it easy to compare data sets by generating a visual graph for each dataset and comparing them on a single plot.
- Data symmetry and skewness are easy to see: The width or narrowness of the box plot, as well as the separation between the box and the whiskers, can tell someone how skewed or symmetrical their data is.
- Similar to a Bell curve: A box plot has many of the same features as a Bell curve but is graphed out in a way that makes it comparable to other datasets.
Because of these features, a box-and-whisker plot sees use in places where data analytics are important, such as in scientific research and statistics.
How to Make a Box Plot in Excel
If you need to know how to make a box and whisker plot in Excel, the good news is that it’s not too hard to do so. There isn’t a box plot generator Excel, however. Let’s go over the steps on how to create a box plot in Excel.
Step 1: Enter the Data
The first step in how to create a box and whisker plot in Excel is to enter your data into the spreadsheet. Line them up in a row for easy access later on in the process. This data will be the Excel box plot template, so ensure that the data is accurate and complete before continuing.
Step 2: Find Your Quartile Values
Next, we need to calculate our values for the box and whisker plot Excel. Thankfully, Excel has some calculations we can use instead of doing it manually:
- Minimum: MIN(cell range)
- First quartile: QUARTILE.INC(cell range, 1)
- Median: QUARTILE.INC(cell range, 2)
- Third Quartile: QUARTILE.INC(cell range, 3)
- Maximum: MAX(cell range)
Place these calculations off to the side of your previous data. Make sure you don’t overwrite your earlier data!
Step 3: Calculate the Quartile Differences
These differences will tell the box plot Excel what the scale will be for the box plot. Do the following calculations and place the data in a third column, using the cell number in place of the value listed:
- (First quartile) – (Minimum)
- (Median) – (First quartile)
- (Third quartile) – (Median)
- (Maximum) – (Third Quartile)
Step 4: Make a Stacked Column Chart
A stacked column start is how to create a box plot in excel. Well, it’s actually what your box plot will start off as. In the Insert tab, select Insert Stacked Column Chart from the Column Chart options. Right-click the new chart and select Edit to add your data as the values for the chart.
Step 5: Convert the Stacked Column Chart to a Box Plot
The last step in how to do a box and whisker plot in Excel is converting the stacked column chart to a box plot. Follow these steps to make the iconic box plot look:
- Click the bottom part of the column and then select Format to get to Format Selection. Choose the No Fill option to remove the bottom of the column.
- Click Design and navigate to the Standard Deviation option and click that.
- Adjust the Error Cap to No Cap and set the Error Percentage to 100%.
After this step, you will have learned how to create a box plot in Excel!
How to Make a Box Plot in Google Sheets
To create box plot in Google Sheets, you have to take fewer steps. Let’s look at what those steps are:
Step 1: Enter the Data
To make a box and whisker plot in Google Sheets, you start by entering the data into one column, just like with Excel. A box plot Google Sheets requires the same data as an Excel plot.
Step 2: Find Your Quartile Values
Use the same commands from Excel to create your quartile data in a secondary column for later. You’ll need these calculations for your box and whisker plot Google Sheets, just like with Excel.
Step 3: Use Box Plot Option With the Data
Highlight your quartile data and click the Insert option at the top. In the Charts options, you’ll find that there is a box plot template in Google Sheets. By clicking that, you will insert a box plot using your quartile data into the worksheet.
Summary
Excel and Google Sheets have different ways of adding a box plot. However, both of these tools do let you make this handy visual aid for data with minimal headaches.
If you need other design tips or utility guides for Excel or Google Sheets, our blog has plenty of help to offer! Feel free to check it and see what else you can learn about these handy bits of software.
Do you need to convert a Word, PPT or other kind of document to PDF?
Use Sizle’s free PDF Converter for fast and free file conversion.
Need to share
a document?
Get the time-saving document management and sharing tool used in 153 countries, free forever.
