Note from Ann: Last week I shared a dataviz how-to guide. Paul Denninger has a great alternative solution to share! He uses hidden bars, also known in the Excel community as floating bars or white bars, to build “two” charts in one. Enjoy!
Getting excel to do complex work is easy; making the output look nice is a different matter. I took the challenge to recreate Ann’s graph because I’m always looking for better ways to display information.
The solution required a stacked bar chart, so the only problem was lining up the starting point for the second dataset. A buffer needed to sit between the end of the first set of bars and the start of the second so that it would force the second set to line up correctly. Hidden data had to fill the gap.
Here’s how I did it.
First, start by inputting the original data. Sorting the rows from large to small will help make the chart easier to build.
First, input the original data
The next step is creating the dummy data. Insert a column between the two existing datasets and populate it by subtracting the first column from “1.” This gives you a series of percentages that start at the end of the first bar and end at “1.” The problem of lining up the second dataset is solved at this point.
Create a column of dummy data
With all of the data in place, creating the chart is easy. Use a stacked bar and include all 3 columns.
Insert a stacked bar chart
Once the chart has been built, it’s time to hide the dummy column. Highlight the red data series and choose the format menu. Fill the series with the same color as your background. This makes the bars blend into the background and leaves them invisible. Do the same for the data labels. The dummy information is now hidden and your second dataset lines up properly.
Make the dummy data invisible
The y-axis still needs to be fixed since the values are organized from low to high. Invert it by highlighting it and opening the format axis menu. Tick the “categories in reverse order” radio button to have the bars arrange themselves from high to low.
Flip the y-axis
Here is the primary display difference between this solution and Ann’s. Due to the hidden data, the x-axis begins at 0 and goes up to 200%. Since it’s a big challenge to make repeating axes in one chart, and one that I haven’t solved, it’s probably best to remove the x-axis altogether. Since the data values are shown in the bars, removing the axis should not make data interpretation more difficult. In fact, it will be easier for people looking at the chart since they don’t have to rescale the second data series in their heads.
Finally, decrease the gap between the bars and invert the fill and text colors for the overall bar. This makes it stand out from the other series. It’s personal preference whether you want to make the data labels bold or not.
Expand, invert the colors and remove the x-axis
Your chart is finished and you have improved on excel’s built in data presentation immensely. Other than the problem with the x-axis, this solution gives the same result as Ann’s. It’s a visually appealing way to compare two or more groups across the same categories.
3 Comments
[…] Dataviz Challenge #3: Alternate Solution by Paul Denninger (emeryevaluation.com) […]
Thanks for the pingback. Love what you’ve done here as well. Really great way to visualise the data and of course with this method, you can compare more than two data sets. Great post!
[…] in previous posts like this one, or b) use floating bars, a strategy demonstrated in previous posts like this one. Stephanie Evergreen blogged about strategy B a few weeks ago and her explanation is pretty […]