I have a love-hate relationship with maps in Excel…
Pros of Excel Maps
- If you’re only making maps once in a while (i.e., you’re not a full-time cartographer), Excel maps are great because you don’t have to purchase/download/install/learn any specialty mapping software.
- Fast to make. Get started here: https://depictdatastudio.com/how-to-make-maps-in-excel-file-to-download/
- Works in Excel, PowerPoint, and Word (so it’s easy to copy/paste maps between those Microsoft programs, too).
- Easy to add your own branding (with Theme Colors and Theme Fonts).
Cons of Excel Maps
- Excel only makes heat maps by default (a.k.a. filled maps, choropleth maps). With behind-the-scenes tricks, you can also make hex maps, tile grid maps, tile grid trendline maps, and more.
- Currently only works with Microsoft 365. Time to upgrade, if you haven’t yet.
- Stickwithitness required. These work best for high-level U.S. maps (e.g., states). The smaller the geography (counties, zip codes), the more troubleshooting required. Making non-U.S. maps? Even more troubleshooting required. You’ve been warned. If you’re not tenacious, turn back now.
Can I Make Excel Maps for Places Outside the U.S.?
Yes!!!
When I was teaching data visualization workshops in South Africa, a woman from Eswatini asked whether she could map her country’s four regions.
After class, I attempted to make a quick YouTube video from my hotel room. I was planning to create a table with some pretend numbers… highlight those values… and insert a map.
And then I ran into all sorts of problems! Boo, Excel. I lost an hour to troubleshooting. I watched other YouTube videos. I read help forums. Here’s what I found.
10 Troubleshooting Tips for Excel Maps
In this video, you’ll learn the top 10 ways that Excel maps can get messed up.
Do you have more tips? Please comment and let us know.
What’s Inside
- 0:00 Intro
- 1:10 Dataviz On The Go
- 2:16 Making a new map – What didn’t work
- 2:56 (1) Make sure you’re on good internet
- 3:38 (2) Convert to Geography (or not)
- 5:20 (3) Make sure your headers are (correctly) labeled
- 6:02 (4) Convert to an Excel Table
- 6:45 (5) Spelling
- 7:13 (6) Leading or trailing spaces
- 7:54 (7) Special characters
- 8:19 (8) Add higher-level geographic details in a column(s) to the left
- 9:35 (9) Check alternative/former names
- 10:06 (10) Check language translations (e.g., both English and siSwati spellings)
- 10:30 Repeating the 10 troubleshooting tips
- 13:46 Your turn
- 14:13 Personal note from Ann
Download the Excel File
It’s here: https://depictdatastudio.ck.page/troubleshootingmapsinexcel
Read the Transcript
Ann K. Emery: [00:00:00] This video is turning out to be a hot mess. It was supposed to be a video like “Yes, you can make non US maps in Excel.” I made a YouTube video a while back, I’ll link to it right here. And it was about making maps in Excel. If you’re new to maps, you can watch that one first. It’s a great getting started video.
I had a US example. People commented like, “can I make non US maps?” Yes, you can. It was supposed to be that video, but then I ran into all sorts of issues. And this has turned into a, “I lost an hour of my life troubleshooting, so you don’t have to” video. And I’m going to show you 10 different things that I had to try to get this map to work.
And you’re going to just go through each of these items methodically with your map. And then at the end, please comment and tell me what the fix was. And hopefully we’ll all learn from each other.
I have a love hate relationship with Excel maps, as you [00:01:00] might be able to tell from, uh, my tone. I’m a little bit frustrated at the moment with how much troubleshooting we have to do sometimes.
So hopefully this video will just, you know, speed it up for everybody.
And by the way, if you’re new here, I’m Anne Emery. You’re watching Dataviz on the go. And I’m here in Johannesburg. Did I say that? I’m teaching a two day class and one of the women in the class, she was based in Eswatini, which you might know as Swaziland.
We had people from all different African countries in my class and we were making some maps and she raised her hand and she was like, “can I make maps for my country for the four regions of Eswatini in Excel?” and I was like, “yeah, of course.” And that’s the video this was supposed to be, right. Of, uh, making the Eswatini regions.
But I’m going to show you what I tried and what didn’t work. Right here. Okay. And then we’re going to create this “after” table. We’re going to have to reformat the table and I’m going to go [00:02:00] through these 10 troubleshooting tips, in order, of most common through least common. So please go in order as you’re troubleshooting and obviously let me know if there’s a number 11 or number 12 on this list
that you have to try that works for you.
All right. First up, here’s what didn’t work, right? You’re supposed to enter your regions. In this case, enter your values, which can be numbers. It can be, these are just made up percentages. It can be currency. Okay. You’re supposed to. So just highlight your table, go to insert, go to the map section and add a filled map, aka heat map, color coded map, choropleth map.
But of course Excel panics right away and it’s like, “uh, uh, uh, you have to have geographic data.” No big deal. We can do that, among many, many other things.
Oh, and I should mention when I was teaching this class in the [00:03:00] conference room, we did have internet, but it was like, you know, how event internet is. It was awful.
So I actually didn’t even get this far. We got stuck on. Cause I was like, “Hey. Mirela, like, come here. I made the map for you.” I was going to do that during the break in class, but we couldn’t because I wasn’t on good enough internet. And here in the hotel, I’m on like, iffy, iffy. It’s not perfect, right?
You’re not gonna be able to make maps from your airplane Wi Fi. From your public Wi Fi at the coffee shop that comes in and out, you have to have good enough Wi Fi. Okay, so earlier, number one didn’t even work.
We’re on number two now, which I expected to happen. We are going to convert to geography. Or not.
Okay. I’ll show you what both of these mean later. So let’s create the “after” table. I’m going to highlight these regions. I’m going to go actually, let me delete this one, right? Cause I want it to link to this. I want it to link to this revamped table I’m going [00:04:00] to make. This is a little hard to do with my one hand, but I got to hold my mini mic.
So you don’t get all the Background noises from the hotel. All right, where were we? Let’s convert these to geography. I’m going to highlight these regions, go to data, go to the little data types down arrow, say geography, please. It goes and connects to Bing maps and comes back down. So it takes a second to do right.
And now we have a map of the four regions. Except now we also have a new issue. We have this little caution sign, which when we click on, it tells us, sorry, we plotted 75 percent of your locations correctly. And you can see the three out of four, and this one is gray. Gray means like something was wrong with it, right?
There wasn’t any data. It tells us with the hover over, there’s no data. And I was like, but there is data there because even when you hover over the [00:05:00] little card and you can see that’s that region there’s data. So then I spent the rest of my hour, my agonizing hour, figuring out all these other things, watching other YouTube videos, reading blog posts and articles, reading help forums where Microsoft staff had responded.
One of the things that came up in a lot of help forum articles was to make sure your headers are labeled, and correctly labeled. That means if you don’t have the word Region here, you might run into issues. If you don’t have some type of column header here, you might run into issues. These words feed into the legend over here.
So if you don’t have it, you might get a error message, but it also just gives you a really poorly labeled legend. So you need to have some words there and they need to be correctly labeled. Like you can’t say that this is a country, but then it’s regions. It has… Obviously, [00:06:00] right? It’s gotta be correct.
Number four, I tried a good old fashioned Excel trick. I tried converting to an Excel table. This is a regular table, a generic table, a lowercase T table. A lot of times Excel tables can solve issues with pivot tables, pivot charts, slicers, so I thought, let me try. The, the trusty fixes, right? What you would do is you’d highlight your lowercase T table.
You go to insert. Here’s the official table. Yes, it has headers like we just talked about. Click okay. And. That, that doesn’t fix anything, right? Same issue with the map. Let me make sure you can read these.
All right. Number five, let’s try some spelling. I was not familiar with these regions before a few hours ago.
So I checked and double and triple checked all the spellings, against what the participants were saying, told me, and wrote for me, [00:07:00] against Wikipedia. I looked at all the websites to make sure that they were spelled correctly. They were. I copied and pasted them from the internet rather than even typing them in.
That wasn’t the case. I checked number six, because I copied and pasted them. I checked for leading or trailing spaces. I don’t visually see any leading or trailing spaces, but I thought let’s check everything Uncover every stone, right? A trailing space would be if you’re typing in or copying and pasting and you accidentally have a space that can cause a lot of issues in Excel and with formulas.
I’m thinking of lookup formulas that rely on exact matches with spaces. Sometimes if you copy paste, or if you download your data from a database or external source, you might have a leading space. I checked them all, that, that was not the issue. I checked for special characters. I’m thinking of Spanish speaking countries [00:08:00] where the names might have accents or they might have the little squiggle over the N.
A lot of different languages other than English have special characters. So you’re going to try with and without to see which one kind of, uh, jiggles Excel correctly, like shakes it so that it works. Not the case here.
Um, number eight, I saw this one on a lot of official Microsoft forums as well. Staff were saying, try adding higher level geographic details, which would go in a column off to the left.
Higher level geographic details are things like this. We’re going to add country so that just to make sure Excel knows we’re talking about Eswatini. So you’re going to type in your header, obviously, and then you just repeat the country name
all the way down. Not just once, right? So that it, it knows these are regions within East Swatini. This is important because a lot of [00:09:00] names are multiple places in the world. So I live in Windermere, Florida, but there’s Windermere, England. There’s a Windermere in Johannesburg. There’s lots of Windermere’s around the world.
So I can’t just make a map of Windermere. I would need to tell Excel, this is the Florida version, right? So I thought maybe these may, who knows, right? Maybe that’s the case.
I also mixed and matched. All of these troubleshooting tips. I thought, well, maybe this has to be converted to geography. Um, but we’re still, this one’s still gray, right? So I’m trying all the things.
Uh, number nine, check alternative or former names. The participant was like, “try Swaziland.” So I tried that.
When I press enter, watch what Excel does. It thinks for a split second, and then it says, nope. It’s Eswatini now, so you’re going to try things like that. Countries and [00:10:00] regions and districts and provinces, they change names. So give it a try.
And then number 10, you’re going to check for language translations. For example, um, Eswatini has two official languages. So I thought maybe the spelling is different of these regions in the two official languages, but that is the name. That is the one name of the region. There’s not like an English version or something, you know, so you’re trying all the different non US, non English things.
Then I got really frustrated and I thought, “this was supposed to take five minutes to make and record this YouTube video.” And here I am an hour into troubleshooting. I should be at dinner. I should be on the hotel treadmill, getting my 10, 000 steps in. Those are the things that I’m gonna do if everything works, any minute now.
And I said, “okay, come on, Ann, come on, brain, you can do it.”
And I went methodically down the list again, I kid you not.
So I started at the top again. I checked the internet. I [00:11:00] disconnected. I reconnected. Everything was fine. I can see my little wifi symbol at the moment is totally fine.
I tried converting to geography or not. I tried, uh, re, I tried like copying and pasting these in. To unregion it, uh, you can see that that doesn’t work. That makes it worse.
I made sure my headers are correctly labeled. I thought, I thought, and then I saw another help forum with somebody actually making a map of South Africa and they ran into the issue where,
oh, what was it? It was like they were supposed to plot provinces, but they used the word district. Or it was supposed to be district, and they used the word provinces. The naming was wrong, and the person like hadn’t realized. Or the data set they got was wrong, or something. So I thought, well, I think these are supposed to be regions.
The woman from Eswatini said they’re regions. The map [00:12:00] card says region. Online, it says there are four regions. The word region was very clear, but I was like, I don’t know, for kicks, let me try all the names, you know, so
I know they’re not states. These are not U. S. states, but I thought, let me try that.
Let me try province.
Let me try something more generic, like, what… I considered Guatemala has departments. Let me try district, because I was thinking of that South Africa help forum.
And earlier district had worked. And then I was like, I got it to work once with district, what’s going on? So this is where it gets wild.
And I was like, let me just start at the top of the list and I’ll just go back down again.
So I tried unconverting these to geography and finally, finally it worked.
I have no idea why. Okay, so to [00:13:00] recap. These are regions. Everybody says they’re regions. Everybody online says they’re regions. Microsoft on the little cards thought they were regions, but for the map to work, I can’t say region. It has to say district.
Typically maps have to typically have the convert to geography. It has to be like categorized as it’s, this isn’t a percentage. This isn’t a text field. It’s not a date. It’s geography. Not the case here. Now everything is correctly filled in. You hover over it. It knows what that region name is. The moral of this video is keep trying.
Just go from the top and let it back down. Try every little combination you possibly can. And then most importantly, please comment below the video. Let me know if you have troubleshooting tip 10 million that you’ve tried.
Let me know where you get stuck.
Let me know what works for you.
I can’t [00:14:00] wait to read through your comments because I know we’re all going to help each other so that we don’t get so frustrated. We can speed this up a little bit more.
Thank you so much in advance for your help in the comments.
Hey, future Ann here. I got my steps in, I had a steak, and now I’m no longer stressed. Funny how that works. And now I’m going to bed. Good night.
Leave a Reply