This was written as a guest post for Ann Emery's Depict Data Studio blog. For the past year I've been participating in Ann's Great Graphs data visualization training course. It has been incredibly helpful to have access to detailed tutorials, templates, and advice from Ann and other visualization experts. I highly recommend it to anyone looking to step up their data visualization skills and make their data more usable.
Below, you'll see how I applied many of the techniques covered in the course to federal grant data.
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.
Deciphering the Data
Goal: 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.
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.
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.
Culling the Charts
Why Separate 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.
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.
Why Stacked Column Charts Didn’t Work:
This set of charts is unreadable because the grant Carryover amounts are so much smaller than the Allocation 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.
Finally, I tried a clustered bar chart.
Finding the Front-runner
Why the Clustered Bar Chart Won:
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 automatically created needs some work.
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.
Tip: 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.’
Next, in the drop-down 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.
Now that my data was sorted, my chart looked like this.
Using techniques covered in Ann’s Great Graphs 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
Customizing the Chart
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:
Now, for a before and after comparison. Which version do you think is easier to understand?
