Can you draw a line in Excel Power Map? Well, it is not built-in, but you can use some interpolation in Excel to simulate a line.
Transcript of the video:
The MrExcel podcast is sponsored by eduPOW – home of the $5 Courses.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1921.
Draw a Line with Excel Power Map.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is about Excel Power Map.
How do we draw a line in Power Map?
Now, that's not built into Excel, but it is possible to simulate drawing a line.
So here we have a simple data set, with just two data points.
This data point over here, on the right hand side, is in Florida and it's actually the Cocoa Beach Pier.
The data point over here, on the left hand side, that's out in California, and that's the Santa Monica Pier.
So, today we want to do a little Pier-to-Pier work and draw a line, that illustrates the connection between those two points.
All right, well, so we're back in Excel.
Here is my big database of the two points: Longitude, Latitude, there's the Height, Category.
I’m going to use Category to make the line at different color, then the beginning and endpoint, and Dates I currently have, these are just arbitrary Dates in here.
The Delta between the Latitude, Longitude (=B3-B2, =C3-C2).
How many points do we want?
Well, to make a nice, smooth line, I'm going to use 3000 points, that's about one point every mile.
And so, that calculates the Delta per point.
All right, so let's copy and paste that data as values, and then I'm going to insert 3000 rows, so F5, let's select A3:A3002.
And then Alt, I, R to insert some rows, all right.
So here's what we're into: Point 1, the Longitude is going be the value just above me plus the Delta (=B2+O$2), press F4 there two times, to lock it down to just row 2, and then copy that across.
The Heights here is always going to be =5, Category is going to be “B”.
And then for the Dates: I want to take the date above me and add 1 (=F2+1), that way, as we activate this, it should go right across, let's get rid of all of these dates here and see what happens when we copy this down.
All right, really nice, and the Point 1 extended to Point 2, Point 3, Point 4, those formulas are working, because I put =5 there, that does not extend.
Category: B, they don't know how to fill B to C, to D, so that all works, and the dates look good.
Down here at the bottom, perfectly, yeah, so I knew I was going to use 3000 points, that's why we end there on 11/26, and the Height goes back up.
All right, so we're going to go back to Power Map and refresh and I'll get rid of the Tour editor, bring back the Layer Pane.
And we're going to say that the color is the Category, and then the Date field is the Time.
All right, and we're going to get rid of this area, and we should be able to come back here to the original data point, which gets us just the Cocoa Beach Pier.
And then when I play the Tour, line animates going across, and finally: the Santa Monica Pier.
So there you go, this Power Map do lines, well no, not built in, but using a little bit of interpolation, you can create your own line.
All right, there you have it, well, hey, thanks for stopping by, we'll see you next time for another netcast – MrExcel.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1921.
Draw a Line with Excel Power Map.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is about Excel Power Map.
How do we draw a line in Power Map?
Now, that's not built into Excel, but it is possible to simulate drawing a line.
So here we have a simple data set, with just two data points.
This data point over here, on the right hand side, is in Florida and it's actually the Cocoa Beach Pier.
The data point over here, on the left hand side, that's out in California, and that's the Santa Monica Pier.
So, today we want to do a little Pier-to-Pier work and draw a line, that illustrates the connection between those two points.
All right, well, so we're back in Excel.
Here is my big database of the two points: Longitude, Latitude, there's the Height, Category.
I’m going to use Category to make the line at different color, then the beginning and endpoint, and Dates I currently have, these are just arbitrary Dates in here.
The Delta between the Latitude, Longitude (=B3-B2, =C3-C2).
How many points do we want?
Well, to make a nice, smooth line, I'm going to use 3000 points, that's about one point every mile.
And so, that calculates the Delta per point.
All right, so let's copy and paste that data as values, and then I'm going to insert 3000 rows, so F5, let's select A3:A3002.
And then Alt, I, R to insert some rows, all right.
So here's what we're into: Point 1, the Longitude is going be the value just above me plus the Delta (=B2+O$2), press F4 there two times, to lock it down to just row 2, and then copy that across.
The Heights here is always going to be =5, Category is going to be “B”.
And then for the Dates: I want to take the date above me and add 1 (=F2+1), that way, as we activate this, it should go right across, let's get rid of all of these dates here and see what happens when we copy this down.
All right, really nice, and the Point 1 extended to Point 2, Point 3, Point 4, those formulas are working, because I put =5 there, that does not extend.
Category: B, they don't know how to fill B to C, to D, so that all works, and the dates look good.
Down here at the bottom, perfectly, yeah, so I knew I was going to use 3000 points, that's why we end there on 11/26, and the Height goes back up.
All right, so we're going to go back to Power Map and refresh and I'll get rid of the Tour editor, bring back the Layer Pane.
And we're going to say that the color is the Category, and then the Date field is the Time.
All right, and we're going to get rid of this area, and we should be able to come back here to the original data point, which gets us just the Cocoa Beach Pier.
And then when I play the Tour, line animates going across, and finally: the Santa Monica Pier.
So there you go, this Power Map do lines, well no, not built in, but using a little bit of interpolation, you can create your own line.
All right, there you have it, well, hey, thanks for stopping by, we'll see you next time for another netcast – MrExcel.
