A box and whisker plot is a graphical representation of statistical data based on the minimum, first quartile, median, third quartile, and maximum values. It's used to visualize the distribution of a dataset to make it easier to understand and compare.
Follow the steps below to make a box and whisker plot in Google Sheets.
Start by entering your data vertically in one column such as Column A. Each row should contain one data point. For example, list test scores from A1, A2, A3, and so on.
To find the minimum value in your dataset, use the formula =MIN(A2:A16) in a new cell (e.g., B2). This function calculates the smallest number in your selected data range.
In another cell (e.g., B2), apply the formula =QUARTILE(A2:A16, 1) to compute the lower quartile. This value represents the 25th percentile, dividing the lower quarter of your data.
Use the formula =MEDIAN(A2:A16) in a separate cell (e.g., D3) to calculate the median of your dataset. The median is the middle value that separates the higher half from the lower half of the data.
In a new cell (e.g., E4), input =QUARTILE(A2:A16, 3) to determine the upper quartile. This value represents the 75th percentile, marking the upper quarter of your dataset.
Find the maximum value by using =MAX(A2:A16) in a different cell (e.g., F2).
Then, insert a new column to the left of your data for the plot's title. Right-click on the column header (e.g., Column B) and select 'Insert 1 column to the left.'
Enter your plot's title in the new column, aligned with the summary values.
Select the cells containing the five summary values (Minimum, Lower Quartile, Median, Upper Quartile, Maximum) and the cell with your plot's title. This selection is crucial for creating the box plot.
Go to the Google Sheets menu, click 'Insert', and then choose 'Chart'. This action will open the Chart editor where you can select your desired chart type.
Within the Chart editor, find and click on the 'Chart type' dropdown menu. Scroll through the options and select the box and whisker plot.
This will transform your selected data into a box plot.
If the data range in the Chart editor doesn’t match your data, manually adjust it. In the 'Data range' section, ensure that the range includes your summary values and the title for accurate representation in the plot.
Use the options in the 'Customize' tab of the Chart editor to modify your plot. You can change aspects like color, add axis titles, and adjust the scale to better represent your data visually.
Finally, analyze your box plot. It provides a visual representation of your data's distribution. The box shows the interquartile range, the line in the box indicates the median, and the 'whiskers' show the overall range of your data.
We hope that you now have a better understanding of how to make a box and whisker plot in Google Sheets. If you enjoyed this article, you might also like our article on how to embed a Google Form in an email or our article on how to create a dependent drop-down list in Google Sheets.
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.