Hello and welcome to TeqTog! This is where I try to help you with working on Google Sheets and provide you with ideas and tips so that you can achieve the best results within less time. In this tutorial article, I wanted to share how you can make a box and whisker plot in Google Sheets. So, I am going to get started!
Introduction to Box and Whisker Box Plot
A box and whisker box plot is an amazing way to present your data analysis. Through preparing a box and whisker box one can easily identify five different primary characteristics of your data; which are:
- Maximum value
- Minimum value
- The median value
- The value of the first quartile
- The value of the third quartile
As you can see, box plots provide information on the locality, spread of your data, as well as they also show you whether your data is skewed or symmetrical and closely they are grouped together.
Benefits of using a Box and Whisker Plots in a Spread Sheet
Having a box and whisker plot in your work sheet can enhance your presentation especially if you are analyzing exploratory data. They can help in the following ways:
- Box plots are a great way to visually summarize your exploratory data analysis. They let the viewer understand some main qualitative aspects about your data.
- Whisker box plots also allow you to compare data collected from different sources. Therefore, by drawing up whisker box plots, you can arrive at effective conclusions and decisions.
- Whisker box plots lets you analyze changes that take place before and after an activity is implemented.
- Whisker box plots are pretty simple to make and use, thereby, saving you time.
How to Make a Box and Whisker Plot in Google Sheets
Unfortunately, Google Sheets does not have its own tool to create box and whisker plots itself. But worry not, because I am going to show you how you can use bar charts in Google Sheets and use them as an alternate to box and whisker plots.
Bar charts/ candlestick charts in Google Sheets can display the highest, lowest and the two quartile values already. So candlestick charts covers most of what a box and whisker plot does for you.
The only differences are, candlestick charts does not show the median marker and, they display their results vertically instead of horizontally.
Now, to make a candlestick charts, you will need a list of values like the one shown below.
First of all, you need to calculate the following:
- Lowest value
- Lower quartile value
- Highest value
- Higher quartile value
Note: A box and whisker plot always contain the above information and always in that exact order.
1. Calculating the five identifiers.
2. Insert a new column on the left of the column for your minimum value.
3. Find the cell exactly on the left side of the cell that has the minimum value. In that cell, type in what you would like as the title of your box and whisker plot.
4. Hold the cell that has the title and from there select all of the cells till the cell which has the maximum value.
5. Find the “Insert” option from the top menu bar and select the “Chart” option from the drop down menu.
6. A dialogue box named “Chart Editor” should open up on the right side of your screen.
7. From the “Setup” page, find the menu that says “Chart type”.
8. You will see that the chart type offers a lot of categories. Find the “Other” category and from there, select the “Candlestick” chart.
9. Your spread sheet should generate a box and whisker plot diagram based on your data.
Note: It is important that you always serialize the five primary qualities of your data in the same order I have shown you.
Understanding the Box and Whisker Plot in Google Sheets
Now that you have got a hand on how to create a box and whisker plot in Google Sheets; it is important that you can also fluently interpret it; so that you will be able to take effective decisions based on this diagram.
- See the straight line on the top of the plot? That line signifies the maximum value.
- The measurement of the top line represents the distance between the highest value and the value of quartile 3. You can assume a skewed data from this distance.
- The top side of the plot represents the value from the third quartile.
- Therefore, the bottom side of the plot represents the value from the first quartile.
- There is also a straight line at the bottom of the box, see? The measurement of this line tells us how far away the minimum value is from the second quartile value. Just like the top whisker, you can also make assumptions about a skewed data based on the length of the bottom whisker.
- At the end of the bottom whisker, you can see the minimum value.
- As for the median value, Google Sheet’s candlestick chart does not generate this; however, you can easily draw a sensible guess. The median value will be right at the middle of the candlestick.
And there you go! I hope my small tutorial on how to make box and whisker plots in Google Sheets will help you the next time you are analyzing exploratory data.
Also Check Out Our More Google Sheets Articles on:
- Keyboard Shortcuts for Google Sheets
- How to Subtotal in Google Sheets [Easy]
- How to Use the Find and Replace Function in Google Sheets