Rodney is trying to label a Cartesian grid in Excel. This is a cool idea - Excel is really the world's largest sheet of graph paper, but the labels for the Cartesian grids aren't working well. In Episode 876, I explore the line tool on the drawing toolbar, the textbox tool, and the XY chart. Also a special deal on a book for your middle school students mentioned at the end of the podcast.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Interesting question sent in today by Rodney.
Rodney's trying to build some Cartesian grids in Excel and running into a very specific problem.
And it's interesting because there's a couple of different solutions for it.
So Rodney's setting up, basically, gridlines using Excel.
I'm just going to put the horizontal gridlines in here.
And then he wants to label those gridlines.
So, for example, maybe this line he wants to call 10; well, unfortunately, you know, if I put the 10 here it's going to appear above the line-- he wants it right on the line.
So Rodney's first attempt was, he said, "We're going to go into Format Cells and Merge those cells, and then use Vertical enter to get it to appear right on the line." Which is perfect, but you see, when you have a merged cell, the borders won't go through the cell-- you can't do that.
And Ronnie said, "Yeah, that's driving crazy.
There has to be a way to get that gridline to go through." Well, one solution that I had was, I go to View; Toolbars; and turn on the Drawing toolbar; and we're actually going to get a line.
Now, in order to draw this line perfectly, I'm going to hold down Shift-- that'll make sure that it's perfectly horizontal-- but I'm also going to hold down Alt because Alt will make sure that it starts at the edge of a cell and goes to the other edge of a cell.
So I draw a line in there and now I'm going to format that line-- so, Ctrl+1, and we can change to some sort of a dashe format, maybe a lighter weight, okay, and now maybe a heavier weight-- and now we end up with the line that goes right through the cell.
So one approach is just to get rid of the borders and use these lines; once you get the perfect line drawn in, you can Ctrl+drag and drag to new location.
Again, if we press down Alt, we'll make sure that it ends up right in the cell border.
So Ctrl+Alt and drag, and we can make copies of these straight down the line, and then we could get rid of the various cell borders.
So that's one solution.
So we'll go into Borders and do None, and you see the lines we drew in now appear.
Now, it'll be tedious to get it set up.
Of course, once you get it set up then you could very easily go through and copy and paste to create new Cartesian grids.
Now, I have another example here.
Another way to solve this problem is to use the text box-- the text box.
So here, I'm actually going to draw a text box in that runs two cells tall.
And initially the text box is just going to look horrible, but after I typed the number-- and I'm going to click on the diagonal lines that surround the text box and then Ctrl+1 to format-- a couple things we can do here, Alignment; Vertical, Center; Horizontal, Right; Colors and Lines I'm going to get rid of the line that's around-- so no line; I'm also going to get rid of the Fill so there's no Fill, so that way we can see the gridlines shining through; click OK.
And now you see that we have a text box.
When we click away from the text box, sure enough the number 10 is exactly where we want it to be.
A completely different approach though, is I set up a little data set here.
Some x and y data going from -10 to 10, and then build an XY chart.
So here's the two points of the XY chart-- you can't even notice them in the corners.
Now, I had to add the minor and major gridlines for both horizontal and vertical, and you know, set the scale up to have a minimum from -10 to 10 with increments of 0.5 for the the minor gridlines.
Man, you get a nice Cartesian grid there with the numbers in the right spot.
Might be even an easier way to go.
So there you have it, several different ways to solve this problem.
Now, hey, I want to point out a book that we have-- it's a book that most of you probably have never heard of, but if you happen to have a student in middle school, you'd be interested in this book.
It's called "Excel for The Math Classroom".
It was written by Bill Haslett and myself, and it's great for teachers who are teaching 6th, 7th, and 8th grade.
So if you want your kid to get some extra credit, this book would be a great way to go.
And if you're in the United States, I have a great deal for you: We have actually a case of books out in the garage that we were handing out to the local school district, and we have those extra.
So if you would like to give your kids teacher this book, just drop me a note: bill@mrexcel.com.
I'm going to ask you to send in 5 bucks to cover postage-- and this is for US only-- and I'll send you a free copy of that book which you can pass along to your students middle school math teacher.
So you can get some extra brownie points there and give the teacher some cool ideas and things like this Cartesian grid, and many other examples of things you could do in Excel.
So I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Interesting question sent in today by Rodney.
Rodney's trying to build some Cartesian grids in Excel and running into a very specific problem.
And it's interesting because there's a couple of different solutions for it.
So Rodney's setting up, basically, gridlines using Excel.
I'm just going to put the horizontal gridlines in here.
And then he wants to label those gridlines.
So, for example, maybe this line he wants to call 10; well, unfortunately, you know, if I put the 10 here it's going to appear above the line-- he wants it right on the line.
So Rodney's first attempt was, he said, "We're going to go into Format Cells and Merge those cells, and then use Vertical enter to get it to appear right on the line." Which is perfect, but you see, when you have a merged cell, the borders won't go through the cell-- you can't do that.
And Ronnie said, "Yeah, that's driving crazy.
There has to be a way to get that gridline to go through." Well, one solution that I had was, I go to View; Toolbars; and turn on the Drawing toolbar; and we're actually going to get a line.
Now, in order to draw this line perfectly, I'm going to hold down Shift-- that'll make sure that it's perfectly horizontal-- but I'm also going to hold down Alt because Alt will make sure that it starts at the edge of a cell and goes to the other edge of a cell.
So I draw a line in there and now I'm going to format that line-- so, Ctrl+1, and we can change to some sort of a dashe format, maybe a lighter weight, okay, and now maybe a heavier weight-- and now we end up with the line that goes right through the cell.
So one approach is just to get rid of the borders and use these lines; once you get the perfect line drawn in, you can Ctrl+drag and drag to new location.
Again, if we press down Alt, we'll make sure that it ends up right in the cell border.
So Ctrl+Alt and drag, and we can make copies of these straight down the line, and then we could get rid of the various cell borders.
So that's one solution.
So we'll go into Borders and do None, and you see the lines we drew in now appear.
Now, it'll be tedious to get it set up.
Of course, once you get it set up then you could very easily go through and copy and paste to create new Cartesian grids.
Now, I have another example here.
Another way to solve this problem is to use the text box-- the text box.
So here, I'm actually going to draw a text box in that runs two cells tall.
And initially the text box is just going to look horrible, but after I typed the number-- and I'm going to click on the diagonal lines that surround the text box and then Ctrl+1 to format-- a couple things we can do here, Alignment; Vertical, Center; Horizontal, Right; Colors and Lines I'm going to get rid of the line that's around-- so no line; I'm also going to get rid of the Fill so there's no Fill, so that way we can see the gridlines shining through; click OK.
And now you see that we have a text box.
When we click away from the text box, sure enough the number 10 is exactly where we want it to be.
A completely different approach though, is I set up a little data set here.
Some x and y data going from -10 to 10, and then build an XY chart.
So here's the two points of the XY chart-- you can't even notice them in the corners.
Now, I had to add the minor and major gridlines for both horizontal and vertical, and you know, set the scale up to have a minimum from -10 to 10 with increments of 0.5 for the the minor gridlines.
Man, you get a nice Cartesian grid there with the numbers in the right spot.
Might be even an easier way to go.
So there you have it, several different ways to solve this problem.
Now, hey, I want to point out a book that we have-- it's a book that most of you probably have never heard of, but if you happen to have a student in middle school, you'd be interested in this book.
It's called "Excel for The Math Classroom".
It was written by Bill Haslett and myself, and it's great for teachers who are teaching 6th, 7th, and 8th grade.
So if you want your kid to get some extra credit, this book would be a great way to go.
And if you're in the United States, I have a great deal for you: We have actually a case of books out in the garage that we were handing out to the local school district, and we have those extra.
So if you would like to give your kids teacher this book, just drop me a note: bill@mrexcel.com.
I'm going to ask you to send in 5 bucks to cover postage-- and this is for US only-- and I'll send you a free copy of that book which you can pass along to your students middle school math teacher.
So you can get some extra brownie points there and give the teacher some cool ideas and things like this Cartesian grid, and many other examples of things you could do in Excel.
So I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
