Power Map

By J. Carlton Collins, CPA

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 (carlton@asaresearch.com) 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 jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.