Seiichi from Japan asks how to fill in blank cells in column A with the value above the blank. Episode 847 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, I think this is the first question we've ever had from Japan-- Si Chi sends in this question.
Data in Column B, the numbers in Column B, that's fine; but in Column A, they didn't fill in all of the data.
So, we have the letter A, and that needs to be copied down to all the blanks until we get to the B, and so on.
A great way to solve this problem, we're going to choose the first blank cell all the way down to the end of the data in Column A. I'll use Ctrl+G for go tol lower left hand corner, choose Special; and then go to Special.
I'm going to choose only the blank cells.
Alright, now, I'm just three keystrokes away-- press the Equal sign, press the Up Arrow, and then press Ctrl+Enter.
That enters that same formula in all of the cells in the selection, which, in this case, are all the blank cells.
So the A gets copied down until there's a B, and then those Bs copy down until there's the C, and so on.
Now, at this point, I want to copy and paste values, but I can't do that on a non-contiguous selection.
So I want to choose all my data again and then you can Edit, Copy; Edit, Paste Special Values.
I'm going to right-click on the right edge, drag right, drag left, let go, Copy Here As Values Only.
A great way to do Paste Values very, very quickly.
Filled in all of those blank cells throughout the data set.
So, thanks to Si Chi for sending in that question, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
[ music ]
Well, I think this is the first question we've ever had from Japan-- Si Chi sends in this question.
Data in Column B, the numbers in Column B, that's fine; but in Column A, they didn't fill in all of the data.
So, we have the letter A, and that needs to be copied down to all the blanks until we get to the B, and so on.
A great way to solve this problem, we're going to choose the first blank cell all the way down to the end of the data in Column A. I'll use Ctrl+G for go tol lower left hand corner, choose Special; and then go to Special.
I'm going to choose only the blank cells.
Alright, now, I'm just three keystrokes away-- press the Equal sign, press the Up Arrow, and then press Ctrl+Enter.
That enters that same formula in all of the cells in the selection, which, in this case, are all the blank cells.
So the A gets copied down until there's a B, and then those Bs copy down until there's the C, and so on.
Now, at this point, I want to copy and paste values, but I can't do that on a non-contiguous selection.
So I want to choose all my data again and then you can Edit, Copy; Edit, Paste Special Values.
I'm going to right-click on the right edge, drag right, drag left, let go, Copy Here As Values Only.
A great way to do Paste Values very, very quickly.
Filled in all of those blank cells throughout the data set.
So, thanks to Si Chi for sending in that question, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
[ music ]
