In this blog, I’ll share some tips on how to create a visually impactful bar chart in Excel that does not come out of the box. There are several steps we need to take in order to take Excel’s built-in charts and make it look like this:
This is bar charts within bar charts! The wider bars show totals for all crime categories, while encapsulating each of the crime category bars inside them. Each bar is actually 4 bars showing 4 important metrics for each sate. That’s a lot of information, and yet the chart is easy to read and doesn’t look messy or overwhelming. That’s why I think this is a powerful chart. But not without some work, which I’ll discuss below.
I’ll start with some semi-made-up crime data for 2018, 2019 by large cities of each state which I obtained from FBI site. I say, semi-made-up because while most of the data is used as-is from FBI site, I had modified some data slightly for technical illustration purposes, so don’t use this data for any other purpose.
The data sheet downloaded from there is as below:
As you see, the data is arranged by State, and then alphabetically by cities in the state…in which the years can appear in any order. The population is of the largest cities, and not states.
From this data, I extracted and grouped the data so that I have a table of selected data only for top 5 most populous states. For some steps on re-shaping and cleaning up of data, see my older post Organizing Raw Dataset To Creating Impactful, Interactive Visualizations
In this example, I’m using year 2019’s three types of crime stats (violent, murder, property crimes) for CA, TX, FL, NY, and IL only.
Once the data cleanup and reshaping is done, we move on to the core part of this post: creating the chart as shown above.
Here are the steps I had to take to create the final chart:
1. Create a new column (I call it Bottom Pts) in the curated data table with just zeros (this will be necessary as we’ll have to add error bars or up-down bars from bottom of x axis to the top of the values and bottom of x axis are 0s for our dataset)
2. Create a 2D clustered column for the table’s data
3. Turn that into a combo chart and render the Total and Bottom Pts as lines on the chart
4. Create the Up/Down bars that’ll go from bottom (0s along x axis) to the top of the Totals as bars
5. Resize the width of the Up/Down bars above so that they completely obscure all types of crime bars (we’ll show them later)
6. Remove the lines (Totals, Bottom Pts) as needed them only to create the Up/Down bars
7. Now create fix the transparency of the big bars that’s obscuring the other bars ‘behind’ them. The transparency can be set to 25%+ depending on the color of the bar you choose. I also choose 2-color gradient instead of one solid color for better effect.
8. At this point, we have all the bars showing but it’d be nice to see the total values above each bar. So, add Data Labels by adding the chart element from [+] menu next to the chart
9. Finally, we don’t need to make the chart busy by keeping redundant visual elements…so, remove the y axis and gridlines.
And we get this beautiful chart:
I’ve created a quick animation for the steps described to show how the chart should look after each step:
(Chart at each stage is shown below for 2.5 seconds; it will loop)
Hope you found this interesting and helpful!
▛ This post is not meant to be a formal tutorial, instead it is to offer key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed. If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible. At the very least, it’ll encourage me to share more posts/tips/knowledge in the future. ▟