- [Narrator] All right, if you're using Google Sheets
and you have some location data in your spreadsheet,
and you want to see how that looks on a map,
we're going to go over something called geo charts,
how to use them, and some alternatives,
because they have some limitations.
All right, so let's get started.
So we have some example data right here,
and there's a few things to notice about this.
One is that it's only two columns,
so you have a column of data with the location,
and a column of data with one data point.
All right, and that's all you can have in these geo charts,
so that's what you want to start with.
And the other aspect is that these locations
are nice and clean.
So these are the exact names of these countries.
So we will select this entire table,
and we'll go to Insert, Chart.
So a geo chart is just another type of chart,
but Google Sheets doesn't guess at first
that that's what you want,
so it started with a pie chart.
Let me tuck my data in here a little bit,
so we're not overlapping.
All right, so let's change this pie chart.
We'll scroll down.
There's two selections under Map.
We'll take a look at them both,
but first we'll do geo chart.
And this did map the data.
It's not smart enough, though,
to start with the right region,
so it always starts with the entire world,
but I only used countries in Africa,
so the first thing that we're going to do
is we're going to go to Customize,
and then drop down the Geo selection,
and instead of world we'll pick Africa.
Now this is as zoomed in as you can get, all right,
so the smallest region in which we can map these countries
in Africa is the entire continent.
And in this case, we're doing maximum elevation,
so we want the largest numbers probably to appear red.
I don't know, it just makes more sense that way,
more extreme you want it red, right?
So we will go and edit this chart, Customize and Geo,
and let's change the minimum to green,
and the maximum to red.
All right, and then you can pick a mid color as well.
So we have red here and green,
so let's just sort of go in the middle of these two,
and pick a yellow.
And then when you look at these,
this is probably apparent that
the highest peak is in Rwanda, if you look closely,
followed by South Africa,
and apparently Senegal is pretty flat.
Who would have known?
So let's go back to Setup,
and talk about what's happening here.
So your data range here was automatically selected,
and that happened because I happened to be
in any active cell in this continuous range of data.
Okay, so Google Sheets was smart enough to figure that out.
I didn't have to change that.
The region, it is asking what range to pick up,
so in this case it's called Country,
that's why it's saying that there.
And then the color is based on the maximum elevation.
Now there were only two columns here to begin with,
so it picked those up relatively easily.
Next we'll move into a more complex example,
and we'll go to the worksheet called States.
And this doesn't look complex at first, right?
And if you had this data,
so this is just city, state, and the population,
I think at first, at least I would think
to highlight the entire amount and go to Insert Chart.
Let me move this over a little bit.
Bring the Editor back up,
and let's go back to geo chart,
but this time we're going to go to a geo chart with markers,
and we'll talk about that,
but the first thing that you'll notice
is a couple of things going on wrong here.
One is it wasn't able to figure out
that these were all in New York,
so it brought me to just the entire world again.
It's not that big of a deal.
We'll was just go back to Customize,
and it's not even letting me change the location.
It's because it's not reading these right,
so we had talked in the beginning
about how the location should be in one column,
so what we'll do here,
we're going to insert a column
to the left of the population,
and we'll call it City, State,
and we'll join two cells together,
so we want to join D8 and use the ampersand,
and then we want to put a comma and a space,
so I just type those in.
We'll close that off with a quote,
because that part was just a text string,
do the ampersand again,
and we're going to reference E8.
Hit Enter and then you have these new values here
that are just kind of a addition, if you will,
it added these two cells together,
but it knows that they're text,
so it just displayed Adams and then New York.
All right, and now Google Sheets is smart enough
where it says ah, I think you probably want
to extend this function all the way down
to the end of the data.
So I'm going to hit the check mark,
because that's a good guess, they're right.
And now I have one column for the location,
and one column for the data,
so let's try to map this.
Going to hit Delete on this geo chart,
and we will insert another chart.
Go down to the geo chart with markers.
And you can see it's working better.
It's still at the wrong zoom level,
but it's all in New York.
So let's hide these two columns.
Move this chart over just a little bit.
And then we'll go into the upper right-hand corner
and choose Edit Chart.
Now let's go to Customize, we'll go to geo,
and we'll say not the world, right?
We're going to go, North America's the next step down,
right after world,
but we want to be more specific and do United States.
All right, so we can't zoom any closer than that,
and we'll come in later and talk
about a possible solution to that,
but for now we're going to talk about
what the markers means.
So as you can see relatively easily,
the markers are sized according to the underlying numbers,
and when you hover over them, you get these little tags
so that's handy.
Adams, New York has a population of 5,000.
I didn't talk about what this data was,
so the second column is just population size.
All right, got that off the great Wikipedia.
We're gonna assume it's right for purposes
of what we're doing,
but these markers give us a little more visual information,
so if we were to switch this back
to the other type of geo chart,
it wouldn't tell us as much, right,
because the only region that it works with here is a state.
All those points are in the same state, so it's all green.
So in this case, the geo chart
with markers can be more specific.
So you can also show this as pins on a map,
and then when you hover over the pins,
and then you can zoom in to just New York,
but hold on to that thought,
we're going to do that in a minute.
Let's switch next to a larger dataset,
and I called the worksheet Large Dataset, very helpful.
And if I scroll down,
so this is just a lot of lines on purpose,
and they'd really clutter up the map,
so let's say that you want to still aggregate data here
and look at it by state, right,
but you have too many lines.
So what you can do in this case,
is you could just insert a pivot table.
So we'll go to a blank space on the worksheet,
and I'll link right now in the upper right-hand corner
to a primmer on pivot tables.
If you're not familiar with them,
you can watch that and learn how to use them.
They are super handy, but for this video,
we're just going to put one in
and assume you know how to use it.
So we'll go to Data, Pivot table,
and we're going to select our data range here
of the states with the associated data.
Going to put the pivot table on this sheet.
We'll go up and put it in G3, click Okay.
All right, so we're going to create this pivot table,
and back again to what we talked about before,
we want only two columns.
In this case, we want to see this by state,
and we want to see the amounts by state.
So first in this pivot table, let's add values,
and the values that we want is the Amount field.
All right, so that's adding all of the amounts together,
and now we want to aggregate those by state.
So the rows that we're going to put in,
we'll say Rows and State,
and I have to fix some data formatting there, apparently,
but this will break the amounts down by state.
Let me grab this format, use the format painter.
Don't know how that happened,
maybe I used this sheet for something else
and now its formatting applied there.
But those numbers were being displayed as dates,
when I really wanted them showing as numbers,
we've fixed that.
And now that you've aggregated this data, all right,
and you have it in two nice clean columns,
and you can put that on a map.
We'll throw it on a map real quick.
And then we'll talk about how to maybe put it
in a more useful map.
So for now we'll do Insert Chart.
We will choose just a regular map,
going to train it as to
what region we're talking about, United States.
And there's the data.
So you'll notice here that
the range really isn't very effective,
because everything, almost everything looks red,
and then one poor little green here.
So what's happening I think is that that green,
so we are picking up the sum of that table,
so that's way outside the bounds,
so let's just go back to the table
and clip the range to H50.
Get back out of there, there.
That makes it much better.
All right, so if you have data
that just neatly covers one region,
geo chart may do everything that you need,
but let's go back to this second worksheet,
and here we have data that just doesn't work very well
in this geo chart, right,
because you want to zoom further in.
So an alternative that we can do,
let me get out of full screen mode here,
and we're going to go to My Maps,
and this is where you can create your own map,
so let's left click on Create Map.
So Untitled map, so we will call this New York Locations.
Go Save, and My Maps has the option
to left click on Import, and look for recent,
and here's the geo data,
this is the spreadsheet we're working on,
but it only looks in the first worksheet,
so let's go back to where we were.
We were going to grab these states,
we're going to slide that over to the left,
and the other thing that we want to do
is we're going to take the city and state,
I'm going to paste them as values,
because they were there as a formula before,
so I want to paste them as values.
We'll get rid of the city and state,
and all of these empty columns.
And we're going to push it up
in the very upper left hand corner.
All right, so that should work well.
We'll go back to My Maps.
We'll go back to Import, pick the geo data, select it,
and then it sees the two columns.
Oh, so it's asking us what column has the position?
All right, so it's not smart enough to know
that it's just the one called City, State.
So we will check that one.
It could be more than one column in this case,
but we had already put it into just one.
We'll continue.
And then it's asking us for a column
that has the title for the placemarks.
So if you know you're going to be using My Maps,
you can plan on this for more than one column.
So you could come back here,
and you could add whatever you wanted
in these subsequent columns.
But for now we'll just name them
with the city and the state.
We'll click Finish.
And then it automatically brings in your data
from Google Sheets.
It puts it where it belongs on the map,
and zooms to the right region.
So this is very different from a geo chart.
It's not showing how your data relates to each other,
as far as the amounts go,
but if you hover over one of them,
left click on it,
and it will show you the amount right there.
So this is handy for seeing where things happen,
but if you want to see what,
you may want to stick with the geo chart.
You could come in here and you could change
the color of the pins and the icons.
And you can also add more data if you want,
or put pictures in here.
Right, so this is more just making a custom map,
and what we went over before
is really charting the amounts on the map.
All right, in this next video,
we're going to show you in Google Sheets
how to automate your work,
where you can record all the steps that you do,
if you're doing the same thing every day.
Next time go in there, press a button, it gets done for you.
I hope to see you in that video, and thanks for watching.