This tutorial was inspired by a foundation I work with.
We were brainstorming what type of map to make.
- Maybe a color-coded map, showing how many grants they gave out in each state?
- Maybe a one-color map, showing which states they worked in at all?
Both of these maps are possible in good ol’ Excel. Here’s how.
What’s Inside
- 0:00 Welcome
- 0:09 What You’ll Learn: 2 Types of Maps in Excel
- 0:49 1st Map: Setting Up the Table
- 1:05 The “Convert to Geography” Button
- 1:53 Inserting the Brand New Map
- 2:19 Recommended Edits for Color-Coded Maps
- 2:46 2nd Map: One-Color Map
- 3:14 Recommended Edits for One-Color Maps
- 4:01 The Finished Products
- 4:08 Get In Touch
Download the File
Download the Excel file here: https://depictdatastudio.gumroad.com/l/MapsInExcel
Transcript
[00:00:00] Hi, I’m Ann Emery. Welcome back to Dataviz On The Go, the series where I make quick tutorials for you in my spare moments between workshops.
And in this tutorial, we’re going to make this two different types of maps inside Excel. This is fake data, but inspired by a real project, inspired by a foundation that I work with, and we were brainstorming what type of map we wanted to make.
So I’m going to show you how to make maps in Excel. They’re new ish. A lot of people don’t even know that they exist. So I’m going to give you the gist of what you need to do behind the scenes to make this work. And I’m going to show you how to make a color coded map with a dark light contrast. And a one color map.
As you can see from the tables up above, we have to set up the tables a little bit differently to get these two different types of maps. All right, on to the demo. So here’s what we’re going to do. We’re going to set up our table and we’ll pretend that we’re working in these different states. And [00:01:00] which one should I do?
I don’t know. It doesn’t, it doesn’t matter. Well, let’s do like Washington state. We can do California. And then the key thing is after you type the third one, You’re gonna cross your fingers. You’re gonna hope that you see this magical button right here. Do you see it? I know it’s small on my screen. Can you see it?
It says convert to geography ding ding ding That’s the one we want you’re gonna click that button because without it the map might not work Might not work. Okay, so click that magic button. It’s gonna add these little map icons that are Again, the key, the magic that you need to avoid any issues. Okay.
And then we’re going to call this like number of grants funded, something like that, you know, whatever your number is, whatever your percentage is, whatever your currency is in real life. And let’s just put in some sample placeholder data here. Then you’re going to highlight your whole table. You’re going to go up to the insert tab and you’re going to grab [00:02:00] a filled map, AKA color coded map, heat map, choropleth map, uh, filled map is Excel’s lingo for this, where it gives you a dark light gradation, right?
It draws attention to the darker colors here. You have to do a little bit of formatting for this. You would probably want to. You know, make the font bigger, make the font darker. You get to choose where you put the legend. You can fill in your own brand colors here. You can decide if you want the states where you didn’t work to be gray, maybe you want them to be white, you get editing power over all that.
If you have questions about the edits, What’s possible, how to do it. Comment below this video. I will help you out. I’ll make a longer tutorial on this if you want. Okay. And then the other type of map would be if you just want to show states where you worked, you know, which is different. Like it’s like where we worked versus didn’t, you have to put in the same number for every state.
You can do a one, one [00:03:00] means, yeah, we worked there versus zero would mean. We didn’t. We didn’t work there. It’s binary. You’re turning it into a binary data set, therefore a binary map. Now on the binary maps, I do recommend a little bit of fine tuning here. Like you wouldn’t, why would you have a legend for the binary map?
That would be really weird. Why would you just have a one here? That’d be silly. And then by default, Excel is going to choose kind of a, a mid. Shade, this is my brand purple. My theme colors are filled in, of course. By default, it’s going to be kind of light, which I don’t think stands out enough against the gray.
So I’d recommend right clicking on any of these filled states. You go to the bottom of the menu, format data series. And then when you open up the series color toggle, you get to choose what fills in. I’m going to choose the main. Accent one color for the lowest and the highest because I just want it to be a one color map, right?
Check out that color contrast. Isn’t that a [00:04:00] little bit nicer your finished products again with a little bit more fine tuning and editing? Would look something like this. Give it a try. Let me know what types of issues do you run into? What types of maps are you curious about making? And then of course, for when you make these for your real data, get in touch.
I’d love to see how you adapt this for your workplace. 3, 2, 1 Don’t forget to like, subscribe, and share!
Leave a Reply