Leave a Reply

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

Condensing Many Data Elements into an At-a-Glance Directory Using Symbols, Reference Codes and Colors

Oct 1st, 2019 / Dashboards / , , , ,
Screenshot of guest author Anna Williams' searchable Excel directory.

Today’s behind-the-scenes article comes from Anna Williams, a public health specialist and student in Depict’s online training program. Great work, Anna! –Ann

Some time ago, I worked on an overseas assignment with a United Nations agency.  

My task was to develop a national directory of organizations working on sexual and reproductive health for one of the agency’s country offices.  

The idea was to develop a “5W” mapping—a who, what, when, where and for whom for the those working on SRH in the health sector.  

To do this, I conducted a survey using SurveyGizmo, but also gave organizations an opportunity to complete the survey by hand, by electronically filling out a Word version of the questionnaire, with me over the phone, or through an in-person meeting.  

After four weeks of lots of outreach (to over 200 health organizations), we received 60 completed surveys from organizations working on sexual and reproductive health. Each one had 234 data elements!  

One of the products I developed was an Excel spreadsheet with all of the data organized and formatted for searchability using filters. 

Before: The Initial Dataset with Basic Formatting 

Here is what the initial version looked like, with only some basic formatting: 

This is a look at where we started. The directory is crowded and not easy to understand.

After: A Searchable Excel Directory 

Here is what the final product I submitted to the client looked like: 

This is the final product complete with color coded columns.

After: An At-a-Glance Directory with Symbols 

However, the agency I was working for also wanted a product that they could print and flip through by hand. To create this, I selected what to me were the 90 most important data elements to include and condensed the information for 60 organizations into a 3-page document:  

Here is a legend I created with color codes and symbols to make the information easier to understand.

The At-A-Glance Directory’s Legend 

I developed a legend to go with this directory (see extract below) and informed my client that one easy option they could consider, especially if they wanted to save money on color printing, would be to print only the legend in color—on a single page—and look at the At-a-Glance Directory on the computer with the legend in hand for reference.  

Here is the legend close up. Color codes highlight ‘Emergency Preparedness Activities’, symbols represent ‘Individuals to Whom Capacity Building is Directed’, and then a coordination of letters and numbers (ex: A1) represent specific subjects that are covered.

The Result 

In the end, my client appreciated both products, but the Searchable Excel Directory (which was essentially a large excel spreadsheet, organized as a user-friendly database) got the most use.  

I think staff from the agency liked being able to answer lots of different questions about who was doing what, where etc. using it as an interactive tool.  

But I sure did put a lot of work into the At-a-Glance Directory and hope to use this way of organizing information in reports and presentations in the future. 

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

Leaders have more important things to do than read Dusty Shelf Reports. Design dashboards that inform, inspire, and look great in just 12 weeks. Spring 2019.

Learn More


#f-post-el-35{display:none !important}

#f-post-el-35{display:none !important}

Learn More