How to make a boxplot by group in Excel

Data visualization is not an optional step in data analytics, as Anscombe’s Quartet makes painfully clear:

Viagra kopen op het internet https://legdelink.nl/viagra-kopen-zonder-recept/ welke man heeft het niet eens geprobeerd. Het ontwijken van goedkope illegale aanbieders is een sport op zich, maar een alternatief zoals het kopen in een apotheek of winkel is er helaas niet.
De ervaringen van onze klanten liegen er niet om: Alles is absoluut van hoogwaardige kwaliteit. Daarbij is een doos bij ons snel opnieuw besteld en bezorgd dankzij dagbezorging. Pakketjes zijn klein genoeg om door de brievenbus te passen. Thuis blijven voor het bezorgmoment is onnodig. Het is een productnaam voor Sildenafil, bedoeld om prestaties in bed te verbeteren. Het bekende blauwe pilletje helpt tegen erectiestoornissen en enkele hartaandoeningen. Heren gebruiken het medicijn om langdurige erecties te kunnen waarborgen, zonder last te krijgen van verslapping.

To understand a dataset’s distribution, for example, seeing is truly believing.

While the Anscombe’s quartet post makes use of Python to demonstrate that famous statistics example, we’ll use Excel here to build an everyday data visualization: the boxplot, or box-and-whisker plot. This plot is used to visualize the distribution of a variable, making particularly clear its quartiles and any outliers.

To learn more about boxplots, check out this post:

Visualizing the distribution of housing sales prices

For this exercise, we’ll take a look at housing sales prices.

Download the exercise file here

You’ll notice the dataset contains an index column and is enclosed in a Table. I do this to all my Excel datasets and you should too. Read more about how and why to do that here:

Univariate boxplot

First we’ll start by visualizing the distribution of all values in the housing column. Because we are only visualizing one variable at a time, this is considered a univariate visualization.

The univariate boxplot is easy enough highlight the data in column B and select the boxplot chart option (Excel calls it “Box & Whisker”). After that, I’ll cut and paste the boxplot to a new worksheet:

We’ll do a few more things to spruce this up:

1. Give the chart a helpful name (not Chart Title!)
2. Format the y axis in currency (double-click on the y axis to get this started)
3. Remove the x axis entirely (double-click and press Delete this time) … the number 1 is not helpful and since there’s only one entity along the x axis, we really don’t need to label it.

Bivariate boxplot

OK, now for a bivariate boxplot: what if we wanted to visualize the distribution of price by airco? We’re now getting into a bivariate boxplot, and this can be a little trickier to swing in Excel.

It’s not so bad with untidy data…

The crazy thing about this is that it’s really easy to make a bivariate boxplot in Excel when your data is not tidy. To understand what I mean, take a look at the below example from Spreadsheeto’s awesome tutorial on boxplots in Excel.

Do you see the difference in how the data is laid out on the left versus the right? On the left, we’ve got three columns where it should really just be one: subject. You may know this as “pivoted” data; tidy is another way to put it.

No fear, there is a way yet to make the bivariate boxplot: the “grouping” variable must go immediately to the left of the numeric variable. So in this case, you’re going to have to move airco in front of price, as has already been done in the previous screenshot. From there, you’ll highlight both columns and insert another boxplot:

Make the same cosmetic adjustment to the bivariate boxplot as you did to the univariate (except this time there’s a reasonable x axis, so don’t delete that) and you should have something like this:

Get more dataviz… get more Excel

This isn’t the most straightforward “hack” to get a desired Excel chart, and to be honest I’m not crazy about having to physically move data around to get the chart I want. That’s why I really suggest at some point learning about R and Python for more reliable visualization and analysis. To get started as an Excel user, check out my book Advancing into Analytics:

All that said, I do live in the real world and understand the staying power of Excel (and that there’s staying power for a good reason!). Looking to learn more about data visualization in Excel? I’ve put together a learning path for just that. Get started here:

What questions do you have about data visualization in general or specifically with Excel? Let me know in the comments.