As a reader of this blog, you probably know I use Microsoft Excel for a lot of my data visualization needs. Excel is easy to use, everyone has it, and it works well with most other programs. One of the topics I like to teach most is how to create data visualizations in Excel. Learning a few strategies and approaches, you can vastly expand the capabilities of the tool to create a variety of data visualizations that are not available in the default drop down menus.
Today, I’m excited to release a step-by-step guide to creating advanced data visualizations in Excel. In it, I walk you through how to create more than 15 different charts: Overlaid gridlines (3 alternatives); Vertical Line; Block shading (annual-annual; monthly-annual); Broken Stacked Bars; Vertical and Horizontal Bullet Charts; Dot Plot; Slope Chart; Vertical and Horizontal Bar-Scatter Combination Plot; Lollipop Chart; Waterfall Chart; Sparklines; Heatmap; Diverging Bars; Tile Grid Map; and a Marimekko Chart.
Here’s the full Table of Contents:
The guide comes with an Excel file that you can use to follow the steps and create your own graphs and as a template for your own use. You can purchase your copy of the version for Excel 2010 in the PolicyViz Shop. I’m currently working on the version for Excel 2016 and will release that soon.
I’m indebted to many students, workshop participants, and other Excel enthusiasts for helping me develop these lessons over the past couple of years. I’m especially indebted to Glenna Shaw for helping me with the layout and design of the final product.
My Excel Philosophy
I follow two philosophies when it comes to creating visualizations in Excel. First, change the defaults. You may like Excel’s default colors—they may be your favorite red-green-blue combination—but as I see it, everybody uses those defaults. Graphs with those defaults then look the same and your work doesn’t stand out. Furthermore, graphs with default colors and layouts look like you didn’t consider your audience’s needs and how your visualizations can best help them.
Second, everything I place on my graphs I try to encode with data. That means I take great effort to avoid text boxes, and drawing lines and shapes. My approach is to insert such objects onto the graph using data by combining chart types or using other charting features. In this way, the entire Excel chart is a single object and can be more easily updated and moved between programs (for example, to PowerPoint or Word). If you draw lines or boxes or text boxes, it’s hard to get them exactly where you want them, it’s hard to update the chart with new data, and you have to copy and paste each object to another program like PowerPoint.
My hope is that this step-by-step guide will help you extend the capabilities of the software and by extension your own capabilities. Because of time or skill or resources, we are often bound by the tools we have at our disposal, but by extending what those tools can do, we can extend what we can create and how we can communicate our work, data, and analysis to our audience.
As a preview of this new guide, here is the short section on Sparklines. If you’d like to download the entire thing, check it out at the PolicyViz Shop.
Sparklines
Sparklines are a simple chart type that show small versions (or “small multiples”) of graphs. Beginning with Excel 2010, Sparklines became a default option, available in the “Insert” tab of the ribbon.
Use the Sparklines tab in the Advanced Data Visualizations with Excel 2010 Hands-On.xlsx spreadsheet to create the chart.
1. Select where you would like to place your Sparklines (though this could be done later too).
2. Select the “Line” option in the “Sparklines” menu in the “Insert” tab on the ribbon.
3. Select your data for the “Data Range:”.
4. Hit OK and the Sparklines fill in.
5. Using the Sparkline Tools, you can format the color of the Sparklines (in the “Sparkline Color” drop down), add markers (in the “Show” tab), or even change the chart type to columns (in the “Type” tab).
Buy the entire 249-page step-by-step Guide to Advanced Data Visualization in Excel
Hi Jon, love the site and the pod. Is a 2013 excel user going to struggle with the commands/instructions or do you think they are similar enough?
Hey Jeff,
Thanks for writing. I think they are pretty similar, but the menus are considerably different. If you’re an experienced Excel user, you’ll be able to figure it out. I’m working on the 2016 guide and hope to have that out in July or August.
Thanks again,
Jon