Back
Excel

Power Map for Excel–May update for Office 365

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Today’s post was written by Igor Peev and Daniel Witriol, program managers on the Power Map team.

In the May 2015 update of Office 365 we are proud to introduce a new core feature to Power Map: Custom Regions. The Custom Regions feature allows data to be mapped to the regions that matter most to you, even if they are not the traditional zip code, county, state or country regions. Custom regions are useful for scenarios involving sales districts, school districts, congressional districts, land lot development, crop rotation, geological analysis, and anything else using custom-defined polygons.

The Custom Regions feature in Power Map allows you to import KML or SHP files which contain custom polygons. Once imported, you can use custom regions in the same manner as you would use standard regions such as zip codes.

KML files can be created with a variety of free tools which are available on the Internet, you can read more about them here. We also support the import of SHP files, you can read more about the SHP file format here. Note that for SHP files we only support the WGS 84 standard coordinate system projection, more info here.

Let’s walk through a specific example. When we can, we like to work with data that can have a social impact. In this case, we’ll analyze data about the homeless rate among students in Washington state school districts.

We obtained the data from these two websites:

At a high level the data looks like this:

Power Map for Excel May update for Office 365 1

There are columns for the school district name, the school year, the county and state the district is located in, and stats breaking down the number of homeless students in the district. Power Map can help visualize this data for authorities trying to reduce homelessness in our communities.

Let’s start exploring the data. By default, Power Map detects the standard geographic fields, which can be used in the visualization. This is what you will see in the UI when you first open this data. The default geocoding level was automatically selected to County level.

Power Map for Excel May update for Office 365 2

But today we are interested in comparing the data between School Districts. To do this, let’s add School District to Geography and Map Level and work with it by mapping it as a Custom Region.

Power Map for Excel May update for Office 365 3

Any time you select Custom Region (.kml, .shp) from the drop-down, Power Map will ask if you want to import custom regions.

Power Map for Excel May update for Office 365 4

Select Yes and browse to the KML or SHP file you want to use. Note you only have to import a region file once per-workbook. Afterwards you can re-use it by simply selecting Custom Region and choosing No to import.

At this point, you can set a friendly name for your custom region. This is helpful when working with more than one set of custom regions. In the case of SHP files, there is also a drop-down allowing you to specify which field in the SHP file contains the names of your region. Power Map tells you how many regions will be imported in the workbook. Select Import and your custom regions are available to use.

Power Map for Excel May update for Office 365 5

To see the highest percentage of the population of homeless students by district, use a shaded regions visualization. In this example, Power Map shows that in some coastal districts over 50 percent of their students have been homeless, and the higher percentages of homelessness occur in rural areas.

Power Map for Excel May update for Office 365 7

To see how the homeless student percentages in the School Districts change over time, add the Year. This provides further insight into changing conditions and patterns which affect the problem.

To manage custom regions after they are imported, select Import Regions on the ribbon.Power Map for Excel May update for Office 365 8In the dialog, you can import a new region set (aka file), delete an existing set, or replace a set should the boundaries and descriptions change.

Power Map for Excel May update for Office 365 9

For more examples of what you can do with the Custom Regions feature watch our video:

New customization features

In addition to the new custom regions functionality, we’ve added some other great features. You can now customize the formatting of your legends. Simply right-click the legend and choose Edit.

Power Map for Excel May update for Office 365 4 UPDATED

You can also customize the background color of text boxes, date/time boxes, legends, and annotations to create emphasis or have it blend in. For example here we’re using color to emphasize an annotation.

Power Map for Excel May update for Office 365 11

To set the background color as transparent, choose the No Fill option. This is the default now for text and date/time boxes.

Power Map for Excel May update for Office 365 13Finally, we’ve removed the extra green bar chrome you saw when you hovered over text boxes, date/time boxes, legends, and 2-D charts. Now it’s easier to line these boxes up or position them in corners. Click anywhere on the box to drag it around and then double-click or right-click to edit, or right-click to remove.

Power Map for Excel May update for Office 365 14

We hope you enjoy this update.

You can download all the files used in the Custom Region demo at: Power Map samples.

—Igor Peev and Daniel Witriol on behalf of the Power Map for Excel team

Top