# Joyplot in Excel

Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.

Peak time for sports and leisure #dataviz. About time for a joyplot; might do a write-up on them. #rstats code at https://t.co/Q2AgW068Wa pic.twitter.com/SVT6pkB2hB

— Henrik Lindberg (@hnrklndbrg) July 8, 2017

First let me share the final outcome.

### Joyplot in Excel – Peak time of the day for sports and leisure

Here is the final overlapped area chart with a bit of formatting thrown in. It is a pretty close imitation of Henrik’s original chart. Click on it to enlarge.

### Creating Joyplot in Excel – Tutorial

As you can guess, the chart is a ** just an overlapped area chart** (ie each area sits behind another, unlike

*stacked area chart*where they are umm, well, stacked!)

Let’s start with a look at data. Henrik’s original data has 10,656 rows, each row containing activity name, time and p value – how much survey respondents enjoyed [@activity] at that time.

Here is a snapshot of first few rows.

### Scrubbing and re-arranging the data

As you can see, while this format is excellent for storing, it is very tedious if we want to make one chart with all series. So let’s scrub.

- We need to figure out if an activity should be included or not. I am using the same criteria as Henrik’s. Exclude activities with p value less than 0.003 or activity title “Playing sports n.e.c. *” (not elsewhere classified)
- To do this, we first pivot the data on activity and max(p). Then filter this pivot two ways – max(p) >=0.003 and label not equal
*Playing sports n.e.c. **

Tip: You may need to enable multiple filters per field in the field settings of row labels. - We will end up with
*28 activities*. - Then add a helper column to original table that looks up the pivot and tells if an activity should be included or not

- To do this, we first pivot the data on activity and max(p). Then filter this pivot two ways – max(p) >=0.003 and label not equal
- Add two more columns to original table to tell peak time and modified time. This will help us in rearranging and sorting the data. Modified time just moves time by 3 hours (Henrik’s chart is plotted from 3AM to 3AM). At this stage our data looks like this:

- Now, pivot the data once again. This time,
- exclude activities by using report filter on include? column.
- Set up peak and activity in row labels area, modified time in column labels area and p in values area.
- Arrange the report in tabular format, turn off sub-totals.
- We get this:

- Calculate normalized values by dividing each p value with maximum p value for that activity. We can use another range of 28×288 cells to do this. We get this:

It will become clear once you look at the charts.*The next 2 steps may seem confusing.*- Define an offset value. Start with 0.5. You can change this later. In a separate 28×288 cell range, calculate gaps by multiplying offset with position of an activity. Something like this:

- Now, finally calculate activity + gap values by adding up respective cells in each of the 28×288 ranges. We get this:

**At this stage, our data is a shape ready for visualizing.**

### Creating and formatting overlapped area chart

The chart creation process has 5 steps.

- Select the 28×288 range of cells created in step 7 and insert an overlapped area chart.
- Now, copy the gaps range (created in step 6 above) and paste them on to area chart as new series (just ctrl+c your data and select the chart, press ctrl+v)
- Adjust the order of series so that each activity is sandwiched by appropriately named gap series
- Tip: adjusting 56 series is painful with the chart select data > move series up/down buttons. Instead, just select the series, look at formula bar. The SERIES formula has last parameter as order. Change this number. It is easy to figure out the number once you try a few.

- Change all gap series fill color to white. This instantly creates the floating area chart effect.
- Change the colors of activity series. Apply white / off-white border to these series. Your joyplot is ready.

**Quick overview of the chart creation process:**

Let’s examine the result of each those 5 steps with a smaller dataset so you can see how everything fits together. Here is the data for this example:

- Create an overlapped area chart with activity+gaps data. We get this:

- Add gaps as new series to chart. You get this:

- Move the gap series so that they sandwich activity series. Use Chart Data > Move series up/down buttons or SERIES formula

- Apply white color fill formatting for gap series. This creates floating area chart effect as below:

- Finally, format the chart by apply some colors and border formatting etc.

So there you go. The final outcome does look joyful.

### Alternatives to Joyplot

While joyplot is awesome, it is not easy to make. Fortunately, there are a few simpler alternatives that we can whip up in Excel as soon as you have either the pivot or normalized values. Below I have shown two such examples. Read about sparklines or conditional formatting heatmaps for more.

**Joyplot alternative – using sparklines:**

Tip: to get axis on your sparkline, just type the times separated by a single space. Then go to format cell (ctrl+1) and set horizontal alignment to distributed. Viola, Excel will fill the cell by adjusting spaces.

**Joyplot alternative – Conditional Formatting Heatmap **

### Download Joyplot Workbook

**Click here to download Joyplot Excel workbook**. Examine the data scrubbing formulas, pivot and chart settings to learn how this is created.

If you are familiar with R, then go thru Henrik’s R code. It is much shorter than the Excel gymnastics we did with circular pivot table referencing. That said, some of the data re-arrangement could be done with same ease in Power Query too.

### Your thoughts on Joyplot?

The only step we missed in Excel implementation is moving average smoothing of the area charts. It can be easily added as a step between 3 and 4 in data stage.

How do you like Joyplot? Would you create something like this for your business / personal data? Share your stories and thoughts in the comments section.

**More joy for you…**

If you love this, you are going to enjoy these charts too.

### Leave a Reply

How to add a lot of Goal Seeking to a model |
Extract currency amounts from text – Power Query Tutorial |

## 18 Responses to “Joyplot in Excel”

Hi Chandoo,

Good work, as always. Thanks for getting this done...this is awesome 🙂

My mouth is wide open ever since I saw this...Can anyone help me?

Where's "Making love"? 🙂

Awesome, dude.

Impressive, but feels a bit like back-converting Qlik into Access.

[…] http://chandoo.org/wp/2017/07/12/joyplot-in-excel/ […]

Hi Chandoo,

Beautiful, beautiful post. As always.

Thanks a lot!

It's a beautiful chart and I admire your Excel Greekery for making it.

As some values are obscured by others i think your 'simpler' alternatives are possibly more usable.

If you happen to find a doctor for that Ruda, please let me know 🙂

To Jeff, it is indeed an activity that also fits the criteria of not lasting long enough to be taken into account.

Totally agreeing with Marc. You must be Chandoo not only to wonder if you can create such a chart in Excel, but also for finding out the how to do it. (What would Jon Peltier say about it?).

In Power BI one can create R-scripts as well. A re-challenge for Chandoo?

awesome admirable charts i want to try this chart making too

Awesome work

Fantastic!

I would place this chart in "looks-cool-but-what-does-it-tell-me?" category. Can't really glean any valuable information from it, but if you focus one activity, say "doing aerobics" (which is partially blocked by the "walking" chart), then this chart could be the beginning of an in-depth analysis to study consumer behavior. If I owned a chain of workout centers and this data reflected behaviors of people that lived in my region/state/local, then it would definitely interest me.

Very nice!!!! it's awesome...

Nice post and i try it l.....i think..... You Write a Big Post In This Day!... So I Say Awesome...

Hi chandoo!

I studied the Joyplot Excel workbook. and had a question of Modified time. I think the formula should be:

=IF([@time]>1260,[@time]-1260,[@time]+180)

(Henrik’s chart is plotted from 3AM to 3AM) so time=0 ? modified time=180

I've been saving the email I received on this posting until I had time to read it. Just finished, and wow! do I love it! I'm not sure I'd ever go through the process of creating it, but just walking through the process with you inspires me with creative ideas. Which is exactly why I enjoy your posts and your emails so much! Thanks!!

can you help to have employee database using excel with nice in mtg presentation

Hi Chandoo,

i would like to count number of days i spent in Hotel (i have start and End date) and spill over days should be factored into subsequent Financial year.

I have done using if function and range, unfortunately if the start and end date changes, spillover is not factored into subsequent FY coz range is not dynamic