Hello good people! My name is Robert Perez and I am a research assistant at Hamai Consulting and data analyst at Youth Policy Institute. I am responsible for cleaning and analyzing data from various sources and designing engaging ways to communicate findings using reports, dashboards, and infographics.
I spend some time at the beginning of the year planning out projects for my department using a number of different tools. One tool I love to use is the gantt chart. I searched for a way to automate the conditional formatting process of coloring each of the representative time units and stumbled upon a template from Chandoo.org.
This particular gantt chart allows the user to enter the starting week, project duration, and completion status, which will automatically populate the lines with colors depending on the department or project name. Formatting this chart took some “Excelbow” grease as the original chart did not have the option to colorize the tasks based on the department or project.
Updating tools like this gantt chart regularly will encourage its use and keep it from being lost in virtual oblivion. Keep in mind the technical skill of your audience and communicate with them during the design process to ensure that what you are creating will be of use to them.
Hot Tip: If you are having trouble understanding the function of a formula, I find it helpful to break out the formula into its component parts and paste each component into their own cells. This way, I can determine the return value of each nested formula, which gives me more context around how each component works together.
Hot Tip: No matter how often you design a visualization or a tool, the question of “who is your audience” will always come up. Harvard Business Review offers some definitions of audience categories that might help with your design process:
Novice: first exposure to the subject; doesn’t want oversimplification
Generalist: aware of the topic, but looking for an overview understanding and major themes
Managerial: in-depth, actionable understanding of intricacies and interrelationships with access to detail
Expert: more exploration and discovery and less storytelling with great detail
Executive: only has time to glean the significance and conclusions of weighted probabilities
Rad Resource: Sometimes I need a bit of inspiration when designing my visuals. Fortunately, there is a veritable bevy of resources online from which we can fuel our creative engines. One of my favorite sites to visit is Contextures.com. The site caters to Excel novices, offering lessons about conditional formatting to VLOOKUPs, and those more experienced learners with lessons about automation using VBA. Of course, there is also Chandoo.org, a site that has one of the friendliest community forums ever.
The American Evaluation Association is celebrating Data Visualization and Reporting (DVR) Week with our colleagues in the DVR Topical Interest Group. The contributions all this week to aea365 come from DVR TIG members. Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org. aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.