While yesterdays podcast talked about using the Fill Handle, there are times when there are methods faster than using the fill handle repeatedly. Episode 511 shows how to fill in a whole column of blank cells in one process.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Yesterday we had a question from Terry about using the fill handle to either copy data down or fill the series.
And you know I started to think about that. I wonder actually what Terry is doing with the data.
If he's you know individually basically, you know, filling in a bunch of blanks or something like that and then going to the next cell and filling in blanks.
And if that's the case there'd be a much faster way to handle this problem.
The one that a lot of people know about is using Ctrl+D to fill the active cell down to all of the cells in the selection. So for example, if I needed to fill this 1 down to all the blanks, I could select the range including the 1 and then hit Ctrl+D and it'll automatically copy that down. Select the next range Ctrl+D It will copy it down and so on.
While I'm talking about that, there's also Ctrl R. So for example, We need it to fill right. Select a value and several blank cells and then Ctrl R will fill right.
Those are both cool tricks but let's say we wanted to fill in the entire range and once we had 5,000 rows of data.
We'll need to fill in all the blanks with the value above it.
Easy way to do that.
We're gonna Select the entire range in the 1 2 3 4 5 and all the blank cells and then on the Edit menu Use "Go to".
Edit, Go to, within the Go to dialog box, We're gonna hit Special and choose Blanks.
This will select just the blank cells and now basically we want to enter a formula that points to the cell just above the current cell.
Fast way to do that is hit the = and the up arrow and then finally we want to enter that formula in all of the cells of the selection.
So we would use Ctrl enter and very quickly then we filled in all of those blanks with that value just above.
One of my favorite tricks in excel. That's Edit Go to Special Blanks and then you use = up arrow control+enter and we'll fill in that whole range.
Now lots of times people say Well I want to change those formulas to values. Strange thing.
We cannot paste values on a non-contiguous selection.
So you want to go back and re select the whole column, Edit Copy, Edit Paste Special Values or whatever method you use for pasting values.
My favorite method is to select the range.
Go to the right edge, right click drag right, drag left, let go and then choose "Copy here as values only" Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
Yesterday we had a question from Terry about using the fill handle to either copy data down or fill the series.
And you know I started to think about that. I wonder actually what Terry is doing with the data.
If he's you know individually basically, you know, filling in a bunch of blanks or something like that and then going to the next cell and filling in blanks.
And if that's the case there'd be a much faster way to handle this problem.
The one that a lot of people know about is using Ctrl+D to fill the active cell down to all of the cells in the selection. So for example, if I needed to fill this 1 down to all the blanks, I could select the range including the 1 and then hit Ctrl+D and it'll automatically copy that down. Select the next range Ctrl+D It will copy it down and so on.
While I'm talking about that, there's also Ctrl R. So for example, We need it to fill right. Select a value and several blank cells and then Ctrl R will fill right.
Those are both cool tricks but let's say we wanted to fill in the entire range and once we had 5,000 rows of data.
We'll need to fill in all the blanks with the value above it.
Easy way to do that.
We're gonna Select the entire range in the 1 2 3 4 5 and all the blank cells and then on the Edit menu Use "Go to".
Edit, Go to, within the Go to dialog box, We're gonna hit Special and choose Blanks.
This will select just the blank cells and now basically we want to enter a formula that points to the cell just above the current cell.
Fast way to do that is hit the = and the up arrow and then finally we want to enter that formula in all of the cells of the selection.
So we would use Ctrl enter and very quickly then we filled in all of those blanks with that value just above.
One of my favorite tricks in excel. That's Edit Go to Special Blanks and then you use = up arrow control+enter and we'll fill in that whole range.
Now lots of times people say Well I want to change those formulas to values. Strange thing.
We cannot paste values on a non-contiguous selection.
So you want to go back and re select the whole column, Edit Copy, Edit Paste Special Values or whatever method you use for pasting values.
My favorite method is to select the range.
Go to the right edge, right click drag right, drag left, let go and then choose "Copy here as values only" Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
