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.
FAQ: Understanding and Creating Box Plots in Excel and Google Sheets
1. What is a Box Plot (Box-and-Whisker Plot)?
A box plot, also known as a box-and-whisker plot, is a graphical representation of data that divides it into four sections: the first quartile, third quartile, median, and outliers. It consists of a rectangular box with lines extending from its short sides, representing these data points.
2. What are the key terms associated with box plots?
Important terms related to box plots include:
- First quartile: The median of the lower half of the data.
- Third quartile: The median of the upper half of the data.
- Median: The middle value in the dataset.
- Minimum (0th percentile): The lowest data point.
- Maximum (100th percentile): The highest data point.
- Interquartile range: The difference between the first and third quartiles.
3. What are the benefits of using a Box Plot?
Box plots offer several advantages, including:
- Providing a visual summary of data variation.
- Identifying visual outliers.
- Facilitating the comparison of multiple datasets.
- Easily visualizing data symmetry and skewness.
- Having similarities to a Bell curve for comparison with other datasets.
4. How can I create a Box Plot in Excel?
To create a box plot in Excel:
- Enter your data into a spreadsheet.
- Calculate quartile values using Excel functions.
- Calculate quartile differences.
- Create a stacked column chart and adjust its formatting to resemble a box plot.
5. Are there any differences in creating Box Plots between Excel and Google Sheets?
The fundamental concept of creating box plots is similar in both Excel and Google Sheets, but specific steps and formula syntax may vary slightly. Google Sheets offers a more straightforward approach for generating box plots.
6. How can I make a Box Plot in Google Sheets?
To create a box plot in Google Sheets:
- Enter your data into a column.
- Calculate quartile values using Google Sheets functions.
- Use the built-in box plot chart option to insert a box plot into the worksheet.
7. Can I customize the appearance of a Box Plot in Excel or Google Sheets?
Yes, you can customize the appearance of your box plot by adjusting chart formatting options in both Excel and Google Sheets. This includes modifying colors, labels, and chart elements.
8. What are some practical applications of Box Plots?
Box plots are commonly used in analytics, statistics, scientific research, and data analysis to visually summarize and compare datasets, identify outliers, and assess data distribution.
9. Are there any online tools or software for creating Box Plots?
Yes, there are various online tools and dedicated statistical software packages available for creating box plots. These tools often offer more advanced customization options and are useful for larger datasets.
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.
File sharing made simple.
Fast, secure document sharing with live analytics. Trusted by teams in over 170 countries.
