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

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    How to Make a Small Multiples Bar Chart in Excel: A Single Stacked Bar Chart

    Updated on: Jun 19th, 2013
    Data Visualization in Excel
    , , ,
    Collage of clipboard, clock, people, calendar and calculator.

    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.

    Screenshot of a Microsoft Excel spreadsheet with data entered into it.

    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.

    Screenshot of Microsoft Excel spreadsheet with data entered into it including a column with 'dummy' data.

    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.

    Microsoft Excel spreadsheet that includes data and a stacked bar 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.

    Microsoft Excel spreadsheet with data and bar charts.

    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.

    Microsoft Excel spreadsheet with data and bar charts showing how to 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.

    Microsoft Excel spreadsheet with data and bar charts that have been expanded, colors inverted and x-axis removed.

    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.

    More about pdenninger
    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.

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

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You Might Like

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

    Enroll

    How to Make Interactive Dashboards in Excel

    Interactive (a.k.a. dynamic) dashboards are a great option for technical audiences that have the time and interest to explore the data for themselves. In this blog post, you’ll learn about the four pieces needed: Datasets, linked to pivot tables, linked to pivot charts, linked to slicers.

    More »

    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.

    Enroll

    Subscribe

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