Q: I’d like to plot a year’s worth of our company’s data on a map and then view that data animated over a period of time to see if we can visually detect trends. I’ve seen this kind of reporting before, so I know it can be done, but I don’t know how. Can you direct me to this type of solution?
A: The solution you seek can be achieved with Microsoft Power Map—a free, add-in utility included with Office 365 subscriptions (a preview version is also available for stand-alone versions of Excel 2013). Following are steps for installing and using this new add-in to analyze your data.
1. Install the add-in. To install the Power Map add-in in Excel 2013, from the File tab, select Options, Add-Ins; then, at the bottom of the Excel Options dialog box, select COM Add-ins from the dropdown box and click the Go button. In the resulting COM Add-Ins dialog box (pictured below), check the box labeled Microsoft Power Map for Excel.
This action will install the add-in and insert the Power Map icon on the Insert tab’s Tours group, as circled in the picture below.
2. Prepare your data. Make sure your data contain columns of both dates and locations. The locations can be countries, states, counties, cities, ZIP codes, addresses, or GPS coordinates. As an example, I downloaded a database of 38,449 arrest records for Chicago occurring in 2011, a partial listing of which is pictured below. (You can download an Excel file containing this data set at carltoncollins.com/chicago.xlsx.)
Note that, in this example, column B contains dates and times, and columns H and I contain latitude and longitude coordinates.
3. Create a Power Map. Select any single cell in the data range, then from the Insert tab, select Map, Launch Power Map, and then click New Tour (or select Carlton’s Tour of Chicago Crime to see a Power Map that has already been created). The New Tour screen should appear as pictured below.
Notice that Power Map recognizes the GPS coordinates and plots the data on the globe, which, from the view shown above, appears as a tiny blue dot over Chicago. Rolling the mouse wheel and clicking the directional arrows allow you to zoom in to the Chicago area, as pictured below.
To complete this Power Map, check the boxes labeled Date and Description (to focus on this specific set of information in the Power Map), and then in the Time field dropdown box (located in the bottom of the Layer 1 dialog box, as pictured below), select the option labeled Data accumulates over time (so the data bars grow larger as the Power Map is set into motion).
As finishing touches, click the Legend button to display a legend, change the Theme to display a black background, and rotate the map a little more clockwise. Tools are also available to add informational Text Boxes and animate the map using built-in movements such as Circle, Figure 8, and Fly Over. An example of the resulting map is pictured below.
You can create additional map views of the data by clicking the Add Scene button located in the Power Map ribbon, and then repeating step 3 (this step).
4. Produce your results. Once you have created the Power Map(s), you can produce the results into a video suitable for emailing, displaying in a presentation, or publishing to the web. To produce a video, click the Create Video button located in the Power Map ribbon, adjust the video options as desired, and click Create.
You can view an example of a rough Power Map video I created at tinyurl.com/mko4o48.
Although Power Map is included only in Office 365 subscriptions, a preview version of Power Map that works with all 2013 versions of Excel can be downloaded at tinyurl.com/dxzwjhc.
J. Carlton Collins (firstname.lastname@example.org) is a technology consultant, CPE instructor, and a JofA contributing editor.
Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2013, 2010, and 2007 versions, unless otherwise specified.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to email@example.com. We regret being unable to individually answer all submitted questions.