9 Comments

  • MpPierce says:

    Great post, and I especially like seeing all of the options you presented in the video. Thanks for that! I had 2 how-to questions for you: (1) In the “handout” version of the chart, do you hardcode the values (198 total, 140 males, etc.) and the color formatting? Or is there a way to do that via formulas? I’m thinking, because of the formatting, that it’s done by hand. And (2) for the labels in the first bar…did you do those manually or with formulas? I know you can display both the series name as well as the value, but when I do that, it displays “males 29” instead of “29 males.” Thanks!

    • Ann K. Emery says:

      For the handout, I inserted a text box and wrote a few sentences by hand. If this graph were staying inside of Excel, you could write a formula that automatically tallies the annual amounts and you could use conditional formatting to automatically color-code those numbers.
      For the labels in the first stacked bar chart, I think I just typed “males” and “females” into the boxes with the numeric labels. I often write formulas that concatenate the numbers and the words together and then tell the chart to use those concatenated cell ranges as the labels.

  • […] K. Emery shows the steps for planning and building an Excel chart, from the sketching phase, to the completed chart. What works, and what doesn’t? […]

  • Your information is so helpful – and your instructions great — amazing you speak so quickly yet are easy to understand. I really appreciate your generous sharing of information. Alicia

  • Ione Farrar says:

    Question, I am recreating the stacked vertical bar chart. On the data labels, it shows up as Female 15, rather than 15 Female in your example. How do you get excel to place the label in that order?

    • Ann K. Emery says:

      Hi Ione, good question. I added numeric labels to all the columns (just like you would normally do). Then, I double-clicked inside the labels on the far left and manually typed “youth,” “females,” and “males” into the boxes.

      • Ione Farrar says:

        Brilliant! They even copy into another document as is. Plus, the trick of the transparent total is fantastic. I have struggled over the years with putting total, ending up with misaligned text boxes. Thank you!

        • Ann K. Emery says:

          The transparent total ends up saving a lot of time in the long run. I used to create text boxes above each of the columns, which always end up misaligned and sloppy. Glad to hear that you gained a new trick!

  • Leave a Reply to Alicia Stahlberg Cancel reply

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

    Wondering whether pie charts are okay? Ready to move beyond pie charts, but not sure what to use instead?

    Let’s walk through a real-life example!

    A few weeks ago I was in Indianapolis with the juvenile detention alternatives initiative. This group wanted to graph:

    • how many youth went to juvenile detention centers,
    • by gender, and
    • by quarter.

    Brainstorming

    There are a dozen ways to graph each dataset. For this data, we could utilize:

    1. table
    2. heat table
    3. quarter-by-quarter pies (well, not the best choice)
    4. one year-end pie
    5. line graph with numbers
    6. small multiples line graphs with numbers
    7. clustered bar
    8. small multiples bar
    9. stacked bars with numbers
    10. stacked bars with percentages
    11. stacked area with numbers
    12. stacked area with percents
    13. and probably a few more that I haven’t thought of yet

    Sketching a dozen options for displaying quarterly breakouts by gender.

    Before: Quarter-by-Quarter Pies

    Naturally, the quarter-by-quarter-no-way-these-don’t-work-pies were currently being used by a few people in the room. I adjusted the wording from youth going to detention centers to youth being served so that this example is relevant to even more of you.

    The quarter-by-quarter pies are off the table. According to my pie chart guidelines, gender breakdowns are one of the only times it’s okay to use a pie chart (nominal variable, just a few slices, etc.).

    But it’s really hard to make comparisons across multiple pies. Your eyes would have to jump from the 1st quarter pie to the 2nd quarter pie to the 3rd quarter pie to the 4th quarter pie and back again. I’m tired just thinking about it.

    Pie chart makeover: Quarterly breakdowns by gender. Here's the before version.

    After

    After we sketched ideas and discussed the pros and cons of each approach, we decided that stacked bars with numbers were the most promising. Stacked bars can display the number of males, females, and the totals. They’re also easy to label (compared to, say, a stacked area chart). We used vertical bars because time is ordinal.

    My rule of thumb is to display numbers for anything less than 100 and percentages for anything over 100. Fewer than 100 youth went to these detention centers each quarter, so we displayed numbers.

    For a Slideshow: Stacked Columns with a Short Title

    Here’s the after version for a slideshow:

    Pie chart makeover: Quarterly breakouts by gender. Here's the after version for a slideshow.
    For a Report: Stacked Columns with a Subtitle

    Here’s the after version for a handout or report, which intentionally contains a bit more explanatory text.

    Pie chart makeover: Quarterly breakouts by gender. Here's the after version for a slideshow.

    Bonus: Make This Yourself

    No, I didn’t add the totals to each quarter’s bar chart with text boxes. Yes, that’s possible. It’s also a ton of work to crunch the numbers, create text boxes, and center them perfectly above each bar. This is a stacked bar chart with three segments: males, females, and totals. The total segment has a transparent fill so it looks invisible.

    If you want to explore the strategy in more detail, watch the video or download the template.

    Bonus: Download the Templates


    Purchase the templates ($5)

    More about Ann K. Emery
    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.

    9 Comments

  • MpPierce says:

    Great post, and I especially like seeing all of the options you presented in the video. Thanks for that! I had 2 how-to questions for you: (1) In the “handout” version of the chart, do you hardcode the values (198 total, 140 males, etc.) and the color formatting? Or is there a way to do that via formulas? I’m thinking, because of the formatting, that it’s done by hand. And (2) for the labels in the first bar…did you do those manually or with formulas? I know you can display both the series name as well as the value, but when I do that, it displays “males 29” instead of “29 males.” Thanks!

    • Ann K. Emery says:

      For the handout, I inserted a text box and wrote a few sentences by hand. If this graph were staying inside of Excel, you could write a formula that automatically tallies the annual amounts and you could use conditional formatting to automatically color-code those numbers.
      For the labels in the first stacked bar chart, I think I just typed “males” and “females” into the boxes with the numeric labels. I often write formulas that concatenate the numbers and the words together and then tell the chart to use those concatenated cell ranges as the labels.

  • […] K. Emery shows the steps for planning and building an Excel chart, from the sketching phase, to the completed chart. What works, and what doesn’t? […]

  • Your information is so helpful – and your instructions great — amazing you speak so quickly yet are easy to understand. I really appreciate your generous sharing of information. Alicia

  • Ione Farrar says:

    Question, I am recreating the stacked vertical bar chart. On the data labels, it shows up as Female 15, rather than 15 Female in your example. How do you get excel to place the label in that order?

    • Ann K. Emery says:

      Hi Ione, good question. I added numeric labels to all the columns (just like you would normally do). Then, I double-clicked inside the labels on the far left and manually typed “youth,” “females,” and “males” into the boxes.

      • Ione Farrar says:

        Brilliant! They even copy into another document as is. Plus, the trick of the transparent total is fantastic. I have struggled over the years with putting total, ending up with misaligned text boxes. Thank you!

        • Ann K. Emery says:

          The transparent total ends up saving a lot of time in the long run. I used to create text boxes above each of the columns, which always end up misaligned and sloppy. Glad to hear that you gained a new trick!

  • Leave a Reply to Alicia Stahlberg Cancel 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

    Inside our flagship dataviz course, you’ll learn software-agnostic skills that can (and should!) be applied to every software program. You’ll customize graphs for your audience, go beyond bar charts, and use accessible colors and text.

    Enroll

    Subscribe

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