Yesterday Bill introduced us to the new GeoFlow Add-in, coming soon for Excel. Today in Episode #1654, Bill gives us an Introduction to how it works and how we can work with it!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1654: GeoFlow mechanics.
Well, hey yesterday I showed you that cool new GeoFlow add-in.
It's currently in beta for Excel 2013 Pro Plus but I didn't show you how we actually set things up so let's talk about that.
Here's my data set.
This is a smaller data set than the one I had yesterday because I don't want you to have to sit here and watch the whole thing get geocoded.
218 libraries, I have the address, city, state, zip.
I also have the population of the county.
Long story about this, I got this from the Azure DataMarket.
The date when the book is projected to hit the library and then I just put a random category here: A,B, and C. So first thing, GeoFlow uses data that's in PowerPivot.
To get this data in the PowerPivot, I want to make it into a table.
Ctrl + T and we'll give this a nice name like a Library.
All right, so now that we have the data in a table we can go to PowerPivot and say we want to add this to the data model.
Okay, now if we were using Power View, I would definitely say, you know, come back here to the Advanced tab and specify the geographic fields but GeoFlow doesn't require that right now so I'm not going to go through and do that.
We'll just return back to Excel and then on the Data tab, 3D map, Explore in 3D.
All right, the first thing we have to do is specify which fields are the geographic fields so I'm going to choose all of them because I don't have zip codes in a few places.
So City, State, and Zip.
See it's interesting the City, State, and Zip they get but for whatever reason I haven't figured out what word they want for Street Address so you always have to go in and fix this one.
I’ve tried Street, I've tried Address, that just doesn't seem to work and we click Map It and what happens now is they actually go out to Bing.
You can see down here in the lower left-hand corner.
They are retrieving all those 215 locations.
That was fast.
Now with five thousand records, definitely it takes a little bit more time.
So there we have our data points.
That's cool but we can do better things.
The chart type currently is a column chart and I want to be able to control the height.
So I'm going to use the county population as the height of each column.
There we go.
So LA pops right up and smaller places here in the middle-- Midwest are smaller.
What else do we have?
We have category.
Category will change the color of the various items so I'm going to take that category field, the A,B,C and put that here in categories.
Then we have three different colors that appear.
Now in this particular case, this-- you know, the categories were just a RAND of A, B, and C so they're not necessarily meaningful here.
All right, now one, I’m going to call this a bug and it's still there even in the later build.
If you accidentally hover over a column, it's going to show you all the information from that column.
Well, except it doesn't show you the name.
All right, so these keep popping up and I wish there was a way at this point to turn them off but there's not.
Now, at this point we can use the tools I showed you yesterday so I'm going to just drag to get Florida.
I'm going to Ctrl, wheel mouse in to zoom in and then re-center.
Use the Alt and drag up the tip so we can actually see the heights of the columns a little bit better.
If we wanted to create a tour, we could add a scene.
So we could add Florida to our scene and then we can zoom up or travel up and show the Northeast Corridor.
Maybe I will tip that one a little bit, rotate and then Add Scene and then fly over and show my home state of Ohio as well as Indiana and Chicago.
Add Scene.
Then we’ll cruise out and take a look at Microsoft home base in Seattle.
Here I might want to zoom in a little bit more so Ctrl, wheel mouse and you notice at this point that we're not really getting labels.
There's a theme that provides labels.
So I'll choose that theme and so now they'll actually label the map.
So Add Scene.
All right and then to play the whole tour, let's click Play.
So it goes to each spot on the tour, pauses there for a little bit and then moves on to the next spot on the tour.
See even the theme switched there when we zoomed in on Seattle.
All right, so pretty nice.
The one other piece that we have here, and I'm going to close the tour editor for right now, is the time component.
The time component allows us to see the points appear over a period of time and I do have-- this as a mailing that we did to libraries.
I do have the dates when I expect each thing to arrive.
So go back to-- no labels here and I'm going to take the Date field and drag that down to the Time component.
All right and they give us some options.
Do we want to persist the time data or just have it appear and disappear?
I always use Temporal Accumulation.
Hopefully in the later versions they're going to explain what these mean or put better words here.
I always have to kind of experiment to see how it looks.
All right, so what we're going to see when I click Play here, this is the United States Postal Service shipping from Akron, Ohio how long it takes to arrive at various places.
So we should see it kind of spread outwards as the days go along throughout the United States from an epicenter of right here.
We use Media Me to send these so, you know, it could take 10 to 14 days to get across the country out there to California.
So there you go.
An illustrated time animation of the data going into GeoFlow.
Great little tool.
I’m in love with this tool.
It's currently-- I have I think what is a second beta.
There's later bits and hopefully a public beta coming soon, a final release to people with Excel 2013 Pro Plus at the end of 2013 or early 2014.
All right. Hey, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1654: GeoFlow mechanics.
Well, hey yesterday I showed you that cool new GeoFlow add-in.
It's currently in beta for Excel 2013 Pro Plus but I didn't show you how we actually set things up so let's talk about that.
Here's my data set.
This is a smaller data set than the one I had yesterday because I don't want you to have to sit here and watch the whole thing get geocoded.
218 libraries, I have the address, city, state, zip.
I also have the population of the county.
Long story about this, I got this from the Azure DataMarket.
The date when the book is projected to hit the library and then I just put a random category here: A,B, and C. So first thing, GeoFlow uses data that's in PowerPivot.
To get this data in the PowerPivot, I want to make it into a table.
Ctrl + T and we'll give this a nice name like a Library.
All right, so now that we have the data in a table we can go to PowerPivot and say we want to add this to the data model.
Okay, now if we were using Power View, I would definitely say, you know, come back here to the Advanced tab and specify the geographic fields but GeoFlow doesn't require that right now so I'm not going to go through and do that.
We'll just return back to Excel and then on the Data tab, 3D map, Explore in 3D.
All right, the first thing we have to do is specify which fields are the geographic fields so I'm going to choose all of them because I don't have zip codes in a few places.
So City, State, and Zip.
See it's interesting the City, State, and Zip they get but for whatever reason I haven't figured out what word they want for Street Address so you always have to go in and fix this one.
I’ve tried Street, I've tried Address, that just doesn't seem to work and we click Map It and what happens now is they actually go out to Bing.
You can see down here in the lower left-hand corner.
They are retrieving all those 215 locations.
That was fast.
Now with five thousand records, definitely it takes a little bit more time.
So there we have our data points.
That's cool but we can do better things.
The chart type currently is a column chart and I want to be able to control the height.
So I'm going to use the county population as the height of each column.
There we go.
So LA pops right up and smaller places here in the middle-- Midwest are smaller.
What else do we have?
We have category.
Category will change the color of the various items so I'm going to take that category field, the A,B,C and put that here in categories.
Then we have three different colors that appear.
Now in this particular case, this-- you know, the categories were just a RAND of A, B, and C so they're not necessarily meaningful here.
All right, now one, I’m going to call this a bug and it's still there even in the later build.
If you accidentally hover over a column, it's going to show you all the information from that column.
Well, except it doesn't show you the name.
All right, so these keep popping up and I wish there was a way at this point to turn them off but there's not.
Now, at this point we can use the tools I showed you yesterday so I'm going to just drag to get Florida.
I'm going to Ctrl, wheel mouse in to zoom in and then re-center.
Use the Alt and drag up the tip so we can actually see the heights of the columns a little bit better.
If we wanted to create a tour, we could add a scene.
So we could add Florida to our scene and then we can zoom up or travel up and show the Northeast Corridor.
Maybe I will tip that one a little bit, rotate and then Add Scene and then fly over and show my home state of Ohio as well as Indiana and Chicago.
Add Scene.
Then we’ll cruise out and take a look at Microsoft home base in Seattle.
Here I might want to zoom in a little bit more so Ctrl, wheel mouse and you notice at this point that we're not really getting labels.
There's a theme that provides labels.
So I'll choose that theme and so now they'll actually label the map.
So Add Scene.
All right and then to play the whole tour, let's click Play.
So it goes to each spot on the tour, pauses there for a little bit and then moves on to the next spot on the tour.
See even the theme switched there when we zoomed in on Seattle.
All right, so pretty nice.
The one other piece that we have here, and I'm going to close the tour editor for right now, is the time component.
The time component allows us to see the points appear over a period of time and I do have-- this as a mailing that we did to libraries.
I do have the dates when I expect each thing to arrive.
So go back to-- no labels here and I'm going to take the Date field and drag that down to the Time component.
All right and they give us some options.
Do we want to persist the time data or just have it appear and disappear?
I always use Temporal Accumulation.
Hopefully in the later versions they're going to explain what these mean or put better words here.
I always have to kind of experiment to see how it looks.
All right, so what we're going to see when I click Play here, this is the United States Postal Service shipping from Akron, Ohio how long it takes to arrive at various places.
So we should see it kind of spread outwards as the days go along throughout the United States from an epicenter of right here.
We use Media Me to send these so, you know, it could take 10 to 14 days to get across the country out there to California.
So there you go.
An illustrated time animation of the data going into GeoFlow.
Great little tool.
I’m in love with this tool.
It's currently-- I have I think what is a second beta.
There's later bits and hopefully a public beta coming soon, a final release to people with Excel 2013 Pro Plus at the end of 2013 or early 2014.
All right. Hey, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
