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.

Original Data

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.

Dummy Column

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.

Setting Chart

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.

Removing Dummy Bars

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.

Fix the vertical axis

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

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.

When he isn’t playing with Excel and learning to use R better, Paul Denninger is trying to start a company. He currently manages investments for people and is on a constant search for data to analyze. He spends enough time working with nonprofits that he might make a career change and do it full time. You can see more of his work on his blog, Risk Assets.