Continuing the process started in podcast 702, I will take a look at how to fill in all the blanks in the "outline view" of Column B. Episode 703 will show 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! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday, we started to take a look at this data set, that I eventually want to create a pivot table from but currently it's in the wrong format.
Yesterday, we separated the model information from Column B out to Column A.
Now, I need to fill in these blank cells basically, I want to take the word east and copy down to all the detail rows for east and then central and south and west, all the way through the data set.
Well, there's a very easy way to do this.
I'm going to select all of the blank cells and I'm going to use go to special.
Now, the fast way to get to that is to press the [ F5 ] key for go to, and then click [ special ].
If you're in Excel 2007, we can use find and select go to special.
If you're in Excel 2003, it's edit and then go to and go to special.
Either way, we're going to choose blanks and then click [ ok ] and that selects from within the original selection just the blank cells.
Now, we're three keystrokes away, the equal sign, the up arrow, and then [ ctrl enter ], will fill in all of those cells.
Now, of course at this point, I want to convert those formulas to values, but I have a non-contiguous range selected.
So, I can't copy and paste values here.
I need to re select the data.
So, move up one cell, [ ctrl shift down arrow ], [ ctrl C ] to copy and then paste.
Paste values, in order to convert those two values.
Well, now tomorrow.
We'll take a look at a few more steps to get rid of the blank columns and blank rows and then on Friday, take a look at how to solve these quarters going across, an amazing trick.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Yesterday, we started to take a look at this data set, that I eventually want to create a pivot table from but currently it's in the wrong format.
Yesterday, we separated the model information from Column B out to Column A.
Now, I need to fill in these blank cells basically, I want to take the word east and copy down to all the detail rows for east and then central and south and west, all the way through the data set.
Well, there's a very easy way to do this.
I'm going to select all of the blank cells and I'm going to use go to special.
Now, the fast way to get to that is to press the [ F5 ] key for go to, and then click [ special ].
If you're in Excel 2007, we can use find and select go to special.
If you're in Excel 2003, it's edit and then go to and go to special.
Either way, we're going to choose blanks and then click [ ok ] and that selects from within the original selection just the blank cells.
Now, we're three keystrokes away, the equal sign, the up arrow, and then [ ctrl enter ], will fill in all of those cells.
Now, of course at this point, I want to convert those formulas to values, but I have a non-contiguous range selected.
So, I can't copy and paste values here.
I need to re select the data.
So, move up one cell, [ ctrl shift down arrow ], [ ctrl C ] to copy and then paste.
Paste values, in order to convert those two values.
Well, now tomorrow.
We'll take a look at a few more steps to get rid of the blank columns and blank rows and then on Friday, take a look at how to solve these quarters going across, an amazing trick.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
