1. […] Dataviz Challenge #3: Alternate Solution by Paul Denninger (emeryevaluation.com) […]

2. 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!

3. […] 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 […]

# Dataviz Challenge #3: Alternate Solution by Paul Denninger

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.

Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

1. […] Dataviz Challenge #3: Alternate Solution by Paul Denninger (emeryevaluation.com) […]

2. 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!

3. […] 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 […]

## Soar Beyond the Dusty Shelf Report

Most “professional” reports are too long, dense, and jargony. Transform your reports with these practical tips.

## Great Graphs

Data visualization best practices, practical how-tos, tutorials in multiple software platforms, and guest experts. Designed with busy number-crunchers in mind.