This is an Eval Central archive copy, find the original at depictdatastudio.com.

During Office Hours, Sue Griffey walked us through the pros and cons of 3 different mapping approaches:

Pin drop maps (made for free with Google)
Heat maps (made with Excel)
Tile grid maps (also made in Excel)
Overlay maps (tile grids, icon stacks, or waffles on top of traditional geographic maps)

In this video, you’ll learn about the challenges of each approach. You’ll also gain some how-to tips. Thanks, Sue!

What’s Inside

0:00 Intro: Mapping course registrants
0:58 Ann’s ideas from previous Office Hours session
1:21 Google’s “pin drop” maps
2:30 Excel’s heat maps
3:27 Excel’s binary maps (filled vs. empty)
3:42 Pros and cons of Excel maps
5:40 Pros and cons of tile grid maps
6:50 The “overlay” of the tile grid map on the geography map
7:33 World map challenges
8:06 Sue’s preference
9:20 How to make Google’s pin drop maps
9:55 Ann’s ideas and commentary
11:20 Sue’s homework assignment: Try icon stacks + an overlay
12:40 Sue’s final thoughts

Related YouTube Lessons about Mapping

Resources Mentioned in the Video

Transcript

[00:00:00] You’re watching Dataviz On The Go, the series where you learn dataviz time savers inside everyday software like Excel.

In this lesson, you’re going to learn about maps with Sue Griffey.

Several weeks ago, I brought to office hours some mapping I was doing. I have a short course and I wanted to map the number of registrants I’ve had.

Um, and I’ve been doing this over time, since I started the course, and I work globally, so I needed a world map. So I brought a couple examples, including a tile map that a friend of Ann’s and mine developed, Jon Schwabish, and I I was also working on what Ann had taught us about geography, the geography part of charting in Excel.

So I actually have three different things to show you and then I’ll tell you where I landed and why. So I’m going to show you the progression. Not surprisingly, when [00:01:00] I was doing this, Ann said, here are the three things you need to try. So you’ll notice as I switch each topic in the bottom right corner is a check mark of what we’re starting on.

The data set. That is really easy, 79 countries, 652 people. And two years ago for my infographic at the end of 2022, I used something that was in Google. It’s a free, it’s not just a Google map. You go to your own map settings and they give you a world map. You can look at it and I hadn’t used it then for two years until this time.

Both times, it’s only required me to upload a CSV file, which is good. Now, you can see the little white panel at the top, and I’ll show you after it’s in the next slide. I decided to start with this because I know, knew how to use it. Um, they’ve added a few more features. So, When I [00:02:00] added the CSV file, it automatically graded, as you can see in the top right, um, from dark gray where there were 56 or more to light gray.

And, uh, I discovered I could choose to, choose to show data labels, which it couldn’t do a couple years ago. So what it looks like up close, that circled five, it, you can see it, but it’s not that easy to see all the data labels. You can’t do anything more. So that was the map I started with. And then I went back to Excel to try to figure out what Excel would give me for a map, because Ann’s examples have used data labels.

State, U. S. state level maps only, as I recall. And I discovered, oh, there is a way to do a heat map in Excel world charting. I’m going to show you the examples, then I’ll show you the behind the scenes. And I discovered that it did let you put in data labels. Now, obviously, this isn’t all [00:03:00] 79 countries. So, here’s my plus minus.

It was pretty easy, similar to Google Maps, to create this map. You cannot change anything except the color for the gradient. You can’t annotate it. I can’t even change the plot size. And in fact, when I pasted this version, the previous version, in as an ex, as a Microsoft Excel object, I couldn’t even move it around very easily.

But I was also able. With, um, just a slightly revised dataset to get a binary map out of it and so I’ve tried both. And they’re both very nice for an overall look, for sure. And easy. Here’s my comments and thoughts. Things I tried and funny things I found. So Ann talks about creating the geography list, and you can go, um, tag a list of countries or states, hit the geography button, and then you get a list that looks like this one [00:04:00] that has some little symbol before each country name.

But I discovered I accidentally had, because Excel always wants you to put the labels and the data in before it’ll give you a chart, I did. And what I came up with, for some reason Canada and India came up, their counts came up with a ward in Kolkata in India. I don’t know why. So when I took that off and just made it plain data back to what it is on the bottom left, then it was fine.

So I used the plain data, I went to more map charts, that’s where you get the world map view, but you only get that one map. So you can do it with or without the geography designation, meaning if you just have a list, don’t worry about defining it as geography. This is a version I did do with Geography. So, this is one of the two I showed you, and this is the default color scheme.

Here’s the variations. It gives you these three variations only, [00:05:00] and it gives you these three color variations, or these several color variations, but you can’t mix and match. You can’t, you have to choose them, and then it defines the grades automatically. And these are the only choices you have, um, the, the top and bottom are automatically ticked.

When I tick data labels, then I got the data labels, but you can see I got them in very few countries. Not that I expect a U. S. map on PowerPoint to be able to show, I mean a world map, to be able to show everything there is, uh, in 79 countries, especially some that are very small, like in Europe. And then We talked about going back to the tile world map, and Ann wanted, um, had helped me put the numbers in.

She helped me correct the formula. We figured out how to do this. This came from John Schwabisch, who Ann was talking about a few minutes ago. And Ann helped me get the formula for the data labels. So the data [00:06:00] labels come right next to the country name, which was in there. The way John has it set up and something’s locked down because I can’t change a color if I move things.

There’s a whole formula of how, a process of how I had to paste things next door to the one I wanted to put in and then paste it again to get, to, to not get a change in color and to get the right name of the country. For my audience, they won’t be necessarily familiar with a tile map. This is, you know, if someone told you this is the world, you might see it, but without telling you, I think it’s still really awkward, even though it’s appealing to me to have every country name labeled, and for people to be able to pick out their countries easily, which we can’t do on a general world map.

So I did try and, uh, sort of an overlay of a world map, um, and tried to see if that would be better. [00:07:00] It, I couldn’t get it underneath the Excel grid, obviously, so the overlay is making things very light and I couldn’t, the one thing I wanted to do is to try to highlight around each cell where there was a country represented, make that white or somehow make it stand out so you could see in the whole map.

No matter what continent you were in, you could see a certain designation for where the countries are. But even with this, I wouldn’t use this for, uh, really much of any reason. Here are my challenges. I mentioned some of them. You can’t change the tile colors in the dark gray and the dark green. It’s really hard to read, uh, the writing in it.

No matter whether I chose, it was black, I chose white, it’s still hard to read it. There’s a stepped process I had to do to add new country data labels, so I can’t update this automatically. I could, the Excel one, I could do more quickly because it’s just a list of names and the counts that I got from [00:08:00] the pivot table.

But this is the three step process, Ann, I would have had to do. So let me show you my preference. I’m going to go over each one as it’s independent. The tile map, this is the tile map that does have numbers in it, and I was able to bold those numbers where people were represented from their country. But it’s hard to understand.

It’s a world map. It’s hard to make, and it’s hard to update. The Excel map, slightly blurry I know, but the Excel map is easy to understand that it’s a world map. It was pretty easy to make, but there weren’t many adjustments I could make, and it was limited for me. And then the Google map, where the countries are already labeled for me, and most of them will stay in, and people can probably pick themselves out pretty easily, even at the, uh, Map level.

For me, that’s the, [00:09:00] that was the one that had the most adjustments. You could change the marker, uh, color. I like the gray because it helped, um, against the green. And this is where I went. I’m probably just going to go back to this if I do a map in the infographic for the end of this year. And I’m just wanting to show you all.

This is how easy it is. You go to this place, you create a new map, and, um, I will put this link in the chat for anybody who’s interested in it, but I think I’ve exercised in all three mapping options to the extent that I could. And definitely I looked at the return on investment for how much time it would take me to do something or not.

So I’m, I am going to share the recording with Jon cause I want him to see what we’ve done with this with your help. So that’s everything. Thank you so much for sharing behind the scenes of your thought process. You [00:10:00] went through so many different types of maps. Um, this one that this tool I was not aware of, people had told me about this, but I hadn’t actually seen the screenshot of this before.

In map making terms, we call it a pin drop map, like if you’ve ever gone to a restaurant or a national park and you put the physical pin in of where you’re from. This is really nice for city level data, in particular, would be at strongpoint. I think the fact that it can do that Shading to show the amount is really promising too.

Um, I totally agree with you that kind of like the regular map with the gradients because you can’t really label all the countries or I don’t know. It’s just, it’s a lot of detail that maybe you don’t even need. I’m still a fan for you of the binary map. Just showing where people were from or not. But again, I’m an outsider to your data.

So for me, I’m like, wow, look at all these different countries that are [00:11:00] represented that Sue gets to work with all these different people. And for me, that’s, that’s so amazing. But for you, that might be like, yeah, duh. I know that that’s old news and you might need a higher level of detail. Um, the tile grid maps, they’re so promising for, I think, small Smaller geographies, but again, like the world map, like you said, what are people even looking at?

Okay, can I give you a homework assignment in all your spare time to try next? More homework? Absolutely. Um, the overlay, the overlay was so promising. I saw one, I saved it on LinkedIn, I saw this two days ago, I, uh, Shelley C., who I should be connected to, I guess, I’m not following, I don’t know her last name, uh, I guess she made this?

Yeah, at the Associated Press. This is kind of an overlay, right? This is, can you see how this is similar to what you had? It’s Electoral College Units. Not quite tiles, uh, [00:12:00] not an icon array. Stacks? Icon stacks, I think would be the technical name for this. All of us Dataviz people disagree what these are called.

Um, over top of the map. So there might, there might be some type of overlay with yours that could work. I wouldn’t rule that out. So in all your spare time, if you fiddle with the formatting there and get anything promising, let me know. I’d love to see if that could be developed further. Well, this is intriguing because it also gives you the numbers.

You know, it’s kind of like a waffle chart made in the form of the states, um, the states outline. But anyway, so I did all the work for people to know what their choices are so far. This, I will say for me, it’s got to be free. It’s got to be relatively quick. I agree the binary map does when I made it. I was like, oh, wow, that’s great.

But I’m also wanting to share at the end of [00:13:00] this year, I’ll probably write everybody from every course and just say, just want you to be able to see how many people from your country and things like that, just so people Recognize. Because there are some countries where in Africa and Asia that I’ve had a lot of folks, Nigeria and India primarily, but lots of Europeans, lots of, I’m getting many more U.

S. folks now, which I didn’t the first two years. Thank you for all your encouragement.