Back in Episode 795, I asked about the best way to fill 1, 2, 3 without copying the formatting. 3 different approaches to solve this problem will appear in Episode 797. Even if you don't have this issue, you will learn some surprising things about the fill handle's behavior.
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.
On Friday's podcast, at the end, I asked a question.
I kind of lamented the fact that if I held down the CONTROL key here to extend the 1 to 2, 3, 4, 5, 6, that I automatically get the borders, and when I choose FILL WITHOUT FORMATTING, Microsoft is not smart enough to keep it at fill series.
It switches back to 1, 1, 1, 1, 1, and I asked if anyone had any good solutions for this.
Well, you know, one solution of course is just to enter a formula here of =1+ whatever is above me and then copy that and basically PASTE SPECIAL, choosing FORMULAS, and that'll copy the numbers down, and some other ideas that were sent in.
Several people -- Neal, Rene, and Laura -- all said, well, just put in the 1 and the 2, and now that you've selected two cells and copy it down, it will automatically extend the series, and then you can choose FILL WITHOUT FORMATTING and it solves the problem.
Another interesting one, and this was from India, Vaibhav said, well, hey.
I edit the cell and I change it to put an ‘ before it, of course changing it to text.
Now when I drag down, it automatically goes to 2, 3, 4, 5, 6, and I can FILL WITHOUT FORMATTING.
Now, my one problem with this though is that it's left our data as text, and if you have to do VLOOKUPs or something later, that could be a problem.
So, you might have to do the extra step of DATA, TEXT TO COLUMNS, FINISH in order to convert those back to numbers.
But I think that the best solution came from the UK from Derek.
Now, Derek has written in before.
He said, look, what you're going to do is you're going to select 2 cells, not above each other but the one and the number next to it, and then when you drag the fill handle down, it will automatically go to 2, 3, 4, 5, 6, and then FILL WITHOUT FORMATTING.
You've solved the problem.
Now, okay.
Now, to be fair, in the original problem, I think I had data here in the column to the left.
So, in that case, you might have to temporarily insert a column, select the cells, drag down, and FILL WITHOUT FORMATTING, and then delete the extra column.
Any of these will seem to work.
I kind of like Derek’s solution of selecting the blank cell to the right which then interestingly forces Excel to take the 1 and extend it to 2, 3, 4, 5, 6 -- you know, even better than using the CONTROL key, which is my usual method.
So, lots of different ways to solve this problem.
I want to thank everyone for writing in and thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
On Friday's podcast, at the end, I asked a question.
I kind of lamented the fact that if I held down the CONTROL key here to extend the 1 to 2, 3, 4, 5, 6, that I automatically get the borders, and when I choose FILL WITHOUT FORMATTING, Microsoft is not smart enough to keep it at fill series.
It switches back to 1, 1, 1, 1, 1, and I asked if anyone had any good solutions for this.
Well, you know, one solution of course is just to enter a formula here of =1+ whatever is above me and then copy that and basically PASTE SPECIAL, choosing FORMULAS, and that'll copy the numbers down, and some other ideas that were sent in.
Several people -- Neal, Rene, and Laura -- all said, well, just put in the 1 and the 2, and now that you've selected two cells and copy it down, it will automatically extend the series, and then you can choose FILL WITHOUT FORMATTING and it solves the problem.
Another interesting one, and this was from India, Vaibhav said, well, hey.
I edit the cell and I change it to put an ‘ before it, of course changing it to text.
Now when I drag down, it automatically goes to 2, 3, 4, 5, 6, and I can FILL WITHOUT FORMATTING.
Now, my one problem with this though is that it's left our data as text, and if you have to do VLOOKUPs or something later, that could be a problem.
So, you might have to do the extra step of DATA, TEXT TO COLUMNS, FINISH in order to convert those back to numbers.
But I think that the best solution came from the UK from Derek.
Now, Derek has written in before.
He said, look, what you're going to do is you're going to select 2 cells, not above each other but the one and the number next to it, and then when you drag the fill handle down, it will automatically go to 2, 3, 4, 5, 6, and then FILL WITHOUT FORMATTING.
You've solved the problem.
Now, okay.
Now, to be fair, in the original problem, I think I had data here in the column to the left.
So, in that case, you might have to temporarily insert a column, select the cells, drag down, and FILL WITHOUT FORMATTING, and then delete the extra column.
Any of these will seem to work.
I kind of like Derek’s solution of selecting the blank cell to the right which then interestingly forces Excel to take the 1 and extend it to 2, 3, 4, 5, 6 -- you know, even better than using the CONTROL key, which is my usual method.
So, lots of different ways to solve this problem.
I want to thank everyone for writing in and thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
