• […] 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 […]

• # Dataviz Challenge #3: Alternate Solution by Paul Denninger

Updated on: Jun 19th, 2013
Data Visualization in Excel

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.

Sr. Portfolio Manager with over 15 years managing institutional and HNW investment accounts. Deep knowledge of financial modeling and risk management. Preference for quality assets at a reasonable price that are profitable, cash flow positive and have the opportunity for margin enhancement and/or high improving ROIC. Heavy reliance on deep dive due diligence designed to understand business, financial strategy and industry dynamics paired with extensive valuation analysis. Thorough knowledge of fundamental analysis, asset allocation and statistical analysis.  Corporate financial analysis and decision making acumen stem from Directorship at a mid-sized not-for-profit. Ability to drill down to gain a detailed view of the finance function. High comfort level with cash flow analysis, financing needs, retail operations strategy, annual budgeting and balance sheet management.

• […] 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 […]

## Soar Beyond the Dusty Shelf Report

Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

## 3 Tips for Visualizing Social Change Data

I recently had the chance to be on the Community Possibilities® podcast with Ann Price. Ann owns Community Evaluation Solutions and she started the podcast as a way to connect with community leaders to talk about root causes, dig deeper into understanding social and health inequities and to connect by talking with each other instead of at each other. We connected through our mutual speaking coach and have since followed each other’s careers and were excited to talk together.

## Great Graphs in Excel

Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.

## Subscribe

Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.