MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table on a Map

September 29, 2017 - by Bill Jelen

Pivot Table on a Map

Display an Excel Pivot Table on a Map using the new Power Map or 3D Maps feature in Windows versions of Excel. Animate data over time using Excel. Zoom in on the neighborhood of each store location.

3D Maps (née Power Map) is available in the Office 365 versions of Excel 2013 and all versions of Excel 2016. Using 3D Maps, you can build a pivot table on a map. You can fly through your data and animate the data over time.

3D Maps lets you see five dimensions: latitude, longitude, color, height, and time. Using it is a fascinating way to visualize large data sets.

3D Maps can work with simple one-sheet data sets or with multiple tables added to the Data Model. Select the data. Choose Insert, 3D Maps.

Insert 3D Maps
Insert 3D Maps

Next, you need to choose which fields are your geography fields. This could be Country, State, County, Zip Code, or even individual street addresses.

Geography Fields
Geography Fields

You are given a list of fields in your data the drop zones HEIGHT, CATEGORY, and TIME.

Height, Category and Time Fields
Height, Category and Time Fields

Here is a map of Merritt Island, Florida. The various colors are different housing allotments. Each colored dot on the map is a house with a dock, either on a river or one of many canals dredged out in the 1960s and 1970s.

Merritt Island, Florida Map
Merritt Island, Florida Map

Using the time slider, you can go back in time to any point. Here is when NASA landed the first man on the Moon. The NASA engineers had just started building waterfront homes a few miles south of Kennedy Space Center.

Time Slider to Go To 1969
Time Slider to Go To 1969

Use the wheel mouse to scroll in. You can actually see individual streets, canals, and driveways.

Wheel Mouse to Scroll In
Wheel Mouse to Scroll In

Hold down the Alt key and drag sideways to rotate the map. Hold down the Alt key and drag up to tip the map so your view is closer to the ground.

Rotate the Map
Rotate the Map

Hover over any point on the map to get details such as last sale date and amount.

In the default state of Power Map, each data point occupies about one city block. To be able to plot many houses on a street, use the Gear Wheel, Layer Options and change the thickness of the point to 10%.

To get the satellite imagery, open the Themes dropdown and use the second theme.

The 3D Maps feature provides a completely new way to look at your data. It is hard to believe that this is Excel.

By the way, my thanks to Tony Giannotti at Serving Brevard Realty for showing me many of the houses in this area. If you need a house near Merritt Island, give Tony a call.

Thanks to Igor Peev and Scott Ruble at Microsoft for this cool new feature.

Illustration: Libby Norcross
Illustration: Libby Norcross

Watch Video

  • Power Map is in Office 365 editions of Excel 2013
  • Power Map Preview can be downloaded for Excel 2013
  • In Windows editions of Excel 2016, renamed to 3D Maps
  • Works with one worksheet (as a Ctrl + T table or not)
  • Works with multiple worksheets added to the Data Model
  • Once in Power Map, choose Geography
  • Category changes color
  • Height plots the height
  • Consider a flat map to see the whole world
  • Add a time field to animate over time
  • Wheel mouse to scroll in
  • Alt + mouse up or down to tilt
  • Alt+ mouse left or right to rotate
  • Add labels
  • Theme 2
  • Use the time scrubber to animate data over time

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2038
  • PivotTable on a map
  • I am podcasting this entire book click
  • the I in the top right hand corner to
  • get to the playlist for all of these
  • videos alright this time we're going to
  • take a pivot table and put it on a map
  • this is data that people from who
  • watched episode mm mmm sewed filled out
  • this data I asked for named country and
  • postal code I had to fill in a few
  • cities and states for countries that I
  • guess the postal code wasn't recognized
  • by Bing if you're in Excel 2016 it's
  • called a 3d map if you're in Excel 2013
  • and you're paying the office 365 fee
  • it's called power map if you're in Excel
  • 2013 then you don't have this on the
  • insert tab go out and download power map
  • preview and you can get a beta version
  • that's pretty close to what I'm going to
  • show you today all right so I want to
  • take this data and we're gonna put it on
  • a map okay so it automatically
  • recognized the city country state and
  • zip code as those fields I would be
  • allowed to add more fields if I have
  • miss named one I'm gonna try and change
  • things by taking the category field and
  • putting in category that will get me
  • different colors so a B and C those
  • really aren't that important so I'm
  • probably gonna get rid of that legend by
  • pressing delete I have a numeric field
  • this was just a ran back to the height
  • just to add some variability to each
  • place and now that I have these all I
  • also added a date field I'm gonna put
  • that in time alright so now that I've I
  • have these fields on the the the field
  • list okay so now that I've applied the
  • right fields to the field list I'm
  • actually going to close the field list
  • so I can see more of the map and over
  • here this tour pane I'm going to close
  • and that and here's what we have so
  • every row on the excel screen with 83%
  • accuracy and a bunch of these were it
  • just
  • veracruz it wanted it without a space
  • and this one I don't understand it was
  • exactly right and so there's only one
  • here that it couldn't find
  • so all of the data except for one point
  • is here and I can cruise around the map
  • okay let's take a look at some of the
  • different things we can do here I'm
  • starting out with the the column chart
  • and with the column chart I've already
  • changed the colors of the categories now
  • to navigate here I can just click and
  • drag to recenter I can control I can
  • just use the wheel mouse to zoom in and
  • out and then the Alt key the Alt key if
  • I hold down the Alt key and drag up I
  • get to tip the map drag down I get to
  • look at it from above and then left and
  • right I can rotate the map and look at
  • it from different areas I always like to
  • add map labels here I'm great at
  • geography but I'm not awesome at
  • geography so the map labels help and as
  • you zoom in the map labels will
  • definitely help identify cities and
  • things like that all right the other
  • types of maps here I'm gonna change over
  • to a country map and then the filled
  • area all right so now we actually have
  • the darker it is the higher the rating
  • and then the various items now the
  • problem is though I can't see this is
  • awesome how great the MrExcel world is
  • right there's people from all over who
  • filled out my survey and so since I want
  • to see the whole map at once I'm going
  • to use a flat map and then zoom out all
  • right and that lets me see the whole
  • world so if I need to see the US and
  • Australia at the same time opposite
  • sides of the globe I can still manage to
  • do that the other ones I'm not a big fan
  • of the heat map it just doesn't look
  • good to me
  • it seems kind of meaningless and then a
  • bubble chart alright so we'll go back to
  • the the the clustered column chart here
  • and then this is one feature that is not
  • there in the preview version so if you
  • just have railer excel 2013 I'm going to
  • add a filter by country and ask for just
  • USA
  • and then once I have the USA then I'm
  • gonna show the data by zip code all
  • right so we have a vault all of our data
  • there and we'll take the category field
  • and put that back in so that way we have
  • the different colors all right so now we
  • have a nice map here of all the folks in
  • the United States who answered the
  • survey but you'll see this time
  • indicator up here and I've had a random
  • date some date in September and so I can
  • watch this date this data accumulate
  • over time I'll press the play button and
  • as the dates change up there you'll see
  • the various dates pop in now this is the
  • date you fell out the survey it's just a
  • completely random date so I can
  • illustrate that feature and then the
  • other thing we can do is they have a lot
  • of different themes here we always start
  • out with this white theme but the third
  • theme actually the second thing thing
  • number two will apply a satellite map so
  • let's zoom in here zoom in zoom in zoom
  • in and so there you can actually see you
  • know what's going on here's the launch
  • pads at Kennedy Space Center and that
  • should be me right there somewhere in 3
  • 2 9 5 3 all right so just an amazing way
  • to look at data this is all excel data
  • right using power map in Excel 2013 the
  • office 365 or power map preview in Excel
  • 2013 or 3d maps in Excel 2016 great
  • great way to blow away your manager and
  • of course you can you can actually
  • create scenes and then create a video
  • and a tour and all things like that
  • which I've never actually really used
  • that I always just want to be able to
  • interactively scroll in scroll out and
  • see various items you know and you can
  • even you can even hover and see you know
  • all the values there in case there's a
  • data point that's interesting so just
  • beautiful
  • beautiful bit of technology in power map
  • alright power map is tip number 39 in
  • this book buy the entire book you have
  • so much
  • knowledge in the palm of your hand click
  • that I up here in the top right hand
  • corner to get to a link where you can
  • buy the book
  • alright recap power map it is started in
  • Excel 2013 if you have office 365 there
  • was a power map preview for those in
  • Excel 2013 without office 365 in Windows
  • editions of 2016 it's there all the time
  • called 3d maps it works with one
  • worksheet ctrl T the data or multiple
  • worksheets and add them to the data
  • model you choose your geographies
  • category changes the color height plots
  • the height if you need to see the whole
  • world to use a flat map time field to
  • animate over time wheel mouse to scroll
  • in alt mouse up or down to tilt alt
  • Mouse left or right to rotate you can
  • add labels and then it's actually theme
  • to thing to is my favorite one with a
  • satellite map cool cool bit of way to
  • show boring Excel data on a mat hey
  • thanks for stopping by we'll see you
  • next time for another net cast from mr.
  • Excel

Download File

Download the sample file here: Podcast2038.xlsx

Title Photo: stokpic / Pixabay