Oil continues to be the top fuel source globally, but there are significant mix differences between regions as shown in this marimekko chart. While oil was the leading fuel source in North America, Europe, South & Central America and Africa, coal was at the top in Asia Pacific and natural gas led the way in the CIS and the Middle East based on 2017 consumption data from the BP Statistical Review of World Energy.
I was curious that Asia Pacific was 43% of the global total (and growing at 3%) so I suspected that a follow-up question would relate to how much of that was China versus other countries. I created a second marimekko chart using the data to show total energy consumption by country and region. You'll notice that China is the largest energy consumer and it is 55% of the Asia Pacific total.
How I Created the Marimekko Charts
I pulled the data for this chart from a table in a PDF on the BP website. First, I copied the entire table and pasted it into a new Word document. Then, I highlighted the entire table (Ctrl C) and selected Table, Convert Text to Table. I copied this table and pasted it into an Excel workbook. Next, I deleted unneccesary rows and I checked that the data was properly aligned in columns. I added a column to calculate YOY growth and then I copied the rows I wanted from the data (total rows for each region), and pasted them into another worksheet. Using this small subset of the data, I added a row to calculate % of Total for each region and a column to calculate % of Total for each fuel type. Finally, I copied this data range and pasted it into the Mekko Graphics chart datasheet.
Determining How Data Will Appear in the Chart
Using Bars and Series from the Mekko Graphics Edit Chart ribbon, I created an Other series for this chart to consolidate the small Nuclear and Renewable series. I also set the % of Total and YOY Growth series to be data rows and formatted them to show as percentages. On the Bars tab, I set the % of Total series to be a data column and formatted it to show as as percentage. Under Options, I chose to sort the bars with the widest on the left.
Formatting the Chart
I included the series name and the calculated percentage as the labels in the chart. To manage the small labels in each bar, I multi-selected them (using Ctrl+ Click) and chose Annotate from the right click menu. To include the data column value for the Other series, I went back to the data sheet and added a calculation (Nuclear+Renewables) in the data column cell. I adjusted the chart margins to make room for the data column and data rows and then I added a Y axis title by right clicking on the axis and entering the title into the Axis Title box in the floating menu.
I followed similar steps for the second marimekko, but I included values and calculated percentages as labels to make it easy to compare consumption for different countries. I chose to color the chart by bar (Format Chart, General, Coloring Mode By Bar) to highlight differences between regions.
Take These Charts and Make Them Your Own
Here are some other posts that may interest you.