MrExcel.com - Your One Stop for Excel Tips & Solutions

Use an Alternate Map for 3D Map

June 08, 2023 - by

Use an Alternate Map for 3D Map

Maybe you want to plot quality performance in a manufacturing plant. Or which displays in a retail store generate the highest sales. The 2015 edition of Power Map lets you replaced the Globe with any alternate map image.

The first step is to get a 2-dimensional image of your floor plan.

Using Photoshop or another tool, figure out the X and Y points of various locations on the image. Be careful, because Photoshop measures the Y position from the top of the image, while Excel XY Scatter charts measure the Y location from the bottom of the image. If you know the height of the image, it is easy enough to label the first column PhotoShopY and then build a real Y column of =Height-Photoshop Y.

As you add the data to Power Map, declare those fields to by X and Y. Click on Custom Map and fill out this dialog.

Do not let Excel figure out the X and Y values (unless you have a data point in the each corner of the image.) Fill in the exact pixel width and height in pixels. If you did not calculate the Height-Y, you can use Flip Axis for the Y.

Upload your picture.

In the Custom Map Options, you specify the Min, Max, Scale, Offset and Flip Axis for both X and Y locations. There are checkboxes for Auto Detect X and Y values and to Swap X & Y data. At the bottom, an icon to Import picture as background.
Figure 1139. Adding a picture as the background in 3D-Map.

The result is a flat map where you can use the navigation keys to fly through your shop floor, examining the quality scores.

The Custom Map is an Excel grid listing 12 Excel functions. A column appears next to each, indicating the popularity of the function.
Figure 1140. My “shop floor” is Excel….

There is a filter feature in 3D Map. There is one oddity: the Filter dropdown will never show more than 50 items. If you need something beyond the first 50 items, you have to use the Search box.

When the Filters pane says (Not All Items Showing) at the bottom, use the Search pane to find what you are trying to filter to.
Figure 1141. Filtering in Power Map.

This article is an excerpt from Power Excel With MrExcel

Title photo by Possessed Photography on Unsplash