Introducing the Urban Institute Data Visualization Style Guide’s Open-Source Excel Add-In
Recently, we released an updated version of the Urban Institute Data Visualization Style Guide, including new details on Urban’s data visualization branding, best practices, and accessibility. Though the first official Urban Data Visualization Style Guide was meant to be a living document, it hadn’t been substantially updated or revised since its creation in 2015.
In that interim, our data visualization and design teams have changed, as has our work process and data visualization and design needs. Further, individuals and teams within Urban have created tools to complement the guide, including a PowerPoint template, an R theme (urbnthemes), and an Excel add-in. These additional tools highlighted a key aspect of why we’ve sought to revise the style guide: not just to include guidance on how to use color palettes, interactivity, accessibility, and animation but also to update the Urban Institute Excel add-in and offer it as an open source tool for the first time.
At Urban, most data visualizations for reports, briefs, and slide presentations are built in the Microsoft Office suite of tools. There are exceptions: graphs and charts for Urban Wire are built in Datawrapper, custom visualization and data tools for the website are built in Svelte and D3, and a growing set of analysts use R for their data visualization needs. Creating a tool Urban researchers can use to more quickly and easily format their graphs can make analysts’ work more efficient and consistent, and reduce the burden placed on our editors.
In this post, I describe how the Excel add-in works and how you can modify it to meet your data visualization team’s needs. Although a deeper understanding of how Visual Basic for Applications (VBA) code works is helpful to modify the add-in, its modularity means anyone can apply their own styles.
How the new Urban graphing styles add-in works
The Urban Excel add-in was built in the VBA coding language that sits behind all Microsoft tools. The add-in can be easily installed on both PC and Mac operating systems by simply saving the file (in .xlam) format to the correct folder and installing it through Excel’s Options menu. For step-by-step instructions on how to install it on both types of computers, see the “Using the Excel Macro” section of the new style guide.
This new add-in implements several improvements over the initial version of the tool:
· In the first version, users would create the basic chart using the built-in Excel graph menu, then implement Urban styles using the add-in. In the new version, that process is streamlined with a single button where the user selects their data and the graph is created and styled simultaneously.
· The initial Select a Style dialog box is bigger, easier to read, and includes more active advice to chart creators. For example, the box now informs users to use sentence-style capitalization for text and to ensure the order of entries in the legend matches the order of the series in the graph.
· Both versions enabled users to create slope charts and dot plots — two chart types Urban researchers use more frequently than in the past — but were limited to no more than 10 series. That limit has been removed and chart creators now have more control over styling.
· In the past, exporting high-resolution charts out of Excel was difficult — the user would either need to take a screenshot or drop it into PowerPoint and export. The newer versions of Excel (on PCs) offer an export menu, which has been integrated into the add-in. Now, the user can click the Export button toexport their graphs to a specific folder location and file type just like in any other tool. That capability doesn’t exist on Mac computers, so we continue to use the process of copying the Excel graph, opening a new file in the Preview program, and saving the graph as a high-resolution PNG.
Using the add-in for your own graphs
The new version of the add-in is available for use and editing from Urban’s public Github site. Once downloaded, you can edit the styles and formats to match your own data visualization design specifications.
To open the add-in and edit for your styles, double-click the .xlam file, and select the Visual Basic button in the Developer tab. (If the Developer tab does not appear in your version of Excel, you can make it appear by going to File > Options > Customize Ribbon > Main Tabs and selecting the checkbox next to Developer.) Inside the file, you will find 27 separate modules with numerous procedures in each. The m_CommonFunctions module is the primary module that consists of styling options to adjust gridlines, axes, chart size, logo, and more.
The full list of modules that control the styles are as follows:
· m_CommonFunctions. This module contains most of the main styling subroutines, including references and pointers for colors, fonts, chart size, x-axis styling, logo, source line, title, subtitle, gridlines, and specific chart types styling (e.g., pie charts, slope charts, dot plots, and scatterplots).
o Calls: F_WhichStyle module. This is the Select a Style dialog box that enables the user to select a graph for web or print.
· Color modules to set Urban color palettes
o m_ColorButtons. Assigns the primary color palettes to buttons on the ribbon.
o m_CommonBlueFillColorFunctions. Sets the blue-shaded color palettes.
o m_CommonFillColorFunctions. Sets the primary Urban color palettes.
o m_CommonLineColorFunctions. Sets color palettes for line charts.
· Other modules
o m_ExportChart. Defines the chart exporting engine.
o m_Instructions. Creates the Instructions buttons with notes for Urban employees.
o m_StartWithGray. Creates the StartWithGray button that makes every object on the graph gray.
o m_UrbanLabelLastPoint. Creates the LabelLastPoint button that adds a label to the end of the series in the chart.
· The remaining modules include specific options for each chart type and make calls back to other modules listed above. Some modules are more complicated than others but typically refer to the CommonFunctions module to control aspects that are consistent across graph types.
o m_UrbanAreaChart
o m_UrbanAreaChart_BlueRamp
o m_UrbanBarChart
o m_UrbanBarChart_BlueRamp
o m_UrbanColumnChart
o m_UrbanColumnChart_BlueRamp
o m_UrbanDotPlot
o m_UrbanLineChart
o m_UrbanMarkersLineChart
o m_UrbanPieChart
o m_UrbanScatterplot
o m_UrbanSlopeChart
o m_UrbanStackedBarChart
o m_UrbanStackedBarChart_BlueRamp
o m_UrbanStackedColChart_BlueRamp
o m_UrbanStackedColumnChart
To create buttons on the add-in that match your color palette and design aesthetic, you will need to use a design tool like Adobe Illustrator, Figma, Canva, or even PowerPoint. Each image is a 32 x 32 pixel square, and an Adobe Illustrator file with the existing buttons and colors can be downloaded from Urban’s Github site. There are different ways to build the actual ribbon and assign modules to each, but I found the Office RibbonX open-source tool from Fernando Andreu to be the easiest and most stable tool available.
An example of editing the add-in
To demonstrate the add-in’s potential, let’s recreate this column chart without a logo and a different color palette. This example only requires editing two areas of the add-in code: Omit the call to the InsertLogo procedure and change the color palette.
In the m_UrbanColumnChart procedure, we put a single quotation mark (‘) in front of the bInsertLogo=InsertLogo(cht) line. The single quotation mark is the comment marker in VBA, so the code will skip over that subroutine and not include the logo. (Obviously, you could also delete any mention of the InsertLogo line and routine, but by commenting it out, you keep it for a future instance when you might want to include a logo in your charts.)
bOuterFormat = OuterFormat (cht)
bFormatXAxisTitle = FormatXAxisTitle (cht)
bInsertLogo = InsertLogo (cht)
bInsertSource = InsertSource (cht)
bFormatTitle = FormatTitle (cht)
bFormatGridlines = FormatGridlines (cht)
bFormatXAxis = EormatXAxis (cht)
bFormatFillColors = FormatFillColors (cht)
Changing the color palette is a bit more involved. Here, we use the m_ColorButtons routine to change the colors the add-in will use. Each variable in the below block of code (e.g., giRGByellowcolor) is assigned a color and used elsewhere in the program.
Public Const giRGByellowcolor As Long = 1163261 ‘RGB (253, 191,17)
Public Const giRGBblackcolor As Long = 0 ‘RGB (0,0, 0)
Public Const giRGBgraycolor As Long = 13816530 ‘RGB (210, 210, 210)
Public Const giRGBpinkcolor As Long = 9109740 ‘RGB (236, 0, 139)
Public Const giRGBgreencolor As Long = 4765525 ‘RGB (85, 183, 72)
Public Const giRGBwhitecolor As Long = 16777215 ‘RGB (255, 255, 255)
Public Const giRGBdarkgraycolor As Long = 5855324 ‘RGB (92, 88, 89)
If you haven’t worked with color in VBA before, you’re probably wondering how to get the Excel color codes (e.g., 1163261 for giRGByellowcolor) rather than the more standard RGB or HEX codes. There are two ways to do so. First, in the “Immediate” window in the VBA editor, you can simply type a question mark before the RGB code, as in: ?RGB(253,191,17). Excel will return the code for you directly in that window. Alternatively, you can do some simple math to build the codes for yourself. The equation is R + G*256 + B*256², which, in this case, would be: 253 + 191*256 + 17*256*256 = 253 + 48896 + 1114112 = 1163261.
In this case, we’ll use the color palette from the 2016 Eurostat style guide, keeping the variable names as they are in the existing code. You could instead change the variable name everywhere it appears in the project, but this option could be more time intensive as giRGByellowcolor is referenced 17 total times. Using the new colors, we get:
Public Const giRGByellowcolor As Long = 11824710 ‘ ?RGB (70,110,180)
Public Const giRGBblackcolor As Long = 9522095 ‘RGB (175,75,145)
Public Const giRGBgraycolor As Long = 3319270 ‘RGB (230,165,50)
Public Const giRGBpinkcolor As Long = 14786560 ‘RGB (0,160,225)
Public Const giRGBgreencolor As Long = 4960125 ‘RGB (125,175,75)
Public Const giRGBwhitecolor As Long = 4603065 ‘RGB (185,60,70)
Public Const giRGBdarkgraycolor As Long = 3654585 ‘RGB (185,195,55)
Now, by clicking the column chart button in the add-in and selecting the Web option in the pop-up box, we get a chart that uses Eurostat colors and omits the Urban logo.
Looking ahead
We hope the revised version of the Urban Data Visualization Style Guide will serve as a model to other organizations seeking to make their data visualizations easier to create and more consistent across teams and tools. We are continuing to build on this guide to include more guidance for specific charts, templates in Excel and R, and language and tone for graph text. For now, however, we hope the guide and the Excel tool will help you and your team create better and more effective charts and graphs.
