Map Charts

Learn Excel from MrExcel Podcast, Episode 2061: Map Charts in Excel

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  It's November 23rd, and if you have Office 365, at the end of this month you're going to be getting a great new feature back here on the Insert tab called a Map chart, a Filled Map chart.  Cool, huh? 

Now, here's the problem.  If you are a customer of Microsoft and you gave them the $400 to buy a stand-alone Office DVD or streaming or, you know, your Volume Licensing customer, unfortunately, Microsoft is at war with you, right? They don't seem to appreciate the 400 bucks you gave them, and this feature will not be in your version of Office.  They're trying to get everyone to switch over to Office 365.  And you see they have this great new page of the Office 365 Exclusive.  I bristle when I hear that.  Alright, that means if you're not giving them $10 a month, you're not going to get the new features which I think is disappointing. 

Alright, but, let's say you're on Office 365 and you have this feature, you get this feature here at the end of November.  It works at several different levels.  So here I have data by Country, and I'll choose a Filled Map, alright?  BAM!  There it is.  How cool is that.  So for every country that I have, we have the different colors here and there is a bit of customizing that we can't do.  Of course, they offer the different styles here, different background colors but, you know, we have more than that.  Now, this happens to be Orange production so maybe I can choose the orange color and that works out great.  But if we come back here to the Format tab and go to Chart Area, and choose Series Oranges and then Format Selection, over here we now have a lot of choices.   They offer several different map projections: the old-fashioned Mercator projection, the Miller projection and the Robinson projection.  So you could choose whichever one you want.  Of course, the Robinson and Miller are better than the ancient Mercator projection.  Map area is Automatic or Only regions with data.  Now, this is kind of interesting in this chart.  I would choose Only regions with data, we only get the countries that have data points, alright.  And I'm not sure that's appropriate here but later on, it might be.  And then Map Labels: Best fit only or Show all.

So, let's add those map labels and go to More Data Label Options.  Yup, see.  That to me seems like a bug.  In order to actually be able to format the map labels, you have to choose the Map Labels and then Format Selection and then we can control whether we're showing the Value or the Category name.  In some cases, I want to see the name of the country which I think makes more sense.  Alright, and then we'll go back to our Series.  Alright, so now we get the names of the countries and we can choose map labels are Best fit only or Show all.  And then also, if we go to Only regions with data, you know, that might actually make sense now that we've added the various regions with data.  Not sure. 

Alright, so this is at the country level.  We can also do things by state.  So we'll Insert, insert a Filled Map, alright.  And there, the highest numbers get the darkest color, lowest numbers get the lightest color. 

Okay, now again, let's try the same thing we do with the country.  So we'll go into: Format, choose the Data Series, Format that Selection.  So, we want to see Only the reasons with data, that'll zoom in for us just on that portion of the country.  Series Color, we can choose the colors that are used here.  Now in this case, I think I'm going to go with a dark green to light green, like that.  Or maybe you want to go from a dark green to a really dark green, alright, your choice there.  Or even from a really light green to a really dark green.  And there's also, there are other things which I've kind of experimented with but I really have not found anything here that I like with a diverging three colors.  So, so far, fan of the Sequential 2-color like that.  Again, let's try and add Data Labels, alright, by default they're going to show me the Values, and I'm not sure I want to see the Values, like maybe I don't.  I’m bad at geography, I need to know the state names.  So you have to select the Data Labels and then go to Format Selection, and then you can say that I want to see the Category Name, but not the Value.  Category Name but not the Value.  So, Ohio, West Virginia, Virginia, North Carolina, South Carolina, Georgia and Florida, is trying to fit them in there, the best that it can. 

Alright, it also goes down to the county level, so here I have all the counties in Florida and their population, we’ll take a look at that.  Insert, Map, choose a Filled Map.  Alright, and so now based on population, we're getting our various values.  The other one that I kind of like is, you know, I'm not always a fan of this low-to-high kind of thing here, but creating categories.  Alright, so I take the 60, some counties from Florida and I want to show them based on, you know, their water.  They’re on the Atlantic Ocean, Gulf Coast or they're landlocked.  So, Insert a Field Map here, alright.  You can see all these counties in blue touched the Atlantic Ocean, counties in orange are the Gulf or the North Gulf, and then the counties in gray have essentially no access to big water.

Alright now, again here, if we want to see the county names: first, you have to add the Data Labels and for some reason, the + sign is missing here.  I've no idea why.  Maybe I've - I don't know, who knows.  Alright, so we'll come up here and Add Chart Element, Data Labels, and I want to Show the data labels, and then go to Data Labels, More Data Label Options where nothing appears to offer what I'm looking for.  So we have to click on the Data Labels, that selection, and then go to Format, Format the Selection, and then here’s where they've hidden the very same.  So we'll take the Value out, put the Category name in and if we make the chart larger we’ll actually be able to see the individual counties like that.

Hey, if you need to change the colors of a category chart or any of the new IV charts, beautiful tip here from John Pelletier.  He gave me this heads up when I complained that it was so hard to change it via the page layout.  Way to do is click on the Legend, that selects all the legends, then click on one legend, and then Ctrl+1 to format that and then we can change the fill color of that legend entry which will change everything.  So maybe we go to a light blue there for the Gulf, open this up, go to More Colors, change a different- to a different color there, click OK and so on, by formatting the legend.  Thanks to John for that awesome tip.

By zip code, yep, they go to the zip code here.  So we have all the zip codes in Brevard County, Florida.  Insert a Map, Filled map.  Alright, now this is one definitely where you want to look at just the items with data because they're showing us all of Florida and you can barely see what's going on there.  So, we're going to go to the Format tab, and Series Value, Format the Selection, and then Map Area: Only the regions with data.  And that'll zoom in on Brevard County like that.  Alright, so suppose I have a map created from a pivot table.  Here under PivotChart you'll see that they have a whole bunch of different types of charts but you can't create this type of chart with data inside a pivot table.  Okay, so what we're going to do instead, we're going to do instead is create something that is not a pivot table.  So here's the pivot table that's going to have the data for the chart and I even have a slicer here so I can choose which type of citrus production to use.  And then, off on the left-hand side here, I have the county, the state and then a VLOOKUP into the pivot table to return the data.  Notice I used IFNA there to return as 0 in case I happen to choose a crop that doesn't have values for one particular county here or something like that. 

Alright, and then also out here on the right-hand side, to get this label to work, I created a second pivot table and hook this pivot table up to the slicers.  And this pivot table just has the crop name and you'll see that as we choose - So if I would choose Grapefruit and Oranges, I get two rows there.  Another Office 365 exclusive is the TEXTJOIN function.  TEXTJOIN separated by a comma space, Ignore blank value is True and just a large area here to grab all of those items.  I'm going to try and use that as a title in my map chart.  Alright, so we'll create a map chart from this data over here, so Insert, choose Maps, alright.  And we now have citrus production by county.  And I want to have this react to those slicers there so it will be a cool chart.

So, a couple of things we want to do.  We want to change the colors.  Of course, I want to have, at least choose the orange-ish one, like that.  Okay, do i want to show the whole state?  Ahh, I'm not sure.  Let's see what it looks like with just the counties that are selected.  So if we go to Series Grapefruits and Format Selection, we’ll change this to Only regions with data which will kind of zoom in a little bit.  Alright, and then as I choose from the drop-down here, if I choose just White Grapefruit we can see that these two counties are the leading counties there.  Tangerines, Honey Tangerines, Tangelos, All citrus, alright.  So, this is cool. 

But now I want to make this chart title react and grab the value from the cell.  Alright, what if we want to get this chart title to come from a cell in the worksheet?  Right, that would be beautiful.  All Excel charts have been able to do this for decades, but IV charts tell me not to do it.  It's really, really frustrating.  So my work around here is replace this chart title with spaces.  Don't get rid of the chart title, we need to keep it there to keep the plot area.  See, we are now allowed to resize this plot area so I need to keep that fake chart title up there.  And then with the chart selected: Insert, go to Shapes, and the text box.  Draw a text box there above where the title should be or where the title is, the blank title is, click that text box and then with the text box selected, equal, and click on the formula cell like that, and you'll actually get a title that will now update along with the chart.  Frustrating, we have to go to that extra bit but that's the way it is.   

Alright, well there you have it.  November 2016 brings a new Map Chart feature exclusive to Office 365.  If you’re a customer, good paying customer who bought something other than Office 365, so sorry for you.  This is different than Power Map because it can be embedded right in your worksheet.  It's only a shape map, not points, can't do points.  Just shapes, has to be outline shapes, can be at the county level, state, country, state, county, zip code.  In other countries, we've learned this: if you put the word Region, if you don't have like Counties, if you have Region, you have to say Regions instead of Region or it doesn't work. 

I showed you how to change the color, how to change the labels.  You have to click on the label itself and then Format Selection, choose a series, you can change the projection, looked at Category Maps where we change the colors of each county instead of the sliding scale like this.  And then, like other new IV charts, can't have the chart title come from a cell.

Alright, well hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.