2 Comments

  1. Pam Kato says:

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

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

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!

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

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.

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.

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.

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.

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

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.

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.

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!

2 Comments

  1. Pam Kato says:

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

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

Related Courses

Most “professional” reports are too long, dense, and jargony. Transform your reports with these practical tips. You’ll never look at reports the same way again.

Learn More

From spreadsheet stress to superstardom in just 12 weeks. Data analysis techniques & time-saving secrets for busy number-crunchers.

Learn More

SPONSORED

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

Learn More