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:
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.
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.
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.
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.
If you use box plots everyday and find yourself regularly copying 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.
Schedule requests for automatic updates
Reference cell values in requests
Stack multiple URLs in a single request
Run all saved requests at once
Crypto API tutorials and more:
Sponsored by Zink Virtual Tip Jar & Rewording.io Paraphrasing Tool
Accept donations on Twitch | Accept donations on Discord | Accept donations on YouTube | Accept donations on Telegram | Accept donations on Twitter | Accept donations on TikTok | Accept donations on Instagram | Accept donations on LinkedIn | Accept donations on Facebook | Accept donations on your Website