I recently joined a presentation at a meeting of the Professional Legal Trainers Group in New York City to discuss some of the new chart types in Excel 2016. The crowd favorite was the new built-in 3D Maps. While not technically a chart type, this feature enables even a casual Excel user to generate stunning graphics. Attorneys should explore this tool to generate trial graphics, client pitches, motion exhibits, and other communications. In this post I’ll demonstrate the use of 3D Maps using a set of data listing public donations to the Obama-Biden transition project downloaded from the Socrata website.
As seen below, the dataset includes information for about 56,000 discrete donations, including city, state, and ZIP. We’ll use the 3D Maps tool to plot these geographically.
There are several requirements for using 3D Maps.
- Your data must be in the form of a table – one record for each row, a descriptive Header row, and no blank rows or columns.
- There must be some kind of recognizable location data, be it ZIP code, city, latitude/longitude coordinates, etc.
- You must have an internet connection – Excel will use Bing to generate the map and plot the data
- You must be running Excel 2016
Click anywhere in the data table and launch 3D Maps from the Insert ribbon, as shown below. There is no need to run a pivot table or organize the data:
The first time you do this, Excel will ask for permission to install an add-in. Click yes. The process of installing the add-in may lock up your computer for a couple minutes.
Excel then syncs up with Bing, which scans your data for location information and generates a default “tour” which flags all the locations that it found in your data based on a field that it chooses. You can spin and tilt this globe, and zoom in on any location you choose. It’s even more fun than it sounds.
Bing used the City information for this default plot, and not too accurately–its own estimate is that it only found 84% of the locations (most of the rest were misspelled–click on the “84%” for details). Here I will delete the default location fields and start over with the “Add Fields” tool.
I choose ZIP from the available fields to prompt Bing to organize and plot the data by ZIP code. On my machine it takes a couple minutes, but it is fun to watch the map filling up with blue flags.
Once the flags are all filled in, expand the Add Field choices under the Height option. Here you can plot any available metrics against the ZIP code. I am choosing Amount to see the donations plotted by ZIP.
Now we’re getting somewhere. Note that the default display is the Sum of the donations for each ZIP code. From the dropdown you could change this to Count (to show the number of donations), or the Maximum, Average, etc. — not unlike a Pivot Table.
To get a really impressive display, manipulate the thickness of the columns and the overall scale of the column height under the Layer Options – these dramatically change the look of the map. You can also adjust the color and shape of the columns.
To provide the reader some context, you may also want to insert Map Labels from the Home ribbon. Here is the display I settled on.
You can click on any data point to learn the ZIP code and dollar amount it represents–apparently, Obama is big in St. Pete.
You can also generate beautiful 2D maps by clicking Flat Map from the Home ribbon. In this example I’ve tilted the Flat Map all the way back, mapped by State rather than Zip, and applied a new color scheme.
I would be remiss if I neglected to point out an issue with this dataset. Once you plot the data by ZIP Code, you can spin the globe and you’ll see some data popping up in Europe. According to the plot, Bulgaria was an especially fecund source of donations: the humble town of Obichnik, Bulgaria ostensibly donated $26,225 to the transition project. What the heck?!
It turns out that Excel dropped the leading zero for certain ZIP Codes, and Bing did not correctly associate the resulting 4-digit Zips with their American counterparts, instead casting about for a world city with that 4-digit Zip. Thus, for example, the ZIP code for Greenwich, CT (06831) was shortened to 6831, which Bing associated with Obichnik, Bulgaria. In an earlier post, I discussed a trick for adding the correct number of padding zeroes to incorrectly formatted Bates numbers by using the TEXT function. The same technique would work here to restore the leading zeroes in these ZIP codes.
We’ve just scratched the surface of what is possible with 3D Maps. Think about the potential to enhance your trial graphics, motion exhibits, expert reports, client pitches, and other communications with this great new tool.
Thank you for reading. If you would like your team to learn to use Excel more effectively, please get in touch with Ben Kusmin of Excel Esquire.
Excel Esquire 