Data workflow issues are a big challenge at any organization that uses and communicates data. It doesn’t really seem to matter what data analysis or visualization tool your organization uses—Excel, JavaScript, R, Tableau, a custom tool, or something else—figuring out how to move data from collection to analysis to publication can frustrate lots of people working within and across teams. And managing those tools so that more people in the organization can use the same code, same design, or same branding is often difficult, if not impossible.

At the Urban Institute—where one of my roles is to try to help researchers communicate their work—most researchers create their data visualizations using Excel. As you probably know—because everyone uses Excel—one of the limitations of Excel is creating graph templates so that graphs have the same color, layout, and branding. To try to streamline the workflow process, I’ve created an Excel add-in that adds a tab to the Excel ribbon from which anyone at Urban can quickly and easily style their graphs to match Urban standards.

First, some quick background. Urban is a nonprofit research institution in Washington, DC dedicated to elevating the debate around social and public policy issues. We have a number of different centers concentrating on a range of issues from justice policy to health policy to housing to programs for low-income Americans (my home center). On top of that, Urban has a terrific communications staff (my other home at Urban) that consists of media and public affairs teams, social media and blog teams, an editorial crew, a digital and web development staff, and a staff of about 4 working in design and data visualization.

The research staff uses a suite of tools to extract, analyze, and visualize their data. The primary statistical software programs researchers use are Stata, SAS, Python, and R. Most people use Excel to create their graphs and visualizations (interactive visualizations are usually custom-produced using d3, Mapbox, and others). Regardless of the report type—blog posts, longer research reports, or shorter briefs or memos—all graphs follow the Urban style guide. But applying those styles to graphs in Excel can be tedious and time-consuming. It often falls to the editorial, blog, and social media staffs to fix and style graphs from the researchers to meet the Urban guidelines.

Previous graph styling tools

Up to this point, researchers styled their Excel graphs using a color palette that was pushed centrally and accessible through the Page Layout tab; however, that color palette did not enable researchers to style the graphs. A PowerPoint template was also pushed centrally and contains different slide types and colors. Urban uses the open source Lato font, which was pushed centrally to all computers in the office.

The (new) Urban Graphing Styles Add-In

In an effort to streamline this process, give more control to the researchers, and reduce the burden placed on editors, I created an Excel add-in that can be used to style Excel graphs following Urban guidelines. The add-in is built using the VBA programming language and (in this version, at least) consists of separate buttons for each graph type, which, when selected, styles the graph. I’ve also created a button that generates dummy data and inserts a slope chart and dot plot. A separate button turns all graph elements to gray (so that the chart creator and strategically and purposefully add color and other objects to the chart to highlight points of interest); another button labels the last point of each series; and a final button can be used to export the chart in PNG file format.

The biggest challenge to creating the add-in is knowing how to code in VBA (big hat-tip to Jon Peltier for helping me streamline some of my code and helping me solve some of the more difficult issues). I used a cool (free) add-in from Andy Pope to add images and organize the ribbon (note: Pope’s add-in makes things really simple, but it has a tendency to crash, so be forewarned). I also used some nice video tutorials from Jon Acampora to get started.

The add-in will by no means solve every issue: I’m sure I’ll hear from colleagues about issues and crashes and enhancements they’d like to see (I already have my own list), but I’ve tried to create it flexibly so that if someone has, say, already deleted the legend, it doesn’t pop back up in the styled graph.

The data workflow issue is a big challenge. Whether your organization uses Excel, a custom tool, or some other analysis and visualization tool, figuring out how to move data from collection to analysis to publication can frustrate lots of people. Within our “dataflow” at Urban, my hope is that my Urban colleagues will be able to use this Excel add-in to style their graphs so they can spend more time working on effectively visualizing and communicating their data.