{"id":4399,"date":"2019-01-19T18:31:00","date_gmt":"2019-01-19T23:31:00","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4399"},"modified":"2019-01-19T18:32:21","modified_gmt":"2019-01-19T23:32:21","slug":"4399","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4399","title":{"rendered":"Did you know \u2026 Excel can use maps to visualize data?"},"content":{"rendered":"

I remember visiting my uncle at a NASA design lab sometime in the mid-80\u2019s \u2013 it was a huge cavernous room that he explained used<\/em> to house the computer. A computer his graphing calculator could draw circles around. It was a powerful visual reminder how quickly computing technology advances \u2013 components are smaller, more powerful, and simpler to use.<\/p>\n

More than two decades ago, I wrote a visualization application that presented a graphical representation of the geographic distribution of records. Which is a long way of saying it showed where something happened to a lot of people. The application was part of a cooperative effort between the FBI and local law enforcement \u2013 a data mining project meant to identify serial offenders across jurisdictional boundaries I wanted to be able to visualize where<\/em> different types of crime were occurring and identify anomalies, so I built a program to do so. It took months to develop and took hours to crunch values and draw a map. The first time I used Excel to visualize frequency distribution on a map, I thought of that NASA computer room. What used to take a high-end Unix server with a RISC processor and tonnes (for the time) of memory \u2013 not to mention an entire summer of code development \u2013 is clickity-click and done on my little laptop. And the results are nicer:<\/p>\n

<\/p>\n

How do you create this type of visualization? First you need data with something that is mappable \u2013 the example here is going to show the office locations listed in PeopleSoft. Click within the data set.<\/p>\n

<\/p>\n

On the ribbon bar, select \u201cInsert\u201d then select \u201c3D Map\u201d in the \u201cTours\u201d section.<\/p>\n

<\/p>\n

If you have not used it before, you will be asked to enable data analysis service.<\/p>\n

A new window will be displayed \u2013 select the column you want to map. Here, I am using zip codes, which is mapped to the \u201cPostal Code\u201d field in my spreadsheet. If your fields do not map automatically, you will need to click the drop-down next to a location data type and select the appropriate column.<\/p>\n

<\/p>\n

There are different types of visualization \u2013 here, I have switched to a \u201cheat map\u201d where the color of the blob represents how many records fall into this zip code. It is a quick way of identifying clusters \u2013 hot spots.<\/p>\n

<\/p>\n

You can control the look of the map as well \u2013 here, I have switched to a flat map and added location labels.<\/p>\n

<\/p>\n

If you would like to include a copy of your map in another program \u2013 say, this Word document \u2013 select \u201cCapture Screen\u201d from the ribbon bar. You can also create a video to show an animated view of your map (zooming in on specific locations, rotating the globe to see people over in Mongolia)<\/p>\n

<\/p>\n

After you\u2019ve clicked \u201cScreen Capture\u201d, just paste and an image of your map will be inserted into your file \u2013 see!<\/p>\n

<\/p>\n

Going A Little Farther:<\/h2>\n

Data isn\u2019t perfect, and even when the data looks<\/em> good it may not map properly. My sister used to live on a street in New Jersey that does not exist on a map. The post office affirmed it was the correct address, but UPS and FedEx claimed it didn\u2019t exist. It was funny to me, but I wasn\u2019t the one trekking two kids down to the neighbor on the main road who nicely accepted packages for her. She moved before they ever got the address situation sorted, but I\u2019ve got first-hand experience with addresses that don\u2019t map in some systems but are perfectly fine in others. Why do I mention this? The map visualization provides a \u201cMapping confidence\u201d statistic \u2013 it is the percentage that appears above the box where you select the location data to be mapped. 98% is pretty good \u2013 there are a handful of records that don\u2019t appear on the map \u2026 but the data I am presenting is a decent representation of our employee office locations. A low percentage would indicate that your map does not accurately convey your data.<\/p>\n

<\/p>\n

What if my map confidence level is low? Click on the map confidence value to see what didn\u2019t<\/em> map. There are some marked with a result that is questionable \u2013 spot-checking them, 03109 is Manchester NH and 10001 is New York, NY. The one with no resolution, according to the US Postal Service lookup isn\u2019t<\/em> a valid postal code. If your data is wrong, fix it \ud83d\ude0a In cases where the data is<\/em> right but the application isn\u2019t confident about the location, you can add additional data to make the address more specific (here, I might increase the confidence by having the zip+4, or including the street address in my data set).<\/p>\n

<\/p>\n

You can filter data in your map \u2013 first we\u2019ll need some field on which to filter. Here, I\u2019ve added the employee\u2019s department to my data set.<\/p>\n

<\/p>\n

On the right-hand pane, expand \u201cFilters\u201d. Click \u201cAdd filter\u201d.<\/p>\n

<\/p>\n

Select the column on which to filter data. A unique list of values will be presented \u2013 you can scroll through it or start typing the value to search. Once you find what you want to display, click the check-box before the value.<\/p>\n

<\/p>\n

Now we are visualizing where people in my<\/em> department work.<\/p>\n

<\/p>\n

If your data is hard to see \u2013 records are distributed out fairly<\/em> evenly across the map \u2013 you can increase the area of influence to make smaller<\/em> clusters easier to identify. Scroll to the bottom of the right-hand pane and drag the \u201cRadius of influence\u201d slider to the right. If you have very<\/em> clustered data, you can drag the slider to the left to turn a large red blob into a more nuanced visualization.<\/p>\n

<\/p>\n

When you have finished visualizing your data, click \u201cFile\u201d on the ribbon bar and select \u201cClose\u201d.<\/p>\n

<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I remember visiting my uncle at a NASA design lab sometime in the mid-80\u2019s \u2013 it was a huge cavernous room that he explained used to house the computer. A computer his graphing calculator could draw circles around. It was a powerful visual reminder how quickly computing technology advances \u2013 components are smaller, more powerful, …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-4399","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4399"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4399\/revisions"}],"predecessor-version":[{"id":4418,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4399\/revisions\/4418"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}