WSJ Everest Chart
This is the first post of a new series I call “Do It in Excel”. I will be recreating data visualizations of several sources in Excel and explaining how to do it. For this post, I’ll be recreating WJS Everest Chart.
Do it in Excel series will feature charts and infographics selected by me or you. I will recreate those data visualizations using Excel and post tutorials under Do it in Excel category. So please don’t hesitate to ask for recreations of visualizations you like in Excel. I will try my best to make them all in Excel.
I saw the data visualization below on twitter, twitted by WSJ News Graphics on 28/05/2014. Because of the topic, I will call it WSJ Everest Chart.
It is supposedly located in this page but I could only find a vertical version of it when I visited the page.
Since this is a waffle chart, I decided the best way to create this visualization is to use conditional formatting icon sets. This way our chart will be responsive to changes in data.
To start things, let’s enter the data into a blank sheet.
We will also need a static range of cells which we are going to use as a reference for comparison. This range will have the same structure as our plot area with dots. But it will have numbers from 1 to 100. You can see the range in the picture to the right.
We are going to arrange 16 more ranges like this and input a formula to determine whether there will be a dot in a given cell based on our fatality data. Here is a picture of how it looks:
You can see here that my formula (bottom left) results either 100 (for to be dotted cells) or 0 (for cells to be empty) based on comparison of our based data and reference data.
I arranged column widths for cells with dots as 16px. Font size is 8pt. For label cells between dot blocks, I used 16pt. I use 55% zoom for this chart to have it look pretty. But you may use different size settings based on your screen resolution.
You can also see that I arranged a label area by merging two middle cells above each 20 cell block. I ınputted a formula in it to display dot number. This formula counts 100 values in the cells below itself and displays the count. If the cell block just below it is full, it shows nothing since the label above will display the total.
And lastly you can see the horizontal axes label is formulated to read related data series name.
Rest of the plot area consists of cells and formulas that has same styling and formulations with different range references.
Now all is set, we need to arrange our conditional formatting rules to create dot visualization. Here is how to do it:
Select formulated cells of the first column of fall and then apply following conditional formatting:
Then while cells are still selected, reopen conditional formatting menu and access “Manage Rules” Screen. Select the rule you created (only rule) and press “Edit Rule” button. This will open “Edit Formatting Rule” screen. Set options on this screen as shown below (I have marked the options that I changed):
Now your column will look like this:
Now copy any cell with a red dot and paste it to all first columns of your data series with Paste as Formatting (shortcut Menu key + R). Don’t worry about labels turning to dots, etc. We will clear their conditional formatting once we finish pasting.
Select the second column of Fall series and apply same conditional formatting but change red dot with gray dot. Than copy this formatting to other second columns of remaining series. When finished, select label cells and clear conditional formatting rules for those cells from the menu shown below:
Your chart is supposed to look like this at this point:
From here on all we need to do is formatting. Because we already have a chart that responds to changes in source data. So it will show whatever data and labels we input. Bus since we are replicating the WSJ Everest Chart in this post, I will leave the numbers intact.
- Hide columns G to J.
- Select all the cells in and around your plot area and set fill color as lightest gray.
- Partially remove borders between series (crop them from top) to create space for floating text and legend.
Insert a secondary worksheet and prepare text boxes for floating text on the chart and images for legend (I made dots just like the ones on the chart and write legend next to them on a gray background. Than copy them and pasted as picture).
Here is the final form:
You can download WSJ Everest Chart remake from here:
For any questions and request please don’t hesitate to leave a comment below.