• Megan Johanson

Taking Federal Grant Data from Dull to Dynamite with Custom Visualizations

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.

Excel file showing 2018 allocation and incoming 2017 carryover dollar amounts for various federal grants, separated by school districts.

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.

Bar chart for 2018 Federal Grant Allocations created automatically in Excel.

Automatically created Excel bar chart for Incoming 2017 Carryover separated by federal grant type.

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.

Stacked bar charts for 2018 allocation and incoming 2017 carryover by type of grant.

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.

100% stacked bar charts for 2018 allocation and incoming 2017 carryover amounts separated by grant type.

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.

Clustered bar chart showing 2018 allocation and incoming 2017 carryover amounts by grant type, using Excel's standard formatting.

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

Screenshot of the steps for sorting the grant allocation amounts in a table from left to right, so that the highest values are on the left.

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.

Screenshot of the resorted table of grant funds, with the highest allocated grant on the left.

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

Clustered bar chart of the 2018 allocation and incoming 2017 carryover amounts, using the resorted data table. Excel places the highest values at the bottom of the chart.

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

Reformatted clustered bar chart with the highest value grant at the top of 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:

Finalized clustered bar chart with customized colors, title, and subtitle.

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

Before image of the unsorted data table in Excel followed by an After image of the finalized clustered bar chart.