Moving Forward Blog

Microsoft Excel Power Map – Custom Maps – by R. McNeely TW BI Practice Mgr

  • 16 Dec, 2014
  • C Nelson

Example: Accounts Receivable Office Layout:  The Accounts Receivable Office Layout example will take advantage of the Microsoft’s Excel Power Map – Custom Maps.  Power Map Custom Maps allows you to overlay data onto any image.  A Custom Map utilizes the X,Y geometric coordinate system.  I first created an office floor layout in Visio to represent a typical accounts receivable office (see image 1).  I then saved a version of the floor layout as a JPEG.

Image 1

Aligning the image with the data can be tricky.  I tried various options, and found that aligning the data with the image pixel coordinates is the most precise.  I opened the Office Floor JPEG in Microsoft Paint and captured the pixel coordinates for each chair (see image 2).

Image 2 The data set includes an Account Representative Name, their X,Y chair coordinates.  The Acct Rep Team, Date, Collection amount, Liquidation percentage, and the average age of their portfolio (see image 3).  Make sure you have the latest update for Microsoft Excel by opening Excel and selecting File -> Account -> Update Options -> Update Now.  Having all the updates will make sure you have the Custom Map capabilities since it is a new feature to Power Map. Image 3

Now we can combine the image and the data in Power Map.  In Excel select Insert -> Map -> Launch Power Map.  A round earth will first appear on the screen.  Select your X, Y columns from the data set, and map them to X Coordinate, and Y Coordinate accordingly.  Power Map will prompt you below to change to a Customer Map (click yes).  You will now see the Custom Map Options.  Browse for your JPEG as the background picture, and select Pixel Space to align the data via pixel X, Y coordinates (see image 4).

Image 4

Now you can apply utilize the typical Power Map features like multiple layers, bar and heat map visualizations.  I used the filter feature to display only the current month of collections for a few of the layers (see video).




Share This Story