Thursday, September 13th, 2018 / by Excel Champ
One of the exciting new features of Excel 2016 is the capability to generate geographical maps. This feature, called Power Maps, is now also available for Excel 2013. The types of maps that can be generated with Power Maps are only limited by your imagination!
After covering the basics, we will demonstrate how to create a heatmap of police incidents in Seattle. Since the data set is large, we will leverage Excel’s Data Model.
Power Maps can be accessed with the 3D Map icon, found in the Tours section of the Insert Tab.
If you don’t see the 3D Map Icon, go to this link to learn what is necessary to obtain it.
Some users just need to install Power Maps, while others need an upgraded subscription or purchase of Excel.
Let’s begin the process of creating a heatmap using a large set of publicly available incident data from the Seattle Police Department. This data set has nearly 1.1 million rows, so it exceeds the limit of an Excel worksheet – and will require the use of the Excel Data Model.
If you want to try this out on your computer, you can obtain the data from this link and export it to a CSV file for Excel.
To load the data into the data model, follow these steps:
2. Navigate to the downloaded file and import.
3. After selecting “Import,” the following popup will appear. Select “Load To” from the dropdown box next to Load.
Note: if you select “Load,” the data will load into an Excel worksheet, but some of the rows will be omitted because Excel’s row limit is 1,048,576
4. In the Import Data Form, Select the “Only Create Connection” radio button and check “Add this Data to the Data Model.”
5. In the Data Tab, when the Queries & Connections icon is selected, the Seattle_Police_Department Query will appear in the panel on the right.
Now that the data is loaded, it is ready to use. For our purposes, we won’t worry about doing the typical preliminary steps of data exploration or cleansing prior to generating the map. Instead, we will proceed directly to creating a heatmap of the number of incidents, with the ability to filter by offense type. Here are the steps to creating the heatmap:
2. From the Field list, drag latitude and longitude over to the Location box.
3. Drag the General Offence Number over to the Height box.
4. In the Height box, select “Count” as the aggregate function.
5. Drag Offence Type to the Filter box
6. Select the Heatmap Icon (4th from left) under Data in the Layer panel
7. Change the name “Layer 1” to “Offenses.”
8. Zoom into the Seattle area and view the heatmap.
If you are interested in seeing a map that only includes the various types of assault, then the filter checkboxes can be used to filter the data and display the corresponding heatmap.
Now that you know how to access and set up a map, you can try to gain further insights into the data by experimenting with layers and fields. If you can’t seem to create the map that you want, our team of Excel experts is available to assist.
Let us help you design and develop a rock-solid solution for your firm. Contact our team to schedule a free consultation by calling 1-800-682-0882, or visit our website at ExcelHelp.com to submit an inquiry online.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.