How to Draw Boxplot in Google Sheets
You might also like:
- CoinStats: a portfolio manager for tracking crypto holdings from multiple wallets and exchanges
- Best Crypto APIs: a comprehensive list of the best APIs for cryptocurrency traders, hodlers, and devs
Our Crypto API Integration Tutorials
Binance API | Coinbase API | Coingecko API | Coinmarketcap API | CoinAPI | Bitmex API
What is a Box Plot?
A box plot (or box and whisker chart) is a way of visualizing numerical data through their quartiles. It is often used by marketing and sales teams to plot sales, inventory, and other metrics.
People who participate in stock or crypto market trading may find box plots similar to candlestick charts. That's because they are structured the same way — middle boxes with lines (called whiskers) extending from them on opposite sides.
A box plot shows the following points of data:
- Minimum
- Lower quartile
- Median
- Upper quartile
- Maximum
These graphs show the distribution of a dataset, how much values vary, and whether the dataset is evenly distributed or asymmetric. It is a simple method of visualizing data, but it has very useful real-world applications.
Box Plots vs. Candlestick Charts
While box plots may look similar to candlestick charts, the 2 are used for very different things. Candlestick charts are most often used in the finance industry for plotting stock prices. They are mainly used in the vertical position, which makes them look like candlesticks, hence the name.
Box plots, on the other hand, tend to be used in non-finance industries, often for analyzing sales performance, inventory, and even sports scores. They can be displayed horizontally or vertically.
How to Create a Box Plot in Google Sheets
1. To create a box plot in Google Sheets, first you must prepare your data. Lay your data out neatly onto a column on your sheet:
2. Prepare the aforementioned points of data — the minimum, lower quartile, upper quartile, and maximum. Google Sheets lets you use special functions to get this data without having to crunch your own numbers.
Minimum:
=min(A2:A11)
Lower quartile:
=quartile(A2:A11,1)
Upper quartile:
=quartile(A2:A11,3)
Maximum:
=max(A2:A11)
Just replace "A2:A11" with your own data range to compute the corresponding values for your own box plot. Enter these functions along a row on your sheet and set a name for each row on the far left column. You can add as many rows to your data set as you like — each one having its own box-and-whiskers.
These values will be the foundation of your box plot.
3. Now that you're done preparing your data, create your chart. Go to Insert > Chart then change your Chart Type to Candlestick Chart. Your chart should look like this:
You might wonder why we're using a candlestick chart instead of a box plot after establishing the difference between the two charts — and the reason for that is because Google Sheets doesn't have box plots. This method is the next best thing.
The only issue with this method is that you can't see the median since candlestick charts don't use that data. The good news, however, is that the essential data is still there. Visualizing the interquartile range using this method should be enough for most use cases.
Automating Box Plot Data in Google Sheets
If you use box plots everyday and find yourself regularly importing data and pasting it into Google Sheets for visualization, it's important for you to know how easily you can automate that process.
Just connect your Google Sheet to your data source's API using the Apipheny add-on. Apipheny sends the data straight to your sheet, and your chart updates automatically. Doing this lets you skip all the manual work so you can focus on what you do best.
More API Tutorials
How to Draw Boxplot in Google Sheets
Source: https://apipheny.io/creating-box-plots-in-google-sheets/