The Microsoft Office suite is the primary publication tool for many organizations, including the Urban Institute. But one of the challenges in creating graphs in Microsoft Excel is its limited templating tools. There is no straightforward way to create consistent graph templates so that different graphs have the same color, font, layout, and branding.
Manually editing graphs to ensure consistent branding across an organization that produces hundreds of publications a year adds a lot of time in the creation and publication process. To streamline that process, we created an Excel add-in that adds a tab to the ribbon that anyone at Urban can use to quickly and easily style their graphs to match Urban standards.
Even though the Urban Institute publishes a lot of interactive data visualizations, data tools, and blog posts, the majority of our charts are created for our research publications. To write and publish those reports, Urban’s research and communications staff use the Microsoft Office ecosystem — reports are written and formatted in Word, graphs are made in Excel, and slides are made in PowerPoint. Researchers use a suite of tools to extract, analyze, and visualize their data, including Stata, SAS, SPSS, and, increasingly, Python and R (as my Urban colleagues have previously written on maps, why we use R, and R Markdown). But many — maybe even most — researchers at Urban still use Excel to create their final graphs and visualizations.
Previous graph styling tools
Regardless of the report type — blog posts, short briefs, memos, or longer research reports — researchers typically start with a Word template with detailed defaults and instructions for formatting. But until recently, guidance for data visualizations only instructed that all graphs follow the Urban style guide. That style guide includes specific details for axis appearance and labels, title and subtitle positions, and line width and color. But applying those styles to graphs in Excel without a template was often tedious and time-consuming.
In the past, the editorial, blog, and social media staff fixed and styled graphs from researchers to meet these guidelines. The exception is interactive visualization features, which are usually custom-produced using D3, Mapbox, and other JavaScript libraries. But these HTML graphics can be styled with standardized CSS style sheets, allowing much of the style guide to be repeatedly applied across interactive products.
Before we created the new Excel add-in, researchers styled their Excel graphs using a color palette and font (Lato) pushed from Urban’s Data and Tech team to everyone’s computer and made accessible through Excel’s Page Layout tab. Even with those two formatting elements available on everyone’s computer, graph creators still had to size and position the text, choose the right color combinations, and style the lines, bars, and other graph encodings themselves, leading to a lot of additional work for themselves, and the editorial, blog, and social media staff.
The Urban graphing styles add-in
To streamline this process, give researchers more control, and reduce the burden placed on editors, we created an Excel add-in that automatically applies Urban styles. The add-in is built using the Visual Basic Application (VBA) programming language — the language that sits behind all Microsoft products — and consists of separate buttons for each graph type. Once a user creates a graph, he or she selects the graph type to apply the formats. The add-in automatically applies the right colors, fonts, data styles, and positions to the various text elements.
The add-in also includes a button that generates dummy data to create a slope chart and dot plot, useful graph types that users often don’t know how to build in Excel. This function allows users to select the number of data series and then paste in their own data. A separate button turns all graph elements to gray, so the chart creator can strategically and purposefully add color and other objects to the chart and highlight points of interest. Another button on the tab adds a label to the last point of each series, and another button exports the selected chart in PNG file format.
We also provided training and instructional guides for Urban staff. We published a step-by-step installation guide and videos on Urban’s intranet, and we held several open meetings to demonstrate how to install and use the tool.
Since it was released to Urban staff, the add-in has been used across the organization, saving the research and communications staff hundreds of hours. Instead of focusing on the detailed style and formatting requirements of publishing their work, they can spend more time focusing on the research and determining the best ways to communicate their work to their audience.
The add-in by no means solves every data visualization issue in Excel. There are lots of options and extensions that could be included to help users create even more graphs or different style or output types. And our meetings and instruction guides didn’t meet everyone’s needs — though many researchers use the tool, there are still people in the organization who don’t know it exists. But as people find it valuable to simplify and streamline their report creation process, the tool will get wider use.
Creating the add-in
The biggest challenge in creating the add-in was coding in VBA. I have an extensive coding background, but VBA requires a different style and syntax and a different way of thinking. I want to give a big hat-tip to Jon Peltier at Peltier Tech for helping me streamline some of my code and solving some difficult issues.
Excel has a macro recorder that records specific steps that can be captured and extended for more general use, which I used to learn some of the programming syntax. In addition to the usual suspects of Stack Exchange and Stack Overflow to help solve some of the basic programming challenges, I used a free add-in from Andy Pope to add images and to organize the ribbon. (Pope’s add-in makes things simple, but it has a tendency to crash, so be forewarned.) I also used some video tutorials from Jon Acampora at Excel Campus to get started.
Wrap up
Whether your organization uses Excel, a custom tool, or another established data visualization program, figuring out how to move data from collection to analysis to publication within your organization’s style guidelines can be frustrating. For our work at Urban, we hope our colleagues will use this Excel add-in for their graphs, so they can spend less time styling and more time working on effectively visualizing and communicating their data.
