MrExcel's Learn Excel #797 - Filling 1 without formatting

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 15, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top