WSJ Uber Chart

Here is another installation of Do it in Excel series, WSJ Uber Chart. I found this chart a while ago on twitter. It is a nice design that can be made with default Excel tools. Let’s do it in Excel.

Here is the original chart we are trying to re-create. Since the chart is published by Wall Street Journal and it is about Uber, I called it WSJ Uber Chart:

This chart shows the progression of by year as summary and by city as in detail. Color usage is also quite nice and makes this chart a beautiful one.

To replicate this chart in Excel, we will need to make two charts. A line chart and a column chart. For the line chart, most obvious choice for a tool is Conditional Formatting. To start, we need to have a dataset to apply conditional formatting. Create the dataset shown below on an empty sheet:

Select all numbers and apply conditional formatting as shown below (since conditional formatting doesn’t have much of a color option on shapes, we are going to use closest colors available):

Then set cell color to light gray and dark gray as shown below (I also set font size to 14 for city names and set vertical alignment to center):

Now we are going to draw borders. Set border color to same light gray of the background and select line style as the thickest (last option). Then click Draw Lines button and draw borders manually. It is not as time-consuming as it sounds. Here is our chart with borders drawn:

Now it is the column chart’s turn. We need a dataset for this chart too. Create the dataset shown below in the cells that are not around the chart (on another sheet).

Select this data and insert a Clustered Column Chart. Do following adjustments:

• Select series named “Orange” and set is as “secondary axis”.
• Set Gap Width option for both series to 20%.
• Click on primary axis (left one) and delete it.
• Set max value for right axis to 300.
• Change series colors to match with colors of the line chart (dots).
• Delete chart title and legend.
• Click on gridlines, set color to black, set dashes to “round dot” and set weight to 1pt.
• Set chart background color to “No Fill” and shape outline to “No Outline”.
• Add/Delete/Format labels to match with the original.

Let’s check where we are at, at this point:

Now we are going to insert two textboxes to finalize our chart. Insert one textbox and set fill color to same gray of the background and shape outline to “No Outline”, then make a copy of it. Fill them with chart title and detail as seen in the original chart and place them as seen in the picture below (I made the lower box transparent after it blocked the gridline):

Now you can fill the text parts above the charts and mission is completed. You can insert circles to the beginning of last two lines and color them accordingly to act as bullets. Here is the finished product:

For this exercise, we used conditional formatting, chart formatting and general formatting tricks like painting background, hiding stuff with shapes, etc.

If you want this chart to behave as a whole and cannot be editable, you can use Linked Picture trick to have a one piece and solid WSJ Uber Chart.

If you like to download this chart: Download WSJ Uber Chart