2 Comments

  • Pam Kato says:

    I love this! Thanks for sharing your thoughts on making graphs.

  • Regina Lukich says:

    Love it! Appreciate your work to visualize this data for all!!! and make it more interesting!

  • Leave a Reply

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

    Taking Federal Grant Data from Dull to Dynamite with Custom Visualizations

    Updated on: Sep 24th, 2019
    Data Analysis, Data Visualization
    , , , ,
    A before/after data visualization makeover from Megan Johansonas a GIF.

    Megan Johanson is the Data Analytics and Insights Manager at the Educational Service Center of Central Ohio. She’s also a student in Depict Data Studio’s hybrid synchronous/asynchronous training program and recently began her own data visualization blog at https://www.meganjohanson.com/. –Ann

    If you’ve ever worked with publicly available government data, you probably know the headache and eye strain that comes with trying to make sense of a huge spreadsheet.  

    In this blog post, I’m going to explain how you can easily create a customized chart that will make your federal grant data conversations a breeze.  

    I’ll also provide you a look at the chart types I considered and then rejected along the way to my winning chart. 

    Step 1: Deciphering the Data

    These were the goals for my project: 

    • Visualize the federal grant Allocation amounts for each school district, so the breakdown of funds can be seen at a glance.  
    • Additionally, I wanted to identify which grants had large Carryover amounts (funds allocated in the prior year but not used), indicating an opportunity for the district to provide more services to students with their existing funds. 

    The data were stored in a table structured like the one below, with the grant type listed across the top (note: data have been changed).  

    Unfortunately, the table requires way too much concentration and zig-zagging across the screen to understand what is happening within each district. 

    This spreadsheet is a little hectic and difficult to understand. Let's revamp it!
    This spreadsheet is a little hectic and difficult to understand. Let’s revamp it!

    I focused on each district separately by creating a new sheet for each and copying over the grant titles and relevant data. And because it’s hard to know from looking at a table what type of chart would best represent the data, I tried a few different types.  

    Step 2. Culling the Charts  

    I started with separate bar charts for the grant Allocation and Carryover amounts. I didn’t spend any time formatting at this point, because I was trying to determine the best chart type to display the two sets of data for each district.  

    Why Bar Charts Didn’t Work

    Basic bar charts for grant Allocation and Carryover worked okay, but require visual zig-zagging to compare the two types of data for the same grant. 

    This photo shows the 2018 allocation.
    This photo shows the 2018 allocation.
    This photo shows the 2017 carryover.
    This photo shows the 2017 carryover.

    Why Stacked Column Charts Didn’t Work 

    The grant Allocation and Carryover data are each a Part to Whole relationship, so I tried variations of stacked column charts next.  

    This worked for Allocation and Carryover separately. However, I wanted to be able to compare the Allocation and Carryover amounts for the same grant next to each other and these charts did not work for that. 

    This set of charts is unreadable because the grant Carryover amounts are so much smaller than the Allocation amounts. 

    This photo shows how the chart is unreadable with the carryover amounts.
    This photo shows how the chart is unreadable with the carryover amounts.

    Why 100% Stacked Column Charts Didn’t Work

    This chart duo is misleading because it’s too easy to overlook that the total amounts are very different.  

    This photo shows how the chart is not easy to understand the differentials in the amounts.
    This photo shows how the chart is not easy to understand the differentials in the amounts.

    Finding the Frontrunner: Why the Clustered Bar Chart Won 

    Finally, I tried a clustered bar chart.  

    This chart type meets all of my needs and displays the data accurately.  

    It allowed me to compare the Allocation and Carryover amounts for the same grant on the same scale without squishing the information.  

    However, the version Excel that was automatically created needs some work. 

    This photo shows how the chart will need some improving to be more understandable.
    This photo shows how the chart will need some improving to be more understandable.

    Step 3. Sorting the Data 

    It is easiest to read a chart when the categories are in a logical order and since the Allocation amounts were much larger than the Carryover amounts, I decided to sort the data by Allocation amount.  

    Here’s a tip for sorting data: You can sort numbers left to right by highlighting the relevant cells. In my case that was C1 through I3. Then go to Data – Sort – Options and select ‘Sort left to right.’  

    This photo highlights sorting options.
    This photo highlights sorting options.

    Next, in the dropdown under Sort by, I selected Row 2 to sort by 2018 Allocation. I also changed the Order option to ‘Largest to Smallest.’ Then I pressed OK.

    Here are more order options.
    Here are more order options.

    Now that my data was sorted, my chart looked like this. 

    Here is an improved version of the chart.
    Here is an improved version of the chart.

    Step 4. Improving Readability 

    Using techniques covered in Ann’s Great Graphs: Transform Spreadsheets to Stories online course, I made several small changes to increase readability: 

    1. Reversed the order of the grants on the Y-axis 
    2. Removed chart boarder 
    3. Changed the gap width to 30% 
    4. Deleted the X-axis labels 
    5. Added data labels directly to the bars 
    6. Removed the X-axis gridlines 
    7. Moved the legend above the chart 
    Here is the chart with the above steps taken.
    Here is the chart with the above steps taken.

    Step 5. Customizing the Chart with Colors, Fonts, and a Takeaway Title 

    To take the chart to the next level, I customized it by: 

    1. Changing the default colors to branded ones
    2. Changing the font colors of the data labels and legend to match the relevant bar colors.  
    3. Deleting the Chart Title box and inserting a text box with a title and subtitle that tell a story unique to this district. 
    4. Tweaking the font sizes to create a clear hierarchy where the category labels are larger than the data labels and legend text. 
    5. Changing the grant type labels to black so they would stand out more than the data labels.  

    This is what the final chart looked like: 

    A photo of the final chart.
    A photo of the final chart.

    Before and After: From Raw Data to Edited Graph 

    Now, for a before and after comparison. Which version do you think is easier to understand? 

    The before and after photo. We made so much progress!
    The before and after photo. We made so much progress!
    More about Megan Johanson
    Megan Johanson is the Data Analytics and Insights Manager at the Educational Service Center of Central Ohio. She earned her PhD in Cognitive Psychology and has over 10 years of research and evaluation experience. Megan has a strong foundational knowledge of research methodology and is skilled at making data easy to understand. In every project, Megan aims to use data to create insights that lead to action. Connect with Megan at http://www.meganjohanson.com/.

    2 Comments

  • Pam Kato says:

    I love this! Thanks for sharing your thoughts on making graphs.

  • Regina Lukich says:

    Love it! Appreciate your work to visualize this data for all!!! and make it more interesting!

  • 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

    Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

    Enroll

    Subscribe

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