After several weeks of technology charts, I thought it would be refreshing to look at beer prices around the world. Visual Capitalist recently featured this data in a map, but it's also well-suited to a horizontal bar chart.
There are many factors that influence beer prices, including cost of living, currency values and taxes. It's not surprising to see high cost cities like Hong Kong and Singapore near the top of the list. Dubai claimed the most expensive spot largely due to regulatory issues that restrict the sale of beer. In contrast, Prague is a higher cost city with a very low beer price, likely due to high demand. The Czech Republic has the highest per capita beer consumption (see this bubble chart). Manila, the city with the lowest beer price, has a low cost of living.
Horizontal Bar Chart
I created this horizontal bar chart using Deutsche Bank data featured in this Visual Capitalist post. To make the chart more readable, I selected 25 of the cities from the report--the 12 most expensive, 12 least expensive and Chicago, which had the median price for the data set.
How I Created This Horizontal Bar Chart
- I entered the data into an Excel spreadsheet with the cities as rows and a column for average beer price.
- Next, I added a column to the spreadsheet to calculate the % change for each city compared to the beer price in Chicago.
- I inserted a horizontal bar chart into a PowerPoint slide, copied the data from Excel and pasted it into the chart data sheet.
- In the chart data sheet, I added a column and entered either a ▲ or a ▼symbol (based on the price vs. Chicago). I found these symbols in the Office Character Map application. Then, I used the switch bars and series button since my bars were entered as rows instead of columns.
- Using Bars and Series (Series tab), I de-selected Chart and selected the Data check box for the % change and symbol series.
- Next, I multi-selected the bar labels and dragged them to the left of the chart. I did the same with the two data columns.
- For a cleaner look, I hid the axes using Format Axes and added an X axis title.
- To create a legend, I added columns to the data sheet for each region and entered a very small value for this column in one row. This allowed the names of the region to appear in the chart legend.
- By right clikcing on the legend and choosing Format Legend, I changed the placement to lower right and displayed just the 4 regions.
- I changed the font colors of the data columns to red (prices above the median) and green (prices below the median).
- Next, I set the region colors shown in the legend using Bars and Series, Series, Style tab.
- Finally, I colored the bars by multi-selecting, right clicking and choosing the color from the segment drop down menu.